Suppose that Bloopers customers paid their bills with an average 3-month delay (instead of 2 months) and that Bloopers inventories were 20% rather than 15% of next years expenses. Use the below Blooper spreadsheet.
See SPREADSHEET 9.1 in the eBook for the baseline project evaluation and resulting NPV assuming 15% inventory expense SPREADSHEET 9.1 Financial projections for Blooper's Magnoosium mine (dollar values in thousands)
| Inputs | | Spreadsheet Name |
| Initial Investment | 12,500 | Investment |
| Salvage value | 2,500 | Salvage |
| Initial revenue | 22,000 | Initial_rev |
| Initial expenses | 11,000 | Intial_exp |
| Inflation rate | 0.05 | Inflation |
| Discount rate | 0.12 | Disc_rate |
| Acct receiv. as % of sales | 1/4 | A_R |
| Inven. as % of expenses | 0.2 | Inv_pct |
| Tax rate | 0.21 | Tax_rate |
SPREADSHEET 9.1 Financial projections for Blooper's Magnoosium mine (dollar values in thousands) A. Inputs Initial Investment 12,500 Investment Salvage value 2,500 Salvage Initial revenue 22,000 Initial_rev Initial expenses 11,000 Intial exp Inflation rate 5% Inflation Discount rate 12% Disc_rate Acct receiv. as % of sales 16.7% AR Inven. as % of expenses Iny pct Tax rate 21% Tax_rate 15% 0 1 2 3 4 5 6 Year: B. Fixed assets Investments in fixed assets Sales of fixed assets CF, invest in fixed assets 12,500 1,975 1,975 -12,500 0 0 0 C. Operating cash flow Revenues Expenses Depreciation Pretax profit Tax Profit after tax Cash flow from operations 22,000 11,000 2.500 8,500 1,785 6,715 9,215 23,100 11,550 2.500 9,050 1.901 7,149 9,649 24,255 12,128 2.500 9,627 2.022 7,605 10,105 25,468 12,734 2.500 10,234 2.149 8,085 10,585 26,741 13,371 2.500 10,870 2.283 8,587 11,087 D. Working capital Working capital Change in working capital CF, invest in wk capital 2.200 2,200 -2,200 7,810 5,610 -5,610 8,201 391 -391 8,611 410 410 9,041 430 430 6,685 -2,356 2,356 0 -6,685 6,685 E. Project valuation Total project cash flow Discount factor PV of cash flow Net present value -14,700 1 -14,700 21,269 3,605 0.8929 3,219 9,258 0.7972 7,381 9,695 0.7118 6,901 10,155 0.6355 6,453 13,443 0.5674 7,628 8,660 0.5066 4,387 SPREADSHEET 9.1 Financial projections for Blooper's Magnoosium mine (dollar values in thousands) A. Inputs Initial Investment 12,500 Investment Salvage value 2,500 Salvage Initial revenue 22,000 Initial_rev Initial expenses 11,000 Intial exp Inflation rate 5% Inflation Discount rate 12% Disc_rate Acct receiv. as % of sales 16.7% AR Inven. as % of expenses Iny pct Tax rate 21% Tax_rate 15% 0 1 2 3 4 5 6 Year: B. Fixed assets Investments in fixed assets Sales of fixed assets CF, invest in fixed assets 12,500 1,975 1,975 -12,500 0 0 0 C. Operating cash flow Revenues Expenses Depreciation Pretax profit Tax Profit after tax Cash flow from operations 22,000 11,000 2.500 8,500 1,785 6,715 9,215 23,100 11,550 2.500 9,050 1.901 7,149 9,649 24,255 12,128 2.500 9,627 2.022 7,605 10,105 25,468 12,734 2.500 10,234 2.149 8,085 10,585 26,741 13,371 2.500 10,870 2.283 8,587 11,087 D. Working capital Working capital Change in working capital CF, invest in wk capital 2.200 2,200 -2,200 7,810 5,610 -5,610 8,201 391 -391 8,611 410 410 9,041 430 430 6,685 -2,356 2,356 0 -6,685 6,685 E. Project valuation Total project cash flow Discount factor PV of cash flow Net present value -14,700 1 -14,700 21,269 3,605 0.8929 3,219 9,258 0.7972 7,381 9,695 0.7118 6,901 10,155 0.6355 6,453 13,443 0.5674 7,628 8,660 0.5066 4,387
