finance
yugigogo09
ExcelModelCleanCo.xlsxAssumptions
| ASSUMPTIONS | ||||||
| Clean Co | Hist | Hist | Proj | |||
| 2014 | 2015 | 2016 | 2017 | 2018 | ||
| Sales Growth | 5% | 5% | 5% | |||
| Cost of Goods Sold (COGS) as % of Sales | 61% | 61% | 61% | |||
| Depreciation as % of Beginning PPE | 27% | 27% | 27% | |||
| Selling General & Administrative % of Sales | 10% | 10% | 10% | |||
| Tax Rate | 30.0% | 30.0% | 30.0% | |||
| Dividend Growth | 3.0% | 3.0% | 3.0% | |||
| Accounts Receivable % of Sales | 8% | 8% | 8% | |||
| Inventories % of COGS | 36% | 36% | 36% | |||
| Accounts Payable % of COGS | 7% | 7% | 7% | |||
| Capital Expenditures % of Sales | 15% | 15% | 15% | |||
| Existing Debt - assume unchanged |
Calcs
| CALCS | ||||||
| Clean Co | Hist | Hist | Proj | Proj | Proj | |
| 2014 | 2015 | 2016 | 2017 | 2018 | ||
| Beginning Net PPE | 110.0 | |||||
| Plus Capex | 150.0 | |||||
| Minus Depreciation | (30.0) | |||||
| Ending Net PPE | 230.0 | |||||
| Beginning Retained Earnings | 190.0 | |||||
| Plus Net Income | 185.0 | |||||
| Minus Dividends | (28.0) | |||||
| Ending Retained Earnings | 347.0 |
IncStat
| Income Statement | ||||||
| Clean Co | Hist | Hist | Proj | Proj | Proj | |
| 2014 | 2015 | 2016 | 2017 | 2018 | ||
| Income Statement | ||||||
| Sales | 1,000.0 | 1,050.0 | ||||
| COGS ex-Dep | (600.0) | (650.0) | ||||
| Depreciation | (25.0) | (30.0) | ||||
| Gross Margin | 375.0 | 370.0 | ||||
| SG&A | (100.0) | (105.0) | ||||
| Pretax Profit | 275.0 | 265.0 | ||||
| Taxes | (85.0) | (80.0) | ||||
| Net Income | 190.0 | 185.0 | ||||
| Total Dividends | 27.0 | 28.0 | ||||
BalSheet
| BALANCE SHEET | ||||||
| Balance? | YES | YES | YES | YES | YES | |
| Clean Co | Hist | Hist | Proj | Proj | Proj | |
| 2014 | 2015 | 2016 | 2017 | 2018 | ||
| Assets | ||||||
| PROJECTED EXCESS CASH | ||||||
| Cash | 30.0 | 50.0 | ||||
| A/R | 90.0 | 85.0 | ||||
| Inventories | 220.0 | 235.0 | ||||
| Total Current Assets | 340.0 | 370.0 | ||||
| Gross PPE | 290.0 | 440.0 | ||||
| Accum Depreciation | (180.0) | (210.0) | ||||
| Net PPE | 110.0 | 230.0 | ||||
| Total Assets | 450.0 | 600.0 | ||||
| Liabs & Equity | ||||||
| Current Liabs | ||||||
| PROJECTED BORROWING | ||||||
| Accounts Payable | 45.0 | 48.0 | ||||
| Total Current Liabs | 45.0 | 48.0 | ||||
| Long-Term Liabs | ||||||
| Long-Term Debt | 190.0 | 180.0 | ||||
| Total Liabs | 235.0 | 228.0 | ||||
| Equity | ||||||
| Common Stock | 25.0 | 25.0 | ||||
| Retained Earnings | 190.0 | 347.0 | ||||
| Total Equity | 215.0 | 372.0 | ||||
| Total Liabs & Equity | 450.0 | 600.0 |
Cashflow
| CASHFLOW | ||||||
| Clean Co | Hist | Hist | Proj | Proj | Proj | |
| 2014 | 2015 | 2016 | 2017 | 2018 | ||
| Cashflow From Operations | ||||||
| Net Income | 185.0 | |||||
| Depreciation | 30.0 | |||||
| Change in A/R | 5.0 | |||||
| Change in Inv | (15.0) | |||||
| Change in A/P | 3.0 | |||||
| Total Cash from Operations | 208.0 | |||||
| Cashflow from Investing | ||||||
| Capital Expenditures | (150.0) | |||||
| Total Cash from Investing | (150.0) | |||||
| Cashflow from Financing | ||||||
| Increase (Decrease) in LTD | (10.0) | |||||
| Dividends | (28.0) | |||||
| Total Cash from Financing | (38.0) | |||||
| Net Cashflow | 20.0 | |||||
| Beginning Cash | 30.0 | |||||
| Additions to Cash | 20.0 | |||||
| Ending Cash | 50.0 |
