Ch10 Excel Practice Problems Student Without Solution

School: Michigan State University - Course: FOR 3 - Subject: Accounting

You are hired to analyze a potential new product--a caulking compound that Cory' R&D people developed for the residential construction industry.Cory's marketing manager thinks they can sell 115,000 tubes per year at a price of $3.25 each for 3 years, after which the product will be obsolete.The required equipment would cost $150,000, another $25,000 for shipping and installation (Total book value of the equipment is 150,000 plus 25,000). Net working capital would increase by $20,000 at the beginning of the project, and get released at the end of year 3. Variable cost per unit is $1.95, fixed costs (exclusive of depreciation) would be $70,000 per year, and fixed assets would be depreciated under MACRS with a 3-year life.(MACRS depreciation rates are 33%, 45%, 15% of the total book value at year 0 for year 1, 2, 3.)When production ceases after 3 years, the equipment should have a market value of $15,000.Cory's tax rate is 20%, and it uses a 10% WACC for average-risk projects. Step 1: depreciation schedule33%45%15% 0123 depreciation amount$57,750$78,750$26,250 book value$175,000$117,250$38,500$26,250 Set up table for step 2: unit sales115,000 sales price per unit$3.25 variable cost per unit$1.95 fixed cost$70,000 tax rate20% Required Rate of Return (WACC)10% Step 2: income statement 0123 sales revenue$373,750$373,750$373,750 variable costs$224,250$224,250$224,250 fixed costs$70,000$70,000$70,000 depreciation$57,750$78,750$26,250 EBIT$21,750$750$53,250 TAXES$4,350$150$10,650 EBIT*(1-Tax rate)$17,400$600$42,600 Step 3: adjusting for cash flows adjust for depreciation$57,750$78,750$26,250 OPERATING CASH FLOW $75,150$79,350$68,850 adjust for NWC-$20,000 adjust for NWC$20,000 investing activities-$175,000