Question

answer using excel please

123 Inc. is considering purchasing a new machine. The machine will cost $3,500,000. The machine will be used for a project that lasts 3 years. The expected salvage of the machine at the end of the project is $300,000. The machine will be used to produce widgets. The marketing department has forecasted that the company will be able to sell 280,000 widgets per year. The marketing department believes that the company will be able to charge $22 per widget. The production department, has indicated that the variable cost per widget will be $14. The company has forecasted that the incremental fixed costs associated with the project are $180,000. The company believes that the project will require an initial investment in operating networking capital of $90,000. Thereafter, the investment in operating networking capital will be 12% of sales. The CCA rate is 30%, the tax rate is 34%, and the required rate of return is 10%. Note: All of Calculations should be rounded to the nearest dollar. a) Calculate the NPV of the project. Show all your work. b) Should the company purchase the new machine. Why? c) Your boss has a number of concerns regarding the project. Therefore, she has asked you to determine the number of units that the company must sell each year for the NPV to be greater than zero. d) Your boss has also asked you to determine which input (units sold, price per unit, variable cost per unit, or fixed cost) has the greatest forecasting risk. Therefore, your boss has asked you to do a sensitivity analysis. Your boss wants you to vary the input forecast by 10% and determine the impact on the NPV of the project. Based on this analysis you are to determine which input has the greatest forecasting risk. Therefore, you should decrease the number of units sold by 10%, then you should decrease the price per unit by 10%, next you should increase the variable cost per unit by 10%, and finally you should increase the fixed cost by 10% e) Finally, your boss has asked you to perform a scenario analysis. Therefore, your boss has asked to include two new scenarios. A pessimistic scenario and an optimistic scenario. Your boss wishes to know the NPV of the project under these two additional scenarios. The value of the inputs for each scenario are shown in the table below

Answer 1

a)

	Depreciation Schedule
Year	Opening	Depreciation@30%	Closing
1	$ 3,500,000	1,050,000	$ 2,450,000
2	$ 2,450,000	735,000	$ 1,715,000
3	$ 1,715,000	514,500	$ 1,200,500

Year	0	1	2	3
CAPEX	$   3,500,000
Salvage Value				$              300,000
Sales (280000*$22)		$         6,160,000	$           6,160,000	$           6,160,000
Less:Variable Costs		$         3,920,000	$           3,920,000	$           3,920,000
Contribution		$         2,240,000	$           2,240,000	$           2,240,000
Less:Fixed Costs(specific)		$            180,000	$              180,000	$              180,000
Loss on sale of asset		$                       -	$                         -	$              900,500
Depreciation expense		$         1,050,000	$              735,000	$              514,500
EBT		$         1,010,000	$           1,325,000	$              645,000
Tax @ 34%		$            343,400	$              450,500	$              219,300
EAT		$            666,600	$              874,500	$              425,700
Add: Depreciation		$         1,050,000	$              735,000	$              514,500
Add: Loss on sale of asset(non cash)		$                       -	$                         -	$              900,500
Less: Change in Working Capital	$         90,000	$            649,200	$                         -	$             (739,200)
Add: Salvage value		$                       -	$                         -	$              300,000
Net cash flows	$ (3,590,000)	$        1,067,400	$           1,609,500	$           2,879,900
PVF @ 10%	1	0.9091	0.8264	0.7513
PV of cash flows	-3590000	970364	1330165	2163711
NPV of the project	$      874,240

b) Yes, the company should purchase the machine for the project as it results in positive NPV of $874,240.

c) No of units the company must atleast sell each year for NPV to be positive is determined by the following equation

3590000=	(8x-180000-1050000)*.66+1050000-649200
	+
	(8x-180000-735000)*.66+735000
	+
	(8x-180000-514500-900500)*.66+514500+739200+300000+900500

After solving the above equation, the value of X (no of units) =213,420

d) Using excel, sensitivity analysis can be done in a snap by changing the variables or we can use formulas just as shown above.

Decrease in no of units sold by 10% would result in decrease of NPV by 42.05%

Decrease in price per unit by 10% would decrease NPV by 115.65%

Increase in Variable cost per unit by 10% would decrease NPV by 73.60%

Increase in FC by 10% would decrease the NPV by 3.38%

Therefore, Input of Selling Price per unit does have the greatest forecasting risk.

e) Scenario 1 - Pessimistic

Year	0	1	2	3
CAPEX	$   3,500,000
Salvage Value				$              300,000
225000 units
Contribution @ $3		$            675,000	$              675,000	$              675,000
Fixed Costs(specific)		$            220,000	$              220,000	$              220,000
Loss on sale of asset		$                       -	$                         -	$              900,500
Depreciation expense		$         1,050,000	$              735,000	$              514,500
EBT		$           (595,000)	$             (280,000)	$             (960,000)
Tax @ 34%		$           (202,300)	$               (95,200)	$             (326,400)
EAT		$           (392,700)	$             (184,800)	$             (633,600)
Add: Depreciation		$         1,050,000	$              735,000	$              514,500
Add: Loss on sale of asset(non cash)		$                       -	$                         -	$              900,500
Less: Change in Working Capital	$         90,000	$            649,200	$                         -	$             (739,200)
Add: Salvage value		$                       -	$                         -	$              300,000
Net cash flows	$ (3,590,000)	$                8,100	$              550,200	$           1,820,600
PVF @ 10%	1	0.9091	0.8264	0.7513
PV of cash flows	-3590000	7364	454711	1367844
NPV of the project	$ (1,760,082)

Scenario 2 - Optimistic

Year	0	1	2	3
CAPEX	$   3,500,000
Salvage Value				$              300,000
350000 Units
Contribution @ $18		$         6,300,000	$           6,300,000	$           6,300,000
Fixed Costs(specific)		$            150,000	$              150,000	$              150,000
Loss on sale of asset		$                       -	$                         -	$              900,500
Depreciation expense		$         1,050,000	$              735,000	$              514,500
EBT		$         5,100,000	$           5,415,000	$           4,735,000
Tax @ 34%		$         1,734,000	$           1,841,100	$           1,609,900
EAT		$         3,366,000	$           3,573,900	$           3,125,100
Add: Depreciation		$         1,050,000	$              735,000	$              514,500
Add: Loss on sale of asset(non cash)		$                       -	$                         -	$              900,500
Less: Change in Working Capital	$         90,000	$            649,200	$                         -	$             (739,200)
Add: Salvage value		$                       -	$                         -	$              300,000
Net cash flows	$ (3,590,000)	$        3,766,800	$           4,308,900	$           5,579,300
PVF @ 10%	1	0.9091	0.8264	0.7513
PV of cash flows	-3590000	3424364	3561074	4191811
NPV of the project	$   7,587,249