A B C DE G H J K L M N 0 P Q 3 You must strategically analyze the feasibility of a potential new product by Sunnies. Sunnies thinks they can sell 115,000 tubes per year at a price of $4 each for 3 years, after which the product will be obsolete. The required equipment would cost $150,000, plus another $25,000 for shipping and installation. Current inventory would increase by $35,000, while current account payable would rise by $15,000. Variable costs (COST OF GOODS) would be 60 percent of sales revenues, and the fixed assets would be depreciated under MACRS with a 3-year life (see below). When production ceases after 3 years, the equipment should have a salvage value $13900. Sunnie's tax rate is 40 percent, and it uses a 10 percent WACC for average-risk projects. 5 Find the required Year O investment, the annual after-tax operaing cash flows, and the terminal year cash flow, and then calculate the project's NPV, IRR, MIRR, and payback. 7 8 10 11 Input Data 12 Equipment cost plus installation 40% 13 Increase in Inventory $175,000 $20,000 Tax rate WACC 10% 14 Salvage Value $13,900 15 Unit sales 115,000 16 Sales price per unit 17 Operating costs (% of sales) 18 Variable Cost Per Unit 19 Equipment Depreciation Schedule% 20 Year 2 45.0% 40.0% 21 22 Equipment 23 Inventory 24 25 26 Unites Sales 27 Price Per Unit 28 Total revenues 29 Variable costs (60%) COST OF GOODS 30 Depreciation (equipment) 31 Total Costs 32 Oper. Income before taxes (EBIT) 33 Taxes on operating Income (40%) 34 A-T operating Income 35 Add back depreciation 36 Operating cash flow 37 Salvage value 38 Terminal Year Cash Flows NOTE: DO NOT HAVE THE TAX ON SALVAGE VALUE) 39 NOTE: DO NOT HAVE THE RECOVERY OF WORKING CAPITAL SECTION 40 Net Cash Flow (Time line of cash flows) 41 42 43 Now calculate the project's NPV, IRR, MIRR, and payback. Do these indicators suggest that the project should be 44 accepted? 45 Results 46 NPV 47 48 IRR (Note: Do not use fx function but use the general calculations with formule) (Note: IRR uses fx) 49 50 MIRR 51 (Note: Use general formula for calculation on Cash Flow and use fx for 52 IN 53 54 55 56 57 58 Payback 50 50 (Note: Do not use fx function but use the general calculations with formula) (Note: Do not use fx function but use the general calculations with formula) 61 Discounted Payback Years 62 63 64 9 Operating Cash Flows over the Project's Life: $4.00 60% $2.40 Initial Cost Year 1 Y PV PMT FV Year 3 15.0% DE F G 34 A-T operating Income 35 Add back depreciation. 36 Operating cash flow 37 Salvage value 38 Terminal Year Cash Flows 39 40 Net Cash Flow (Time line of cash flows) 41 42 43 Now calculate the project's NPV, IRR, MIRR, and payback. Do these Indicators suggest that the project should be 44 accepted? 45 Results 46 NPV 47 48 IRR 49 50 MIRR 51 52 N 53 IY 54 55 PV PMT FV 56 57 58 Payback 59 60 61 Discounted Payback Years 62 63 64 VARIABL 65 Sales Price E COSTS 66 Scenario Probability Unit Sales per Unit (COG) 67 68 $5.00 $2.00 Best Case Base Case 25% 50% 25% 140,000 115,000 69 $4.00 $2.40 70 Worst Case 100,000 $3.00 $3.00 71 72 Expected NPV = 73 Standard Deviation = 74 Coefficient of Variation = Std Dev / Expected NPV = 75 76 d. If the project appears to be more or less risky than an average project, find its risk-adjusted NPV, IRR, and payback. 77 CV Range for Risk Control 0.5 78 CV range of firm's average-risk project: to 1.5 79 Low-risk WACC = WACC High-risk WACC = 7% 10% 80 81 13% 82 83 Risk-adjusted WACC = (Use IF fuction to set the condition) 84 Risk adjusted NPV = 85 IRR= 86 MIRR= B NPV H 1 J K L M N O NOTE: DO NOT HAVE THE TAX ON SALVAGE VALUE) NOTE: DO NOT HAVE THE RECOVERY OF WORKING CAPITAL SECTION (Note: Do not use fx function but use the general calculations with formula) (Note: IRR uses fx) (Note: Use general formula for calculation on Cash Flow and use fx for (Note: Do not use fx function but use the general calculations with formula) (Note: Do not use fx function but use the general calculations with formula) A B C DE G H J K L M N 0 P Q 3 You must strategically analyze the feasibility of a potential new product by Sunnies. Sunnies thinks they can sell 115,000 tubes per year at a price of $4 each for 3 years, after which the product will be obsolete. The required equipment would cost $150,000, plus another $25,000 for shipping and installation. Current inventory would increase by $35,000, while current account payable would rise by $15,000. Variable costs (COST OF GOODS) would be 60 percent of sales revenues, and the fixed assets would be depreciated under MACRS with a 3-year life (see below). When production ceases after 3 years, the equipment should have a salvage value $13900. Sunnie's tax rate is 40 percent, and it uses a 10 percent WACC for average-risk projects. 5 Find the required Year O investment, the annual after-tax operaing cash flows, and the terminal year cash flow, and then calculate the project's NPV, IRR, MIRR, and payback. 7 8 10 11 Input Data 12 Equipment cost plus installation 40% 13 Increase in Inventory $175,000 $20,000 Tax rate WACC 10% 14 Salvage Value $13,900 15 Unit sales 115,000 16 Sales price per unit 17 Operating costs (% of sales) 18 Variable Cost Per Unit 19 Equipment Depreciation Schedule% 20 Year 2 45.0% 40.0% 21 22 Equipment 23 Inventory 24 25 26 Unites Sales 27 Price Per Unit 28 Total revenues 29 Variable costs (60%) COST OF GOODS 30 Depreciation (equipment) 31 Total Costs 32 Oper. Income before taxes (EBIT) 33 Taxes on operating Income (40%) 34 A-T operating Income 35 Add back depreciation 36 Operating cash flow 37 Salvage value 38 Terminal Year Cash Flows NOTE: DO NOT HAVE THE TAX ON SALVAGE VALUE) 39 NOTE: DO NOT HAVE THE RECOVERY OF WORKING CAPITAL SECTION 40 Net Cash Flow (Time line of cash flows) 41 42 43 Now calculate the project's NPV, IRR, MIRR, and payback. Do these indicators suggest that the project should be 44 accepted? 45 Results 46 NPV 47 48 IRR (Note: Do not use fx function but use the general calculations with formule) (Note: IRR uses fx) 49 50 MIRR 51 (Note: Use general formula for calculation on Cash Flow and use fx for 52 IN 53 54 55 56 57 58 Payback 50 50 (Note: Do not use fx function but use the general calculations with formula) (Note: Do not use fx function but use the general calculations with formula) 61 Discounted Payback Years 62 63 64 9 Operating Cash Flows over the Project's Life: $4.00 60% $2.40 Initial Cost Year 1 Y PV PMT FV Year 3 15.0% DE F G 34 A-T operating Income 35 Add back depreciation. 36 Operating cash flow 37 Salvage value 38 Terminal Year Cash Flows 39 40 Net Cash Flow (Time line of cash flows) 41 42 43 Now calculate the project's NPV, IRR, MIRR, and payback. Do these Indicators suggest that the project should be 44 accepted? 45 Results 46 NPV 47 48 IRR 49 50 MIRR 51 52 N 53 IY 54 55 PV PMT FV 56 57 58 Payback 59 60 61 Discounted Payback Years 62 63 64 VARIABL 65 Sales Price E COSTS 66 Scenario Probability Unit Sales per Unit (COG) 67 68 $5.00 $2.00 Best Case Base Case 25% 50% 25% 140,000 115,000 69 $4.00 $2.40 70 Worst Case 100,000 $3.00 $3.00 71 72 Expected NPV = 73 Standard Deviation = 74 Coefficient of Variation = Std Dev / Expected NPV = 75 76 d. If the project appears to be more or less risky than an average project, find its risk-adjusted NPV, IRR, and payback. 77 CV Range for Risk Control 0.5 78 CV range of firm's average-risk project: to 1.5 79 Low-risk WACC = WACC High-risk WACC = 7% 10% 80 81 13% 82 83 Risk-adjusted WACC = (Use IF fuction to set the condition) 84 Risk adjusted NPV = 85 IRR= 86 MIRR= B NPV H 1 J K L M N O NOTE: DO NOT HAVE THE TAX ON SALVAGE VALUE) NOTE: DO NOT HAVE THE RECOVERY OF WORKING CAPITAL SECTION (Note: Do not use fx function but use the general calculations with formula) (Note: IRR uses fx) (Note: Use general formula for calculation on Cash Flow and use fx for (Note: Do not use fx function but use the general calculations with formula) (Note: Do not use fx function but use the general calculations with formula)