M09-03-SpreadsheetCalculations

Model09-03ASpreadsheetCalculations012345A.FreeCashFlow:1.Revenues10,000$11,500$13,225$15,209$17,490$20,114$2.Less:Costofgoodssold8,0509,25810,64612,24314,0803.Less:Selling,general,&admin.exp.1,1501,3231,5211,7492,0114.Less:Depreciation5756617608751,0065.Add:Mergereconomies-----6.Pretaxoperatingincome(NOI)=EBIT1,7251,9842,2812,6243,0177.Less:Interestexpense4605296087008058.Incomebeforetax1,2651,4551,6731,9242,2129.Less:Incometax50658266977088510.Netincome7598731,0041,1541,32711.Add:(Interestexpense)(1-T)27631736542048312.Aftertaxoperatingincome(NOI)(1-T)1,0351,1901,3691,5741,81013.Add:Depreciation5756617608751,00614.Grosscashflows1,6101,8522,1292,4492,81615.Less:Capitalexpenditures1,0351,1901,3691,5741,81016.Less:Workingcapitalrequirements46052960870080517.Annualfreecashflows(FCF)115$132$152$175$201$B.PresentValueofSupernormalFCF18.DiscountedannualFCF,ks=11.0%104$107$111$115$119$19.TotalpresentvalueofFCF(summationofRow18)=557$C.CalculatingTerminalValue20.Terminalvalueinyearnkc=Terminalperiodcostofcapital=12%gc=Terminalperiodgrowthrate=4%Ic=Terminalperiodnetinvestmentrate=3%TV=[1810-(20114)(0.03)]x[(1+0.04)/(0.12-0.04)]=$15,68921.PresentvalueofterminalvaluePVTV==15688.6/(1+0.11)^5=$9,310D.CalculatingFirmValue22.Presentvalueofsupergrowthcashflows557$23.Presentvalueofterminalvalue9,31024.Enterpriseoperatingvalue9,867$25.Add:Marketablesecurities20026.Entityvalue10,067$27.Less:Totalinterest-bearingdebt40028.Equityvalue9,667$29.Numberofshares50030.Valuepershare19.33$nskTV)1(+[]ccccgkgIRTNOITV-+--=)1()()1(55Model09-03BSpreadsheetGeneratingModelPanelAInputs:Timerelationships:Baseyearrevenues$10,000Baseyear2000Revenuegrowthrate,initialperiod15.0%Initialyearofprojection2001Discountrate,initialperiod11.0%Lastyearofprojection2005Discountrate,terminalperiod12.0%Terminalperiodgrowthrate4.0%Terminalperioddepreciation3.0%Terminalperiodcapitalexpenditures5.0%Terminalperiodworkingcapitalrequirements1.0%Taxrate40%PanelB–InitialGrowthPeriodYears123456Revenuen+1Relation2001200220032004200520061.Revenues##########################################2.EBITDA20.0%2,3002,6453,0423,4984,0233,7653.Depreciation5.0%5756617608751,0066284.EBIT=NOI15.0%1,7251,9842,2812,6243,0173,1385.Less:Interestexpense3.0%3453974565256036286.EBT=(4)-(5)1,3801,5871,8252,0992,4142,5107.Less:Tax@40%5526357308409651,0048.Netincome=(6)-(7)8289521,0951,2591,4481,5069.Add:Interestexpense*(1-T)=(5)*(1-T)20723827431536237710.NOPAT=(4)*(1-T)=(8)+(9)1,0351,1901,3691,5741,8101,88310a.Add:depreciation5.0%5756617608751,00662810b.Less:capitalexpenditures9.0%1,0351,1901,3691,5741,8101,04610c.Less:workingcapitalrequirements4.0%46052960870080520911.Freecashflow=(10)+(10a)-(10b)-(10c)1151321521752011,25512.Discountfactor@11.0%0.9010.8120.7310.6590.59313.Presentvaluesoffreecashflow104$107$111$115$119$14.Sumofinitialperiodpresentvalues557$PanelC–TerminalPeriod15.EBITDAof20063,765$16.Freecashflow,20061,255$[Rn+1[m(1–T)+d–I]]17.Terminalperioddiscountrate8.0%(kc–gc)18.Terminalvaluein200615,68919.Presentvaluefactor:1/(1+0.11)^n0.593520.Presentvalueofterminalvalue9,310$PanelD–CalculationofEquityValue21.Presentvalueofsupergrowthcashflows557$22.Presentvalueofterminalvalue9,31023.Enterpriseoperatingvalue9,867$24.Add:Marketablesecurities20025.Entityvalue#######25.Less:Totalinterest-bearingdebt40026.Equityvalue9,667$27.Numberofsharesoutstanding50028.Valuepershare19.33$