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. e 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 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 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. Your Name(s) r L. Utilize the Components Cash Flows Approach to analyze Shrieves new product project INPUT DATA r= 10% Key Output: T= 31% NPV = $ (200.00) d=CCA rate = Machine Cost = 200,000 Shipping Cost = Installation cost = Change in NOWC= # Units = Price = = Costs = Inflation - Salvage Value = Economic life = years B. Operating Cash Flows (in 1000s) A. Initial Outlay (in 1000s) Yrs New Machine Cost 200.00 Intial Outlay Plus: Setup & Training #Units Capital Cost 200.00 Price Change in NOWC Costs Initial Investment CF 200.00 NCF-BT Tax NCF-AT PV of Operating CF's 1 2 3 4 $0.000 C. Ending Cash Flows Salvage Value NOWC Recovery NCFS PV of Salv + NOWC $0.00 D. NPV of CCA Tax Shield C = Cost 200.00 S=Salv Value d=CCA rate= T- r= cost of capital = n= (1 + 0.5r Sdt 1 PV of CCATS #DIV/0! PVCCATS = art 3) ()-(*) (a ton) +53 1+r r+ +r)" NPV = Investment Outlay + PV Project CF + PV CCATS + PV Ending CF Investment Outlay $ (200.00) PV of Operating CF PV CCATS PV Ending CF NPV of project $ (200.00) PROJECT Decision: ACCEPT / REJECT II. Sensitivity Analysis 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 values except one. This one variable is then changed, often by specified percentages, and the resulting effect on NPV is noted. 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 analysis for WACC as shown below. NPV NPV NPV Dev from base case WACC #DIV/0! Units Sold #DIV/01 Salvage #DIV/0! -20% #DIV/0! -20% 1000 38.231 -20% 20 #DIV/01 -10% #DIV/0! -10% 1125 58.33 -10% 22.5 #DIV/0! 0% 10% #DIV/0! 0% % 1250 86.61 0% 25 #DIV/01 10% 11% #0 #DIV/01 10% 1375 114.9 10% 27.5 #DIV/01 20% 12% #DIV/0! 20% 1500 143.18 20% 30 #DIV/0! 8% 9% NPV Values for Deviations from Base Case Sensitivity Analysis Units Sold Copy Dev from base case WACC -20% Copy -10% Cells 0% 072 to 10% 076 20% Here Salvage Copy Cells 1 0.9 0.8 0.7 0.6 0.5 0.4 0.3 0.2 0.1 Cells F72 to F76 172 to 176 Here Here 0 -20% 20% -10% 0% 10% Deviation from Base-Case Value (%) WACC Units Sold Salvage What does this graph show you? can III. Scenario Analysis Scenario analysis extends risk analysis in two ways: (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 (2) It allows us to bring in the probabilities to see the combined effects of changes in several variables Squared Deviation times probability Scenario Probability Unit Sales Unit Price NPV (1000's) 25% $250.00 Best Case Base Case 1700 1300 $ $ 293.58 (200.00) ( 50% $200.00 $175.00 $25,629.06 $15,032.71 $176.97 These NPV values are copied from the appropriate worksheet Worst Case 25% 900 Mean - Expected NPV - ($27) Standard Deviation = $202 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? IV. NPV Break-even Makes NPV =0 0 Variable Price Machine Cost #units Use Goal Seek (in Data - Forecast - What if Analysis) Key Output NPV Goal Seek 1.NO ? X t 10% T: d=CCA rate 20% Machine Cost 200.000 Shipping Costa 10.000 installation cost 30.000 Change in NOW 37,000 Units 1.200 Price - 225 Costs 125 Inflation 2% Selva Value = 20.000 Economic life 4.00 years Tatil SESIT Toy 0 By sharging cell 5823 t OK References: 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 S30,000 to install the equipment. The machinery has an economie 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. 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 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 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. 2 3 3 L. Utilize the Components Cash Flows Approach to analyze Shrek's new product project r= 10% T= 31% B. Operating Cash Flows (in 1000s) A. Initial Outlay (in 1000s) ) Yrs New Machine Cost 200.00 Intial Outlay Plus: Setup & Training & 40.00 # Units Total Initial Outlay 240.00 Price Change in NWC 37.00 Costs Initial Outlay CF 277.00 NCF-BT Tax NCF-AT PV of Operating CF's 1,700.000 0.250 0.125 212.500 65.875 146.625 $484.412 1,700.000 0.258 0.129 218.875 67.851 151.024 1,700.000 0.265 0.133 225.441 69.887 155.554 1,700.000 0.273 0.137 232.204 71.983 160.221 C. Ending Cash Flows Salvage Value NOWC Recovery NCFS PV of Salv + NOWC 25.00 37.00 62.00 $42.35 SdT D. NPV of CCA Tax Shield C = Cost S= Salv Value d=CCA rate T= r= cost of capital = no PV of CCATS CAT PVCCATS = (- r+d + 0.5r 1+r 240.00 25 20% 31% 10% 4 4 43.817 -(#)( 1 \(1 + r)" NPV = Investment Outlay + PV Project CF + PV CCATS + PV Ending CF Investment Outlay $ (277.00) PV of Operating CF 484.41 PV CCATS 43.82 PV Ending CF 42.35 NPV of project $ 293.58 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. 4 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 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 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. 1 2 2 3 4 1. Utilize the Components Cash Flows Approach to analyze Shrek's new product project r = 10% T = 31% B. Operating Cash Flows (in 1000s) A. Initial Outlay (in 1000s) Yrs New Machine Cost 200.00 Intial Outlay Plus: Setup & Training 40.00 #Units Total Initial Outlay 240.00 Price Change in NWC 37.00 Costs Initial Outlay CF 277.00 NCF-BT Tax NCF-AT PV of Operating CF's 900.000 0.191 900.000 0.175 0.125 45.000 13.950 31.050 $102.581 900.000 0.180 0.129 46.350 14.369 31.982 900.000 0.186 0.133 47.741 14.800 32.941 0.137 49.173 15.244 33.929 C. Ending Cash Flows Salvage Value NOWC Recovery NCFS PV of Salv + NOWC 25.00 37.00 62.00 $42.35 240.00 25 20% SAT D. NPV of CCA Tax Shield C = Cost S=Salv Value d=CCA rate= T = r= cost of capital = n= PV of CCATS= CdT PVCCATS = ltd. (49) 410.5") - (**)(1+r)) (+ +r 31% 10% 4 43.817 NPV = Investment Outlay + PV Project CF +PV CCATS + PV Ending CF Investment Outlay $ (277.00) PV of Operating CF 102.58 PV CCATS 43.82 PV Ending CF 42.35 NPV of project $ (88.26) Instructions for your tutor: Please input the answers in the highlighted yellow boxes. (with formulas). 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. e 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 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 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. Your Name(s) r L. Utilize the Components Cash Flows Approach to analyze Shrieves new product project INPUT DATA r= 10% Key Output: T= 31% NPV = $ (200.00) d=CCA rate = Machine Cost = 200,000 Shipping Cost = Installation cost = Change in NOWC= # Units = Price = = Costs = Inflation - Salvage Value = Economic life = years B. Operating Cash Flows (in 1000s) A. Initial Outlay (in 1000s) Yrs New Machine Cost 200.00 Intial Outlay Plus: Setup & Training #Units Capital Cost 200.00 Price Change in NOWC Costs Initial Investment CF 200.00 NCF-BT Tax NCF-AT PV of Operating CF's 1 2 3 4 $0.000 C. Ending Cash Flows Salvage Value NOWC Recovery NCFS PV of Salv + NOWC $0.00 D. NPV of CCA Tax Shield C = Cost 200.00 S=Salv Value d=CCA rate= T- r= cost of capital = n= (1 + 0.5r Sdt 1 PV of CCATS #DIV/0! PVCCATS = art 3) ()-(*) (a ton) +53 1+r r+ +r)" NPV = Investment Outlay + PV Project CF + PV CCATS + PV Ending CF Investment Outlay $ (200.00) PV of Operating CF PV CCATS PV Ending CF NPV of project $ (200.00) PROJECT Decision: ACCEPT / REJECT II. Sensitivity Analysis 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 values except one. This one variable is then changed, often by specified percentages, and the resulting effect on NPV is noted. 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 analysis for WACC as shown below. NPV NPV NPV Dev from base case WACC #DIV/0! Units Sold #DIV/01 Salvage #DIV/0! -20% #DIV/0! -20% 1000 38.231 -20% 20 #DIV/01 -10% #DIV/0! -10% 1125 58.33 -10% 22.5 #DIV/0! 0% 10% #DIV/0! 0% % 1250 86.61 0% 25 #DIV/01 10% 11% #0 #DIV/01 10% 1375 114.9 10% 27.5 #DIV/01 20% 12% #DIV/0! 20% 1500 143.18 20% 30 #DIV/0! 8% 9% NPV Values for Deviations from Base Case Sensitivity Analysis Units Sold Copy Dev from base case WACC -20% Copy -10% Cells 0% 072 to 10% 076 20% Here Salvage Copy Cells 1 0.9 0.8 0.7 0.6 0.5 0.4 0.3 0.2 0.1 Cells F72 to F76 172 to 176 Here Here 0 -20% 20% -10% 0% 10% Deviation from Base-Case Value (%) WACC Units Sold Salvage What does this graph show you? can III. Scenario Analysis Scenario analysis extends risk analysis in two ways: (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 (2) It allows us to bring in the probabilities to see the combined effects of changes in several variables Squared Deviation times probability Scenario Probability Unit Sales Unit Price NPV (1000's) 25% $250.00 Best Case Base Case 1700 1300 $ $ 293.58 (200.00) ( 50% $200.00 $175.00 $25,629.06 $15,032.71 $176.97 These NPV values are copied from the appropriate worksheet Worst Case 25% 900 Mean - Expected NPV - ($27) Standard Deviation = $202 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? IV. NPV Break-even Makes NPV =0 0 Variable Price Machine Cost #units Use Goal Seek (in Data - Forecast - What if Analysis) Key Output NPV Goal Seek 1.NO ? X t 10% T: d=CCA rate 20% Machine Cost 200.000 Shipping Costa 10.000 installation cost 30.000 Change in NOW 37,000 Units 1.200 Price - 225 Costs 125 Inflation 2% Selva Value = 20.000 Economic life 4.00 years Tatil SESIT Toy 0 By sharging cell 5823 t OK References: 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 S30,000 to install the equipment. The machinery has an economie 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. 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 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 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. 2 3 3 L. Utilize the Components Cash Flows Approach to analyze Shrek's new product project r= 10% T= 31% B. Operating Cash Flows (in 1000s) A. Initial Outlay (in 1000s) ) Yrs New Machine Cost 200.00 Intial Outlay Plus: Setup & Training & 40.00 # Units Total Initial Outlay 240.00 Price Change in NWC 37.00 Costs Initial Outlay CF 277.00 NCF-BT Tax NCF-AT PV of Operating CF's 1,700.000 0.250 0.125 212.500 65.875 146.625 $484.412 1,700.000 0.258 0.129 218.875 67.851 151.024 1,700.000 0.265 0.133 225.441 69.887 155.554 1,700.000 0.273 0.137 232.204 71.983 160.221 C. Ending Cash Flows Salvage Value NOWC Recovery NCFS PV of Salv + NOWC 25.00 37.00 62.00 $42.35 SdT D. NPV of CCA Tax Shield C = Cost S= Salv Value d=CCA rate T= r= cost of capital = no PV of CCATS CAT PVCCATS = (- r+d + 0.5r 1+r 240.00 25 20% 31% 10% 4 4 43.817 -(#)( 1 \(1 + r)" NPV = Investment Outlay + PV Project CF + PV CCATS + PV Ending CF Investment Outlay $ (277.00) PV of Operating CF 484.41 PV CCATS 43.82 PV Ending CF 42.35 NPV of project $ 293.58 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. 4 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 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 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. 1 2 2 3 4 1. Utilize the Components Cash Flows Approach to analyze Shrek's new product project r = 10% T = 31% B. Operating Cash Flows (in 1000s) A. Initial Outlay (in 1000s) Yrs New Machine Cost 200.00 Intial Outlay Plus: Setup & Training 40.00 #Units Total Initial Outlay 240.00 Price Change in NWC 37.00 Costs Initial Outlay CF 277.00 NCF-BT Tax NCF-AT PV of Operating CF's 900.000 0.191 900.000 0.175 0.125 45.000 13.950 31.050 $102.581 900.000 0.180 0.129 46.350 14.369 31.982 900.000 0.186 0.133 47.741 14.800 32.941 0.137 49.173 15.244 33.929 C. Ending Cash Flows Salvage Value NOWC Recovery NCFS PV of Salv + NOWC 25.00 37.00 62.00 $42.35 240.00 25 20% SAT D. NPV of CCA Tax Shield C = Cost S=Salv Value d=CCA rate= T = r= cost of capital = n= PV of CCATS= CdT PVCCATS = ltd. (49) 410.5") - (**)(1+r)) (+ +r 31% 10% 4 43.817 NPV = Investment Outlay + PV Project CF +PV CCATS + PV Ending CF Investment Outlay $ (277.00) PV of Operating CF 102.58 PV CCATS 43.82 PV Ending CF 42.35 NPV of project $ (88.26) Instructions for your tutor: Please input the answers in the highlighted yellow boxes. (with formulas)