In Section 1.4, the analytics study team was tasked with making a recommendation for the best level

Question:

In Section 1.4, the analytics study team was tasked with making a recommendation for the best level of advertising for the VRX2000 during the upcoming first quarter (Q1). They used historical data and an analysis of the relationship between sales and advertising to determine that sales would be approximately 7.01 √Advertising + 400. Then, factoring in the quarterly fixed costs of $100,000, the unit variable cost of $295, and the unit revenue of $400 for the VRX 2000, they used optimization to determine that the advertising level that would maximize profit was approximately $135,000. As indicated in Figure 1.9, this was predicted to lead to sales of approximately 2,979 units for the VRX2000 with a total profit of $77,425. As the analytics study team stressed, however, all of this is only a prediction. Forecasts are often wrong, at least by a little bit.

Management at VRX followed the advice of the analytics study team and set an advertising budget of $135,000 for Q1. It is now the end of Q1, and the results have come in. Actual sales were 2,847 units, somewhat lower than forecast. It is now time to set an advertising budget for Q2.

a. As VRX has not noticed substantial seasonality effects that might cause sales to fluctuate from quarter to quarter, they expect sales in a typical Q2 to be similar to a typical Q1 (for any given advertising level). Should VRX reuse the existing model that would suggest they should also set the advertising level at $135,000 for Q2? Provide some arguments for both why this might, or might not, be a good idea.

b. The analytics study team recommends incorporating the new data into the model. Add the new data from Q1, and then recreate a scatter plot similar to Figure 1.1 to visualize the relationship between sales and advertising. Does the new data suggest a significant change in this relationship?

c. Assuming the same type of square-root relationship between sales and advertising, that is, Sales ≈ a √Advertising + b, incorporate the new data from Q1 and recreate a new scatter plot similar to Figure 1.5, with Sales on the vertical axis and the square root of advertising on the horizontal axis. Use Excel’s trendline feature to determine the values for a and b that provides the best fit for the prediction formula (Sales ≈ a √Advertising + b).

d. Using the updated prediction formula from part c, update the optimization model from Figure 1.7, and then re-run Solver to find the best advertising level for Q2. Compare the results from this updated optimization model to the results from the original model shown in Figure 1.9. How did factoring in the new data from Q1 affect the results?

e. Suppose VRX sets the advertising budget for Q2 as determined in part d, rounded to the nearest $1,000. Suppose further that actual sales in Q2 turn out to be 3,012. Repeat parts b, c, and d to now determine the best advertising level for Q3.

Data from Figure 1.9