SPREADSHEET 10 1 Financial projections for Blooper's Magnoosium mine (dollar values in millions) A Inputs Initial investment 150 Salvage value 20 Initial revenues 150 Variable costs ( of revenues) 40 0 Initial fixed costs 40 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 Capital Investments Investments in fixed assets 150 00 Sales of fixed assets 15 80 Cash flow investment in fixed assets 150 00 15 80 C Operating cash flow Revenues 150 00 157 50 165 38 173 64 182 33 Variable expenses 60 00 63 00 66 15 69 46 72 93 Fixed expenses 40 00 42 00 44 10 46 31 48 62 Depreciation 30 00 30 00 30 00 30 00 30 00 Pretax profit 20 00 22 50 25 13 27 88 30 78 Tax 4 20 4 73 5 28 5 86 6 46 Profit after tax 15 80 17 78 19 85 22 03 24 31 Operating cash flow 45 80 47 78 49 85 52 03 54 31 D Working capital Working capital 15 00 40 75 42 79 44 93 47 17 30 39 0 00 Change in working capital 15 00 25 75 2 04 2 14 2 25 16 79 30 39 Cash flow from investment in working capital 15 00 25 75 2 04 2 14 2 25 16 79 30 39 0 408 0 408 0 408 0 408 0 250 E Project valuation Total project cash flow 165 00 20 05 45 74 47 71 49 78 71 10 46 19 Discount factor 1 00 0 8929 0 7972 0 7118 0 6355 0 5674 0 5066 PV of cash flow 165 00 17 90 36 46 33 96 31 64 40 34 23 40 Net present value 18 70 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 Ust 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 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 Ust 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

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 21, 2024

SPREADSHEET 10.1 Financial projections for Blooper's Magnoosium mine (dollar values in millions) A. Inputs Initial investment 150 Salvage value 20 Initial revenues 150 Variable costs

SPREADSHEET 10.1 Financial projections for Blooper's Magnoosium mine (dollar values in millions)
A. Inputs
Initial investment	150
Salvage value	20
Initial revenues	150
Variable costs (% of revenues)	40.0%
Initial fixed costs	40
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. Capital Investments
Investments in fixed assets	150.00
Sales of fixed assets							15.80
Cash flow investment in fixed assets	-150.00						15.80

C. Operating cash flow
Revenues		150.00	157.50	165.38	173.64	182.33
Variable expenses		60.00	63.00	66.15	69.46	72.93
Fixed expenses		40.00	42.00	44.10	46.31	48.62
Depreciation		30.00	30.00	30.00	30.00	30.00
Pretax profit		20.00	22.50	25.13	27.88	30.78
Tax		4.20	4.73	5.28	5.86	6.46
Profit after tax		15.80	17.78	19.85	22.03	24.31
Operating cash flow		45.80	47.78	49.85	52.03	54.31

D. Working capital
Working capital	15.00	40.75	42.79	44.93	47.17	30.39	0.00
Change in working capital	15.00	25.75	2.04	2.14	2.25	-16.79	-30.39
Cash flow from investment in working capital	-15.00	-25.75	-2.04	-2.14	-2.25	16.79	30.39
		0.408	0.408	0.408	0.408	0.250
E. Project valuation
Total project cash flow	-165.00	20.05	45.74	47.71	49.78	71.10	46.19
Discount factor	1.00	0.8929	0.7972	0.7118	0.6355	0.5674	0.5066
PV of cash flow	-165.00	17.90	36.46	33.96	31.64	40.34	23.40
Net present value	18.70

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