Problem $2.$

$($Ebook Case $1\mathrm{.}1$ on page $3$ VRX Revisited: Updating the Model with New Data Over Time

In Section $1\mathrm{.}4,$ the analytics study team was tasked with making a recommendation for the best level of advertising for the VRX$2000$ during the upcoming first quarter $($Q$1).$ They used historical data and an analysis of the relationship between sales and advertising to determine that sales would be approximately $\backslash (7\mathrm{.}01\backslash$sqrt$\{$A d v e r t i s i n g ~$\}+400\backslash ).$ Then, factoring in the quarterly fixed costs of $\backslash (\backslash$$ $100,000\backslash ),$ the unit variable cost of $\backslash (\backslash$$ $295\backslash ),$ and the unit revenue of $\backslash (\backslash$$ $400\backslash )$ for the VRX $2000,$ they used optimization to determine that the advertising level that would maximize profit was approximately $\backslash (\backslash$$ $135,000\backslash ).$ As indicated in Figure $1\mathrm{.}9,$ this was predicted to lead to sales of approximately $2,979$ units for the VRX$2000$ with a total profit of $\backslash (\backslash$$ $77,425\backslash ).$ 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 $\backslash (\backslash$$ $135,000\backslash )$ for Q$1.$ It is now the end of Q$1,$ 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 Q$2.$

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 Q$2$ to be similar to a typical Q$1($for any given advertising level$).$ Should VRX reuse the existing model that would suggest they should also set the advertising level at $\backslash (\backslash$$ $135,000\backslash )$ for Q $2?$ 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 Q$1,$ and then recreate a scatter plot similar to Figure $1\mathrm{.}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 $\backslash (\backslash$approx a $\backslash$sqrt$\{$A d v e r t i s i n g$\}+$b $\backslash ),$ incorporate the new data from Q$1$ and recreate a new scatter plot similar to Figure $1\mathrm{.}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 $\backslash ($ a $\backslash )$ and $\backslash ($ b $\backslash )$ that provides the best fit for the prediction formula $($Sales $\backslash (\backslash$approx a $\backslash$sqrt$\{\backslash$text $\{$ Advertising $\}\}+$b $\backslash )).$

d$.$ Using the updated prediction formula from part $\backslash ($ c $\backslash ),$ update the optimization model from Figure $1\mathrm{.}7,$ and then re$-$run Solver to find the best advertising level for Q$2.$ Compare the results from this updated optimization model to the results from the original model shown in Figure $1\mathrm{.}9.$ How did factoring in the new data from Q$1$ affect the results?

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

Remarks:

$-$ Show ALL your work in one sheet for full credit.

$-$ Round the expected units sold to the nearest integer.