AutoSave OH Ch 12 Comprehensive Spreadsheet Problem - Excel Searth File Home Insert Page Layout Formulas Data Review View Developer Help Power Pivot X Times New Roman ' ' Number Paste BTU- 10 Wrap Text O. AEE ESE Merge & Center $ . % 88 Conditional Forma Formatting Table Styles Clipboard Font Alignment 5 Number A15 Xf B D E F G H 1 Chapter 12 Comprehensive Spreadsheet Problem 2 3 Problem 12-18 4 You must analyze a potential new product-a caulking compound that Cory Mateials' R&D people developed for use in the 5 residential construction industry. Cory's marketing manager thinks the company can sell 115,000 tubes per year at a price 6 of $3.25 each for 3 years, after which the product will be obsolete. The purchase price of the required equipment, including 7 shipping and installation costs, is $175,000, and the equipment is eligible for 100% bonus depreciation at the time of 8 purchase. Current assets (receivables and investories) would increase by $35,000, while curent liabilities (accounts payable 9 and accruals) would rise by $15,000. Variable cost per unit is $1.95, and fixed costs would be $70,000 per year. When 10 production ceases after 3 years, the equipment should have a market value of $15,000. Cory's tax rate is 25%, and it uses 11 a 10% WACC for average-risk projects. 12 13 a. Find the required Year O investment outlay after bonus depreciation is considered and the project's annual cash flows. 14 Then calculate the project's NPV, IRR, and regular payback. Assume at this point that the project is of average risk 15 16 Part 1. Key Input Data 17 Equipment cost plus installation Market value of equipment after 3 yrs 18 Increase in current assets 19 Increase in current liabilities Tax rate WACC A15 x A B C D E F G H 12 13 . Find the required Year O investment outlay after bonus depreciation is considered and the project's annual cash flows. 14 Then calculate the project's NPV, IRR, and regular payback. Assume at this point that the project is of average risk. 15 16 Part 1. Key Input Data 17 Equipment cost plus installation Market value of equipment after 3 yrs 18 Increase in current assets Tax rate 19 Increase in current liabilities WACC 20 Unit sales 21 Sales price per unit 22 Variable cost per unit (in dollars) 23 Fixed costs 24 25 26 Part 2. After-Tax Salvage Value at end of Year 3 Equipment 27 Estimated Market Value 28 Book Value 29 Expected Gain or Loss 30 Taxes paid or tax credit 31 After-tax salvage value 32 33 Part 3. Project Cash Flow Analysis 3 34 CH BA 12oroblem Number Paste Bru AA Wrap Text Merge & Center INI $ % 9 6893 Clipboard Font Alignment Number A15 X A E F G H 0 1 2 3 0 $0.00 0 $0.00 0 $0.00 B D 33 Part 3. Project Cash Flow Analysis 34 35 Investment Outlays ar Time=0 36 CAPEX-Equipment - Cost (1-T) 37 Increase in NOWC 38 39 Operating Casle Flows over the Project's Life 40 Units sold 41 Sales price 42 43 Sales revenues 44 Variable costs 45 Fixed operating costs 46 Depreciation: 100% Bonus Depreciation in Year 0 47 EBIT (Operating income) 48 Taxes on operating income (2596) 49 EBIT (1 - 1) - After-tax operating income 50 Add back depreciation 51 EBIT (1 - 1) + Depreciation 52 Terndral Castle Flows at Time = 3 53 After-tax salvage value 54 Recovery of net operating working capital 55 56 Project FCFS 12problem 0 1 Type here to search O 51 EBIT (1 - T) + Depreciation 52 Terminal Cash Flows at Time = 3 33 After-tax salvage value 4 Recovery of net operating working capital 5 6 Project FCFS 7 8 Part 4. Key Output: Evaluation of the Proposed Project 9 0 Net Present Value (at 10%) 1 IRR 2. 3 Payback period 4 5 6 CON UN 9 0