Scribd Logo
Upload

Welcome to Scribd!

  • 105 views

    Sensitivity Analysis in Excel Template

    Uploaded by

    uyenbp.a2.1720
    This document provides a sensitivity analysis of a discounted cash flow valuation of Alibaba conducted by Dheeraj Vaidya. It includes projections of Alibaba's free cash flows from 2010 to 2021 and calculates the net present value of the explicit forecast period and terminal period. It then conducts a sensitivity analysis by varying the weighted average cost of capital from 7% to 11% to see the impact on valuation. The adjusted equity value of Alibaba is calculated as $191 billion based on the base case assumptions.

    Copyright:

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

    Sensitivity Analysis in Excel Template

    Uploaded by

    uyenbp.a2.1720
    105 views8 pages
    This document provides a sensitivity analysis of a discounted cash flow valuation of Alibaba conducted by Dheeraj Vaidya. It includes projections of Alibaba's free cash flows from 2010 to 2021 and calculates the net present value of the explicit forecast period and terminal period. It then conducts a sensitivity analysis by varying the weighted average cost of capital from 7% to 11% to see the impact on valuation. The adjusted equity value of Alibaba is calculated as $191 billion based on the base case assumptions.

    Original Title

    Sensitivity Analysis in Excel Template.xls

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides a sensitivity analysis of a discounted cash flow valuation of Alibaba conducted by Dheeraj Vaidya. It includes projections of Alibaba's free cash flows from 2010 to 2021 and calculates the net present value of the explicit forecast period and terminal period. It then conducts a sensitivity analysis by varying the weighted average cost of capital from 7% to 11% to see the impact on valuation. The adjusted equity value of Alibaba is calculated as $191 billion based on the base case assumptions.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    105 views8 pages

    Sensitivity Analysis in Excel Template

    Uploaded by

    uyenbp.a2.1720
    This document provides a sensitivity analysis of a discounted cash flow valuation of Alibaba conducted by Dheeraj Vaidya. It includes projections of Alibaba's free cash flows from 2010 to 2021 and calculates the net present value of the explicit forecast period and terminal period. It then conducts a sensitivity analysis by varying the weighted average cost of capital from 7% to 11% to see the impact on valuation. The adjusted equity value of Alibaba is calculated as $191 billion based on the base case assumptions.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    You are on page 1of 8

    Sensitivity Analysis in Excel Template

    Prepared by Dheeraj Vaidya, CFA, FRM


    [email protected]

    visit - www.wallstreetmojo.com
    Sensitivity Analysis in Excel
    Alibaba IPO
    DCF Valuations

    Free Cash Flow Projections Historicals


    (RMB in Million Except Per Share Amounts) Mar-10 Mar-11

    EBIT (873) 1,322


    Free Cash Flow to Firm
    EBIT x (1-t) (1,420) 1,099
    Add: Depreciation & amortization
    Less: Capex
    Add: Change in working capital
    FCFF

    Free cash flow to firm of explicit period (2015E to 2019E)


    Mar-10 Mar-11
    Cash Flows
    NPV of explicit period 321,953

    Terminal value @ perpetual growth (2019) Mar-10 Mar-11


    Terminal value as of 2019
    NPV of terminal value 812,148

    DCF Valuation Summary

    DCF calculation as of Valuation (RMB) Valuation (US$)


    NPV of explicit period 321,953 51,791
    NPV of terminal value 812,148 130,646
    Enterprise Value 1,134,100 182,437
    + Cash $48,550 7,810
    - Debt ($40,310) (6,484)
    Equity value 1,142,340 183,851
    + Short term investments, restricted cash, investment securit 12,587 2,025
    +Investment in equity investees 13,009 2,093
    + Alipay valuation 24,866 4,000
    Stock Option Value $3,207 516
    Adjusted Equity Value 1,189,595 191,452

    Share Price 486.21 78.25

    Sensitivity Analysis
    191.45
    1%
    2%

    growth
    3%
    4%
    5%
    6.0%
    Historicals Forecasts
    Mar-12 Mar-13 Mar-14 Mar-15 Mar-16

    5,015 10,751 26,514 33,072 41,871

    4,252 9,202 24,575 29,103 36,009


    715 805 947 2,691 5,069
    (749) (1,046) (3,010) (9,225) (11,679)
    4,285 2,260 3,884 4,861
    13,246 24,772 26,453 34,261

    Mar-12 Mar-13 Mar-14 Mar-15 Mar-16


    26,453 34,261

    Mar-12 Mar-13 Mar-14 Mar-15 Mar-16


    0 0

    Cost of Capital 9.00%


    Infinite growth rate 3.00%
    WACC
    7% 8% 9% 10% 11%
    Forecasts
    Mar-17 Mar-18 Mar-19 Mar-20 Mar-21

    53,260 68,042 86,110 99,367 109,558

    45,271 57,155 70,610 79,493 85,455


    8,089 11,938 16,831 22,717 29,348
    (14,855) (18,979) (24,018) (27,716) (30,558)
    6,292 8,167 9,982 7,324 5,630
    44,795 58,282 73,405 81,819 89,875

    Mar-17 Mar-18 Mar-19 Mar-20 Mar-21


    44,795 58,282 73,405 81,819 89,875

    Mar-17 Mar-18 Mar-19 Mar-20 Mar-21


    - - - - -
    Mar-22

    115,580

    86,685
    36,493
    (32,238)
    3,327
    94,267

    Mar-22
    94,267

    Mar-22
    1,618,255

    You might also like