chapter03
SETTING UP THE FINANCIAL STATEMENT MODELSales growth 10%Current assets/Sales 15%Current liabilities/Sales 8%Net fixed assets/Sales 77%Costs of goods sold/Sales 50%Depreciation rate 10%Interest rate on debt 10.00%Interest paid on cash and marketable securities 8.00%Tax rate 40%Dividend payout ratio 40%Year 0 1Income statementSales 1,000 1,100 2% 2,447.00 1,915.96 1,562.344% 3,128.45 2,299.84 1,802.896% 4,491.36 2,939.65 2,163.728% 8,580.08 4,219.26 2,765.0910% nmf 8,058.09 3,967.8412% nmf nmf 7,576.0714% nmf nmf nmf16% nmf nmf nmfNote: Data tables are discussed in Chapter 31.= I F ( B 7 5 2% 2,447.00 1,915.96 1,562.344% 3,128.45 2,299.84 1,802.896% 4,491.36 2,939.65 2,163.728% 8,580.08 4,219.26 2,765.0910% nmf 8,058.09 3,967.8412% nmf nmf 7,576.0714% nmf nmf nmf16% nmf nmf nmf注意: 模拟运算表在31章中讨论.= I F ( B 7 5 = B 7 6 , n m f , B 8 7 )销 售 增 长 率 与 股 权 价 值1 6 %1 4 %1 2 %1 0 %8 %6 %4 %2 %0 %1 , 0 4 01 , 0 5 01 , 0 6 01 , 0 7 01 , 0 8 01 , 0 9 01 , 1 0 01 , 1 1 01 , 1 2 0销 售 增 长 率股权价值A B C D E1001011021031041051061071081091101111121131141151161171181194 51,464 1,611(732) (805)(32) (32)26 33(189) (220)538 587(215) (235)323 352(129) (141)194 211371 459220 2422,031 2,364(904) (1,124)1,127 1,2401,718 1,941117 129320 320450 450830 1,0421,718 1,9414 5323 352189 220(20) (22)11 12(291) (333)19 19(16) (20)214 228第一个财务模型F G H I J K12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849504 5214 2281,598 - =G58*(1+B55)/(B54-B55)214 1,8264 5214 2281,598 - =G79*(1+B76)/(B75-B76)214 1,826- =NPV(B75,C81:G81)*(1+B75)0.5- =B87 , 模拟运算表头销 售 增 长 率 与 股 权 价 值1 6 %1 4 %1 2 %1 0 %8 %6 %4 %2 %0 %1 , 0 4 01 , 0 5 01 , 0 6 01 , 0 7 01 , 0 8 01 , 0 9 01 , 1 0 01 , 1 1 01 , 1 2 0销 售 增 长 率股权价值F G H I J K5152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989916% 18% 20% 22% 24% 26%1,188.82 1,032.29 907.35 805.37 720.58 649.001,310.08 1,121.12 974.36 857.11 761.31 681.591,471.75 1,235.34 1,058.12 920.35 810.19 720.101,698.09 1,387.62 1,165.81 999.40 869.93 766.322,037.61 1,600.82 1,309.39 1,101.03 944.61 822.812,603.47 1,920.62 1,510.41 1,236.55 1,040.62 893.423,735.18 2,453.61 1,811.94 1,426.27 1,168.64 984.207,130.34 3,519.60 2,314.48 1,710.85 1,347.86 1,105.25nmf 6,717.58 3,319.58 2,185.15 1,616.70 1,274.71销 售 增 长 率 与 股 权 价 值1 6 %1 4 %1 2 %1 0 %8 %6 %4 %2 %0 %1 , 0 4 01 , 0 5 01 , 0 6 01 , 0 7 01 , 0 8 01 , 0 9 01 , 1 0 01 , 1 1 01 , 1 2 0销 售 增 长 率股权价值F G H I J K100101102103104105106107108109110111112113114115116117118119NEGATIVE CASH BALANCES: ILLUSTRATIONSales growth 20% - Increased from 10%Current assets/Sales 20% - Increased from 15%Current liabilities/Sales 8%Net fixed assets/Sales 80% - Increased from 77%Costs of goods sold/Sales 50%Depreciation rate 10%Interest rate on debt 10.00%Interest paid on cash and marketable securities 8.00%Tax rate 40%Dividend payout ratio 50% - Increased from 40%Year 0 1 2 3Income statementSales 1,000 1,200 1,440 1,728Costs of goods sold (500) (600) (720) (864)Interest payments on debt (40) (40) (40) (40)Interest earned on cash and marketable securities 6 4 (0) (6)Depreciation (100) (124) (156) (194)Profit before tax 366 440 524 624Taxes (147) (176) (210) (249)Profit after tax 220 264 314 374Dividends (110) (132) (157) (187)Retained earnings 110 132 157 187Balance sheetCash and marketable securities 80 28 (36) (113)Current assets 200 240 288 346Fixed assetsAt cost 1,100 1,384 1,732 2,157Depreciation (300) (424) (580) (774)Net fixed assets 800 960 1,152 1,382Total assets 1,080 1,228 1,404 1,615Current liabilities 80 96 115 138Debt 400 400 400 400Stock 450 450 450 450Accumulated retained earnings 150 282 439 626Total liabilities and equity 1,080 1,228 1,404 1,615Year 0 1 2 3Free cash flow calculationProfit after tax 264 314 374Add back depreciation 124 156 194Subtract increase in current assets (40) (48) (58)Add back increase in current liabilities 16 19 23Subtract increase in fixed assets at cost (284) (348) (425)Add back after-tax interest on debt 24 24 24Subtract after-tax interest on cash and mkt. securities (3) 0 4Free cash flow 101 118 137A B C D E12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849504 52,074 2,488(1,037) (1,244)(40) (40)(13) (21)(242) (299)742 884(297) (354)445 530(223) (265)223 265(209) (325)415 4982,675 3,306(1,016) (1,315)1,659 1,9911,865 2,163166 199400 400450 450849 1,1141,865 2,1634 5445 530242 299(69) (83)28 33(518) (631)24 248 13159 186NEGATIVE CASH BALANCES: ILLUSTRATIONF G1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950负数现金平衡:说明销售增长率 20% - 从10%开始增长流动资产/销售收入 20% - 从15%开始增长流动负债/销售收入 8%固定资产净值/销售收入 80% - 从77%开始增长销售成本/销售收入 50%折旧率 10%负债的利息费用率 10.00%现金和流动证券的利息支付率 8.00%税率 40%股利支付率 50% - 从40%开始增长年 0 1 2 3损益表销售收入 1,000 1,200 1,440 1,728销售成本 (500) (600) (720) (864)负债的利息费用 (40) (40) (40) (40)现金和流动证券的利息收入 6 4 (0) (6)折旧 (100) (124) (156) (194)税前利润 366 440 524 624税金 (147) (176) (210) (249)税后利润 220 264 314 374股利 (110) (132) (157) (187)未分配的利润 110 132 157 187资产负债表现金和流动证券 80 28 (36) (113)流动资产 200 240 288 346固定资产原值 1,100 1,384 1,732 2,157折旧 (300) (424) (580) (774)固定资产净值 800 960 1,152 1,382总资产 1,080 1,228 1,404 1,615流动负债 80 96 115 138长期负债 400 400 400 400股本金 450 450 450 450累积未分配的利润 150 282 439 626负债和股东权益总计 1,080 1,228 1,404 1,615年 0 1 2 3自由现金流量计算税后利润 264 314 374追加折旧 124 156 194减去流动资产的增加 (40) (48) (58)追加流动负债的增加 16 19 23减去固定资产原值的增加 (284) (348) (425)加债务的税后利息 24 24 24减去现金和流动证券的税后利息 (3) 0 4自由现金流量计算 101 118 137A B C D E12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849504 52,074 2,488(1,037) (1,244)(40) (40)(13) (21)(242) (299)742 884(297) (354)445 530(223) (265)223 265(209) (325)415 4982,675 3,306(1,016) (1,315)1,659 1,9911,865 2,163166 199400 400450 450849 1,1141,865 2,1634 5445 530242 299(69) (83)28 33(518) (631)24 248 13159 186负数现金平衡:说明F G1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950NO NEGATIVE CASH BALANCESSales growth 20% - Increased from 10%Current assets/Sales 20% - Increased from 15%Current liabilities/Sales 8%Net fixed assets/Sales 80% - Increased from 77%Costs of goods sold/Sales 50%Depreciation rate 10%Interest rate on debt 10.00%Interest paid on cash and marketable securities 8.00%Tax rate 40%Dividend payout ratio 50% - Increased from 40%Year 0 1 2 3Income statementSales 1,000 1,200 1,440 1,728Costs of goods sold (500) (600) (720) (864)Interest payments on debt (40) (40) (42) (47)Interest earned on cash and marketable securities 6 4 1 -Depreciation (100) (124) (156) (194)Profit before tax 366 440 524 622Taxes (147) (176) (209) (249)Profit after tax 220 264 314 373Dividends (110) (132) (157) (187)Retained earnings 110 132 157 187Balance sheetCash and marketable securities 80 28 0 0Current assets 200 240 288 346Fixed assetsAt cost 1,100 1,384 1,732 2,157Depreciation (300) (424) (580) (774)Net fixed assets 800 960 1,152 1,382Total assets 1,080 1,228 1,440 1,728Current liabilities 80 96 115 138Debt 400 400 436 514Stock 450 450 450 450Accumulated retained earnings 150 282 439 626Total liabilities and equity 1,080 1,228 1,440 1,728Year 0 1 2 3Free cash flow calculationProfit after tax 264 314 373Add back depreciation 124 156 194Subtract increase in current assets (40) (48) (58)Add back increase in current liabilities 16 19 23Subtract increase in fixed assets at cost (284) (348) (425)Add back after-tax interest on debt 24 25 28Subtract after-tax interest on cash and mkt. securities (3) (1) 0Free cash flow 101 118 137A B C D E12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849504 52,074 2,488(1,037) (1,244)(56) (67)- -(242) (299)739 878(296) (351)443 527(222) (263)222 2630 0 - =G39-G28-G32415 4982,675 3,306(1,016) (1,315)1,659 1,9912,074 2,488166 199610 728 - =MAX(G28+G32-G35-G37-G38,F36)450 450847 1,1112,074 2,4884 5443 527242 299(69) (83)28 33(518) (631)34 400 0159 186NO NEGATIVE CASH BALANCESF G H1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950无负数现金平衡销售增长率 20% - 从10%开始增长流动资产/销售收入 20% - 从15%开始增长流动负债/销售收入 8%固定资产净值/销售收入 80% - 从77%开始增长销售成本/销售收入 50%折旧率 10%负债的利息费用率 10.00%现金和流动证券的利息支付率 8.00%税率 40%股利支付率 50% - 从40%开始增长年 0 1 2 3损益表销售收入 1,000 1,200 1,440 1,728销售成本 (500) (600) (720) (864)负债的利息费用 (40) (40) (42) (47)现金和流动证券的利息收入 6 4 1 -折旧 (100) (124) (156) (194)税前利润 366 440 524 622税金 (147) (176) (209) (249)税后利润 220 264 314 373股利 (110) (132) (157) (187)未分配的利润 110 132 157 187资产负债表现金和流动证券 80 28 0 0流动资产 200 240 288 346固定资产原值 1,100 1,384 1,732 2,157折旧 (300) (424) (580) (774)固定资产净值 800 960 1,152 1,382总资产 1,080 1,228 1,440 1,728流动负债 80 96 115 138长期负债 400 400 436 514股本金 450 450 450 450累积未分配的利润 150 282 439 626负债和股东权益总计 1,080 1,228 1,440 1,728年 0 1 2 3自由现金流量计算税后利润 264 314 373追加折旧 124 156 194减去流动资产的增加 (40) (48) (58)追加流动负债的增加 16 19 23减去固定资产原值的增加 (284) (348) (425)加债务的税后利息 24 25 28减去现金和流动证券的税后利息 (3) (1) 0自由现金流量计算 101 118 137A B C D E12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849504 52,074 2,488(1,037) (1,244)(56) (67)- -(242) (299)739 878(296) (351)443 527(222) (263)222 2630 0 - =G39-G28-G32415 4982,675 3,306(1,016) (1,315)1,659 1,9912,074 2,488166 199610 728 - =MAX(G28+G32-G35-G37-G38,F36)450 450847 1,1112,074 2,4884 5443 527242 299(69) (83)28 33(518) (631)34 400 0159 186无负数现金平衡F G H1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950TARGET DEBT-EQUITY RATIOCash is fixed, ratio of debt/equity changes in each yearSales growth 10%Current assets/Sales 15%Current liabilities/Sales 8%Net fixed assets/Sales 77%Costs of goods sold/Sales 50%Depreciation rate 10%Interest rate on debt 10.00%Interest paid on cash & marketable securities 8.00%Tax rate 40%Dividend payout ratio 60%Year 0 1 2 3Income statementSales 1,000 1,100 1,210 1,331Costs of goods sold (500) (550) (605) (666)Interest payments on debt (32) (30) (29) (28)Interest earned on cash & marketable securities 6 6 6 6Depreciation (100) (117) (137) (161)Profit before tax 374 409 445 483Taxes (150) (164) (178) (193)Profit after tax 225 246 267 290Dividends (135) (147) (160) (174)Retained earnings 90 98 107 116Balance sheetCash and marketable securities 80 80 80 80Current assets 150 165 182 200Fixed assetsAt cost 1,070 1,264 1,486 1,740Depreciation (300) (417) (554) (715)Net fixed assets 770 847 932 1,025Total assets 1,000 1,092 1,193 1,305Current liabilities 80 88 97 106Debt 320 287 284 276Stock 450 469 457 451Accumulated retained earnings 150 248 355 471Total liabilities and equity 1,000 1,092 1,193 1,305Target debt-equity ratio 0.53 0.40 0.35 0.30Year 0 1 2 3Free cash flow calculationProfit after tax 246 267 290Add back depreciation 117 137 161Subtract increase in current assets (15) (17) (18)Add back increase in current liabilities 8 9 10Subtract increase in fixed assets at cost (194) (222) (254)I n i t i a l ( y e a r 0 ) d e b t / e q u i t y r a t i o : = B 3 6 / ( B 3 7 + B 3 8 )I n i t i a l ( y e a r 0 ) d e b t / e q u i t y r a t i o : = B 3 6 / ( B 3 7 + B 3 8 )A B C D E12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849Add back after-tax interest on debt 18 17 17Subtract after-tax interest on cash & mkt. securities (4) (4) (4)Free cash flow 176 188 201A B C D E5051524 51,464 1,611(732) (805)(29) (32)6 6(189) (220)521 560(208) (224)313 336(188) (202)125 13480 80220 2422,031 2,364(904) (1,124)1,127 1,2401,427 1,562117 129302 331 - =G41*(G37+G38)412 372 - =G33-G35-G36-G38596 7301,427 1,5620.30 0.304 5313 336189 220(20) (22)11 12(291) (333)TARGET DEBT-EQUITY RATIOCash is fixed, ratio of debt/equity changes in each yearF G H1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484917 19(4) (4)214 228F G H505152目标负债/权益比率现金固定, 目标负债/权益比率按年变化销售增长率 10%流动资产/销售收入 15%流动负债/销售收入 8%固定资产净值/销售收入 77%销售成本/销售收入 50%折旧率 10%负债的利息费用率 10.00%现金和流动证券的利息支付率 8.00%税率 40%股利支付率 60%年 0 1 2 3损益表销售收入 1,000 1,100 1,210 1,331销售成本 (500) (550) (605) (666)负债的利息费用 (32) (30) (29) (28)现金和流动证券的利息收入 6 6 6 6折旧 (100) (117) (137) (161)税前利润 374 409 445 483税金 (150) (164) (178) (193)税后利润 225 246 267 290股利 (135) (147) (160) (174)未分配的利润 90 98 107 116资产负债表现金和流动证券 80 80 80 80流动资产 150 165 182 200固定资产原值 1,070 1,264 1,486 1,740折旧 (300) (417) (554) (715)固定资产净值 770 847 932 1,025总资产 1,000 1,092 1,193 1,305流动负债 80 88 97 106股本金 320 287 284 276累积未分配的利润 450 469 457 451负债和股东权益总计 150 248 355 4711,000 1,092 1,193 1,305目标负债/权益比率0.53 0.40 0.35 0.30年 0 1 2 3自由现金流量计算税后利润 246 267 290追加折旧 117 137 161减去流动资产的增加 (15) (17) (18)追加流动负债的增加 8 9 10减去固定资产原值的增加 (194) (222) (254)I n i t i a l ( y e a r 0 ) d e b t / e q u i t y r a t i o : = B 3 6 / ( B 3 7 + B 3 8 )初 始 的负 债 /权 益 比 率 := B 3 7 / ( B 3 8 + B 3 9 )A B C D E12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849加债务的税后利息 18 17 17减去现金和流动证券的税后利息 (4) (4) (4)自由现金流量计算 176 188 201A B C D E5051524 51,464 1,611(732) (805)(29) (32)6 6(189) (220)521 560(208) (224)313 336(188) (202)125 13480 80220 2422,031 2,364(904) (1,124)1,127 1,2401,427 1,562117 129302 331 - =G41*(G37+G38)412 372 - =G33-G35-G36-G38596 7301,427 1,5620.30 0.304 5313 336189 220(20) (22)11 12(291) (333)目标负债/权益比率现金固定, 目标负债/权益比率按年变化F G H1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484917 19(4) (4)214 228F G H505152PROJECT FINANCENo dividends, debt repayment schedule fixed, net fixed assets constantSales growth 15%Current assets/Sales 15%Current liabilities/Sales 8%Costs of goods sold/Sales 45%Depreciation rate 10%Interest rate on debt 10.00%Interest paid on cash and marketable securities 8.00%Tax rate 40%Dividend payout ratio 0% - No dividends until all the debt is paid offYear 0 1 2 3Income statementSales 1,150 1,323 1,521Costs of goods sold -518 -595 -684Interest payments on debt -90 -70 -50Interest earned on cash and marketable securities 1 3 9Depreciation -211 -233 -257Profit before tax 333 428 539Taxes -133 -171 -216Profit after tax 200 257 323Dividends 0 0 0Retained earnings 200 257 323Balance sheetCash and marketable securities 0 19 64 173Current assets 200 173 198 228Fixed assetsAt cost 2,000 2,211 2,443 2,700Depreciation 0 -211 -443 -700Net fixed assets 2,000 2,000 2,000 2,000Total assets 2,200 2,192 2,262 2,401Current liabilities 100 92 106 122Debt 1,000 800 600 400Stock 1,100 1,100 1,100 1,100Accumulated retained earnings 0 200 456 780Total liabilities and equity 2,200 2,192 2,262 2,401FREE CASH FLOW CALCULATIONYear 0 1 2 3Profit after tax 200 257 323Add back depreciation 211 233 257Subtract increase in current assets 28 -26 -30Add back increase in current liabilities -8 14 16Subtract increase in fixed assets at cost -211 -233 -257Add back after-tax interest on debt 54 42 30A B C D E123456789101112131415161718192021222324252627282930313233343536373839404142434445464748Subtract after-tax interest on cash and
中华第一财税网文库