麦肯锡公司估值模型教学.xls

MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS FILE MCK 1 XLS 1 TABLE 1 HISTORICAL INCOME STATEMENTSTABLE 5 FORECASTED INCOME STATEMENTS Income statement 6 5 4 3 2 10123456789101112 Revenues197 6222 3272 3299 5350 0418 9505 4598 6690 6768 2846 7924 4999 71070 91136 11193 61229 41266 31304 3 Operating expenses 175 4 205 8 249 6 274 7 320 5 383 6 467 4 538 8 621 5 691 4 762 0 831 9 899 7 963 8 1022 5 1074 2 1106 5 1139 7 1173 9 Depreciation 12 8 9 3 11 2 13 0 15 0 17 7 26 4 28 7 32 0 36 9 41 0 45 1 49 1 53 0 56 5 59 7 62 5 64 0 64 5 Operating income9 47 211 511 814 517 611 631 137 139 943 747 350 854 157 159 660 562 665 9 Interest income 0 00 00 00 00 90 70 60 00 00 00 00 00 00 00 00 00 00 00 0 Interest expense 0 1 0 4 0 8 1 0 3 4 4 1 10 1 10 4 10 4 11 7 12 3 13 3 14 3 15 2 16 0 16 8 17 3 17 5 17 8 Earnings before taxes9 36 810 710 812 014 22 120 726 628 231 434 036 538 941 042 943 245 148 1 Revaluation of deferred income taxes0 00 00 00 00 00 00 00 00 00 00 00 0 Income taxes 3 3 2 4 3 8 4 2 5 0 6 1 0 7 8 1 10 4 11 0 12 2 13 3 14 3 15 2 16 0 16 7 16 8 17 6 18 8 Net income6 04 46 96 67 08 11 412 616 217 219 120 822 323 725 026 126 327 529 4 Statement of retained earnings 6 5 4 3 2 10123456789101112 Beginning retained earnings62 563 765 868 669 874 072 585 1101 4118 6131 0142 1152 3161 3168 9174 8176 8178 7 Net income4 46 96 67 08 11 412 616 217 219 120 822 323 725 026 126 327 529 4 Common dividends 3 2 4 8 3 8 5 8 3 9 2 90 00 00 0 6 7 9 7 12 1 14 7 17 5 20 3 24 3 25 5 23 3 Ending retained earnings62 563 765 868 669 874 072 585 1101 4118 6131 0142 1152 3161 3168 9174 8176 8178 7184 8 direct forecast TABLE 2 HISTORICAL BALANCE SHEETSTABLE 6 FORECASTED BALANCE SHEETS 6 5 4 3 2 10123456789101112 Operating cash4 04 45 46 06 08 410 111 713 515 016 618 119 621 022 223 424 124 825 5 Excess marketable securities 10 93 020 510 30 05 80 00 00 00 00 00 00 00 00 00 00 00 00 0 Trade receivables17 924 433 033 343 949 857 767 778 186 895 7104 5113 0121 0128 4134 9139 0143 1147 4 Other receivables1 52 02 72 76 24 95 76 57 58 49 210 110 911 712 413 013 413 814 2 Inventories1 92 12 82 59 010 911 99 711 112 413 714 916 117 318 319 319 820 421 0 Prepaid expenses4 35 15 36 02 44 45 09 511 012 213 514 715 917 118 119 019 620 220 8 Current assets40 541 069 760 867 584 290 4105 1121 2134 9148 6162 3175 5188 0199 5209 5215 8222 3229 0 Gross property plant and equipment100 0117 7128 2155 6204 7272 5297 6334 2381 1422 7463 5502 6539 1572 1600 7624 1638 1649 5668 8 Accumulated depreciation 37 7 42 3 48 7 56 5 71 9 86 9 103 4 121 8 140 4 159 7 178 9 197 6 215 4 232 0 246 9 259 9 270 7 278 8 287 1 Net property plant ABCDEFGHIJKLMNOPQRST 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS FILE MCK 1 XLS 2 and equipment62 375 479 599 1132 8185 6194 2212 4240 8263 1284 7305 0323 7340 1353 7364 2367 5370 6381 7 Total assets102 8116 4149 2159 9200 3269 8284 6317 5362 0397 9433 3467 3499 2528 1553 2573 7583 3592 9610 7 Short term debt0 30 80 91 511 512 520 719 019 422 122 925 026 828 530 031 232 232 533 0 Accounts payable7 311 011 910 514 216 218 924 127 830 934 137 240 243 145 748 149 551 052 5 Other current liabilities13 913 518 218 521 427 828 838 043 848 753 758 763 468 072 175 778 080 482 8 Total current liabilities21 525 331 030 547 156 568 481 091 0101 8110 7120 8130 5139 5147 8155 0159 7163 9168 2 Long term debt5 511 516 221 740 290 694 897 0110 6114 7124 8133 9142 3149 8156 2161 2162 7164 9170 3 Deferred income taxes8 711 012 615 519 625 125 330 835 439 343 146 950 553 856 759 260 561 963 7 Common stock 4 64 923 623 623 623 623 623 623 623 623 623 623 623 623 623 623 623 623 6 Retained earnings62 563 765 868 669 874 072 585 1101 4118 6131 0142 1152 3161 3168 9174 8176 8178 7184 8 Total common equity67 168 689 492 293 497 696 1108 7125 0142 2154 6165 7175 9184 9192 5198 4200 4202 3208 4 Total liabilities and equity102 8116 4149 2159 9200 3269 8284 6317 5362 0397 9433 3467 3499 2528 1553 2573 7583 3592 9610 7 Invested capital70 788 998 6120 6164 7220 0236 9255 4290 4318 3345 5371 5395 5417 0435 3449 9455 8461 6475 5 Debt invested capital 8 2 13 8 17 3 19 2 31 4 46 9 48 8 45 4 44 8 43 0 42 8 42 8 42 8 42 8 42 8 42 8 42 8 42 8 42 8 NOPLAT invested capital ROIC 8 7 9 6 9 2 8 9 8 1 3 2 9 9 10 0 9 3 9 2 9 1 9 0 8 9 8 9 8 8 8 4 8 6 9 0 direct forecast TABLE 3 HISTORICAL FREE CASH FLOWTABLE 7 FORECASTED FREE CASH FLOW Free cash flow 5 4 3 2 10123456789101112 Revenues222 3272 3299 5350 0418 9505 4598 6690 6768 2846 7924 4999 71070 91136 11193 61229 41266 31304 3 Operating expenses 205 8 249 6 274 7 320 5 383 6 467 4 538 8 621 5 691 4 762 0 831 9 899 7 963 8 1022 5 1074 2 1106 5 1139 7 1173 9 Depreciation 9 3 11 2 13 0 15 0 17 7 26 4 28 7 32 0 36 9 41 0 45 1 49 1 53 0 56 5 59 7 62 5 64 0 64 5 EBIT7 211 511 814 517 611 631 137 139 943 747 350 854 157 159 660 562 665 9 Taxes on EBIT 2 6 4 1 4 6 6 0 7 4 4 4 12 1 14 5 15 6 17 0 18 5 19 8 21 1 22 3 23 3 23 6 24 4 25 7 Revaluation of deferred income taxes0 00 00 00 00 00 00 00 00 00 00 00 0 Change in deferred income taxes2 31 62 94 15 50 25 54 63 93 93 83 63 32 92 51 31 31 9 NOPLAT6 99 010 112 615 77 424 427 228 230 532 634 636 337 738 938 239 642 1 Add back depreciation9 311 213 015 017 726 428 732 036 941 045 149 153 056 559 762 564 064 5 Gross cash flow16 220 223 127 633 433 853 259 265 171 577 783 789 394 398 6100 7103 5106 6 Change in working ABCDEFGHIJKLMNOPQRST 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS FILE MCK 1 XLS 3 capital if increase 5 15 62 410 42 58 30 36 65 65 65 65 45 14 74 12 62 62 7 Capital expenditures if positive 22 415 332 648 770 535 047 060 359 262 665 567 869 470 270 265 767 175 6 Gross investment27 520 935 059 173 043 347 366 964 868 271 173 274 574 974 368 369 878 4 Free cash flow 11 3 0 7 11 9 31 5 39 6 9 55 9 7 70 33 36 710 514 819 424 232 433 828 2 Financial cash flow 5 4 3 2 10123456789101112 Incr Decr in excess marketable securities 7 917 5 10 2 10 35 8 5 80 00 00 00 00 00 00 00 00 00 00 00 0 After tax interest income 0 00 00 0 0 5 0 4 0 40 00 00 00 00 00 00 00 00 00 00 00 0 Incr Decr in short and long term debt 6 5 4 8 6 1 28 5 51 4 12 4 0 4 14 1 6 8 10 9 11 1 10 3 9 2 7 8 6 2 2 5 2 5 5 9 After tax interest expense 0 20 50 62 12 56 26 36 47 17 58 18 79 39 810 210 610 710 8 Common dividends 3 24 83 85 83 92 90 00 00 06 79 712 114 717 520 324 325 523 3 Incr Decr in common stock 0 3 18 70 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0 Financial cash flow 11 3 0 7 11 9 31 5 39 6 9 55 9 7 70 33 36 710 514 819 424 232 433 828 2 Effective tax rate on EBIT3 6 21 8 14 3 12 9 10 9 36 3 21 5 26 5 29 3 30 2 31 0 32 0 32 9 33 8 34 8 36 8 36 9 36 2 TABLE 4 HISTORICAL RATIOS FOR FORECAST ASSUMPTIONSTABLE 8 FORECAST ASSUMPTIONS 6 5 4 3 2 10123456789101112 Operations Real growth g 15 0 12 0 8 0 7 0 6 0 5 0 4 0 3 0 2 0 0 0 0 0 0 0 Inflation i 3 0 3 0 3 0 3 0 3 0 3 0 3 0 3 0 3 0 3 0 3 0 3 0 Revenue growth c 12 5 22 5 10 0 16 9 19 7 20 6 18 5 15 4 11 2 10 2 9 2 8 2 7 1 6 1 5 1 3 0 3 0 3 0 Operating expenses revenues88 8 92 6 91 7 91 7 91 6 91 6 92 5 90 0 90 0 90 0 90 0 90 0 90 0 90 0 90 0 90 0 90 0 90 0 90 0 Working cap revenues Operating cash revenues2 0 2 0 2 0 2 0 1 7 2 0 2 0 2 0 2 0 2 0 2 0 2 0 2 0 2 0 2 0 2 0 2 0 2 0 2 0 Trade receiv s revenues9 1 11 0 12 1 11 1 12 5 11 9 11 4 11 3 11 3 11 3 11 3 11 3 11 3 11 3 11 3 11 3 11 3 11 3 11 3 Other receiv s revenues0 8 0 9 1 0 0 9 1 8 1 2 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 Inventories revenues1 0 0 9 1 0 0 8 2 6 2 6 2 4 1 6 1 6 1 6 1 6 1 6 1 6 1 6 1 6 1 6 1 6 1 6 1 6 Prepaid exp s revenues2 2 2 3 1 9 2 0 0 7 1 1 1 0 1 6 1 6 1 6 1 6 1 6 1 6 1 6 1 6 1 6 1 6 1 6 1 6 Accounts payable rev s3 7 4 9 4 4 3 5 4 1 3 9 3 7 4 0 4 0 4 0 4 0 4 0 4 0 4 0 4 0 4 0 4 0 4 0 4 0 Other curr liab s rev s7 0 6 1 6 7 6 2 6 1 6 6 5 7 6 3 6 3 6 3 6 3 6 3 6 3 6 3 6 3 6 3 6 3 6 3 6 3 Property Plant and Equipment PPE PPE economic life n 10 F sub g see Sect 6 10 0000 ABCDEFGHIJKLMNOPQRST 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 MCKAY VALUATION BY FREE CASH FLOW AND ABNORMAL EARNINGS METHODS FILE MCK 1 XLS 4 F sub c see Sect 6 8 7861 Real PPE rev s K 0 580 M see Section 6 0 510 H see Section 6 0 426 Net PPE revenues31 5 33 9 29 2 33 1 37 9 44 3 38 4 35 5 34 9 34 2 33 6 33 0 32 4 31 8 31 1 30 5 29 9 29 3 29 3 Depreciation net PPE 14 9 14 9 16 4 15 1 13 3 14 2 14 8 15 1 15 3 15 6 15 8 16 1 16 4 16 6 16 9 17 2 17 4 17 4 Retirements net PPE 7 5 6 4 6 5 0 4 2 0 5 3 5 3 6 3 7 3 8 3 9 3 10 3 11 2 12 2 13 2 14 2 15 2 15 2 Taxes Tax rate 39 0 39 0 39 0 39 0 39 0 39 0 39 0 39 0 39 0 39 0 39 0 39 0 39 0 39 0 39 0 39 0 39 0 39 0 39 0 PPE tax life q 5 J see Section 8 2 160 Timing diff s net PPE35 8 37 4 40 6 40 1 37 8 34 7 33 4 37 1 37 7 38 3 38 8 39 4 40 0 40 5 41 1 41 7 42 2 42 8 42 8 Interest rate items Real borrowing rate6 0 6 0 6 0 6 0 6 0 6 0 6 0 6 0 6 0 6 0 6 0 6 0 Nominal borrowing rate9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 Real cost of equity11 8 11 8 11 8 11 8 11 8 11 8 11 8 11 8 11 8 11 8 11 8 11 8 Nominal cost of equity14 8 14 8 14 8 14 8 14 8 14 8 14 8 14 8 14 8 14 8 14 8 14 8 Book value target for financial strength57 2 57 2 57 2 57 2 57 2 57 2 57 2 57 2 57 2 57 2 57 2 57 2 This year s short term last year s long term debt 20 0 20 0 20 0 20 0 20 0 20 0 20 0 20 0 20 0 20 0 20 0 20 0 last year s net PPE VALUE CALCULATIONS 123456789101112 1 Free Cash Flow Excess marketable securities at year start 0 00 00 00 00 00 00 00 00 00 00 00 0 Interest bearing debt at year start 115 5115 9130 0136 8147 7158 8169 1178 3186 2192 4194 9197 4 Free cash fl at year end 5 9 7 70 33 36 710 514 819 424 232 433 828 2 WACC10 1 10 1 10 1 10 1 10 1 10 1 10 1 10 1 10 1 10 1 10 1 10 1 Computed value of oper assets at year start 198 5212 8242 0266 2290 0312 7333 9353 0369 4382 6389 1394 8 Computed equity value at year start including deferred taxes 83 096 8112 0129 4142 3153 9164 8174 7183 2190 2194 2197 4 Desired E D E year 1250 0 Result E D E year 1250 0 Result E D E all years41 8 45 5 46 3 48 6 49 1 49 2 49 4 49 5 49 6 49 7 49 9 50 0 Drive to 0 by Goal Seek vary year 1 Book value ABCDEFGHIJ