Scribd Logo
Upload

Welcome to Scribd!

  • 8 views

    Data Visualization

    Uploaded by

    gautam

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Data Visualization

    Uploaded by

    gautam
    8 views11 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    8 views11 pages

    Data Visualization

    Uploaded by

    gautam

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 11

    Financial modelling

    Data Visualization
    Data Visualization using Charts

    ©2018 Grant Thornton India LLP. All rights reserved.


    Presenting Historical & Projected Data together
    • Present the historical sales and projected sales under various scenarios together

    Figures in USD'000

    Forecast
    Year Actual Sales Best Case Moderate Case Worst Case
    2015 156
    2016 120
    2017 186
    2018 198
    2019 234
    2020 267 205 154
    2021 280 215 161
    2022 319 245 184
    2023 358 275 206
    2024 449 345 259

    ©2018 Grant Thornton India LLP. All rights reserved.


    Presenting Historical & Projected Data together

    Sales (USD'000)
    500
    450
    400
    350
    300
    250
    200
    150
    100
    50
    -
    2015 2016 2017 2018 2019 2020 2021 2022 2023 2024

    Actual Sales Best Case Moderate Case Worst Case

    ©2018 Grant Thornton India LLP. All rights reserved.


    Profit & Loss Bridge

    Creating P&L bridge


    Amt in USD'000
    Revenue 200
    COGS (40)
    G&A (20)
    Depn. (35)
    Interest (15)
    Taxes (10)
    Net Profit 80

    ©2018 Grant Thornton India LLP. All rights reserved.


    Variance Analysis
    • Walk between Budgeted and Actual Revenue

    Variance Analysis
    Amt in USD'000
    Bud. Rev 100
    Price var. (20)
    Vol. var. 5
    Bad debts (12)
    Others 2
    Actual Rev 75

    ©2018 Grant Thornton India LLP. All rights reserved.


    Dynamic Chart with Option Buttons
    • A company sells its products locally and also exports them. Draw up a chart to visualize data through
    an Option button form control to show sales of either domestic market or export market at a time

    Figures in USD'000
    Domestic Sales Export Sales
    Product Domestic Sales Export Sales
    Apple Watch 285 45
    iPad 391 53 Domestic Sales
    iPhone 640 78 800
    iPod 581 101
    600
    Macbook 136 25
    400

    200

    -
    Apple Watch iPad iPhone iPod Macbook

    ©2018 Grant Thornton India LLP. All rights reserved.


    Dynamic Chart with Combo Box
    • A company has prepared sales projections data at product level. Draw up a chart to show sales
    projections for a selected product in combo box

    Sales $ mn 2020 2021 2022 2023 2024


    Product A 110 122 50 138 192 Product D
    Product B 170 198 32 86 66
    200
    Product C 156 80 124 122 162
    Product D 98 148 116 182 122 150
    Product E 118 120 22 52 160
    100
    Total 652 668 344 580 702
    50

    0
    2014 2015 2016 2017 2018

    ©2018 Grant Thornton India LLP. All rights reserved.


    Dynamic Chart with Scroll Bar
    • Rai Pvt Ltd started its business in 1980 and has successfully grown its business. The company wants
    to visually represent the sales data since inception year till the year 2019 in a dynamic way showing
    only 6 years at a time

    Year Sales $ mn
    Sales $ mn
    1980 96
    1,150
    1981 146
    1,100
    1982 206
    1,050
    1983 208 1,000
    … … 950
    … … 900
    … … 850
    2018 1,232 2010 2011 2012 2013 2014 2015

    2019 1,400

    ©2018 Grant Thornton India LLP. All rights reserved.


    Dynamic Chart using OFFSET function
    • In the previous case question, the sales data was shown for a fixed period of 6 years, while the
    company wants to specify the duration for which the data should be presented

    The company shall specify –


    1. Starting year no. Sales $ mn
    2. Total no. of years for which data is required 900
    to be shown (Note - basis the total no. of 800
    years, the chart should update dynamically) 700
    600
    Enter Starting Year no. 2000 500
    400
    Total no. of years to show on chart 5
    300
    200
    Trick: 100
    • Use Excel’s OFFSET function -
    • Store the above function as a Name and call 2000 2001 2002 2003 2004

    that in chart data points

    ©2018 Grant Thornton India LLP. All rights reserved.


    Questions

    You might also like