您好,欢迎访问三七文档
当前位置:首页 > 财经/贸易 > 资产评估/会计 > M10-02-GeneralDCFSpreadsheetValuation
Model10-02AGeneral10-YearDCFSpreadsheetValuationModel(DollarAmountsinMillionsExceptperShare)Year0Year1Year2Year3Year4Year5Year6Year7Year8Year9Year10Yearn+1PanelA–InputsforPresentValueCalculations1.Netrevenues################################################################################################2.Revenuegrowthrate4.0%4.0%4.0%4.0%4.0%4.0%4.0%4.0%4.0%4.0%3.0%3.NOI17,680$18,387$19,123$19,888$20,683$21,510$22,371$23,266$24,196$25,164$18,296$4.Cashtaxrate38.0%38.0%38.0%38.0%38.0%38.0%38.0%38.0%38.0%38.0%38.0%5.Incometaxes6,7186,9877,2677,5577,8608,1748,5018,8419,1959,5626,9526.NOPAT10,962$11,400$11,856$12,330$12,824$13,336$13,870$14,425$15,002$15,602$11,343$7.+Depreciation4,1604,3264,4994,6794,8675,0615,2645,4745,6935,9216,0998.–Changeinworkingcapital1,5601,6221,6871,7551,8251,8981,9742,0532,1352,2202,2879.–Capitalexpenditures4,6804,8675,0625,2645,4755,6945,9226,1596,4056,6613,81210.–Changeinotherassetsnet1,3001,3521,4061,4621,5211,5821,6451,7111,7791,85015211.Freecashflows7,582$7,885$8,200$8,528$8,869$9,224$9,593$9,977$10,376$10,791$11,191$12.WACC9.18%9.18%9.18%9.18%9.18%9.18%9.18%9.18%9.18%9.18%9.18%13.Discountfactor0.915920.838910.768370.703760.644590.590390.540750.495290.453640.4155014.Presentvalues6,944$6,615$6,301$6,002$5,717$5,446$5,188$4,941$4,707$4,484$PanelB–OperatingRelationships(Asa%ofRevenues)NOI17.0%17.0%17.0%17.0%17.0%17.0%17.0%17.0%17.0%17.0%12.0%NOPAT10.5%10.5%10.5%10.5%10.5%10.5%10.5%10.5%10.5%10.5%7.4%Depreciation4.0%4.0%4.0%4.0%4.0%4.0%4.0%4.0%4.0%4.0%4.0%Changeinworkingcapital1.5%1.5%1.5%1.5%1.5%1.5%1.5%1.5%1.5%1.5%1.5%Capitalexpenditures4.5%4.5%4.5%4.5%4.5%4.5%4.5%4.5%4.5%4.5%2.5%Changeinotherassetsnet1.25%1.25%1.25%1.25%1.25%1.25%1.25%1.25%1.25%1.25%0.1%Freecashflow7.3%7.3%7.3%7.3%7.3%7.3%7.3%7.3%7.3%7.3%7.3%PanelC–ValuationCalculationsPartI-CostofCapitalInputsPartII-TerminalValue(TV)PartIII-ValuationCalculation(a)Risk-freerate5.60%TV=FreeCashFlowsn+1/(WACC-g)(1)PVofcashflows,Year1-1056,344$(b)Beta0.80=11191/(0.0918-0.030)(2)PVofterminalvalue75,240$(c)Equityriskpremium7.00%=$181,083(3)Marketablesecurities10,000(d)Taxrate38.0%(4)Totalvalueofthefirm########(e)Costofequity11.20%(5)Valueofdebt20,000(f)Costofdebt(before-tax)7.20%(6)Valueofequity########(g)Costofdebt(after-tax)4.46%(7)Sharesoutstanding3,500(h)Capitalstructure,%equity70.00%(8)Intrinsicshareprice34.74$(i)BaseWACC9.18%Model10-02BGeneral5-YearDCFSpreadsheetValuationModel(DollarAmountsinMillionsExceptperShare)Year0Year1Year2Year3Year4Year5Yearn+1PanelA–InputsforPresentValueCalculations1.Netrevenues########################################################2.Revenuegrowthrate4.0%4.0%4.0%4.0%4.0%3.0%3.NOI17,680$18,387$19,123$19,888$20,683$15,038$4.Cashtaxrate38.0%38.0%38.0%38.0%38.0%38.0%5.Incometaxes6,7186,9877,2677,5577,8605,7146.NOPAT10,962$11,400$11,856$12,330$12,824$9,323$7.+Depreciation4,1604,3264,4994,6794,8675,0138.–Changeinworkingcapital1,5601,6221,6871,7551,8251,8809.–Capitalexpenditures4,6804,8675,0625,2645,4753,13310.–Changeinotherassetsnet1,3001,3521,4061,4621,52112511.Freecashflows7,582$7,885$8,200$8,528$8,869$9,198$12.WACC9.18%9.18%9.18%9.18%9.18%9.18%13.Discountfactor0.915920.838910.768370.703760.6445914.Presentvalues6,944$6,615$6,301$6,002$5,717$PanelB–OperatingRelationships(Asa%ofRevenues)NOI17.0%17.0%17.0%17.0%17.0%12.0%NOPAT10.5%10.5%10.5%10.5%10.5%7.4%Depreciation4.0%4.0%4.0%4.0%4.0%4.0%Changeinworkingcapital1.5%1.5%1.5%1.5%1.5%1.5%Capitalexpenditures4.5%4.5%4.5%4.5%4.5%2.5%Changeinotherassetsnet1.25%1.25%1.25%1.25%1.25%0.1%Freecashflow7.3%7.3%7.3%7.3%7.3%7.3%PanelC–ValuationCalculationsPartI-CostofCapitalInputsPartII-TerminalValue(TV)(a)Risk-freerate5.60%TV=FreeCashFlowsn+1/(WACC-g)(b)Beta0.80=9198/(0.0918-0.030)(c)Equityriskpremium7.00%=$148,837(d)Taxrate38.0%(e)Costofequity11.20%PartIII-ValuationCalculation(f)Costofdebt(before-tax)7.20%(1)PVofcashflows,Year1-531,579$(g)Costofdebt(after-tax)4.46%(2)PVofterminalvalue95,939$(h)Capitalstructure,%equity70.00%(3)Marketablesecurities10,000(i)BaseWACC9.18%(4)Totalvalueofthefirm########(5)Valueofdebt20,000(6)Valueofequity########(7)Sharesoutstanding3,500(8)Intrinsicshareprice33.58$
本文标题:M10-02-GeneralDCFSpreadsheetValuation
链接地址:https://www.777doc.com/doc-6842879 .html