Question

(Related to Checkpoint 13.2 and Checkpoint 13.3) (Comprehensive risk analysis) Blinkeria is considering introducing a new line of hand scanners that can be used to copy material and then download it into a personal computer. These scanners are expected to sell for an average price of $98 each, and the company analysts performing the analysis expect that the firm can sell 107,000 units per year at this price for a period of five years, after which time they expect demand for the product to end as a result of new technology. In addition, variable costs are expected to be $19 per unit and fixed costs, not including depreciation, are forecast to be $1,010,000 per year. To manufacture this product, Blinkeria will need to buy a computerized production machine for $10.1 million that has no residual or salvage value, and will have an expected life of five years. In addition, the firm expects it will have to invest an additional $302,000 in working capital to support the new business. Other pertinent information concerning the business venture is provided here: a. Calculate the project's NPV. b. Determine the sensitivity of the project's NPV to a(n) 10 percent decrease in the number of units sold. c. Determine the sensitivity of the project's NPV to a(n) 10 percent decrease in the price per unit. d. Determine the sensitivity of the project's NPV to a(n) 10 percent increase in the variable cost per unit. e. Determine the sensitivity of the project's NPV to a(n) 10 percent increase in the annual fixed operating costs. f. Use scenario analysis to evaluate the project's NPV under worst- and best-case scenarios for the project's value drivers. The values for the expected or base-case along with the worst- and best-case scenarios are listed here: a. The NPV for the base-case will be $ 10,738,251. (Round to the nearest dollar.) b. The project's NPV to a(n) 10 percent decrease in the number of units sold will be $ (Round to the nearest dollar.) Data Table Data Table Best Case Initial cost of the machine Expected life Salvage value of the machine Working capital requirement Depreciation method Depreciation expense Cash fixed costs-excluding depreciation Variable costs per unit Required rate of return or cost of capital Tax rate $10,100,000 5 years $0 $302,000 straight line $2,020,000 per year $1,010,000 per year $19 10.5% 34% Unit sales Price per unit Variable cost per unit Cash fixed costs per year Depreciation expense Expected or Base Case 107,000 $98 $(19) $(1,010,000) $(2,020,000) Worst Case 73,830 $86.24 $(20.52) $(1,232,200) $(2,020,000) 140,170 $118.58 $(17.48) $(919, 100) $(2,020,000) ? Print Done Final Check

Answer 1

a. Project NPV:

A B с 4 5 Particulars 6 Sales Volume 7 Selling price per unit 8 Annual Sales 9 Variable cost per unit 10 Annual Variable cost 11 Fixed Costs 12 Depreciation 13 EBIT 14 Tax at 34% 15 Net Income 16 Depreciation 17 Net Operating Cash-flows 18 Discount factor at 10.5% for 5 years 19 Present Value of Operating Cash flows 20 Initial Project Cost 21 Working Capital Requirement Base Steps 107,000 (A) = Given in Question 98 (B) = Given in Question 10,486,000 (C) = (A)*(B) 19 (D) = Given in Question 2,033,000 (E) = (A)*(D) 1,010,000 (F) = Given in Question 2,020,000 (G) = (N)/5 5,423,000 (H) = (E) - (F) - (G) 1,843,820 (0) = (H)*34% 3,579,180 (0) = (H)-(0) 2,020,000 (G) = (N)/5 5,599,180 (K) = (0) + (G) 3.743 (L) = ((1-(1+10.5%)^-5)/10.5% 20,956,937 (M) = (K) * (L) (10,100,000) (N) = Given in Question (302,000) (0) = Given in Question (P) = Assumed to be recovered 183,314 (0)*1/(1+10.5%)^5) 10,738,251 (0) = (M)+(N)+(0)+(P) 22 Present Value of recovery of Working Capital 23 Net Present Value 24

NPV = $10,738,251

Workings:

A B C 4 5 Particulars 6 Sales Volume 7 Selling price per unit 8 Annual Sales 9 Variable cost per unit 10 Annual Variable cost 11 Fixed Costs 12 Depreciation 13 EBIT 14 Tax at 34% 15 Net Income 16 Depreciation 17 Net Operating Cash-flows 18 Discount factor at 10.5% for 5 years 19 Present Value of Operating Cash flows 20 Initial Project Cost 21 Working Capital Requirement Base Steps 107000 (A) = Given in Question 98 (B) = Given in Question =B6 B7 (C) = (A)*(B) 19 (D) = Given in Question =B6*B9 (E) = (A)*(D) 1010000 (F) = Given in Question --B20/5 (G) = (N)/5 =B8-B10-B11-B12 (H) = (E) - (F) - (G) =B13*34% (0) = (H)*34% =B13-B14 (2) = (H)-(1) =B12 (G) = (N)/5 =SUM(B15:B16) (K) = (J) + (G) =((1-(1+10.5%)^-5)/10.5% (L) = ((1-(1+10.5%)^-5)/10.5% =B17*B18 (M) = (K) * (L) =-10.1*10^6 (N) = Given in Question -302000 TO) = Given in Question 22 Present Value of recovery of Working Capital --B21*1/(1+10.5%)^5 23 Net Present Value =B19+B20+B21+B22 24 (P) = Assumed to be recovered( (0)*1/(1+10.5%)^5) (0) = (M)+(N)+(O)+(P)

b. Sensitivity of project NPV to 10% decrease in number of units sold:

10% decrease in number of units sold	10,700	107,000*10%
Lower Sales	1,048,600	107000*98
Lower Variable cost	203,300	107000*19
Lower Contribution before tax	845,300	Lower Sales - Lower Variable Cost
Lower Contribution after tax	557,898	Lower Contribution before tax * (100%-34%)
Discount factor at 10.5% for 5 years	3.743	((1-(1+10.5%)^-5))/10.5%
Present Value of Contribution after tax	2,088,133	Lower Contribution after tax * Discount factor at 10.5% for 5 years
Reduction in NPV due to 10% decrease in number of units sold	2,088,133
Sensitivity of NPV to decrease in number of units sold ($/per unit)	195.15	Change in NPV/Change in number of units sold= 2,088,133/10,700
Sensitivity of NPV to 10% decrease in number of units sold (%)	19.45%	2,088,133/10,738,251

c. Sensitivity of project NPV to 10% decrease in selling price per unit:

10% decrease in selling price per unit	9.80	$98*10%
Lower Sales	1,048,600	107000*9.8
Lower Sales after tax	692,076	Lower Sales * (100%-34%)
Discount factor at 10.5% for 5 years	3.743	((1-(1+10.5%)^-5))/10.5%
Present Value of Lower Sales after tax	2,590,342	Lower Sales after tax* Discount factor at 10.5% for 5 years
Reduction in NPV due to 10% decrease in price per unit	2,590,342
Sensitivity of NPV to decrease in selling price per unit ($ per unit)	264,320.65	Change in NPV/Change in selling price per unit= 2,590,342/9.80
Sensitivity of NPV to 10% decrease in selling price per unit (%)	24.12%	2,590,342/10,738,251

d. Sensitivity of project NPV to 10% increase in variable cost per unit:

10% increase in variable price per unit	1.90	$19*10%
Higher Variable cost	203,300	107000*1.9
Higher Variable cost after tax	134,178	Higher Variable cost * (100%-34%)
Discount factor at 10.5% for 5 years	3.743	((1-(1+10.5%)^-5))/10.5%
Present Value of Higher Variable cost after tax	502,209	Higher Variable cost after tax* Discount factor at 10.5% for 5 years
Reduction in NPV due to 10% increase in Variable cost per unit	502,209
Sensitivity of NPV to increase in Variable cost per unit ($ per unit)	264,320.65	Change in NPV/Change in increase in Variable cost per unit = 502,209/1.90
Sensitivity of NPV to 10% increase in Variable cost per unit (%)	4.68%	502,209/10,738,251

e. Sensitivity of project NPV to 10% increase in fixed cost:

10% increase in annual fixed cost	101,000	$1,010,000*10%
Higher Fixed cost after tax	66,660	101,000* (100%-34%)
Discount factor at 10.5% for 5 years	3.743	((1-(1+10.5%)^-5))/10.5%
Present Value of Higher Fixed cost after tax	249,499	Higher Fixed cost after tax* Discount factor at 10.5% for 5 years
Reduction in NPV due to 10% increase in annual fixed cost	249,499
Reduction in NPV due to increase in annual fixed cost ($ per unit)	2.47	Change in NPV/Change in increase in annual fixed cost = 249,499/101,000
Sensitivity of NPV to 10% increase in fixed cost (%)	2.32%	249,499/10,738,251

f. Project's NPV under worst and best case scenario

D E G H 4 5 6 7 00 9 10 11 12 13 14 15 Particulars Sales Volume Selling price per unit Annual Sales Variable cost per unit Annual Variable cost Fixed Costs Depreciation |EBIT Tax at 34% Net Income Depreciation Net Operating Cash-flows Discount factor at 10.5% for 5 years Present Value of Operating Cash flows Initial Project Cost Working Capital Requirement Present Value of recovery of working Capital Net Present Value Change in NPV from Base Case Worst Case Best Case Steps 73,830 140,170 (A) = Given in Question 86.24 118.58 (B) = Given in Question 6,367,099 16,621,359 (C) = (A)*(B) 20.52 17.48 (D) = Given in Question 1,514,992 2,450,172 (E) = (A)*(D) 1,232,200 919,100 (F) = Given in Question 2,020,000 2,020,000 (G) = (N)/5 1,599,908 11,232,087 (H) = (E) - (F) - (G) 543,969 3,818,910 (0) = (H)*34% 1,055,939 7,413,177 (0) = (H)-(0) 2,020,000 2,020,000 (G) = (N)/5 3,075,939 9,433,177 (K) = (J) + (G) 3.743 3.743 (L) = ((1-(1+10.5%)^-5)/10.5% 11,512,804 35,307,046 (M) = (K) * (L) (10,100,000) (10,100,000) (N) = Given in Question (302,000) (302,000) (0) = Given in Question (P) = Assumed to be recovered 183,314 183,314 (0)*1/(1+10.5%)^5) 1,294,118 25,088,360 (Q) = (M)+(N)+(O)+(P) (9,444,133) 14,350,109 (Q)-$10,738,251 16 17 00 19 20 21 22 23 24

Workings:

D E G H 4 5 6 7 8 9 10 11 12 13 Particulars Sales Volume Selling price per unit Annual Sales Variable cost per unit Annual Variable cost Fixed Costs Depreciation EBIT Tax at 34% Net Income Depreciation Net Operating Cash-flows Discount factor at 10.5% for 5 years Present Value of Operating Cash flows Initial Project Cost Working Capital Requirement Worst Case Best Case Steps 73830 140170 (A) = Given in Question 86.24 118.58 (B) = Given in Question =G6*67 =H6*H7 (C) = (A)*(B) 20.52 17.48 (D) = Given in Question =G6*69 =H6H9 (E) = (A)*(D) |1232200 919100 (F) = Given in Question =-G20/5 =-H20/5 (G) = (N)/5 =G8-G10-G11-G12 =H8-H10-H11-H12 (H) = (E) - (F)- (G) =G13*34% =H13*34% (1) = (H)*34% =G13-14 =H13-H14 (1) = (H)-(0) =G12 =H12 (G) = (N)/5 I-SUM(G15:16) I-SUM(H15:H16) (K) = (J) + (G) =(1-(1+10.5%)^-5)/10.5% =((1-(1+10.5%)^-5)/10.5% (L) = ((1-(1+10.5%)^-5)/10.5% =G17*G18 =H17*H18 (M) = (K) * (L) -10100000 - 10100000 (N) = Given in Question -302000 -302000 (O) = Given in Question 14 15 16 17 18 19 20 21 22 23 Present Value of recovery of Working Capital --G21*1/(1+10.5%)^5 Net Present Value =G19+G20+G21+G22 Change in NPV from Base Case =G23-B23 =-H21*1/(1+10.5%)^5 =H19+H20+H21+H22 =H23-B23 (P) = Assumed to be recovered( (0) *1/(1+10.5%)^5) (Q) = (M)+(N)+(0)+(P) (Q)-$10,738,251 24

Note:

Sensitivity of worst case and best case can be found faster using the excel utility --- Scenario Manager:

Given below is an example of worst case scenario using this excel utility

Step 1:

Use the base case NPV table and Go to Data---What-if analysis---Scenario Manager and select the cells that needs to be changed.

D E F K M N 4 5 6 Edit Scenario ? 7 on 9 Scenario name: Sensitivity Changing cells: SFS6, SFS7, SF59,5F511 Ctrl+click cells to select non-adjacent changing cells. Comment: 10 11 12 13 Base 107,000 98 10,486,000 19 2,033,000 1,010,000 2,020,000 5,423,000 1,843,820 3,579,180 2,020,000 5,599,180 3.743 20,956,937 (10,100,000) (302,000) 14 Particulars Sales Volume Selling price per unit Annual Sales Variable cost per unit Annual Variable cost Fixed Costs Depreciation EBIT Tax at 34% Net Income Depreciation Net Operating Cash-flows Discount factor at 10.5% for 5 years Present Value of Operating Cash flows Initial Project Cost Working Capital Requirement Present Value of recovery of Working Capital Net Present Value Change in NPV from Base Case 15 16 Protection Prevent changes 17 18 Hide 19 OK Cancel 20 21 22 183,314 10,738,251 23 24

Step 2:

Click Ok and then enter the revised values

D E F K M N 4 5 Scenario Values ? X 6 7 8 9 10 11 Enter values for each of the changing cells. 1: SF56 73830 2: SFS7 86.24 3: SFS9 20.52 SF$11 12322001 4 12 13 Add OK Cancel Base 107,000 98 10,486,000 19 2,033,000 1,010,000 2,020,000 5,423,000 1,843,820 3,579,180 2,020,000 5,599,180 3.743 20,956,937 (10,100,000) (302,000) 14 Particulars Sales Volume Selling price per unit Annual Sales Variable cost per unit Annual Variable cost Fixed Costs Depreciation EBIT Tax at 34% Net Income Depreciation Net Operating Cash-flows Discount factor at 10.5% for 5 years Present Value of Operating Cash flows Initial Project Cost Working Capital Requirement Present Value of recovery of working Capital Net Present Value Change in NPV from Base Case 15 16 17 00 19 20 21 22 183,314 10,738,251 23 24

Step 3:

Click OK

D E F K M N O 4 Scenario Manager ? х 5 6 7 Scenarios: Sensitivity Add... on 9 Delete 10 Edit... 11 12 Merge... 13 Base 107,000 98 10,486,000 19 2,033,000 1,010,000 2,020,000 5,423,000 1,843,820 3,579,180 2,020,000 5,599,180 3.743 20,956,937 (10,100,000) (302,000) 14 Summary... Particulars Sales Volume Selling price per unit Annual Sales Variable cost per unit Annual Variable cost Fixed Costs Depreciation | EBIT Tax at 34% Net Income Depreciation Net Operating Cash-flows Discount factor at 10.5% for 5 years Present Value of Operating Cash flows Initial Project Cost Working Capital Requirement Present Value of recovery of Working Capital Net Present Value Change in NPV from Base Case 15 16 Changing cells: SFS6, SFS7, SFS9,5F511 Comment: 17 18 19 20 21 Show Close 22 183,314 10,738,251 23 24

Step 4:

Click Show. The NPV of worst case of $1,294,118 will be shown in the same table

D E F K M N o 4 X 5 Scenario Manager ? 5 7 Scenarios: Sensitivity Add... 8 Delete 9 10 Edit... 11 Merge... 12 13 14 Base 73,830 86 6,367,099 21 1,514,992 1,232,200 2,020,000 1,599,908 543,969 1,055,939 2,020,000 3,075,939 3.743 11,512,804 (10,100,000) (302,000) Summary... Particulars Sales Volume Selling price per unit Annual Sales Variable cost per unit Annual Variable cost Fixed Costs Depreciation EBIT Tax at 34% Net Income Depreciation Net Operating Cash-flows Discount factor at 10.5% for 5 years Present Value of Operating Cash flows Initial Project Cost Working Capital Requirement Present Value of recovery of working Capital Net Present Value Change in NPV from Base Case 15 16 Changing cells: SFS6, SFS7,SFS9,5F511 Comment: 17 18 19 20 21 Show Close 22 183,314 1,294,118 23 24

The same steps can be repeated for best case or any other scenario.