For the new-product model in Problem 9 of Chapter 11, suppose that the first-yea
ID: 2566215 • Letter: F
Question
For the new-product model in Problem 9 of Chapter 11, suppose that the first-year sales volume is normally distributed with a mean of 100,000 units and a stan- dard deviation of 10,000. Use the NORM.INV func- tion 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.
Problem 9 -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. Develop a spreadsheet model to calculate the net present value of profit over a 3-year period, assum- ing a 4% discount rate.
Explanation / Answer
Calculation
Year
Unit
Remarks
Sales price
Remarks
Variable cost per unit
Remarks
Fixed cost
Remarks
1
80000
Given in question
12
Given in question
3
Given in question
400000
Given in question
2
83200
80000+4%
12.5
12+0.5
3.15
3+5%
432000
400000+8%
3
86528
83200+4%
13
12.5+0.5
3.3075
3.15+5%
466560
432000+8%
Calculation of total sales and total variable cost
Year
1
2
3
Unit
80000
83200
86528
Selling price per unit
12
12.5
13
Sales revenue
960000
1040000
1124864
Unit
80000
83200
86528
Variable cost per unit
3
3.15
3.3075
Total variable cost
240000
262080
286191.4
Total fixed cost
400000
432000
466560
Net present value
Year
1
2
3
Sales revenue
960000
1040000
1124864
Total variable cost
240000
262080
286191.4
Total fixed cost
400000
432000
466560
Total cost
640000
694080
752751.4
Profit ( cash flow)
320000
345920
372112.6
Discount factor @ 4%
0.961538
0.924556
0.888996
Present value@4%
307692.3
319822.5
330806.8
Total net present value (sum of above)
958321.6
Use of function
Probability corresponding to normal distribution
Mean (unit)
Standard deviation (unit)
No of unit (x) ( obtain using NORM.INV function)
0.95
100000
10000
116449
0.9
100000
10000
112816
0.85
100000
10000
110364
0.8
100000
10000
108416
0.75
100000
10000
106745
0.7
100000
10000
105244
0.65
100000
10000
103853
0.6
100000
10000
102533
0.55
100000
10000
101257
0.5
100000
10000
100000
Year
Unit
Remarks
Sales price
Remarks
Variable cost per unit
Remarks
Fixed cost
Remarks
1
80000
Given in question
12
Given in question
3
Given in question
400000
Given in question
2
83200
80000+4%
12.5
12+0.5
3.15
3+5%
432000
400000+8%
3
86528
83200+4%
13
12.5+0.5
3.3075
3.15+5%
466560
432000+8%