M10-06-4-StageSpreadsheet

Model10-06A4-StageSpreadsheetDCFValuation,GeneralTerminalValueFormatInputRelationsRevenueYear01,000$Stage1Stage2Stage3Stage41-56-1516-2021-¥RevenuesGrowthRate(g)20.0%33.0%==3.0%3.0%OperatingMargin(%Rev)5.0%20.0%15.0%10.0%InterestExpense(%Rev)2%2%2%2%Depreciation(%Rev)5%5%5%4%InvestmentRate(%Rev)15%15%8%6%TaxRate(%EBIT)15%20%40%40%CostofCapital,(k)or(WACC)15%15%15%15%($inmillions)1234567891011121314151617181920ornIncrementalFreeCashFlow:1.RevenuesGrowthRate20.0%20.0%20.0%20.0%20.0%33.0%33.0%33.0%33.0%33.0%33.0%33.0%33.0%33.0%33.0%27.0%21.0%15.0%9.0%3.0%2.Revenues1,200$1,440$1,728$2,074$2,488$3,309$4,402$5,854$7,786$10,355$13,773$18,318$24,362$32,402$43,095$54,730$66,223$76,157$83,011$85,501$3.–CostofGoodsSold,SG&A1,0801,2961,5551,8662,2392,4823,3014,3915,8397,76710,32913,73818,27224,30132,32143,78452,97960,92666,40968,4014.–Depreciation6072861041241652202933895186899161,2181,6202,1552,7373,3113,8084,1514,2755.Pretaxoperatingincome(NOI=EBIT)6072861041246628801,1711,5572,0712,7553,6644,8726,4808,6198,2109,93411,42412,45212,8256.–InterestExpense242935415066881171562072753664876488621,0951,3241,5231,6601,7107.Incomebeforetaxes(EBT)36435262755967921,0541,4011,8642,4793,2974,3855,8327,7577,1158,6099,90010,79111,1158.–IncomeTaxes5689111191582112803734966598771,1661,5512,8463,4443,9604,3174,4469.Netincome(NI)31374453634776348431,1211,4911,9832,6383,5084,6666,2064,2695,1655,9406,4756,66910.+TaxShield:(1-T)x(6)20242935425370941251662202933905186906577959149961,02611.NOPAT(9+10)516173881065307049371,2461,6572,2042,9313,8985,1846,8954,9265,9606,8547,4717,69512.+Depreciation6072861041241652202933895186899161,2181,6202,1552,7373,3113,8084,1514,27513.–Investment1802162593113734966608781,1681,5532,0662,7483,6544,8606,4644,3785,2986,0936,6416,84014.FreeCashFlow(FCF)(69)$(83)$(99)$(119)$(143)$199$264$351$467$621$826$1,099$1,462$1,944$2,586$3,284$3,973$4,569$4,981$5,130$15.DiscountFactor(DF)*0.8700.7560.6580.5720.4970.4320.3760.3270.2840.2470.2150.1870.1630.1410.1230.1070.0930.0810.0700.06116.DiscountedFCF(60)$(63)$(65)$(68)$(71)$86$99$115$133$154$178$205$238$275$318$351$369$369$350$313$17.TotalPresentValueofFCF:3,225$Year21orn+118.TerminalValue(TV)=FCFn+1/(ksatge4-gstage4)=3523/(0.15-0.03)=29,355$1.RevenuesGrowthRate3.0%19.PVofFCFStage1(327)$2.Revenues88,066$20.PVofFCFStage21,7993.–CostofGoodsSold,SG&A75,73721.PVofFCFStage31,7534.–Depreciation3,52322.PresentValueofTV(PVTV)=TVxDFn=29355x0.061=1,7945.Pretaxoperatingincome(NOI=EBIT)8,80723.OperatingValue05,019$6.–InterestExpense1,76124.Plus:MarketableSecurities-7.Incomebeforetaxes(EBT)7,04525.Less:TotalInterestBearingDebt-8.–IncomeTaxes2,81826.EquityValue5,019$9.Netincome(NI)4,22727.NumberofShares1,00010.+TaxShield:(1-T)x(6)1,05728.PriceperShare5.0211.NOPAT(9+10)5,28412.+Depreciation3,523Ratios13.–Investment5,284t=Year1t=Year614.FreeCashFlow(FCF)3,523$29.OperatingValue0/Revenuet4.181.5230.OperatingValue0/NOIt83.647.5831.OperatingValue0/EBITDAt41.826.0732.OperatingValue0/NIt164.0110.53*DiscountFactor=DFt=1/(1+k1)x1/(1+k2)x1/(1+k3)x…x1/(1+kt)andkt=costofcapitalforyeartM-26Model10-06B4-StageSpreadsheetDCFValuation,TerminalValueFCFGrowingatgstage4InputRelationsRevenueYear01,000$Stage1Stage2Stage3Stage41-56-1516-2021-¥RevenuesGrowthRate(g)20.0%33.0%==3.0%3.0%OperatingMargin(%Rev)5.0%20.0%15.0%InterestExpense(%Rev)2%2%2%Depreciation(%Rev)5%5%5%InvestmentRate(%Rev)15%15%8%TaxRate(%EBIT)15%20%40%CostofCapital,(k)or(WACC)15%15%15%15%($inmillions)1234567891011121314151617181920ornIncrementalFreeCashFlow:1.RevenuesGrowthRate20.0%20.0%20.0%20.0%20.0%33.0%33.0%33.0%33.0%33.0%33.0%33.0%33.0%33.0%33.0%27.0%21.0%15.0%9.0%3.0%2.Revenues1,200$1,440$1,728$2,074$2,488$3,309$4,402$5,854$7,786$10,355$13,773$18,318$24,362$32,402$43,095$54,730$66,223$76,157$83,011$85,501$3.–CostofGoodsSold,SG&A1,0801,2961,5551,8662,2392,4823,3014,3915,8397,76710,32913,73818,27224,30132,32143,78452,97960,92666,40968,4014.–Depreciation6072861041241652202933895186899161,2181,6202,1552,7373,3113,8084,1514,2755.Pretaxoperatingincome(NOI=EBIT)6072861041246628801,1711,5572,0712,7553,6644,8726,4808,6198,2109,93411,42412,45212,8256.–InterestExpense242935415066881171562072753664876488621,0951,3241,5231,6601,7107.Incomebeforetaxes(EBT)36435262755967921,0541,4011,8642,4793,2974,3855,8327,7577,1158,6099,90010,79111,1158.–IncomeTaxes5689111191582112803734966598771,1661,5512,8463,4443,9604,3174,4469.Netincome(NI)31374453634776348431,1211,4911,9832,6383,5084,6666,2064,2695,1655,9406,4756,66910.+TaxShield:(1-T)x(6)20242935425370941251662202933905186906577959149961,02611.NOPAT(9+10)516173881065307049371,2461,6572,2042,9313,8985,1846,8954,9265,9606,8547,4717,69512.+Depreciation6072861041241652202933895186899161,2181,6202,1552,7373,3113,8084,1514,27513.–Investment1802162593113734966608781,1681,5532,0662,7483,6544,8606,4644,3785,2986,0936,6416,84014.FreeCashFlow(FCF)(69)$(83)$(99)$(119)$(143)$199$264$351$467$621$826$1,099$1,462$1,944$2,586$3,284$3,973$4,569$4,981$5,130$15.DiscountFactor(DF)*0.8700.7560.6580.5720.4970.4320.3760.3270.2840.