Question

please, i need help as soon as possible.

B- AutoSave Of H File Home Insert Assignment2 (1) - Excel Page Layout Formulas Data Parta Draw fo Review A1 x 1 Parta 2 Pattern: Time Series Plot SUSAN 19 Part 6 20 Regression Model: 21 Estimated Nitrogen Dioxide 9-10am: 22 Estimated Nitrogen Dioxide 3-4pm: 24. Part 25 Regression Model: 26 Estimated Nitrogen Dioxide 9-10am: 27 Estimated Nitrogen Dioxide 3-4pm: 29 Part 01 02 03 04-data Select destination and press ENTER or choose Paste 04 05

please, i need help as soon as possible.

Answer 1

a)

Time series plot

The time series plot is obtained in excel by following this step in excel.

Select all the data values > INSERT > Recommended Charts > X Y (Scatter) > Scatter with Smooth Lines and Markers > OK. The screenshot of the chart is shown below,

Time series plot 80 70 60 50 40 30 20 10 0 0 10 30 40 20

From the plot we can see that there is a slightly upward pattern in sales data and the data values shows up and down seasonal pattern in the sales data with period 6.

b)

Let the seasonal indexes for each hours are defined as,

Indexes	Description
s1	1 if 6:00-7:00 selected otherwise 0
s2	1 if 7:00-8:00 selected otherwise 0
s3
s4
s5
s6
s7
s8
s9
s10
s11

Regression analysis

The multiple regression analysis is done in excel by following steps

Step 1: Write the data values in excel. The data values are shown below,

G HI JK L M A CD E F 1 Level s6 sl s2 s3 s4 s5 s7 s8 s9 s10 s11 0 0 2 25 1 0 0 0 0 0 0 0 0 3 28 0 1 0 0 0 0 0 0 0 0 0 4 35 0 0 1 0 0 0 0 0 0 0 0 5 50 0 0 0 1 0 0 0 0 0 0 0 6 60 0 0 0 0 1 0 0 0 0 0 0 7 60 0 0 0 0 0 1 0 0 0 0 0 40 0 0 0 0 0 0 1 0 0 0 0 9 35 0 0 0 0 0 0 0 1 0 0 0 10 30 0 0 0 0 0 0 0 0 1 0 0 11 25 0 0 0 0 0 0 0 0 0 1 0 12 25 0 0 0 0 0 0 0 0 0 0 1 13 20 0 0 0 0 0 0 0 0 0 0 0 14 28 0 0 0 0 0 0 0 0 0 0 15 30 0 1 0 0 0 0 0 0 0 0 0 LL

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

OME ОМЕ INSERT PAGE LAYOUT FORMULAS DATA REVIEW Data Analysis ZA Data Analysis ? X A Analysis Tools OK Covariance n A Descriptive Statistics Exponential Smoothing F-Test Two-Sample for Variances Fourier Analysis Histogram Moving Average Random Number Generation Cancel Help Rank and Percentile Regression

Step 3: Select Input Y Range: 'Level' column, Input X Range: 'S1, S2,.....S11' column then OK. The screenshot is shown below,

HIJK LM A C DE G 1 Level s1 s2 s3 s4 s5 s6 s7 s8 s9 s10 s11 2 25 1 0 0 0 0 0 0 0 0 0 3 28 0 1 0 0 0 0 0 0 0 0 0 35 0 0 1 0 0 0 0 0 0 0 0 5 50 0 0 0 1 0 0 0 0 0 0 0 Regression X 6 7 Input ок Input Y Range: SA$1:SAS37 Cancel 9 Input X Range SCS1:SMS37 10 Help 11 Labels Constant is Zero 12 95 Confidence Level: % 13 Output options 14 Output Range New Worksheet Ply 15 16 New Workbook 17 Residuals 18 Residuals Residual Plots 19 Standardized Residuals Line Fit Plots 20 Normal Probability 21 Normal Probability Plots 22 H H LL LO st LO

The result is obtained. The screenshots are shown below,

A B C 15 Coefficientsandard Err tStat 16 17 Intercept 21.66667 3.8658055.604698 18 s1 7.666667 5.467073 1.402335 0 11.66667 5.4670732.133988 o 19 s2 20 s3 16.66667 5.467073 3.048554 0 21 s4 34.33333 5.467073 6.280021 1 22 s5 42.33333 5.467073 7.743327 5 23 s6 45 5.467073 8.231095 24 s7 28.33333 5.4670735.182541 2 25 s8 18.33333 5.4670733.353409 0 26 s9 13.33333 5.4670732.438843 0 27 s10 28 s11 3.333333 5.467073 0.609711 o 1.666667 5.467073 0.304855 0 29

The regression equation is.

Y 21.67 7.67 x S11.67 x S2 16.67 x S334.33 x S4+42.33 x S545 x S6 + 28.33 x S718.33 x Ss13.33 x Sg3.33 x S101.67 x S11

The seasonal forecast for September 25 is obtained by putting the value of independent variables in above regression equation,

Forecast

Since 9:00-10:00 is the period number 4, the forecast is 56,

Since 3:00-4:00 is the period number 10, the forecast is 25

X -$Q$3+$Q$4*C5+$Q$5*D5+$Q$6*E5+$Q$7*F5+$Q$8*G5+$Q$9* N5 H5+$Q$10*15+$Q$11*J5+$Q$12*K5+$Q$13*L5+$Q$14*M5 c B C F G H J К L М C P O 1 Period s1 s2 s3 s4 s5 s6 s7 s8 s9 s10 s11 Forecast 0 29.3333 Coefficients 2 1 1 0 0 0 0 0 0 0 0 0 33.3333 3 2 0 1 0 0 0 0 0 0 0 0 Intercept 21.66667 0 38.3333 4 3 0 0 1 0 0 0 0 0 0 0 s1 7.666667 5 56 4 0 0 0 1 0 0 0 0 0 0 0 s2 11.66667 64 6 5 0 0 0 0 1 0 0 0 0 0 0 s3 16.66667 0 66.6667 7 6 0 0 0 0 0 1 0 0 0 0 S4 34.33333 8 7 0 0 0 0 0 0 1 C 0 0 50 s5 42.33333 9 8 0 0 0 0 0 0 C 1 0 0 0 40 s6 45 10 0 0 0 0 0 0 0 C 1 0 0 35 s7 28.33333 11 10 0 0 0 0 C 0 0 1 0 25 s8 18.33333 12 11 0 0 0 0 0 0 0 C 0 0 1 23.3333 13.33333 0 21.6667 3.333333 13 12 0 0 0 0 0 0 0 0 0 0 s10 14 13 1 0 0 0 0 C 0 0 0 0 0 29.3333 s11 1.666667 15 14 0 1 0 0 0 0 0 0 0 0 0 33.3333 16 15 0 0 1 0 0 0 0 0 0 0 0 38.3333 17 16 0 0 0 1 0 0 0 0 0 0 0 56 C C O O C O O O C LL OH LD LO N

c)

Regression analysis with trend

The multiple regression analysis is done in excel by following steps

Step 1: Write the data values in excel. The data values are shown below,

JK L M GHI B C D E F Level Period s1 s2 s3 s4 s5 s7 s8 s9 s10 s11 1 s6 2 25 1 1 0 0 0 0 0 0 0 0 3 28 2 0 1 0 0 0 0 0 0 0 0 0 4 35 3 0 0 1 0 0 0 0 0 0 0 5 50 4 0 0 0 1 0 0 0 0 0 0 0 6 60 5 0 0 0 0 1 0 0 0 0 0 0 7 60 6 0 0 0 0 0 1 0 0 0 0 0 8 40 7 0 0 0 0 0 0 1 0 0 0 0 35 8 0 0 0 0 0 0 0 1 0 0 0 10 30 9 0 0 0 0 0 0 0 0 1 0 0 0 11 25 10 0 0 0 0 0 0 0 0 1 12 25 11 0 0 0 0 0 0 0 0 0 0 1 13 20 12 0 0 0 0 0 0 0 0 0 0 14 28 13 1 0 0 0 0 0 0 0 0 0 0 15 30 14 0 1 0 0 0 0 0 0 0 0 0 15 16 35 0 1 0 0 0 0 0 0 0 0 17 48 16 0 0 0 1 0 0 0 0 0 0 0 18 60 17 0 0 0 0 1 0 0 0 0 0 0 OHO st LC LC LC LC

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

Step 3: Select Input Y Range: 'Level' column, Input X Range: 'S1, S2,.....S11 and Period' column then OK. The screenshot is shown below,

The result is obtained. The screenshots are shown below,

A B 15 Coefficientsandard Err tSt 17 Intercept 11.16667 3.001811 3.719 0.4375 0.072212 6.05 12.47917 3.556046 3.509 16.04167 3.540615 4.530 20.60417 3.5265965.842 37.83333 3.514005 10.76 45.39583 3.502857 12.95 16 18 Period 19 s1 20 s2 21 s3 22 s4 23 s5 24 s6 47.625 3.493168 13.63 25 s7 30.52083 3.484947 8.757 20.08333 3.4782075.774 14.64583 3.472956 4.217 3.4692 1.213 2.104167 3.466945 0.606 26 s8 27 s9 28 s10 4.208333 29 s11 30 LC

The regression equation is.

Y 11.17+0.44 x period12.48 x s1+16.04 x s2+20.60 x s3+37.83 x s4+ 45.40 x s547.63 x s6+30.52x S7+20.08x s8+14.65 x s94.21 x s10+2.10 x s1

The seasonal forecast for September 25 is obtained by putting the value of independent variables in above regression equation,

Forecast

Since September 25 from 9:00-10:00 is the period number 868 (72 days 4th period of 73rd day), the forecast is 428.75,

Since September 25 from 3:00-4:00 is the period number 874 (72 days 10th period of 73rd day), the forecast is 397.75

M G 1 Period s1 s2 s3 s4 s5 s6 s7 s8 s9 s10 s11 Forecast 0 402.0833333 0 406.0833333 Coefficients 2 865 1 0 0 0 0 C 0 C 0 0 11.17 866 0 1 0 0 0 0 0 0 0 0 Intercept 0 411.0833333 4. 867 0 0 1 0 0 0 0 0 0 0 s1 12.48 5 868 C 0 0 1 0 0 0 0 0 0 0 428.75 s2 16.04 20.60 6 869 0 0 0 0 1 0 0 0 0 0 0 436.75 s3 7 870 C 0 0 0 0 1 0 0 0 0 0 439.4166667 S4 37.83 45.40 8 871 0 0 0 0 0 0 1 C 0 0 0 422.75 s5 9 872 C 0 0 0 0 0 1 0 0 0 412.75 s6 47.63 10 873 0 0 0 0 0 0 0 0 1 0 0 407.75 s7 30.52 20.08 11 874 0 0 0 0 0 0 0 0 0 1 0 397.75 s8 1 396.0833333 12 875 0 0 0 0 0 0 0 0 14.65 0 394.4166667 13 876 0 C 0 0 C 0 0 0 0 0 s10 4.21 0 407.3333333 0 411.3333333 14 877 1 0 0 0 0 0 0 0 0 0 s11 2.104167 Period 15 878 0 1 0 0 0 0 0 0 0 0 0.44 16 879 0 0 1 0 0 0 C 0 0 416.3333333 O O O O O O O O O O O O O O LON

d)

The effectiveness of the model can be compare by calculating the MAD (mean absolute deviation value for each method)

ΣActual forecast) MAD period

Without trend,

MAD=4.33

With trend,

MAD=2.50

Since the MAD with trend is less, hence method with trend is more effective.