finance cash flow sheet
Notes
| 1) Have fun with the real data exercise. | ||
| This is a starting point. Feel free to make changes if you wish. Fix any residual errors that may exist. Use it as a 'sandbox'. | ||
| It'll take a few minutes to fill up the data. Then spend time playing around. Change assumptions. Check the impact. | ||
| Use historical data from internet sources. | ||
| Boeing | ||
| Costco | For example use any of these from internet sources | |
| Microsoft | ||
| 2) 'Proforma' tab has proforma data for income and balance sheet. Three color coded zones identify | ||
| data, assumptions and conclusions. Use historical data to construct the proforma. Change assumptions as needed. | ||
| 3) 'FCFF and FCFE', tab constructs the free cashflows several ways using the proforma from 'Proforma' tab. | ||
| 4) 'Value' tab makes assumptions about discount rates and uses data from 'Proforma' and 'FCFF and FCFE' tabs to find value. | ||
| Takeaways (very sketchy description) | ||
| 1) Payout ratio does not affect value | ||
| 2) Cost structure and growth assumptions can radically affect value | ||
| 3) Growth may not enhance value | ||
| 4) Terminal value can be large | ||
| 5) Develop intuition for FCFF FCFE and Value | ||
| 6) Learn the standard process of valuation | ||
| This spreadsheet model | ||
| 1) Sales Growth in a given year will need investment. 10% growth will raise future FCFF by 10%. | ||
| 2) Growth needs to stop at some point to be harvested | ||
| 3) Discounted benefits of growth may be negative, net of initial FCFF requirement. |
Proforma
| Pro Forma Income Statement | Historical Data | ||||||||||
| Year 0 | 1 | 2 | 3 | 4 | 5 | 6 | Assumptions | ||||
| Sales Growth | 0% | 10% | 10% | 10% | 0% | 3% | Conclusions | ||||
| Payout Ratio | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | |||||
| Tax Rate | 40.0% | 40.0% | 40.0% | 40.0% | 40.0% | 34.0% | |||||
| Int. Rate | 10.0% | 10.0% | 10.0% | 10.0% | 10.0% | 10.0% | Value | $688 | |||
| Sales | 750.0 | 750.0 | 825.0 | 907.5 | 998.3 | 998.3 | 1028.2 | ||||
| CGS | 200.0 | 450.0 | 495.0 | 544.5 | 599.0 | 599.0 | 616.9 | 60.0% | % of Sales | ||
| SGA | 150.0 | 75.0 | 82.5 | 90.8 | 99.8 | 99.8 | 102.8 | 10.0% | % of Sales | ||
| Depreciation | 100.0 | 100.0 | 110.0 | 121.0 | 133.1 | 133.1 | 137.1 | 13.3% | % of Sales | ||
| EBIT | 300.0 | 125.0 | 137.5 | 151.3 | 166.4 | 166.4 | 171.4 | ||||
| Interest Expense | 25.0 | 6.5 | 7.2 | 7.9 | 8.7 | 8.7 | 8.9 | 10.0% | % of LT Debt | ||
| Profit bef. Taxes | 275.0 | 118.5 | 130.4 | 143.4 | 157.7 | 157.7 | 162.5 | ||||
| Taxes | 110.0 | 47.4 | 52.1 | 57.4 | 63.1 | 63.1 | 55.2 | ||||
| Net Income | 165.0 | 71.1 | 78.2 | 86.0 | 94.6 | 94.6 | 107.2 | ||||
| Dividend Payment | 28.4 | 31.3 | 34.4 | 37.9 | 37.9 | 42.9 | |||||
| Pro Forma Balance Sheet | |||||||||||
| Year 0 | 1 | 1 | 1 | 1 | 1 | 1 | |||||
| Cash | 90.0 | 90.0 | 99.0 | 108.9 | 119.8 | 119.8 | 123.4 | 12.0% | % of Sales | ||
| Mktbl Securities | 60.0 | 60.0 | 66.0 | 72.6 | 79.9 | 79.9 | 82.3 | 8.0% | % of Sales | ||
| A/R | 120.0 | 120.0 | 132.0 | 145.2 | 159.7 | 159.7 | 164.5 | 16.0% | % of Sales | ||
| Inventory | 100.0 | 100.0 | 110.0 | 121.0 | 133.1 | 133.1 | 137.1 | 13.3% | % of Sales | ||
| Fixed Assets(net) | 280.0 | 280.0 | 308.0 | 338.8 | 372.7 | 372.7 | 383.9 | 37.3% | % of Sales | net | |
| Total | 650.0 | 650.0 | 715.0 | 786.5 | 865.2 | 865.2 | 891.1 | ||||
| A/P | 84.0 | 84.0 | 92.4 | 101.6 | 111.8 | 111.8 | 115.2 | 11.2% | % of Sales | ||
| Oth Curr.Liabilities | 50.0 | 50.0 | 55.0 | 60.5 | 66.6 | 66.6 | 68.5 | 6.7% | % of Sales | ||
| LT Debt | 250.0 | 65.0 | 71.5 | 78.7 | 86.5 | 86.5 | 89.1 | 10% | % of Assets | ||
| Stockholder's Equity | 266.0 | 451.0 | 496.1 | 545.7 | 600.3 | 600.3 | 618.3 | ||||
| Total | 650.0 | 650.0 | 715.0 | 786.5 | 865.2 | 865.2 | 891.1 | ||||
| FCFF | 75.0 | 30.9 | 34.0 | 37.4 | 99.8 | 92.5 | |||||
| FCFE | -113.9 | 33.1 | 36.4 | 40.1 | 94.6 | 89.2 | |||||
| Addn to Retained Income | 165.0 | 42.7 | 46.9 | 51.6 | 56.8 | 56.8 | 64.3 | ||||
| New Equity Isuued | 142.3 | -1.8 | -2.0 | -2.2 | -56.8 | -46.3 | |||||
| LT Debt Issued | -185.0 | 6.5 | 7.2 | 7.9 | 0.0 | 2.6 | |||||
| New External Capital | -42.7 | 4.7 | 5.1 | 5.7 | -56.8 | -43.7 |
FCFF and FCFE
| This tab is tied to 'Proforma' worksheet tab | ||||||||||||
| FCFF two methods | ||||||||||||
| 1.0 | 2.0 | 3.0 | 4.0 | 5.0 | 6.0 | check9/16 | ||||||
| EBIT after Tax | 75.0 | 82.5 | 90.8 | 99.8 | 99.8 | 113.1 | ||||||
| add back Depreciation | 100.0 | 110.0 | 121.0 | 133.1 | 133.1 | 137.1 | ||||||
| subtract increase in WC | 0.0 | 23.6 | 26.0 | 28.6 | 0.0 | 9.4 | ||||||
| subtract capital expenditure | 100.0 | 138.0 | 151.8 | 167.0 | 133.1 | 148.3 | ||||||
| 75.0 | 30.9 | 34.0 | 37.4 | 99.8 | 92.5 | |||||||
| Net Income | 71.1 | 78.2 | 86.0 | 94.6 | 94.6 | 107.2 | ||||||
| add back after tax interest | 3.9 | 4.3 | 4.7 | 5.2 | 5.2 | 5.9 | ||||||
| add back Depreciation | 100.0 | 110.0 | 121.0 | 133.1 | 133.1 | 137.1 | ||||||
| subtract increase in WC | 0.0 | 23.6 | 26.0 | 28.6 | 0.0 | 9.4 | ||||||
| subtract capital expenditure | 100.0 | 138.0 | 151.8 | 167.0 | 133.1 | 148.3 | ||||||
| 75.0 | 30.9 | 34.0 | 37.4 | 99.8 | 92.5 | |||||||
| FCFE three methods | ||||||||||||
| Net Income | 71.1 | 78.2 | 86.0 | 94.6 | 94.6 | 107.2 | ||||||
| add back Depreciation | 100.0 | 110.0 | 121.0 | 133.1 | 133.1 | 137.1 | ||||||
| subtract increase in WC | 0.0 | 23.6 | 26.0 | 28.6 | 0.0 | 9.4 | ||||||
| subtract capital expenditure | 100.0 | 138.0 | 151.8 | 167.0 | 133.1 | 148.3 | ||||||
| add net borrowing | -185.0 | 6.5 | 7.2 | 7.9 | 0.0 | 2.6 | ||||||
| -113.9 | 33.1 | 36.4 | 40.1 | 94.6 | 89.2 | |||||||
| FCFF | 75.0 | 30.9 | 34.0 | 37.4 | 99.8 | 92.5 | ||||||
| subtract after tax interest | 3.9 | 4.3 | 4.7 | 5.2 | 5.2 | 5.9 | ||||||
| add net borrowing | -185.0 | 6.5 | 7.2 | 7.9 | 0.0 | 2.6 | ||||||
| -113.9 | 33.1 | 36.4 | 40.1 | 94.6 | 89.2 | |||||||
| Dividends | 28.4 | 31.3 | 34.4 | 37.9 | 37.9 | 42.9 | ||||||
| add equity repurchases | -142.3 | 1.8 | 2.0 | 2.2 | 56.8 | 46.3 | ||||||
| -113.9 | 33.1 | 36.4 | 40.1 | 94.6 | 89.2 |
Value
| r0 | 14% | Assumptions | This tab is tied to 'Proforma' worksheet tab and the 'FCFF and FCFE' tab | |||||||||
| rb | 13.5% | |||||||||||
| Target debt to value ratio | 0.25 | |||||||||||
| WACC Calculation | ||||||||||||
| Cost of Levered Equity, rs | 14.11% | |||||||||||
| WACC | 12.81% | |||||||||||
| Short Horizon | 0 | 1 | 2 | 3 | 4 | 5 | 6 | |||||
| Growth Rates | 0% | 10% | 10% | 10% | 0% | 3% | From 'Proforma' Worksheet | |||||
| FCFF | 75.0 | 30.9 | 34.0 | 37.4 | 99.8 | 92.5 | From FCFF and FCFE worksheet | |||||
| Short period Interest | 6.5 | 7.2 | 7.9 | 8.7 | 8.7 | From 'Proforma' Worksheet | ||||||
| Short period Interest Tax Shield | 2.6 | 2.9 | 3.1 | 3.5 | 3.5 | |||||||
| Terminal Values | ||||||||||||
| Terminal Value at target debt ratio | $943 | |||||||||||
| Unlevered Terminal Value (UTV) | 0 | 0 | 0 | 0 | $841 | |||||||
| Tax-Shields in Terminal Value | 0 | 0 | 0 | 0 | $102 | |||||||
| Present Values | ||||||||||||
| PV0 of short horizon FCFF @ r0 | $186 | |||||||||||
| PV0 of terminal FCFF or PV0 of UTV @ r0 | $437 | |||||||||||
| PV0 of short horizon tax-shields @ rb | $11 | |||||||||||
| PV0 of tax-shields in Terminal Value @ rb | $54 | |||||||||||
| Total Value of Levered Firm | $688 |