M16-04-LBOSpreadsheetValuation

Model16-04LBOValuationYear0Year1Year2Year3Year4Year5Year6Year7Year8Year9Year10Yearn+1PanelA–InputsforPresentValueCalculations1.Netrevenues10,000$10,670$11,385$12,148$12,949$13,804$14,701$15,657$16,596$17,592$18,648$19,207$2.Revenuegrowthrate6.7%6.7%6.7%6.6%6.6%6.5%6.5%6.0%6.0%6.0%3.0%3.NOI=EBIT1,814$2,049$2,308$2,590$2,761$2,940$3,131$3,319$3,518$3,730$2,881$4.Cashtaxrate(T)34.0%34.0%34.0%34.0%34.0%34.0%34.0%34.0%34.0%34.0%34.0%5.Incometaxes6176977858819391,0001,0651,1291,1961,2689806.NOPAT1,197$1,353$1,523$1,709$1,822$1,941$2,067$2,191$2,322$2,461$1,902$7.+Depreciation7476837297127598097838307045593848.–Changeinworkingcapital53576165697478838893969.–Capitalexpenditures51245536438827629415716688939610.–Changeinotherassetsnet(1,003)(1,002)---------11.Freecashflows2,382$2,525$1,827$1,968$2,236$2,382$2,615$2,772$2,850$2,834$2,094$12.Interestexpense2,754$2,341$1,997$1,888$1,321$1,088$806$487$21$-$590$13.Interesttaxshield936$796$679$642$449$370$274$166$7$-$201$14.Capitalcashflow(CCF)(11+13)3,318$3,321$2,506$2,610$2,685$2,752$2,889$2,937$2,857$2,834$2,294$15.Discountrate(ka)14.70%14.70%14.70%14.70%14.70%14.70%14.70%14.70%14.70%14.70%14.70%16.Discountfactor0.871840.760100.662690.577760.503710.439160.382870.333800.291020.2537317.Presentvalues2,893$2,524$1,661$1,508$1,353$1,208$1,106$980$831$719$PanelB–OperatingRelationships(Asa%ofRevenues)NOI17.0%18.0%19.0%20.0%20.0%20.0%20.0%20.0%20.0%20.0%15.0%NOPAT11.2%11.9%12.5%13.2%13.2%13.2%13.2%13.2%13.2%13.2%9.9%Depreciation7.0%6.0%6.0%5.5%5.5%5.5%5.0%5.0%4.0%3.0%2.0%Changeinworkingcapital0.5%0.5%0.5%0.5%0.5%0.5%0.5%0.5%0.5%0.5%0.5%Capitalexpenditures4.8%4.0%3.0%3.0%2.0%2.0%1.0%1.0%0.5%0.5%0.5%Changeinotherassetsnet-9.4%-8.8%0.0%0.0%0.0%0.0%0.0%0.0%0.0%0.0%0.0%Freecashflow22.3%22.2%15.0%15.2%16.2%16.2%16.7%16.7%16.2%15.2%10.9%PanelC–ValuationCalculationsPartI-ExpectedAssetReturnInputsPartIII-TerminalValue(TV)PartIV-ValuationCalculation(a)Risk-freerate(Rf)9.00%TV=CapitalCashFlowsn+1/(ka-g)(1)PVofcapitalcashflows14,784$(b)Assetbeta(ba)=bU0.760=2294/(0.147-0.03)=2294/0.117=19,608$(2)PVofterminalvalue4,975$(c)Riskpremium(RP)7.50%(3)OperatingValue(V)19,759$(d)Taxrate(T)34.00%PVTV=TV/(1+ka)n(4)Add:Marketablesecurities-=19608x0.25373=4,975$(5)Totalvalue19,759$PartII-ExpectedAssetReturnCalculation(6)Less:Initialbookdebt(D)5,204ka=Rf+(RP)ba(7)Valueofequity14,555$=9.0%+7.5%(0.760)=14.70%(8)Sharesoutstanding229.0(9)Intrinsicshareprice63.56$