There are two worksheet tabs: 1) 'Proforma' tab has proforma data for income and balance sheet. Three color coded zones identify data, assumptions and conclusions. Historical data (Column B) is used to construct the proforma(Columns C to H). You can change assumptions as needed. Play with the proforma spreadsheet to explore its construction. This will help later when we use real data for real companies. 2) 'FCFF and FCFE', tab provides a template to illustrate several alternative approaches to construct FCFF and FCFE. Use data from the 'Proforma' tab to apply this template. To provide you some guideline, I have constructed estimates of FCFE, FCFF, External Financing Needed, LT Debt issued etc. in the 'Proforma' tab. Note: Increase in fixed assets=change in gross assets=change in net fixed assets+current depreciation This is because net fixed assets are net of accumulated depreciation. Old net fixed+increase=new net fixed+current depreciation So increase in assets=change in net fixed+ current depreciation.

Can you please show me cell numbers/steps in Excel?

Historical Data Assumptions Conclusions Sales Growth Payout Ratio Tax Rate Int. Rate Pro Forma Income Statement Year 0 1 2 10% 10% 40.0% 40.0% 40.0% 40.0% 10.0% 10.0% 3 10% 40.0% 40.0% 10.0% 10% 40.0% 40.0% 10.0% 5 10% 40.0% 40.0% 10.0% 6 6 3% 40.0% 34.0% 10.0% Sales CGS SGA ON Depreciation EBIT Interest Expense Profit bef. Taxes 26.7% % of Sales 20.0% % of Sales 13.3% % of Sales % 750.0 200.0 150.0 100.0 300.0 25.0 275.0 110.0 165.0 825.0 220.0 165.0 110.0 330.0 7.2 322.9 129.1 193.7 907.5 242.0 181.5 121.0 363.0 7.9 355.1 142.1, 213.1 998.3 266.2 199.7 133.1 399.3 8.7 390.6 156.3 234.4 1098.1 292.8 219.6 146.4 439.2 9.5 429.7 171.9 257.8 1207.9 322.1 241.6 161.1 483.2 10.5 472.7 189.1 283.6 1244.1 331.8 248.8 165.9 497.6 10.8 486.9 165.5 321.3 10.0% % of LT Debt Taxes Net Income Dividend Payment 85.2 93.8 103.1 113.4 128.5 1 99.0 119.8 1 108.9 72.6 145.2 121.0 338.8 786.5 12.0% % of Sales 8.0% % of Sales 16.0% % of Sales 13.3% % of Sales 37.3% % of Sales 1 149.3 99.5 199.1 165.9 464.5 1078.2 net Cash Mktbl Securities AIR Inventory Fixed Assets(net) Total A/P Oth Curr.Liabilities LT Debt Stockholder's Equity Total FCFF FCFE Addn to Retained Income 77.5 Pro Forma Balance Sheet Year 1 90.0 60.0 66.0 120.0 132.0 100.0 110.0 280.0 308.0 650.0 715.0 84.0 92.4 50.0 250.0 71.5 266.0 496.1 650.0 715.0 1464 -36.4 165.0 116.2 1 131.8 87.8 175.7 146.4 409.9 951.7 123.0 73.2 95.2 660.3 951.7 1 144.9 96.6 193.3 161.1 450.9 1046.8 135.3 80.5 104.7 726.3 1046.8 214.3 79.9 159.7 133.1 372.7 865.2 111.8 66.6 86.5 600.3 865.2 177.1 179.8 140.6 55.0 101.6 60.5 78.7 545.7 786.5 161.0 163.5 11.2% % of Sales 6.7% % of Sales 10% % of Assets 139.3 82.9 107.8 748.1 1078.2 303.5 299.5 194.9 197.8 217.6 170.2 127.8 154.7 192.8 New Equity Isuued LT Debt Issued New External Capital 113.9 -178.5 -64.6 -X8.2 7.2 -71.1 -86.1 7.9 -78.2 -94.7 8.7 -86.0 -104.1 9.5 -94.6 -171.0 3.1 - 167.9 EBIT*(1-T)+ Depreciation - Capital Investments -W/C investments Increase in Equity not funded by Retained Income Net Income + Depreciation- Capital Investments-W/C Investments+ Net Borrowing This tab is tied to 'Proforma' worksheet tab FCFF two methods 1.0 2.0 3.0 4.0 5.0 6.0 EBIT after Tax add back Depreciation subtract increase in WC subtract capital expenditure 198.0 110.0 23.6 217.8 121.0 49.6 239.6 133.1 78.1 263,5 146.4 109.5 289.9 161.1 144.1 328.4 165.9 155.5 213.1 234.4 257.8 283.6 321.3 Net Income add back after tax interest add back Depreciation subtract increase in WC subtract capital expenditure 193.7 0.06 110.0 23.6 121.0 49.6 133.1 78.1 146.4 109.5 161.1 144.1 165.9 155.5 FCFE three methods Net Income add back Depreciation subtract increase in WC subtract capital expenditure add net borrowing 193.7 110.0 23.6 28.0 213.1 121.0 49.6 30.8 234.4 133.1 78.1 33.9 257.8 146.4 109.5 37.2 283.6 161.1 144.1 41.0 321.3 165.9 155.5 13.6 + FCFF subtract after tax interest add net borrowing Dividends add equity repurchases Historical Data Assumptions Conclusions Sales Growth Payout Ratio Tax Rate Int. Rate Pro Forma Income Statement Year 0 1 2 10% 10% 40.0% 40.0% 40.0% 40.0% 10.0% 10.0% 3 10% 40.0% 40.0% 10.0% 10% 40.0% 40.0% 10.0% 5 10% 40.0% 40.0% 10.0% 6 6 3% 40.0% 34.0% 10.0% Sales CGS SGA ON Depreciation EBIT Interest Expense Profit bef. Taxes 26.7% % of Sales 20.0% % of Sales 13.3% % of Sales % 750.0 200.0 150.0 100.0 300.0 25.0 275.0 110.0 165.0 825.0 220.0 165.0 110.0 330.0 7.2 322.9 129.1 193.7 907.5 242.0 181.5 121.0 363.0 7.9 355.1 142.1, 213.1 998.3 266.2 199.7 133.1 399.3 8.7 390.6 156.3 234.4 1098.1 292.8 219.6 146.4 439.2 9.5 429.7 171.9 257.8 1207.9 322.1 241.6 161.1 483.2 10.5 472.7 189.1 283.6 1244.1 331.8 248.8 165.9 497.6 10.8 486.9 165.5 321.3 10.0% % of LT Debt Taxes Net Income Dividend Payment 85.2 93.8 103.1 113.4 128.5 1 99.0 119.8 1 108.9 72.6 145.2 121.0 338.8 786.5 12.0% % of Sales 8.0% % of Sales 16.0% % of Sales 13.3% % of Sales 37.3% % of Sales 1 149.3 99.5 199.1 165.9 464.5 1078.2 net Cash Mktbl Securities AIR Inventory Fixed Assets(net) Total A/P Oth Curr.Liabilities LT Debt Stockholder's Equity Total FCFF FCFE Addn to Retained Income 77.5 Pro Forma Balance Sheet Year 1 90.0 60.0 66.0 120.0 132.0 100.0 110.0 280.0 308.0 650.0 715.0 84.0 92.4 50.0 250.0 71.5 266.0 496.1 650.0 715.0 1464 -36.4 165.0 116.2 1 131.8 87.8 175.7 146.4 409.9 951.7 123.0 73.2 95.2 660.3 951.7 1 144.9 96.6 193.3 161.1 450.9 1046.8 135.3 80.5 104.7 726.3 1046.8 214.3 79.9 159.7 133.1 372.7 865.2 111.8 66.6 86.5 600.3 865.2 177.1 179.8 140.6 55.0 101.6 60.5 78.7 545.7 786.5 161.0 163.5 11.2% % of Sales 6.7% % of Sales 10% % of Assets 139.3 82.9 107.8 748.1 1078.2 303.5 299.5 194.9 197.8 217.6 170.2 127.8 154.7 192.8 New Equity Isuued LT Debt Issued New External Capital 113.9 -178.5 -64.6 -X8.2 7.2 -71.1 -86.1 7.9 -78.2 -94.7 8.7 -86.0 -104.1 9.5 -94.6 -171.0 3.1 - 167.9 EBIT*(1-T)+ Depreciation - Capital Investments -W/C investments Increase in Equity not funded by Retained Income Net Income + Depreciation- Capital Investments-W/C Investments+ Net Borrowing This tab is tied to 'Proforma' worksheet tab FCFF two methods 1.0 2.0 3.0 4.0 5.0 6.0 EBIT after Tax add back Depreciation subtract increase in WC subtract capital expenditure 198.0 110.0 23.6 217.8 121.0 49.6 239.6 133.1 78.1 263,5 146.4 109.5 289.9 161.1 144.1 328.4 165.9 155.5 213.1 234.4 257.8 283.6 321.3 Net Income add back after tax interest add back Depreciation subtract increase in WC subtract capital expenditure 193.7 0.06 110.0 23.6 121.0 49.6 133.1 78.1 146.4 109.5 161.1 144.1 165.9 155.5 FCFE three methods Net Income add back Depreciation subtract increase in WC subtract capital expenditure add net borrowing 193.7 110.0 23.6 28.0 213.1 121.0 49.6 30.8 234.4 133.1 78.1 33.9 257.8 146.4 109.5 37.2 283.6 161.1 144.1 41.0 321.3 165.9 155.5 13.6 + FCFF subtract after tax interest add net borrowing Dividends add equity repurchases