1. Calculate each of the parameters necessary to construct the pro forma balance sheet. (Forecast Year)

2. Construct the pro forma balance sheet. What is the total debt necessary to balance the pro forma balance sheet? (Forecast Year)

3. In this financial planning model, show that it is possible to solve algebraically for the amount of new borrowing. (Forecast Year)

$2.40 $0.36 External Funds needed when operating at full capacity % of Sales Forecast Sales $2 Net Income $0.40 0.2 $0.48 Dividend $0.10 $0.12 Retained Earnings Current Assets $0.40 0.2 $0.48 Fixed Assets $3.60 1.8 Total Assets $4.00 $4.80 Current Liabilities $0.20 $0.24 LTDebt $1 $1 Common Stock Retained earnings $0.80 $1.16 Total Liab + Equity $2.80 External Funds Needed $0.40 $4.32 0.1 $2 $4.40 Chapter 4 Financial Forecast and Cash Flow Analysis Analysis of Cash Flow from Assets and Financial Forecasting Prior Year Forecast Year Sales 780,000 $ 936,000 Costs 53% 415,000 $ 496,080 Depreciation 10% 135,000 $ 161,928 Based on ending year NFA. EBIT 230,000 $ 277,992 Interest 8% 68,000 $ 70,400 Interest on Beginning balance Taxable Income 162,000 $ 207,592 Taxes 34% 55,080 $ 70,581 Net Income 106,920 $ 137,011 Dividends 28% 30,000 $ 38,363 Addition to R.E. 76,920 $ 98,648 Current Assets Net Fixed Assets Total Assets 240,000 1,350,000 1,590,000 $ $ $ 285,755 1,619,280 Driven by Capital Intensity 1,905,035 Driven by TA/Fixed Assets Total Debt Owners Equity Tot. Liab plus OE 880,000 710,000 1,590,000 $ $ $ 1,096,388 Plug 808,648 Driven by Change RE 1,905,035 Cash flow from Assets EBIT Less Tax Plus Depreciation Operating Cash Flow 277,992.0 70,581.3 161,928.0 369,338.72 Change NWC 45,755.29 28 Operating Cash Flow 369,338.72 30 Change NWC 45,755.29 32 Net capital Spend 33 End NFA 34 Beg NFA 35 Plus Depreciation 36 Net Capital Spend 1,619,280.0 1,350,000 161,928.0 431,208.0 38 Cash Flow from Assets $ (107,624.57) 40 Cash Flow Creditors 41 Interest paid $ 42 Change LT Debt 43 Total Cash to Creditors $ 44 70,400.0 (216,387.58) (145,987.58) 45 38,363.0 46 Cash to Shareholders $ 47 48 Cash Flow to Investors $ (107,624.57) 49 Students, try this sensitivity analysis yourselves 51 Sensitivity Analysis 52 1. Assume there is no dividend paid out 53 2. Assume that Fixed Assets is 1.0 times sales $2.40 $0.36 External Funds needed when operating at full capacity % of Sales Forecast Sales $2 Net Income $0.40 0.2 $0.48 Dividend $0.10 $0.12 Retained Earnings Current Assets $0.40 0.2 $0.48 Fixed Assets $3.60 1.8 Total Assets $4.00 $4.80 Current Liabilities $0.20 $0.24 LTDebt $1 $1 Common Stock Retained earnings $0.80 $1.16 Total Liab + Equity $2.80 External Funds Needed $0.40 $4.32 0.1 $2 $4.40 Chapter 4 Financial Forecast and Cash Flow Analysis Analysis of Cash Flow from Assets and Financial Forecasting Prior Year Forecast Year Sales 780,000 $ 936,000 Costs 53% 415,000 $ 496,080 Depreciation 10% 135,000 $ 161,928 Based on ending year NFA. EBIT 230,000 $ 277,992 Interest 8% 68,000 $ 70,400 Interest on Beginning balance Taxable Income 162,000 $ 207,592 Taxes 34% 55,080 $ 70,581 Net Income 106,920 $ 137,011 Dividends 28% 30,000 $ 38,363 Addition to R.E. 76,920 $ 98,648 Current Assets Net Fixed Assets Total Assets 240,000 1,350,000 1,590,000 $ $ $ 285,755 1,619,280 Driven by Capital Intensity 1,905,035 Driven by TA/Fixed Assets Total Debt Owners Equity Tot. Liab plus OE 880,000 710,000 1,590,000 $ $ $ 1,096,388 Plug 808,648 Driven by Change RE 1,905,035 Cash flow from Assets EBIT Less Tax Plus Depreciation Operating Cash Flow 277,992.0 70,581.3 161,928.0 369,338.72 Change NWC 45,755.29 28 Operating Cash Flow 369,338.72 30 Change NWC 45,755.29 32 Net capital Spend 33 End NFA 34 Beg NFA 35 Plus Depreciation 36 Net Capital Spend 1,619,280.0 1,350,000 161,928.0 431,208.0 38 Cash Flow from Assets $ (107,624.57) 40 Cash Flow Creditors 41 Interest paid $ 42 Change LT Debt 43 Total Cash to Creditors $ 44 70,400.0 (216,387.58) (145,987.58) 45 38,363.0 46 Cash to Shareholders $ 47 48 Cash Flow to Investors $ (107,624.57) 49 Students, try this sensitivity analysis yourselves 51 Sensitivity Analysis 52 1. Assume there is no dividend paid out 53 2. Assume that Fixed Assets is 1.0 times sales