Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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
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. Note: Do not round intermediate calculations. Negative amounts should be indicated by a minus sign. Enter your answer in millions rounded to 2 decimal places. \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline 2 & A. Inputs & & & & & & & \\ \hline 3 & Initial investment & 150 & & & & & & \\ \hline 4 & Salvage value & 20 & & & & & & \\ \hline 5 & Initial revenues & 150 & & & & & & \\ \hline 6 & Variable costs (\% of revenues) & 40.0% & & & & & & \\ \hline 7 & Initial fixed costs & 40 & & & & & & \\ \hline 8 & Inflation rate (\%) & 5.0% & & & & & & \\ \hline 9 & Discount rate (\%) & 12.0% & & & & & & \\ \hline 10 & Receivables (\% of sales) & 16.7% & & & & & & \\ \hline 11 & Inventory (\% of next year's costs) & 15.0% & & & & & & \\ \hline 12 & Tax rate (\%) & 21.0% & & & & & & \\ \hline 13 & & & & & & & & \\ \hline 14 & Year: & 0 & 1 & 2 & 3 & 4 & 5 & 6 \\ \hline 15 & B. Capital Investments & & & & & & & \\ \hline 16 & Investments in fixed assets & 150.00 & & & & & & \\ \hline 17 & Sales of fixed assets & & & & & & & 15.80 \\ \hline 18 & Cash flow investment in fixed assets & -150.00 & & & & & & 15.80 \\ \hline 19 & & & & & & & & \\ \hline 20 & C. Operating cash flow & & & & & & & \\ \hline 21 & Revenues & & 150.00 & 157.50 & 165.38 & 173.64 & 182.33 & \\ \hline 22 & Variable expenses & & 60.00 & 63.00 & 66.15 & 69.46 & 72.93 & \\ \hline 23 & Fixed expenses & & 40.00 & 42.00 & 44.10 & 46.31 & 48.62 & \\ \hline 24 & Depreciation & & 30.00 & 30.00 & 30.00 & 30.00 & 30.00 & \\ \hline 25 & Pretax profit & & 20.00 & 22.50 & 25.13 & 27.88 & 30.78 & \\ \hline 26 & Tax & & 4.20 & 4.73 & 5.28 & 5.86 & 6.46 & \\ \hline 27 & Profit after tax & & 15.80 & 17.78 & 19.85 & 22.03 & 24.31 & \\ \hline 28 & Operating cash flow & & 45.80 & 47.78 & 49.85 & 52.03 & 54.31 & \\ \hline 29 & & & & & & & & \\ \hline 30 & D. Working capital & & & & & & & \\ \hline 31 & Working capital & 15.00 & 40.75 & 42.79 & 44.93 & 47.17 & 30.39 & 0.00 \\ \hline 32 & Change in working capital & 15.00 & 25.75 & 2.04 & 2.14 & 2.25 & -16.79 & -30.39 \\ \hline 33 & Cash flow from investment in working capit & -15.00 & -25.75 & -2.04 & -2.14 & -2.25 & 16.79 & 30.39 \\ \hline 34 & & & 0.408 & 0.408 & 0.408 & 0.408 & 0.250 & \\ \hline 35 & E. Project valuation & & & & & & & \\ \hline 36 & Total project cash flow & -165.00 & 20.05 & 45.74 & 47.71 & 49.78 & 71.10 & 46.19 \\ \hline 37 & Discount factor & 1.00 & 0.8929 & 0.7972 & 0.7118 & 0.6355 & 0.5674 & 0.5066 \\ \hline 38 & PV of cash flow & -165.00 & 17.90 & 36.46 & 33.96 & 31.64 & 40.34 & 23.40 \\ \hline 39 & Net present value & 18.70 & & & & & & \\ \hline \end{tabular}
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started