Operating Profit Assignment
February 19th, 2020
Assume that the Vern supplier experiences an increase in the cost of the special flash memory chip required by the premium model. The supplier has indicated that it will pass along the costs to Vern as an increase of $21.00 per PREM model, beginning in July.
By what percentage must unit sales volume increase each month and in total to maintain the same level of quarterly operating profit as before the cost increase? Use GOAL SEEK. Save your file and analyze your results. Explain your results and what information does this result provides to management?
Unit Sales | Unit Volume Sensitivity | 0 | ||||||
UNIT SALES | ACTUAL | FORECAST | ||||||
Month | April | May | June | July | August | September | October | |
TOTAL | 7650 | 8400 | 9200 | 10200 | 11400 | 12700 | 13200 | |
BASE | 7650 | 8400 | 7360 | 7956 | 8550 | 9017 | 9240 | |
PREMIUM | na | na | 1840 | 2244 | 2850 | 3683 | 3960 | |
Overall Sales Mix Sensitivity | 0 | |||||||
Sales Mix Sensitivity | 0.02 | 0.03 | 0.04 | 0.01 | ||||
Sales Mix % | BASE | 0.8 | 0.78 | 0.75 | 0.71 | 0.7 | ||
PREMIUM | 0.2 | 0.22 | 0.25 | 0.29 | 0.3 | |||
Sales Price Sensitivity | -0.02 | -0.02 | -0.02 | -0.02 | ||||
Unit selling price | BASE | 230 | 225.4 | 220.892 | 216.4742 | 212.14468 | ||
PREMIUM | 380 | 372.4 | 364.952 | 357.653 | 350.4999 | |||
Unit cost | BASE | 155 | 155 | 155 | 155 | 155 | ||
PREMIUM | 225 | 225 | 225 | 225 | 225 |
.