For a new product, sales volume in the first year is estimated to be 80,000 units and is projected to grow at a rate of 4% per year. The selling price is $12 and will increase by $0.50 each year. Per-unit variable costs are $3, and annual fixed costs are $400,000. Per-unit costs are expected to increase 5% per year. Fixed costs are expected to increase 8% per year.
a. Develop a spreadsheet model to calculate the net present value of profit over a 3-year period, assuming a 4% discount rate.
b. Construct a tornado chart and explain the sensitivity of each of the model’s parameters on the NPV of profit.
c. Suppose that the first-year sales volume is normally distributed with a mean of 100,000 units and a standard deviation of 10,000. Use the NORM.INV function and a one-way data table to conduct a Monte Carlo simulation to find the distribution of the net present value profit over the 3-year period.