using excel for calculation
meadow_71
ch9example.xlsx9-1 Example
| 9.1 | ||||
| 2015 | ||||
| 2015 Sales | $ 8,000,000 | |||
| Sales increase | 15% | |||
| 2016 Sales | $ 9,200,000 | |||
| 2015 Total Assets | $ 5,000,000 | |||
| Assets grow at same rate as sales… | ||||
| 2015 Liabilities | $ 1,400,000 | |||
| 2015 Accounts Payable | $ 450,000 | <-- Spontaneous liabilites | ||
| 2015 Notes Payable | $ 500,000 | |||
| Accruals | $ 450,000 | <--Spontaneous liabilities | ||
| Forecast After Tax Profit Margin | 6% | |||
| Forecast payout ratio | 40% | |||
| Need AFN Formula pg 367 | ||||
| AFN = Required increase in assets - increase in spontaneous liabilities - increase in retained earnings | ||||
| Required increase in assets = (Assets0/sales0)*change in sales | ||||
| increase in spontaneous liabilities = (spontaneous liabilities0/sales0)* change in sales | ||||
| increase in retained earnings = sales1 * profit Margin * (1-payout ratio) | <- important note, sales here is projected sales… | |||
| Calculated Values | ||||
| Change in Sales | $ 1,200,000 | |||
| year 0 Asset/Sales | 62.50% | |||
| Required Increase to Assets | $ 750,000 | |||
| Spontaneous Liabilites | $ 900,000 | |||
| Year 0 Spontaneous Liabilities/Sales | 11.25% | |||
| Increase in spontaneous liabilities | $ 135,000 | |||
| Increase in retained earnings | $ 331,200 | |||
| AFN | $ 283,800 |
9-8Example
| Balance Sheet | ||||||||||||
| 2015 Actual | ||||||||||||
| ASSETS | ||||||||||||
| Cash | $ 1,080 | |||||||||||
| Receivables | 6,480 | |||||||||||
| Inventories | 9,000 | |||||||||||
| Total Current Asets | $ 16,560 | |||||||||||
| Net Fixed Assets | 12,600 | |||||||||||
| Total Assets | $ 29,160 | |||||||||||
| LIABILITIES AND EQUITY | ||||||||||||
| Accounts Payable | $ 4,320 | |||||||||||
| Accruals | 2,880 | |||||||||||
| Line of Credit | - 0 | |||||||||||
| Notes Payable | 2,100 | |||||||||||
| Total Current Liabilities | $ 9,300 | |||||||||||
| Mortgage Bonds | 3,500 | |||||||||||
| Common Stock | 3,500 | |||||||||||
| Retained Earnings | 12,860 | |||||||||||
| Total Liabilties and Equity | $ 29,160 | |||||||||||
| Step 1 Pro-forma Income statement | ||||||||||||
| 2015 Actual | 2016 Forecast Basis pct | Pro-forma 2016 | Comment | |||||||||
| Income Statement | ||||||||||||
| Sales | $ 36,000 | 115.00% dm: dm: 15% sales growth given | $ 41,400 | Sales growth of 15% | ||||||||
| Operating Costs | 32,440 | 90.11% dm: dm: Operating Costs as pct of sales to remain constant | 37,306 | Operating Costs/Sales remain the same | ||||||||
| EBIT | $ 3,560 | $ 4,094 | ||||||||||
| Interest | 460 | 10.00% dm: dm: 10% interest on debt at end of last year given | 560 | 10% of YE 2015 Debt (Bonds+Notes Payable) | ||||||||
| Pre-tax Earnings | $ 3,100 | $ 3,534 | ||||||||||
| Taxes (40%) | 1,240 | 40.00% dm: dm: Tax rate assumed to be unchanged | $ 1,414 | |||||||||
| Net Income | $ 1,860 | $ 2,120 | ||||||||||
| Dividends | $ 837 | 45% dm: dm: Tax rate assumed to be unchanged | $ 954 | |||||||||
| Addition to Retained Earnings | $ 1,023 | $ 1,166 | <---A | |||||||||
| Step 2 Pro-forma Balance sheet | ||||||||||||
| Balance Sheet | ||||||||||||
| 12/31/15 | 2016 Forecast Basis pct of Sales | Additions | 2016 Pro Forma Before Financing | Financing | 2016 Pro Forma Before Financing | |||||||
| ASSETS | ||||||||||||
| Cash | $ 1,080 | 3.00% | $ 1,242 | $ 1,242 | ||||||||
| Receivables | 6,480 | 18.00% | 7,452 | 7,452 | ||||||||
| Inventories | 9,000 | 25.00% | 10,350 | 10,350 | ||||||||
| Total Current Asets | $ 16,560 | $ 19,044 | $ 19,044 | |||||||||
| Net Fixed Assets | 12,600 | 35.00% | 14,490 | 14,490 | ||||||||
| Total Assets | $ 29,160 | $ 33,534 | $ 33,534 | |||||||||
| LIABILITIES AND EQUITY | ||||||||||||
| Accounts Payable | $ 4,320 | 12.00% | $ 4,968 | $ 4,968 | ||||||||
| Accruals | 2,880 | 8.00% | 3,312 | 3,312 | ||||||||
| Line of Credit | - 0 | - 0 dm: dm: We leave financing at last years levels for this step. | $ 2,128 dm: dm: Source of financing Line of Credit per book. | 2,128 | <---B | |||||||
| Notes Payable | 2,100 | 2,100 dm: dm: We leave financing at last years levels for this step. | 2,100 | |||||||||
| Total Current Liabilities | $ 9,300 | $ 10,380 | $ 12,508 | |||||||||
| Mortgage Bonds | 3,500 | 3,500 | 3,500 | |||||||||
| Common Stock | 3,500 | 3,500 | 3,500 | |||||||||
| Retained Earnings | 12,860 | $ 1,166 dm: dm: This is year-end projection so we add forecast 2016 addition to retained earnings | $ 14,026 | 14,026 | ||||||||
| Total Liabilties and Equity | $ 29,160 | $ 31,406 | $ 33,534 | |||||||||
| Deficit (Surplus) | $ 2,128 dm: dm: This is the amount of extra financing we need. Problems says this will be funded through Line of Credit. If it was a surplus we would either pay down financing or increase dividend payout. |
dm: dm: Tax rate assumed to be unchanged | $ - 0 dm: dm: This is the amount of extra financing we need. Problems says this will be funded through Line of Credit. If it was a surplus we would either pay down financing or increase dividend payout. |
|||||||||
|
dm: dm: We leave financing at last years levels for this step. |
dm: dm: Source of financing Line of Credit per book. |
dm: dm: We leave financing at last years levels for this step. |
dm: dm: 15% sales growth given |
dm: dm: Operating Costs as pct of sales to remain constant |
dm: dm: This is year-end projection so we add forecast 2016 addition to retained earnings |
dm: dm: 10% interest on debt at end of last year given | C | If new debt was added throughout the year, interest expense would be higher. | ||||
| This would reduce net income, which would then decrease projected net income. | ||||||||||||
| Reduced Net Income would reduce retained earnings. | ||||||||||||
| Reduced retained earnings would require an increase to financing |
Use growth rate in sales given, and assumed operating ratios to develop Pro-forma Income statement
Use growth rate in sales to project assets needed to support those sales.
