Chapter 3 spreadsheet supplement

Chart1 75 60 50 78.75 64.8 55 82.6875 69.984 60.5 86.821875 75.58272 66.55 91.16296875 81.6293376 73.205 95.7211171875 88.159684608 80.5255 100.5071730469 95.2124593766 88.57805 105.5325316992 102.8294561268 97.435855 110.8091582842 111.0558126169 107.1794405 116.3496161984 119.9402776263 117.89738455 122.1670970083 129.5354998364 129.687123005 128.2754518587 139.8983398233 142.6558353055 134.6892244517 151.0902070091 156.9214188361 141.4236856742 163.1774235699 172.6135607197 148.494869958 176.2316174555 189.8749167916 155.9196134559 190.3301468519 208.8624084708 163.7155941286 205.5565586 229.7486493179 171.9013738351 222.0010832881 252.7235142496 180.4964425268 239.7611699511 277.9958656746 189.5212646532 258.9420635472 305.7954522421 Growth Two Firms with the Same Earnings but Different Cash Flows Value, Inc. Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Earnings 100.00 105.00 110.25 115.76 121.55 127.63 134.01 140.71 147.75 155.13 Net Investment 25.00 26.25 27.56 28.94 30.39 31.91 33.50 35.18 36.94 38.78 Free Cash Flow 75.00 78.75 82.69 86.82 91.16 95.72 100.51 105.53 110.81 116.35 Investment Rate: 25.0% Return on New Investment 20.0% Growth Rate 5.0% Volume, Inc. Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Earnings 100.00 105.00 110.25 115.76 121.55 127.63 134.01 140.71 147.75 155.13 Net Investment 50.00 52.50 55.13 57.88 60.78 63.81 67.00 70.36 73.87 77.57 Free Cash Flow 50.00 52.50 55.13 57.88 60.78 63.81 67.00 70.36 73.87 77.57 Investment Rate: 50.0% Return on New Investment 10.0% Growth Rate 5.0% Value, Inc. - Scenario A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 NOPLAT 100.00 105.00 110.25 115.76 121.55 127.63 134.01 140.71 147.75 155.13 162.89 171.03 179.59 188.56 197.99 207.89 218.29 229.20 240.66 252.70 Net Investment 25.00 26.25 27.56 28.94 30.39 31.91 33.50 35.18 36.94 38.78 40.72 42.76 44.90 47.14 49.50 51.97 54.57 57.30 60.17 63.17 Free Cash Flow 75.00 78.75 82.69 86.82 91.16 95.72 100.51 105.53 110.81 116.35 122.17 128.28 134.69 141.42 148.49 155.92 163.72 171.90 180.50 189.52 Investment Rate: 25.0% Return on New Investment 20.0% Growth Rate 5.0% Value, Inc. - Scenario B 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 NOPLAT 100.00 110.00 121.00 133.10 146.41 161.05 177.16 194.87 214.36 235.79 259.37 285.31 313.84 345.23 379.75 417.72 459.50 505.45 555.99 611.59 Net Investment 50.00 55.00 60.50 66.55 73.21 80.53 88.58 97.44 107.18 117.90 129.69 142.66 156.92 172.61 189.87 208.86 229.75 252.72 278.00 305.80 Free Cash Flow 50.00 55.00 60.50 66.55 73.21 80.53 88.58 97.44 107.18 117.90 129.69 142.66 156.92 172.61 189.87 208.86 229.75 252.72 278.00 305.80 Investment Rate: 50.0% Return on New Investment 20.0% Growth Rate 10.0% Value, Inc. - Scenario C 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 NOPLAT 100.00 115.00 132.25 152.09 174.90 201.14 231.31 266.00 305.90 351.79 404.56 465.24 535.03 615.28 707.57 813.71 935.76 1076.13 1237.55 1423.18 Net Investment 75.00 86.25 99.19 114.07 131.18 150.85 173.48 199.50 229.43 263.84 303.42 348.93 401.27 461.46 530.68 610.28 701.82 807.09 928.16 1067.38 Free Cash Flow 25.00 28.75 33.06 38.02 43.73 50.28 57.83 66.50 76.48 87.95 101.14 116.31 133.76 153.82 176.89 203.43 233.94 269.03 309.39 355.79 Investment Rate: 75.0% Return on New Investment 20.0% Growth Rate 15.0% 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 g = 5% 75.00 78.75 82.69 86.82 91.16 95.72 100.51 105.53 110.81 116.35 122.17 128.28 134.69 141.42 148.49 155.92 163.72 171.90 180.50 189.52 g = 10% 60.00 64.80 69.98 75.58 81.63 88.16 95.21 102.83 111.06 119.94 129.54 139.90 151.09 163.18 176.23 190.33 205.56 222.00 239.76 258.94 g = 15% 50.00 55.00 60.50 66.55 73.21 80.53 88.58 97.44 107.18 117.90 129.69 142.66 156.92 172.61 189.87 208.86 229.75 252.72 278.00 305.80 Economic Profit 1 2 3 4 5 6 7 8 9 10 ROIC 15% 15% 15% 12% 12% 12% 12% 12% 12% 12% WACC 10% 10% 10% 10% 10% 10% 10% 10% 10% 10% Invested Capital 1000.00 1040.00 1081.60 1124.86 1142.86 1161.15 1179.73 1198.60 1217.78 1237.26 NOPLAT 150.00 156.00 162.24 134.98 137.14 139.34 141.57 143.83 146.13 148.47 Capital Charge 100.00 104.00 108.16 112.49 114.29 116.11 117.97 119.86 121.78 123.73 Economic Profit 50.00 52.00 54.08 22.50 22.86 23.22 23.59 23.97 24.36 24.75 Reinvestment Rate 80% 80% 80% 80% 80% 80% 80% 80% 80% 80% Reinvested Capital 40.00 41.60 43.26 18.00 18.29 18.58 18.88 19.18 19.48 19.80 PV (EP) 343.7304597331 Terminal Growth Rate 1.6% Invested Capital 1000.00 Value of Firm 1343.73 Sheet3