03-09-2021 MBA 700 HOMEWORK # 4 URN IN FOR GRADING Download a copy of the CHICAGO.XLS worksheet. Read "The Nielsen Frozen Orange Juice Data" (follows). For the Sunshine Valley data, create a new variable SVAVPR which is the weekly average price. Clearly this equals the total revenue dollars (SV$) divided by total units sold (SVE) for each week. Print out a few lines of data to show that you have done this step correctly. Use Excel's Chart Wizard to create an appropriately labeled Scatterplot of units sold (SVE) versus weekly average price (SVAVPR) for Sunshine Valley (i.e., with SVE on the Y-axis and SVAVPR on the X-axis). "Right click" on any of the data points and insert a linear trend line. Estimate a linear demand function for the Sunshine Valley brand by calculating the simple regression of units sold (SVE) on weekly average price (SVAVPR). Include the following: a. Your regression output. (Note: please do not include a listing of the data nor a listing of the residuals.) What numbers represent the total variation for the dependent variable, the coefficient of determination, the standard error of estimate, and the intercept and slope coefficients? b. A plot of the residual values as a function of weekly average price. Make sure your plot is large enough so one can comfortably read it! C. A calculation of the estimate of the demand for the Sunshine Valley brand at prices of $25, $30, and $35. d. Provide a brief (no more than one-quarter page) summary of the fit and usefulness of your model as a demand function for Sunshine Valley. Concentrate on what is good and bad with this model. (Saying that there may be other variables that we might include may be true, but it isn't particularly useful at this point.)