A B D E F H 5 6 7 Shrek Casting Company is considering adding a new line to its product mix. The production line would be set up in unused space in Shrek's' main plant. The machinery's invoice price would be approximately $200,000; another $10,000 in shipping charges would be required; and it would cost an additional $30,000 to install the equipment. The machinery has an economic life of 4 years, and would be a class 8 with a 20% CCA rate. The machinery is expected to have a salvage value of $20,000 after 4 years of use. 8 9 10 The new line would generate incremental sales of 1,300 units per year for four years at an incremental cost of $125 per unit in the first year, excluding depreciation. Each unit can be sold 11 for $225 in the first year. The sales price and cost are expected to increase by 3% per year due to inflation. Further, to handle the new line, the firm's net operating working capital 12 would have to increase by an amount of $37,000. The firm's tax rate is 31%, and its overall weighted average cost of capital is 10 percent. 13 14 I. Utilize the Components Cash Flows Approach to analyze Shrieves new product project 15 INPUT DATA Your Name(s) 16 17 r = T = 10% 31% Key Output: NPV = $ (200.00) 18 d = CCA rate = 19 Machine Cost = 200,000 20 Shipping Cost = 21 22 Installation cost = Change in NOWC = 23 # Units = 24 25 Price = Costs = 26 Inflation = 27 Salvage Value = 28 Economic life = 29 30 A. Initial Outlay (in 1000s) 31 New Machine Cost 200.00 years B. Operating Cash Flows (in 1000s) Yrs 1 2 3 4 Intial Outlay 32 Plus: Setup & Training # Units 33 Capital Cost 200.00 34 Change in NOWC 35 Initial Investment CF 200.00 36 37 38 39 40 41 Price Costs NCF-BT Tax NCF-AT PV of Operating CF's $0.000 C. Ending Cash Flows Salvage Value 42 NOWC Recovery 43 44 NCFs PV of Salv + NOWC $0.00 45 46 D. NPV of CCA Tax Shield 47 48 49 C = Cost S=Salv Value d=CCA rate= 200.00 50 T= 51 r=cost of capital = 52 53 n= PV of CCATS= #DIV/O! 54 55 NPV = Investment Outlay + PV Project CF + PV CCATS + PV Ending CF 56 57 Investment Outlay $ PV of Operating CF (200.00) 58 59 PV CCATS PV Ending CF 60 61 NPV of project $ PROJECT Decision: ACCEPT/REJECT (200.00) 62 63 II. Sensitivity Analysis 64 CdT 1+0.5r SdT PVCCATS = (rtd) ((1+r)) 65 Sensitivity analysis measures the effect of changes in a particular variable, say revenues, on a project's NPV. To perform a sensitivity analysis, all variables are fixed at their expected 66 values except one. This one variable is then changed, often by specified percentages, and the resulting effect on NPV is noted. 67 68 An Excel "Data Table" is used below to find NPV for unit sales, salvage value, and cost of capital for the project, with the deviations from base case. This produces the sensitivity 69 analysis for WACC as shown below. 70 NPV NPV NPV 71 Dev from base case WACC #DIV/O! Units Sold #DIV/O! Salvage #DIV/0! 72 -20% 8% #DIV/O! -20% 1000 38.23 -20% 20 #DIV/O! 73 -10% 9% #DIV/0! -10% 1125 58.33 -10% 22.5 #DIV/O! 74 0% 10% #DIV/O! 0% 1250 86.61 0% 25 #DIV/O! 75 10% 11% #DIV/O! 10% 1375 114.9 10% 27.5 #DIV/O! 76 20% 12% #DIV/O! 20% 1500 143.18 20% 30 #DIV/O! 77 78 79 NPV Values for Deviations from Base Case Sensitivity Analysis 80 Dev from base 81 case WACC Units Sold 82 -20% Copy Copy Salvage Copy 83 -10% Cells Cells Cells 84 0% C72 to F72 to 172 to 85 10% C76 F76 176 86 20% Here Here Here 87 NPV 1 0.9 0.8 0.7 0.6 0.5 54 0.4 0.3 0.2 0.1 0 78 79 80 Dev from base NPV Values for Deviations from Base Case 81 case WACC Units Sold Salvage 82 -20% Copy Copy Copy 83 -10% Cells Cells Cells 84 0% C72 to F72 to 172 to 85 10% C76 86 20% Here F76 Here 176 Here 87 88 89 90 91 92 93 94 95 NPV Sensitivity Analysis 1 0.9 0.8 0.7 0.6 0.5 0.4 0.3 0.2 0.1 0 -20% -10% 0% 10% Deviation from Base-Case Value (%) 20% WACC Units Sold - Salvage What does this graph show you? 96 III. Scenario Analysis 97 Scenario analysis extends risk analysis in two ways: 98 (1) It allows us to change more than one variable at a time, hence to see the combined effects of changes in several variables on NPV, and on NPV 99 (2) It allows us to bring in the probabilities to see the combined effects of changes in several variables 100 101 102 Scenario Probability Unit Sales Unit Price NPV (1000's) Squared Deviation times probability 103 104 Best Case 25% 1700 105 Base Case 50% 1300 106 Worst Case 25% 900 $250.00 $200.00 $175.00 $ $ 293.58 (200.00) $25,629.06 $15,032.71 $176.97 These NPV values are copied from the appropriate worksheet 107 108 109 110 111 Mean = Expected NPV = Standard Deviation = Coefficient of Variation = Std Dev / Expected NPV = Can be compare this with other projects scenario analysis results If typical range of CoV for company projects is 0.7 to 1.0, then would this project be considered more or less riskly? ($27) $202 112 113 114 IV. NPV Break-even 115 116 117 118 119 120 121 122 123 124 125 126 127 Variable Price Machine Cost Makes NPV = 0 Use Goal Seek (in Data - Forecast- What if Analysis) #units r = T = 10% 31% Key Output: NPV = $ 102.80 d = CCA rate = 20% Machine Cost = Goal Seek ? 200,000 Shipping Cost = 10,000 Set cell: $E$17 Installation cost = 30,000 Change in NOWC = 37,000 To value: 0 #Units = 1,300 By changing cell: $B$23 Price = 225 Costs = 125 OK Cancel Inflation = 3% Salvage Value = 20,000 Economic life = 4.00 years