Blooper’s analysts have come up with the following revised estimates for its mag
ID: 2790873 • Letter: B
Question
Blooper’s analysts have come up with the following revised estimates for its magnoosium mine:
Range
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.)
Range
Pessimistic Optimistic Initial investment + 30 % – 25 % Revenues – 25 % + 20 % Variable costs + 25 % – 15 % Fixed cost + 50 % – 50 % Working capital + 30 % – 45 % Initial investment (S thousands) Salvage value ($ thousands) Variable costs (% of revenues) Initial fixed costs (S thousands) Initial total expenses (5 thousands) Receivables (% of sales) Inventory (% of next year's costs) Year: Investments in fixed assets Cash flow from fixed assets C. Operating cash flow 15,00015,750 16,538 17,364 18,233 2,000,02,000 2,000 2,000 Change in working capital Cash flow from investment in working capital 225 1,679-3,039 11,5001,3753,9094,069 4,2386,329 4,339Explanation / Answer
Pessimistic Scenario
A. Inputs
Initial Investment ($ thousands)
13,000
Salvage Value ($ thousands)
2,000
Initial Revenue ($ thousands)
11,250
Variable Costs (% of Revenues)
50%
Initial fixed costs($ thousands)
6,000
Initial total expense ($ thousands)
11,625
Inflation Rate
5%
Discount Rate
12%
Receivables (% of Sales)
16.70%
Inventory (% of next year's costs)
15%
Tax Rate
35%
Year
0
1
2
3
4
5
6
B. Fixed Assets
Investment in fixed assets
13,000
Sales of fixed assets
1,300
Cash flow from fixed assets
- 13,000
1,300
C. Operating Cash flow
Revenues
11,250
11,813
12,403
13,023
13,674
Variable Expenses
5,625
5,906
6,202
6,512
6,837
Fixed Expenses
6,000
6,300
6,615
6,946
7,293
Depreciation
2,600
2,600
2,600
2,600
2,600
Pre-tax Profit
- 2,975
- 2,994
- 3,013
- 3,034
- 3,056
Tax
-
-
-
-
-
Profit after tax
- 2,975
- 2,994
- 3,013
- 3,034
- 3,056
Operating Cash flow
- 375
- 394
- 413
- 434
- 456
D. Working Capital
Working Capital
2,267
4,171
4,355
4,548
4,750
2,962
-
Change in working capital
2,267
1,905
184
193
202
- 1,788
- 2,962
Cash flow from investment in working capital
- 2,267
- 1,905
- 184
- 193
- 202
1,788
2,962
0.359
0.357
0.355
0.353
0.210
E. Project Valuation
Total Project Cash flow
- 15,267
- 2,280
- 577
- 606
- 636
1,332
4,262
F. NPV Calculation
PV of cash flows
- 15,266.88
- 2,035.27
- 460.22
- 431.45
- 404.49
755.79
2,159.37
NPV
- 15,683.13
Optimistic Scenario
A. Inputs
Initial Investment ($ thousands)
7,500
Salvage Value ($ thousands)
2,000
Initial Revenue ($ thousands)
18,000
Variable Costs (% of Revenues)
34%
Initial fixed costs($ thousands)
2,000
Initial total expense ($ thousands)
8,120
Inflation Rate
5%
Discount Rate
12%
Receivables (% of Sales)
16.70%
Inventory (% of next year's costs)
15%
Tax Rate
35%
Year
0
1
2
3
4
5
6
B. Fixed Assets
Investment in fixed assets
7,500
Sales of fixed assets
1,300
Cash flow from fixed assets
- 7,500
1,300
C. Operating Cash flow
Revenues
18,000
18,900
19,845
20,837
21,879
Variable Expenses
6,120
6,426
6,747
7,085
7,439
Fixed Expenses
2,000
2,100
2,205
2,315
2,431
Depreciation
1,500
1,500
1,500
1,500
1,500
Pre-tax Profit
8,380
8,874
9,393
9,937
10,509
Tax
2,933
3,106
3,287
3,478
3,678
Profit after tax
5,447
5,768
6,105
6,459
6,831
Operating Cash flow
6,947
7,268
7,605
7,959
8,331
D. Working Capital
Working Capital
670
1,948
2,039
2,135
2,235
2,007
-
Change in working capital
670
1,278
91
96
101
-229
-2,007
Cash flow from investment in working capital
- 670
- 1,278
- 91
-96
-101
229
2,007
0.240
0.239
0.238
0.238
0.203
E. Project Valuation
Total Project Cash flow
- 8,170
5,669
7,177
7,509
7,859
8,560
3,307
F. NPV Calculation
PV of cash flows
- 8,169.90
5,061.92
5,721.28
5,345.01
4,994.27
4,856.94
1,675.35
NPV
19,484.88
A. Inputs
Initial Investment ($ thousands)
13,000
Salvage Value ($ thousands)
2,000
Initial Revenue ($ thousands)
11,250
Variable Costs (% of Revenues)
50%
Initial fixed costs($ thousands)
6,000
Initial total expense ($ thousands)
11,625
Inflation Rate
5%
Discount Rate
12%
Receivables (% of Sales)
16.70%
Inventory (% of next year's costs)
15%
Tax Rate
35%