Question

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 $400000. Per-unit costs are expected to increase 5% per year. Fixed costs are expected to increase 8% per year. develope a spread sheet model to calculate the net present value of profit over a 3- year period, assuming a 4% discount rate.

Answer 1

1 Year 2 Number of Units 3 Selling Price 4 Total Revenue 5 Variable Cost per 6 Total Variable 7 Fixed Costs 8 Total Cost 9 Profit 10 NPV at 4% Change 4% 2 80000ROUND(B2*(1+SF$2),0) <=ROUND(C2*( 1+$F$2),0) increase $12 B3+SF$3 $0.50Increase $960,000 <C3*C2 NA 5% increase NA 8% increase NA NA 496 $3-ROUND(B5*(1+SF$5),2) -ROUND(C5 * (1+$F$5),2) $240,000<C5*C2 400,000-ROUND(B7*(1+SF$7),2ROUND(C7(1+F$7),2) $640,000 <C7+C6 S320,000<C4-C8 <=D4-D8 <=ROUND(B9/(1+$F$10)^B1,2) <-ROUND(C9/(1+$F$10)AC1,2) <-ROUND(D9/(1+$F$10)^D1,2) 12 NPV of Profit SUM(B10:D10) FORMULAS