90.2K

Verified Solution

Link Copied!

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 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|} \hline \multicolumn{8}{|c|}{ SPREADSHEET 10.1 Financial projections for Blooper's Magnoosium mine (dollar values in millions) } \\ \hline A. Inputs & & & & & & & \\ \hline Initial investment & 150A & & & & & & \\ \hline Salvage value & 20 & & & & & & \\ \hline Initial revenues & 150 & & & & & & \\ \hline Variable casts (\% of revenues) & 40.0% & & & & & & \\ \hline Initial fixed costs & 40 & & & & & & \\ \hline Inflation nate (\%) & 5.0% & & & & & & \\ \hline Discount rate (\%) & 12.056 & & & & & & \\ \hline Receivables (\% of sales) & 16.7% & & 1 & & & & \\ \hline Inventory (\% of next year's costs) & 15.0% & & & & & & \\ \hline Tax rate (%) & 21.0% & & & & & & \\ \hline & & & & & & & \\ \hline Year: & 0 & 1 & 2 & & a & a. & 6 \\ \hline B. Capital Investments & & & & 40 & & & \\ \hline Investments in fixed assets: & 150.00 & & & & & & \\ \hline Sales of fixed assets & & & H & & & & 15.80 \\ \hline Cash flow investment in fixed assets & -150.00 & & +1 & +1 & & & 15.80 \\ \hline & & & + & & & & \\ \hline C. Operating cash flow & & & & & & & \\ \hline Revenues & & 150.00 & 15750 & 165.38 & 173.64 & 182.33n & \\ \hline Variable expenses & & 60.00 & 63.00 & 66.15 & 69.46 & 72.93 & \\ \hline Fixed expenses & & 4000 & 42.00 & 44.10 & 46.30 & 48.62 & \\ \hline Depreciation & & 30.00 & 30.00 & 30.00 & 30.00 . & 30.00 & \\ \hline Pretax profit & & 20.00 & 2250 & 25.12 . & 27.88 & 30.78 & \\ \hline Tax & & 4.20 & 4.72 . & 5.28 . & 5.86 & 6.46 & \\ \hline Profit after tax & & 15.80 & 17.78 & 19.85 & 22.03 & 2431 & \\ \hline Operating cash flow & & 45.80 & 47.78 & 49.85 & 52.03 & 54.31 & \\ \hline & & 4 & & & 0 & & \\ \hline D. Working capital & & & & & & & \\ \hline Working capital & 15.00 & 40.75 & 42.79 . & 44.93 & 47.17 & 30.39 & 0.00 \\ \hline Change in working capital & 15.00 & 25.75 & 2040 & 2.14 & 2.25 & -16.79 & -30.39 \\ \hline Cash flow from investment in working cap & tal 5,00 & -25.75 & -2.04 & -2.14 & -2.25 & 16.79 & 3039 \\ \hline & & 0.408 & 0.408 & 0.408 & 0.408 & 0.250 & \\ \hline E. Project valuation & & & & 4 & & & \\ \hline Total project cash flow & -165.00 & 2005 & 45.74 & 47.71 & 49.78 & 71.10n & 46.19 \\ \hline Discount factor & 1.00 & 0.8929 & 0.7972 & \begin{tabular}{|l|l|} 0.7118 \\ \end{tabular} & 0.6355 & 0.5674 & 0,5066 \\ \hline PV of cash flow & -165.00 & 1790 & 36.46 & 33.96 & 31.64 & 40.34 & 23,40 \\ \hline Net present value & 18.70 & & & & & & \\ \hline \end{tabular}

Answer & Explanation Solved by verified expert

See Answer