| | | 0 | 1 | 2 | 3 | 4 | 5 |
| Sensitivity |
| Market Size | | 0% | 0% | 0% | 0% | 0% | 0% |
| Market Share | | 0% | 0% | 0% | 0% | 0% | 0% |
| Price | | 0% | 0% | 0% | 0% | 0% | 0% |
| Cost of Goods Sold | | 0% | 0% | 0% | 0% | 0% | 0% |
| Research and Development | | 0% |
| Total Available Market | | 400,000,000 | 400,000,000 | 400,000,000 | 400,000,000 | 400,000,000 | 400,000,000 |
| Target Market Segment | | 15% | 15% | 15% | 15% | 15% | 15% |
| Company Share of Market Segment | | 0% | 2.0% | 4.0% | 5.0% | 3.0% | 0.0% |
| Estimated units volume | | - 0 | 1,200,000 | 2,400,000 | 3,000,000 | 1,800,000 | - 0 |
| Estimated Selling Price / Unit | | $70.00 | $70.00 | $70.00 | $70.00 | $70.00 | $70.00 |
| INCOME STATEMENT | % of Sales |
| Revenue | | - 0 | 84,000,000 | 168,000,000 | 210,000,000 | 126,000,000 | - 0 |
| Cost of Goods Sold | 40% | - 0 | 33,600,000 | 67,200,000 | 84,000,000 | 50,400,000 | - 0 |
| Depreciation (3 year MACRS) | | | 41,250,000 | 56,250,000 | 18,750,000 | 8,750,000 | - 0 |
| Total Cost of goods sold | | | 74,850,000 | 123,450,000 | 102,750,000 | 59,150,000 | - 0 |
| Gross Margin | | - 0 | 9,150,000 | 44,550,000 | 107,250,000 | 66,850,000 | - 0 |
| Research & Development | | 35,000,000 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Sales & Marketing | 10% | - 0 | 8,400,000 | 16,800,000 | 21,000,000 | 12,600,000 | - 0 |
| General & Administrative | 5% | - 0 | 4,200,000 | 8,400,000 | 10,500,000 | 6,300,000 | - 0 |
| Operating Expense | | 35,000,000 | 12,600,000 | 25,200,000 | 31,500,000 | 18,900,000 | - 0 |
| Operating Margin | | (35,000,000) | (3,450,000) | 19,350,000 | 75,750,000 | 47,950,000 | - 0 |
| Taxes | 35% | (12,250,000) | (1,207,500) | 6,772,500 | 26,512,500 | 16,782,500 | - 0 |
| Profit After Tax | | (22,750,000) | (2,242,500) | 12,577,500 | 49,237,500 | 31,167,500 | - 0 |
| BALANCE SHEET |
| A/R | 10% | - 0 | 8,400,000 | 16,800,000 | 21,000,000 | 12,600,000 | - 0 |
| Inventory | 15% | - 0 | 12,600,000 | 25,200,000 | 31,500,000 | 18,900,000 | - 0 |
| Current Assets | | - 0 | 21,000,000 | 42,000,000 | 52,500,000 | 31,500,000 | - 0 |
| Gross PP&E | | 125,000,000 | 125,000,000 | 125,000,000 | 125,000,000 | 125,000,000 | 125,000,000 |
| MACRS 3 year schedule | | | 33.0% | 45.00% | 15.00% | 7.00% |
| Annual Depreciation | | | 41,250,000 | 56,250,000 | 18,750,000 | 8,750,000 | - 0 |
| Accumulated Deprecaition | | | 41,250,000 | 97,500,000 | 116,250,000 | 125,000,000 | 125,000,000 |
| Net PP&E | | 125,000,000 | 83,750,000 | 27,500,000 | 8,750,000 | - 0 | - 0 |
| Total Assets | | 125,000,000 | 104,750,000 | 69,500,000 | 61,250,000 | 31,500,000 | - 0 |
| A/P | 8% | - 0 | 6,720,000 | 13,440,000 | 16,800,000 | 10,080,000 | - 0 |
| Current Liabilities | | - 0 | 6,720,000 | 13,440,000 | 16,800,000 | 10,080,000 | - 0 |
| Retained Earnings | | (22,750,000) | (24,992,500) | (12,415,000) | 36,822,500 | 67,990,000 | 67,990,000 |
| Memo: Net Working Capital Required | | - 0 | 14,280,000 | 28,560,000 | 35,700,000 | 21,420,000 | - 0 |
| FREE CASH FLOW (FCF) |
| OCF (EBIT less taxes plus non cash items) | | (22,750,000) | 39,007,500 | 68,827,500 | 67,987,500 | 39,917,500 | - 0 |
| Investment in NWC (change in NWC from prior period) | | - 0 | 14,280,000 | 14,280,000 | 7,140,000 | (14,280,000) | (21,420,000) |
| PPE Expenditures (change in LT Assets from prior period) | | 125,000,000 | - 0 | - 0 | - 0 | - 0 | (6,500,000) |
| Free Cash Flow (FCF) | | (147,750,000) | 24,727,500 | 54,547,500 | 60,847,500 | 54,197,500 | 27,920,000 |
| DECISION CRITERIA |
| Payback | 3.14 | | (123,022,500) | (68,475,000) | (7,627,500) | 46,570,000 | 74,490,000 |
| NPV | 19,879,496 |
| IRR | 15.0% |
| MRR | 12.8% |
| Profitability Index | 1.13 | (147,750,000) | 22,479,545 | 45,080,579 | 45,715,627 | 37,017,622 | 17,336,123 |
| WACC | 10% |
Fin319
capitalbudetingexample.xlsx
