Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Bloopers analysts have come up with the following revised estimates for its magnoosium mine: Range Pessimistic Optimistic Initial investment + 40 % 20 % Revenues

image text in transcribedBloopers analysts have come up with the following revised estimates for its magnoosium mine:

Range

Pessimistic Optimistic
Initial investment + 40 % 20 %
Revenues 25 % + 25 %
Variable costs + 20 % 25 %
Fixed cost + 20 % 30 %
Working capital + 40 % 45 %

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.)

A. Inputs
Initial investment ($ thousands) 10,000
Salvage value ($ thousands) 2,000
Initial revenues ($ thousands) 15,000
Variable costs (% of revenues) 40.0%
Initial fixed costs ($ thousands) 4,000
Inflation rate (%) 5.0%
Discount rate (%) 12.0%
Receivables (% of sales) 16.7%
Inventory (% of next year's costs) 15.0%
Tax rate (%) 21.0%
Year: 0 1 2 3 4 5 6
B. Fixed assets
Investments in fixed assets 10,000
Sales of fixed assets 1,580
Cash flow from fixed assets -10,000 1,580
C. Operating cash flow
Revenues 15,000 15,750 16,538 17,364 18,233
Variable expenses 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 630 683 738 796 856
Profit after tax 2,370 2,568 2,775 2,993 3,221
Operating cash flow 4,370 4,568 4,775 4,993 5,221
D. Working capital
Working capital 1,500 4,075 4,279 4,493 4,717 3,039 0
Change in working capital 1,500 2,575 204 214 225 -1,679 -3,039
Cash flow from investment in working capital -1,500 -2,575 -204 -214 -225 1,679 3,039
0.408 0.408 0.408 0.408 0.250
E. Project valuation
Total project cash flow -11,500 1,795 4,364 4,561 4,768 6,900 4,619
Discount factor 1.000 0.893 0.797 0.712 0.636 0.567 0.507
PV of cash flow -11,500 1,603 3,479 3,246 3,030 3,915 2,340
Net present value 6,113.1
Project NPV
Pessimistic Expected Optimistic
Initial investment
Revenues
Variable costs
Fixed costs
Working capital
AutoSave OFF OFF AGE 5 Spreadsheet 101 - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments X [G Calibri (Body) v 11 v AP General Insert I Delete 28-03 v Paste B 1 I U 7 ar Ar = = = Y ideas Conditional Format Formatting as Table Styles Formato Sort & Filter Find & Select Create and Share Adobe PDF M15 X fx B D E F G H . L M N P R 5 UJ W 1 2 4 5 A. Inputs Initial investment is thousands) Salvage value (s thousands Initial revenues is thousands Variablecosis of revenues Initial fined costs $thousands Inflation rate) Discount rate Receivables at sales Inventory of next year's costs! Tax The 10,000 2,000 15,000 100% %, 4,000 5.0% 12 0% %, 16.7% 15.0% 210% 7 8 10 11 Year: 0 1 2 3 4 5 6 12 13 14 15 16 17 10,000 B. Fixed wets Investments in fixed as Sales of fixed acets Cash flow from lixed assets -10,000 1.580 1,580 12 C. Operating cash flow Revenues Variable expenses xed expenses Depreciation Pretax pront Tax Profiter Operating cash flow 15,000 6,000 4.000 2,000 3,000 630 2,370 4,370 15.750 5,300 4,200 2,000 3,250 683 2,56B 4,568 16,538 6,515 4,410 1.200 2.000 3,513 738 2,775 4.775 17,354 6,946 4.631 2,000 3,788 T 796 2,993 4,993 18,233 7,293 4,862 2.000 4,078 256 3.221 5,221 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 D. Working capital Working capital Change in working capital Cash flow from invest in working capital 1,500 1,500 1,500 4,075 2,335 2,575 0.408 4,279 204 -204 0.408 4,493 214 -214 0.408 4.717 225 -225 0.408 3,039 -1,679 1.679 3.250 -3,039 3,039 E. Project valuation Total project cash flow Discount factor PV of cash flow Nel pran vue -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 1,758 0.636 3,030 5,903 0.567 3,915 4,619 0.507 2.340 Sheel 1 + # EP - + 100% AutoSave OFF OFF AGE 5 Spreadsheet 101 - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments X [G Calibri (Body) v 11 v AP General Insert I Delete 28-03 v Paste B 1 I U 7 ar Ar = = = Y ideas Conditional Format Formatting as Table Styles Formato Sort & Filter Find & Select Create and Share Adobe PDF M15 X fx B D E F G H . L M N P R 5 UJ W 1 2 4 5 A. Inputs Initial investment is thousands) Salvage value (s thousands Initial revenues is thousands Variablecosis of revenues Initial fined costs $thousands Inflation rate) Discount rate Receivables at sales Inventory of next year's costs! Tax The 10,000 2,000 15,000 100% %, 4,000 5.0% 12 0% %, 16.7% 15.0% 210% 7 8 10 11 Year: 0 1 2 3 4 5 6 12 13 14 15 16 17 10,000 B. Fixed wets Investments in fixed as Sales of fixed acets Cash flow from lixed assets -10,000 1.580 1,580 12 C. Operating cash flow Revenues Variable expenses xed expenses Depreciation Pretax pront Tax Profiter Operating cash flow 15,000 6,000 4.000 2,000 3,000 630 2,370 4,370 15.750 5,300 4,200 2,000 3,250 683 2,56B 4,568 16,538 6,515 4,410 1.200 2.000 3,513 738 2,775 4.775 17,354 6,946 4.631 2,000 3,788 T 796 2,993 4,993 18,233 7,293 4,862 2.000 4,078 256 3.221 5,221 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 D. Working capital Working capital Change in working capital Cash flow from invest in working capital 1,500 1,500 1,500 4,075 2,335 2,575 0.408 4,279 204 -204 0.408 4,493 214 -214 0.408 4.717 225 -225 0.408 3,039 -1,679 1.679 3.250 -3,039 3,039 E. Project valuation Total project cash flow Discount factor PV of cash flow Nel pran vue -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 1,758 0.636 3,030 5,903 0.567 3,915 4,619 0.507 2.340 Sheel 1 + # EP - + 100%

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

The Global Financial Crisis What Have We Learnt

Authors: Steven Kates

1st Edition

0857934228, 978-0857934222

More Books

Students also viewed these Finance questions

Question

=+b) What percent of Two-Year MBAs are women?

Answered: 1 week ago

Question

What is the background of the situation?

Answered: 1 week ago