Question

Please use Excel Solver.

Consider the following set of time series sales data for a growing company over the past 8 months: Month Sales 1 15 2 13 18 4 22 20 6 23 7 22 8 21 1. Construct a time series plot. What type of pattern exists? 2. Develop a forecast for the next month using the averaging method. 3. Develop a forecast for the next month using the naïve last-value method. 4. Develop a forecast for the next month using a four-month moving average method. 5. Use the Excel Functions SLOPE and INTERCEPT to write the linear regression prediction equation with Months as the independent variable and sales as the dependent variable. 6. Use the prediction equation to estimate the number of sales in month 9.

Answer 1

Solution:

Below will be the time series graph.

X Abhishek Rai Chart Tools Book2 Excel Share Tell me what you want to do Format Help Design View Review Data Formulas Page Layout Insert Home File Chart 2 S T O O N K H G E C 1 + Sales 2 25 4 5 20 6 15 7 8 10 11 12 A 13 14 Sales 15 O 16 17 18 19 20 21 23 24 25 26 27 Sheet1 E - 100% Display Settings 11:31 AM ENG nd) 22-Feb-19 O Type here to search

The graph shows the increasing sales pattern.

To Find the forecast by averaging method,we will find the average of sales for Period 7 & 8 to get the forecast of period 9.

To Find the forecast by naive method,we will use the sales of period 8 as the forecast of period 9.

To Find the forecast by 4 month moving average method, we will find the average of sales for Period 5,6,7 & 8 to get the forecast of period 9.

Below is the working formula for all forecasting mentioned including the Regression.

Below is the result of calculation -

Book2 Excel Abhishek Rai X O Tell me what you want to do &Share File Home Insert Page Layout Formulas Data Review View Help CA 13 A B 1 Forecast Month Sales Averaging Method Naive Method 4 Month Moving Average Method 2 1 15 13 C3 4 AVERAGE(C3:C4) EAVERAGE(C4:C5) AVERAGE(C5:C6) 18 5 CA 4 22 C5 6 - AVERAGE(C3:C6) FAVERAGE(C4:C7) 20 7 C6 AVERAGE(C6:C7) 23 C7 AVERACE S AVERAGE(C9:C10) 22 EAVERAGE( AVERAGE(C7:C10) .. 21 EC10 11 12 13 14 Regression SLOPE(C3:C10,B3:B10) EINTERCEPT(C3:C10,B3 : B10 ) Slope Intercept 15 16 17 18 19 20 21 22 23 24 25 26 27 Sheet1 Display Settings 100 11:40 AM ENG O Type here to search nd) 22-Feb-19

Book2 Excel Abhishek Rai X O Tell me what you want to do Share File Home Insert Page Layout Formulas Data Review View Help C4 13 A C D E F G H L M N 1 Forecast Averaging Method Naive Method 4 Month Moving Average Method Month Sales 2. 15 3 1 4 2 13 15 5 18 14 13 6 22 15.5 18 7 20 20 22 17 23 18.25 8 21 20 22 20.75 25 21 5 21 11 21.5 12 13 14 Regression Slope 15 1.19047619 Intercept 16 13.89285714 17 18 19 20 21 22 23 24 25 26 27 Sheet1 E Display Settings - 1009 11:40 AM ENG O Type here to search ndi) 22-Feb-19

From above, below will be the regression equation -

Regression Equation = 13.8929 + 1.1905 t (up to 4 decimal places)

Forecast for Period 9 (t = 9)

Forecast = 24.6074

Forecast = 24.61