Answer the following based on the provided data:

Quarter 1 N 3 4 5 6 7 8 9 10 Advertising $30,000 $20,000 $15,000 $40,000 $10,000 $50,000 $5,000 $40,000 $20,000 $10,000 $60,000 $5,000 $35,000 $15,000 $70,000 $25,000 $30,000 $60,000 $80,000 $50,000 Sales $1,200,000 $880,000 $1,800,000 $1,050,000 $1,700,000 $350,000 $2,500,000 $760,000 $2,300,000 $1,000,000 $1,570,000 $2,430,000 $1,320,000 $1,400,000 $1,890,000 $3,200,000 $2,200,000 $1,440,000 $4,000,000 $4,100,000 11 12 13 14 15 16 17 18 19 20 1. Apply exponential smoothing to sales revenues using a = 0.2 to set up your computations. (a) Compute the MAD, MSE, and MAPE for the exponential smoothing forecasts. Format the MAD and MSE as Numbers with 0 decimals and MAPE as a Percentage with 1 decimal. (b) Using Excel's Solver (with the GRG Nonlinear engine), determine the optimal weights to minimize the MSE and generate an Answer Report. (e) Using this technique, compute a forecast for sales in the next quarter. Format the forecast as Currency with o decimals. 2. Create a chart (with markers) showing quarterly sales and optimized exponential smoothing forecasts. (There will be two lines: one for actual sales and one for the exponential smoothing values.) (a) Use appropriate chart and axis titles with a legend on the right. (b) Place the chart on a separate sheet using the Move Chart (to a new sheet) option in the Location group on the Design tab. (e) Remove the gridlines and put a border around the plot area. 3. Create a scatter plot for sales versus advertising: (a) Use appropriate chart and axis titles with no legend. (6) Place the scatter plot on a separate sheet using the Move Chart (to a new sheet) option in the Location group on the Design tab (C) Display the regression line with equation and R value on the chart using Excel options, (a) Format the regression line as a solid line (with no dashes or dots). (e) Remove the gridlines and put a border around the plot area. 4. Run Excel's Regression tool (on a new sheet) to develop a simple linear regression model that can be used to predict sales based on advertising. (You may delete the duplicate 95% columns.) 5. On the Regression Summary Output sheet, write Excel formulas to calculate the expected sales for a quarter with advertising expenditures of $32,000, and to compute an approximate 68% prediction interval for your estimate based on the empirical rule. (Do not use the TREND function.) Format the results as Currency with 0 decimals