Hello, I have no idea where to start with this problem and I have to do itin exc

Question

Hello,

I have no idea where to start with this problem and I have to do itin excel
You are considering a new product launch. The project will cost$890,000, have a four-year life, and have no salvage value;depreciation is straight-line to zero. Sales are projected at 190units per year; price per unit $18,200, variable cost per unit willbe $14,600, and fixed costs will be $295,000 per year. The requiredreturn on the project is 12 percent, and the relevant tax rate is35 percent. a. Based on your experience, you think the unit sales, variablecost, and fixed cost projections given here are probably accurateto with +- 10 percent. What are the upper and lower bounds forthese projections? What is the base-case NPV? What are thebest-case and the worst-case scenarios? b. Evaluate the sensitivity of your base-case NPV to changes infixed costs? Hello,

I have no idea where to start with this problem and I have to do itin excel
You are considering a new product launch. The project will cost$890,000, have a four-year life, and have no salvage value;depreciation is straight-line to zero. Sales are projected at 190units per year; price per unit $18,200, variable cost per unit willbe $14,600, and fixed costs will be $295,000 per year. The requiredreturn on the project is 12 percent, and the relevant tax rate is35 percent. a. Based on your experience, you think the unit sales, variablecost, and fixed cost projections given here are probably accurateto with +- 10 percent. What are the upper and lower bounds forthese projections? What is the base-case NPV? What are thebest-case and the worst-case scenarios? b. Evaluate the sensitivity of your base-case NPV to changes infixed costs?

Explanation / Answer

This is difficult to answer without posting an Excelspreadsheet, but I'll give it a shot . . . a. Lay out an annual cash flow model that starts at time zeroand has four annual periods to the right. If you're going touse the NPV function, you can label the columns/periods whateveryou'd like, and that should work since the question appears toconsider annual cash flows (the XNPV function can offer a moreprecise answer with intra-year cash flows; to use it, you'll wantto have the columns labeled as dates, and you might have to loadthe analysis toolkit). Your CF (cash flow) at time=0 is equal to ($890,000), theproject cost. For each of years 1 through 4, your cash flow is: [(Number of units) x (Price per unit - variable cost per unit)- fixed costs - depreciation] x (1-tax rate) + depreciation On excel, lay out each of these (and the "initial projectcost") in a separate row under your column headings. You'llalso want cells with your tax rate, your discount rate and yoursensitivities. Inserting numbers: [(190 x (18,200-14,600) - $295,000 - ($890,000 / 4)] x (1 -0.35) + $890,000 / 4 = $330,725 per year So, the Annual CF line on your spreadsheet should read, fromleft to right, -$890,000, $330,725, $330,725, $330,725,$330,725. Use the NPV function: =NPV(rate, cashflows); you should get a base case NPV, at a 12% discountrate, of $102,257. To run the upper-limit for the projections, increase the unitsales by 10% and decrease the variable and fixed costs by 10%; torun the lower-limit, decrease unit sales by 10% and increase thevariable and fixed costs each by 10%. It's probably best tolay out three different sets of numbers, so copy-and-paste thefirst one, and sensitize in the next two. The upper-limit should get you an NPV of $812,714 (cash flowsin years 1-4 are $592,701); the lower-limit should get you an NPVof -$510,404 (cash flows in years 1-4 are $104,811). b. I'd do a data table that shows what happens to NPV iffixed costs are 50% up to 200% of the $295k base. At 50%, theNPV is $362,262; at 200%, it's -$417,754. Hope this helps. Wish this were easier to illustrate,but I'm new to the site, and I don't see the ability to paste anexcel table.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.