Question

Please answer using excel ONLY & add formulas

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 net working capital of $90,000. Thereafter, the investment in operating net working capital will be 12 % of sales. a nо 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? 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

Values given
Cost of machinery	$3,500,000
Period	3 years
Salvage Value	$300,000
Initial investment in working capital	$90,000
Yearly investment in working capital	12% of sales
No. of widgets sold	280,000
Sales price	$22
Variable cost per widget	$14
Incremental fixed costs	$180,000
Tax rate	34%
Rate of return	10%
Computation of after tax profits
Description	Year 1	Year 2	Year 3
Sales ( 280,000 * $ 22)	$6,160,000	$6,160,000	$6,160,000
Variable Costs ( 280,000 * $ 14)	($3,920,000)	($3,920,000)	($3,920,000)
Contribution	$2,240,000	$2,240,000	$2,240,000
Fixed Costs	($180,000)	($180,000)	($180,000)
Capital Cost Allowance	($525,000)	($892,500)	($624,750)
Net Profit	$1,535,000	$1,167,500	$1,435,250
Tax @ 34%	($521,900)	($396,950)	($487,985)
After tax profit	$1,013,100	$770,550	$947,265
Depreciation for the purpouse of calculating after tax profits
Capital Cost Allowance = 30%
Description	Year 1	Year 2	Year 3
Opening Balance	$3,500,000	$2,975,000	$2,082,500
Capital Cost Allowance	($525,000)	($892,500)	($624,750)
Closing Balance	$2,975,000	$2,082,500	$1,457,750
Note - In the first year only half the normal CCA rate can be claimed. Hence, in
Year 1, CCA has been claimed at 15%
			Year 3
Sales			$6,160,000
Operating Net Working Capital @ 12% on sales			$739,200
Note
Total operating net working capital required @ 12% of sales				$739,000
Less: Initial investment in net working capital				($90,000)
Additional investment in operating net working capital required in Year 1				$649,000
After tax cash flow = After tax profit + Depreciation - Operating Net Working Capital
Description	After tax profit	Depreciation	Operating Net Working Capital	After tax cash flow	PV factor @ 10% 1/(1+r)^n	PV of after tax cash flow
Year 1	$1,013,100	$525,000	($649,200)	$888,900	0.9091	$808,099
Year 2	$770,550	$892,500	$0	$1,663,050	0.8264	$1,374,345
Year 3	$947,265	$624,750	$0	$1,572,015	0.7513	$1,181,055
Salvage value of asset				$300,000	0.7513	$225,390
Present value of cash flow						$3,588,889
Net present value
Present Value of after tax cash flow		$3,363,499
Add: Present Value of Salvage Value of Machinery		$225,390
Less: Initial Cash outflow on purchase of machinery		($3,500,000)
Less: Initial Cash outflow on working capital		($90,000)
Net present value		($1,111)
(b) Since, the net present value of the project is negative (- $ 1,111) the company should not buy the machinery
(c.)
The PV of Sales should more than the initial investment of $ 3,590,000 to be more than zero
The NPV at sale of 280,000 is - $ 1,111.68
To generate NPV greater than zero the PV of cash flow from additional units should be $ 1,111.68 or more
Let us calculate the PV of cash flow from 1 additional unit.
Sales	22.00
Variable Cost	(14.00)
Profit	8.00
Tax @ 34%	(2.72)
After tax profit	5.28
Description	After tax profit	Depreciation	Operating Net Working Capital	After tax cash flow	PV factor @ 10% 1/(1+r)^n	PV of after tax cash flow
Year 1	$5.28	$0.00	($2.64)	$2.64	$0.9091	$2.40
Year 2	$5.28	$0.00	$0.00	$5.28	$0.8264	$4.36
Year 3	$5.28	$0.00	$0.00	$5.28	$0.7513	$3.97
Present value of cash flow on sale of 1 additional unit						$10.73
Note - Cash flow for Operating Net Working Capital for sale of 1 additional unit = $ 22 * 12% = $ 2.64
PV of cash flow for 1 additional unit			$10.73
Desired additional PV			$1,111.68
No, of units needed = Desired PV/PV of cash flow of I unit
No, of additional units needed ( $ 1,111.68 / $ 10.73)			104
Budgeted Units			280,000
Total units needed for NPV more than zero			280,104
(d)
Description	Current	Change	Change	Forecasted
Units	288,000	10%	(28,800)	259,200
Sales Price	$22	10%	($2.20)	$19.80
Variable Cost	$14	10%	$1.40	$15.40
Fixed Cost	$180,000	10%	$18,000	$198,000
Effect on Net Profits
Description	Current	Forecasted	Net Change (Current - Forecasted)	Current Profit	New Profits (Current+ Net Change)
Reduction in units sold by 10%	$6,160,000	$5,702,400	($457,600)	$2,060,000	$1,602,400
Reduction sales price by 10%	$6,160,000	$5,702,400	($457,600)	$2,060,000	$1,602,400
Increase in variable costs by 10%	$3,920,000	$4,435,200	$515,200	$2,060,000	$1,544,800
Increase in fixed costs by 10%	$180,000	$198,000	$18,000	$2,060,000	$2,042,000
Change in variable costs has the greatest forecasting risk
(e.)
Pessimistic Scenario
Computation of after tax profits
Description	Year 1	Year 2	Year 3
Sales ( 225,000 * $ 19)	$4,275,000	$4,275,000	$4,275,000
Variable Costs ( 225,000 * $ 16)	($3,600,000)	($3,600,000)	($3,600,000)
Contribution	$675,000	$675,000	$675,000
Fixed Costs	($220,000)	($220,000)	($220,000)
Capital Cost Allowance	($525,000)	($892,500)	($624,750)
Net Profit	($70,000)	($437,500)	($169,750)
Tax @ 34%	$0	$0	$0
After tax profit	($70,000)	($437,500)	($169,750)
Description	After tax profit	Depreciation	Operating Net Working Capital	After tax cash flow	PV factor @ 10% 1/(1+r)^n	PV of after tax cash flow
Year 1	($70,000)	$525,000	($603,000)	($148,000)	0.9091	($134,547)
Year 2	($437,500)	$892,500	$0	$455,000	0.8264	$376,012
Year 3	($169,750)	$624,750	$0	$455,000	0.7513	$341,842
Salvage value of asset				$300,000	0.7513	$225,390
Present Value of Cash flow						$808,697
Optimistic Scenario
Computation of after tax profits
Description	Year 1	Year 2	Year 3
Sales ( 350,000 * $ 28)	$9,800,000	$9,800,000	$9,800,000
Variable Costs ( 350,000 * $ 10)	($3,500,000)	($3,500,000)	($3,500,000)
Contribution	$6,300,000	$6,300,000	$6,300,000
Fixed Costs	($150,000)	($150,000)	($150,000)
Capital Cost Allowance	($525,000)	($892,500)	($624,750)
Net Profit	$5,625,000	$5,257,500	$5,525,250
Tax @ 34%	($1,912,500)	($1,787,550)	($1,878,585)
After tax profit	$3,712,500	$3,469,950	$3,646,665
Description	After tax profit	Depreciation	Operating Net Working Capital	After tax cash flow	PV factor @ 10% 1/(1+r)^n	PV of after tax cash flow
Year 1	$3,712,500	$525,000	($1,266,000)	$2,971,500	0.9091	$2,701,391
Year 2	$3,469,950	$892,500	$0	$4,362,450	0.8264	$3,605,129
Year 3	$3,646,665	$624,750	$0	$4,271,415	0.7513	$3,209,114
Salvage value of asset				$300,000	0.7513	$225,390
Present Value of Cash flow						$9,741,024
Computation of NPV
Net present value		Pessimistic	Optimistic
Present Value of after tax cash flow		$583,307	$9,515,634
Add: Present Value of Salvage Value of Machinery		$225,390	$225,390
Less: Initial Cash outflow on purchase of machinery		($3,500,000)	($3,500,000)
Less: Initial Cash outflow on working capital		($90,000)	($90,000)
Net present value		($2,781,303)	$6,151,024