Blooper's analysts have come up with the following revised estimates for its magnooslum mine. Initial Investment Revenues Variable costs Fixed cost Working capital Range PESTISC Optimistic 45% - 25% 15% 25% 25% - 25% 2e - 25% 2ent 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) Pessimistic Project NPV Expected Optimistic Initial investment Revenues Variable costs Forced costs Working capital > Clipboard Styles ideas AL C D E F G H 10,000 2,000 15,000 40.0% 4,000 5.0% 12.0% 16.7% 15.0% 21.0% B A. Inputs 2 Initial investment (Sthousands) 3 Salvage value (Sthousands) 4 Initial revenues (Sthousands) 5 Variable costs of revenues) 6 Initial fixed costs (S thousands) 7 Inflation rate (%) 8 Discount rate(%) 9 Receivables (% of sales) 10 Inventory % of next year's costs) 11 Tax rate) 12 13 14 B. Fixed assets 15 investments in fixed assets 16 Sales of fixed assets 17 Cash flow from fixed assets 18 19. C. Operating cash flow 20 Revenues 21 Variable expenses 22 Fixed expenses 23 Depreciation 24 Pretax profit 25 Tax 26 Profit after tax 27 Operating cash flow Sheet1 Year: 0 1 2 3 4 5 6 10,000 -10,000 1.580 1,580 15,000 6,000 4,000 2.000 3,000 630 2,370 4,370 25,750 6,300 4,200 2.000 3,250 683 2.568 4,568 16,538 6,615 4,410 2.000 3,513 738 2.775 17,364 6,946 4,631 2.000 3,788 796 2,993 4.993 18,233 7,293 4,862 2,000 4,078 856 3,221 5,221 100 Help % Paste Font Alignment Conditional Formatting Format as Table Cell Styles Number Cells Editing Ideas Clipboard Styles Ideas A1 fo D E F G H 1,500 1,500 -1,500 4,075 2.575 -2,575 0.408 4,717 225 B 28 29 D. Working capital 30 Working capital 31 Change in working capital 32 Cash flow from investment in working capital 33 34 E. Project valuation 35 Total project cash flow 36 Discount factor 37 PV of cash flow 38 Net present value 39 40 4,279 204 -204 0.408 4,493 214 -214 0.408 -225 3,039 -1,679 1,679 0.250 -3,039 3,039 0.408 -11,500 1.000 -11,500 6,113.1 1,795 0.893 1,603 4,364 0.797 3,479 4,561 0.712 3,246 4,768 0.636 3,030 6,900 0.567 3,915 4,619 0.507 2,340 41 Blooper's analysts have come up with the following revised estimates for its magnooslum mine. Initial Investment Revenues Variable costs Fixed cost Working capital Range PESTISC Optimistic 45% - 25% 15% 25% 25% - 25% 2e - 25% 2ent 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) Pessimistic Project NPV Expected Optimistic Initial investment Revenues Variable costs Forced costs Working capital > Clipboard Styles ideas AL C D E F G H 10,000 2,000 15,000 40.0% 4,000 5.0% 12.0% 16.7% 15.0% 21.0% B A. Inputs 2 Initial investment (Sthousands) 3 Salvage value (Sthousands) 4 Initial revenues (Sthousands) 5 Variable costs of revenues) 6 Initial fixed costs (S thousands) 7 Inflation rate (%) 8 Discount rate(%) 9 Receivables (% of sales) 10 Inventory % of next year's costs) 11 Tax rate) 12 13 14 B. Fixed assets 15 investments in fixed assets 16 Sales of fixed assets 17 Cash flow from fixed assets 18 19. C. Operating cash flow 20 Revenues 21 Variable expenses 22 Fixed expenses 23 Depreciation 24 Pretax profit 25 Tax 26 Profit after tax 27 Operating cash flow Sheet1 Year: 0 1 2 3 4 5 6 10,000 -10,000 1.580 1,580 15,000 6,000 4,000 2.000 3,000 630 2,370 4,370 25,750 6,300 4,200 2.000 3,250 683 2.568 4,568 16,538 6,615 4,410 2.000 3,513 738 2.775 17,364 6,946 4,631 2.000 3,788 796 2,993 4.993 18,233 7,293 4,862 2,000 4,078 856 3,221 5,221 100 Help % Paste Font Alignment Conditional Formatting Format as Table Cell Styles Number Cells Editing Ideas Clipboard Styles Ideas A1 fo D E F G H 1,500 1,500 -1,500 4,075 2.575 -2,575 0.408 4,717 225 B 28 29 D. Working capital 30 Working capital 31 Change in working capital 32 Cash flow from investment in working capital 33 34 E. Project valuation 35 Total project cash flow 36 Discount factor 37 PV of cash flow 38 Net present value 39 40 4,279 204 -204 0.408 4,493 214 -214 0.408 -225 3,039 -1,679 1,679 0.250 -3,039 3,039 0.408 -11,500 1.000 -11,500 6,113.1 1,795 0.893 1,603 4,364 0.797 3,479 4,561 0.712 3,246 4,768 0.636 3,030 6,900 0.567 3,915 4,619 0.507 2,340 41