Blooper’s analysts have come up with the following revised estimates for its mag
ID: 2790581 • Letter: B
Question
Blooper’s analysts have come up with the following revised estimates for its magnoosium mine:
Conduct a sensitivity analysis for each variable and range and compute the NPV for each. Use Spreadsheet 10.1 and accompanying data as a starting point for the analysis. (Do not round intermediate calculations. Negative amounts should be indicated by a minus sign. Enter your answers in thousands rounded to the nearest whole dollar.))
Fixed cost
Working capital
Range Pessimistic Optimistic Initial investment -25% -15% Revenue -20% +25% Variable cost +15% -20% Fixed Cost +40% -45% Working capital +50% -20%Explanation / Answer
A.Inputs Initial Investments ($ Thousands) 10,000 30% -15% Salvage Value( $ Thousands) 2,000 Initial Revenue ($ Thoisands) 15,000 -20% 25% Variable Cost (% of Revenue) 40.0% 15% -15% Initial Fixed Costs ($ Thousands) 4,000 40% -35% Initial Total Expenses ($ Thousands) 10,000 Inflation Rate (%) 5.0% Discount rate (%) 12.0% Receivables (% of Sales) 16.7% 45% -40% Inventory (% of Next years's costs) 15.0% 45% -40% Tax Rate (%) 35.0% Working Capital 1,500 45% -40% Year: 0.0% 1 2 3 4 5 B. Fixed Assets Investment in Fixed Assets 10,000 Sales of Fixed Assets Cash Flow From Fixed Assets (10,000) C. Operating Cash Flow Revenue 15,000 15,750 16,538 17,364 18,233 Variable Expense 6,000 6,300 6,615 6,946 7,293 Fixed Expenses 4,000 4,200 4,410 4,631 4,862 Depreciation 2,000 2,000 2,000 2,000 2,000 Pretax Profit 3,000 3,250 3,513 3,788 4,078 Tax 1,050 1,138 1,229 1,326 1,427 Profit After Tax 1,950 2,113 2,283 2,462 2,650 Operating Cash Flow 3,950 4,113 4,283 4,462 4,650 D. Working Capital Working Capital 1,500 4,080 4,284 4,498 4,723 3,045 Change in Working Capital 1,500 2,580 204 214 225 (1,678) Cash Flow From Investment in Working Capital (1,500) (2,580) (204) (214) (225) 1,678 E. Project Evalution Total Project cash Flow (11,500) 1,370 3,909 4,069 4,237 6,329 F. Real Inflow (11,500) 1,305 3,722 3,875 4,036 6,027 G. NPV Discount Factor at real rate 1 0.8503 0.7231 0.6149 0.5228 0.4446 Initial Investment (10,000) (10,000) Revenue 12,755 11,388 10,168 9,079 8,106 51,497 Variable Cost 5,102 4,555 4,067 3,632 3,242 20,599 Fixed Cost 3,401 3,037 2,712 2,421 2,162 13,732 Working Capital (1,500) (2,194) (148) (132) (118) 746 (3,345) Salvage Value 889 889 Pessimistic Approch A.Inputs Initial Investments ($ Thousands) 13,000 Salvage Value( $ Thousands) 2,000 Initial Revenue ($ Thoisands) 12,000 Variable Cost (% of Revenue) 46.0% Initial Fixed Costs ($ Thousands) 5,600 Initial Total Expenses ($ Thousands) 13,000 Inflation Rate (%) 5.0% Discount rate (%) 12.0% Receivables (% of Sales) 24.2% Inventory (% of Next years's costs) 21.8% Tax Rate (%) 35.0% Working Capital 2,175 Year: 0.0% 1 2 3 4 5 B. Fixed Assets Investment in Fixed Assets 13,000 Sales of Fixed Assets Cash Flow From Fixed Assets (13,000) C. Operating Cash Flow Revenue 12,000 12,600 13,230 13,892 14,586 Variable Expense 5,520 5,796 6,086 6,390 6,710 Fixed Expenses 5,600 5,880 6,174 6,483 6,807 Depreciation 2,600 2,600 2,600 2,600 2,600 Pretax Profit (1,720) (1,676) (1,630) (1,581) (1,530) Tax -602 -587 -570 -553 -536 Profit After Tax (1,118) (1,089) (1,059) (1,028) (995) Operating Cash Flow 1,482 1,511 1,541 1,572 1,605 D. Working Capital Working Capital 2,175 5,445 5,718 6,003 6,304 3,532 Change in Working Capital 2,175 3,270 272 286 300 (2,772) Cash Flow From Investment in Working Capital (2,175) (3,270) (272) (286) (300) 2,772 E. Project Evalution Total Project cash Flow (15,175) (1,788) 1,238 1,255 1,272 4,377 F. Real Inflow (15,175) (1,703) 1,179 1,195 1,211 4,168 G. NPV Discount Factor at real rate 1 0.8503 0.7231 0.6149 0.5228 0.4446 Initial Investment (13,000) (13,000) Revenue 10,204 9,111 8,135 7,263 6,485 41,197 Variable Cost 4,694 4,191 3,742 3,341 2,983 18,951 Fixed Cost 4,762 4,252 3,796 3,389 3,026 19,225 Working Capital (2,175) (2,781) (197) (176) (157) 1,232 (4,253) Salvage Value 889 889 Optimistic Approch A.Inputs Initial Investments ($ Thousands) 8,500 Salvage Value( $ Thousands) 2,000 Initial Revenue ($ Thoisands) 18,750 Variable Cost (% of Revenue) 34.0% Initial Fixed Costs ($ Thousands) 2,600 Initial Total Expenses ($ Thousands) 8,500 Inflation Rate (%) 5.0% Discount rate (%) 12.0% Receivables (% of Sales) 10.0% Inventory (% of Next years's costs) 9.0% Tax Rate (%) 35.0% Working Capital 900 Year: 0.0% 1 2 3 4 5 B. Fixed Assets Investment in Fixed Assets 8,500 Sales of Fixed Assets Cash Flow From Fixed Assets (8,500) C. Operating Cash Flow Revenue 18,750 19,688 20,672 21,705 22,791 Variable Expense 6,375 6,694 7,028 7,380 7,749 Fixed Expenses 2,600 2,730 2,867 3,010 3,160 Depreciation 1,700 1,700 1,700 1,700 1,700 Pretax Profit 8,075 8,564 9,077 9,616 10,182 Tax 2,826 2,997 3,177 3,366 3,564 Profit After Tax 5,249 5,566 5,900 6,250 6,618 Operating Cash Flow 6,949 7,266 7,600 7,950 8,318 D. Working Capital Working Capital 900 2,727 2,863 3,006 3,157 2,284 Change in Working Capital 900 1,827 136 143 150 (873) Cash Flow From Investment in Working Capital (900) (1,827) (136) (143) (150) 873 E. Project Evalution Total Project cash Flow (9,400) 5,122 7,130 7,457 7,800 9,191 F. Real Inflow (9,400) 4,878 6,791 7,102 7,429 8,753 G. NPV Discount Factor at real rate 1 0.8503 0.7231 0.6149 0.5228 0.4446 Initial Investment (8,500) (8,500) Revenue 15,944 14,236 12,710 11,349 10,133 64,371 Variable Cost 5,421 4,840 4,322 3,859 3,445 21,886 Fixed Cost 2,211 1,974 1,763 1,574 1,405 8,926 Working Capital (900) (1,553) (99) (88) (79) 388 (2,331) Salvage Value 889 889 Project NPV Pessimistic Expected Optimistic Initial Investment (13,000) (10,000) (8,500) Revenue 41,197 51,497 64,371 Variable Cost 18,951 20,599 21,886 Fixed Cost 19,225 13,732 8,926 Working Capital (4,253) (3,345) (2,331) Salvage Value 889 889 889