搜索
12345678910111213141516171819202122232425262728293031323334353637383940ABCDEFSETTINGUPTHEFINANCIALSTATEMENTMODELSalesgrowth10%Currentassets/Sales15%Currentliabilities/Sales8%Netfixedassets/Sales77%Costsofgoodssold/Sales50%Depreciationrate10%Interestrateondebt10.00%Interestpaidoncashandmarketablesecurities8.00%Taxrate40%Dividendpayoutratio40%Year01IncomestatementSales1,0001,100--=B16*(1+$B$3)Costsofgoodssold(500)(550)--=-C16*$B$7Interestpaymentsondebt(32)(32)--=-$B$9*(B37+C37)/2Interestearnedoncashandmarketablesecurities69--=$B$10*(B28+C28)/2Depreciation(100)(117)--=-$B$8*(C31+B31)/2Profitbeforetax374410--=SUM(C16:C20)Taxes(150)(164)--=-C21*$B$11Profitaftertax225246--=C22+C21Dividends(90)(98)--=-$B$12*C23Retainedearnings135148--=C24+C23BalancesheetCashandmarketablesecurities80144--=C40-C29-C33Currentassets150165--=C16*$B$4FixedassetsAtcost1,0701,264--=C33-C32Depreciation(300)(417)--=B32-$B$8*(C31+B31)/2Netfixedassets770847--=C16*$B$6Totalassets1,0001,156--=C33+C29+C28Currentliabilities8088--=C16*$B$5Debt320320--=B37Stock450450--=B38Accumulatedretainedearnings150298--=B39+C25Totalliabilitiesandequity1,0001,156--=SUM(C36:C39)12345678910111213141516171819202122232425262728293031323334353637383940ABCDEF建立财务报表模型销售增长率10%流动资产/销售15%流动负债/销售8%固定资产净值/销售77%销货成本/销售50%折旧率10%负债的利息费用率10.00%现金和流动证券的利息支付率8.00%税率40%股利支付率40%年01损益表销售1,0001,100--=B16*(1+$B$3)销货成本(500)(550)--=-C16*$B$7负债的利息费用(32)(32)--=-$B$9*(B37+C37)/2现金和流动证券的利息收入69--=$B$10*(B28+C28)/2折旧(100)(117)--=-$B$8*(C31+B31)/2税前利润374410--=SUM(C16:C20)税金(150)(164)--=-C21*$B$11税后利润225246--=C22+C21股利(90)(98)--=-$B$12*C23未分配的利润135148--=C24+C23资产负债表现金和流动证券80144--=C40-C29-C33流动资产150165--=C16*$B$4固定资产按成本价格1,0701,264--=C33-C32折旧(300)(417)--=B32-$B$8*(C31+B31)/2固定资产净值770847--=C16*$B$6总资产1,0001,156--=C33+C29+C28流动负债8088--=C16*$B$5长期负债320320--=B37股本金450450--=B38累积未分配的利润150298--=B39+C25负债和股东权益总计1,0001,156--=SUM(C36:C39)1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950ABCDEFIRSTFINANCIALMODELSalesgrowth10%Currentassets/Sales15%Currentliabilities/Sales8%Netfixedassets/Sales77%Costsofgoodssold/Sales50%Depreciationrate10%Interestrateondebt10.00%Interestpaidoncashandmarketablesecurities8.00%Taxrate40%Dividendpayoutratio40%Year0123IncomestatementSales1,0001,1001,2101,331Costsofgoodssold(500)(550)(605)(666)Interestpaymentsondebt(32)(32)(32)(32)Interestearnedoncashandmarketablesecurities691420Depreciation(100)(117)(137)(161)Profitbeforetax374410450492Taxes(150)(164)(180)(197)Profitaftertax225246270295Dividends(90)(98)(108)(118)Retainedearnings135148162177BalancesheetCashandmarketablesecurities80144213289Currentassets150165182200FixedassetsAtcost1,0701,2641,4861,740Depreciation(300)(417)(554)(715)Netfixedassets7708479321,025Totalassets1,0001,1561,3261,513Currentliabilities808897106Debt320320320320Stock450450450450Accumulatedretainedearnings150298460637Totalliabilitiesandequity1,0001,1561,3261,513Year0123FreecashflowcalculationProfitaftertax246270295Addbackdepreciation117137161Subtractincreaseincurrentassets(15)(17)(18)Addbackincreaseincurrentliabilities8910Subtractincreaseinfixedassetsatcost(194)(222)(254)Addbackafter-taxinterestondebt19191951525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100ABCDESubtractafter-taxinterestoncashandmkt.securities(5)(9)(12)Freecashflow176188201ValuingthefirmWeightedaveragecostofcapital20%Year0123FCF176188201TerminalvalueTotal176188201NPVofrow611,598--=NPV(B56,C61:G61)Addininitial(year0)cashandmkt.securities80Enterprisevalue1,678Subtractoutvalueoffirm'sdebttoday-320Equityvalue1,358CashandmarketablesecuritiesasnegativedebtNPVofrow61=enterprisevalue1,598Netyear0debt-240--=-B37+B28Equityvalue1,358Valuingthefirm--usinghalf-yeardiscountingWeightedaveragecostofcapital20%Year0123FCF176188201TerminalvalueTotal176188201NPVofrow811,750--=NPV(B76,C81:G81)*(1+B76)^0.5Addininitial(year0)cashandmkt.securities80Enterprisevalue1,830Subtractoutvalueoffirm'sdebttoday-320Equityvalue1,510Growth1,510--=B870%9182%9844%1,0666%1,1728%1,31310%1,51012%1,80714%2,30116%3,28801,0002,0003,0004,000EquityvalueSalesGrowthandEquityValue101102103104105106107108109110111112113114115116117118119ABCDEWACC1,510.4110%12%14%0%1,924.401,590.091,350.72growthrateofsales2%2,333.271,848.811,525.204%3,014.722,236.891,769.466%4,377.632,883.692,135.868%8,466.364,177.292,746.5210%nmf8,058.093,967.8412%nmfnmf7,631.7914%nmfnmfnmf16%nmfnmfnmfNote:DatatablesarediscussedinChapter26.=IF(B76=B3,nmf,B87)1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950FGHIJKFIRSTFINANCIALMODEL451,4641,611(732)(805)(32)(32)2633(189)(220)538587(215)(235)323352(129)(141)1942113714592202422,0312,364(904)(1,124)1,1271,2401,7181,9411171293203204504508301,0421,7181,94145323352189220(20)(22)1112(291)(333)191951525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100FGHIJK(16)(20)214228452142282,511--=G59*(1+B3)/(B56-B3)2142,7