M09-02-SpreadsheetValuations

Model09-02SpreadsheetValuationsPartA:ValuationoftheTarget(InvestmentRequirementsonaGrossBasis)%ofRev01234to1Revenuegrowthrate20%20%20%0%2Revenues(Rt)100%###################################3Totaloperatingcosts(incl.depreciation)80%9601,1521,3821,3824Netoperatingincome(Xt)20%2402883463465Taxes(T),@40%ofNOI8%961151381386Netoperatingincomeaftertaxes[Xt(1-T)]12%$144$173$207$2077Depreciation6%72861041048Grosscashflows18%$216$259$311$311Investmentrequirements9Workingcapital(Iwt)4%$48$58$69$010Capitalexpenditures(Ift)12%14417320710411Totalinvestmentrequirements(gross),(Igt)16%$192$230$276$10412Freecashflows[Xt(1-T)-Igt]2%$24$29$35$20713aDiscountingprocedure1/(1+k)1/(1+k)21/(1+k)31/[k(1+k)3]13bDiscountvalues,k=10%0.9090.8260.7517.51314Presentvalues$22$24$26#######15Sumofpresentvalues=valueoftarget#######PartB:ValuationoftheTarget(InvestmentRequirementsonaNetBasis)%ofRev0123b3e4Before-taxcashflows(Xt)20%$240$288$346$3465Taxes(T),@40%ofNOI8%961151381386After-taxcashflows[Xt(1-T)]12%14417320720711Investmentrequirements(net),(It)10%120144173012Freecashflows[Xt(1-T)-It]2%$24$29$35$20713aDiscountingprocedure1/(1+k)1/(1+k)21/(1+k)31/[k(1+k)3]13bDiscountvalues,k=10%0.9090.8260.7517.51314Presentvalues$22$24$26#######15Sumofpresentvalues=valueoftarget#######