Question

After working with the data, you realize that since Half-Foods was acquired, its staffing and sales strategies has changed quite drastically and therefore the historical data is not valid any more. Especially, there has been a significant reduction of staff. So you decide to collect new data from your stores. In the table below, you can find the average number of employees and the sales for each Half-Foods store in the past six months. Build a regression model that uses average number of employees to explain sales.

Average Number of Employees 8.74 9.42 10.2 6.81 15.31 6.14 6.68 11.3 6.36 13.74 12.57 9.77 19.79 23.64 11.24 5.32 13.41 15.3 20.94 21.88 24.43 6.18 16.67 1.2 1.11 4.69 9.1 12.49 23.67 10.13 5.76 21.84 4.1 0.07 21.82 9.79 21.44 10.77 5.4 5.49 24.41 11.53 5.71 25.97 8.97 10.93 16.72 2.59 15.86 21.93 5.72 15.14 18.39 0.1 13.25 8.85 17.25 23.54 22.01 7.31 12.2 11.2 16.35 16.18 14.81 22.48 25.07 18.66 13.41 4.61 3.94 22.6 12.02 18.04 24.8 24.74 9.03 23.5 10.87 22.82 6.17 18.04 22.61 16.31 20.11 21 19.25 14.98 17.25 0.01 6.91 11.5 2.48

Sales ($M) 149.95 150.05 136.3 129.25 177.18 96.32 160.9 120.28 110.63 149.05 175.98 150.29 193.63 319.17 170.51 111.55 129.16 181.5 280.74 234.75 248.49 77.78 142.72 65.22 46.04 93.74 171.14 98.06 347.57 176.19 43.89 318.41 112.32 41.18 272.37 84.75 329.2 75.87 91.38 30.33 312.43 150.53 111.12 360.98 93.97 162.18 215.79 91.23 271.51 285.16 130.16 319.84 262.94 63.24 240.44 122.23 285.24 293.39 318.12 63.61 157.51 148.61 184.38 279.23 187.97 345.79 362.55 281.03 205.33 51.46 97.1 220.72 134.85 257.46 321.77 415.03 74.08 277.87 191.98 266.27 157.43 225.1 303.17 148.61 198.18 298.13 214.34 230.61 219.41 96.03 168.33 155.29 67.64

Based on this analysis, how many additional sales (in $M) does an extra employee bring?

What is the p-value of the coefficient of Average Number of Employees?

You are not satified with the model created in Question 3. After thinking long and hard about this, you discover that sales is not a linear function of number of employees. It is possible that adding new employees will not have the same effect after some point. Therefore, you decide to model a curvilinear relationship.

Regress sales on number of employees and squared number of employees. Using this model, predict the sales of a store with 18 employees.

What are the expected sales for this store?

Answer 1

The regression analysis is done in excel by following steps

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

BC BD Average Number of Employees, x Sales ($M), y 1 8.74 149.95 9.42 3 150.05 4 10.2 136.3 5 6.81 129.25 177 18 6 15.31.

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

SERT REVIEW VIE PAGE LAYOUT FORMULAS DATA Data Analysis ZA A7 Data Analysis Analysis Tools OK Exponential Smoothing F-Test Two-Sample for Variances Fourier Analysis Histogram Moving Average Random Number Generation Rank and Percentile Regression Sampling t-Test: Paired Two Sample for Means Cancel Help X

Step 3: Select Input Y Range: 'y' column, Input X Range: 'x' column then OK. The screenshot is shown below,

ВН ВС BD BF BG BI Average Number of Employees, x Sales ($M), y 149.95 150 as! 8.74 2 Regression 4 Input 5 OK Input Y Range: SBDS 1:SBD594 6 Cancel 7 Input X Range: SBCS1:SBCS94 Help Labels Constant is Zero 95 % Confidence Level: 10 11 Output options 12 O output Range: 13 New Worksheet Ply: 14 O New Workbook 15 Residuals 16 Residuals Residual Plots Standardized Residuals Line Fit Plots 17 X CC CO LO

The result is obtained. The screenshot is shown below,

A C D 1 SUMMARY OUTPUT 2 3 Regression Statistics 4 Multiple F 0.893515 R Square 6 Adjusted 0.796153 5 0.798368 7 Standard 41.30716 8 Observati 93 10 ANOVA df gnificance F 11 SS MS F 12 Regressio 1 614803.7 614803.7 360.3178 2.12E-33 13 Residual 91 155271.6 1706.282 14 Total 92 770075.3 15 P-value Lower 95%Upper 95 %ow Coefficientsandard Err 16 t Stat 17 Intercept 33.71137 9.091223 3.708123 0.000359 15.65277 51.76997 15 18 Average N 11.37178 0.599081 18.98204 2.12E-33 10.18178 12.56179 1 LL

The regression equation is,

y=33.7114-+11.3718.2

For one additional employee sales will increase by $11.3718

P-value 2.12E-33 0.0000

Now, the regression model for number of employees and squared number of employees is done in excel by following steps

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

BC BD BE Average Number of 1 Employees, x Sales ($M), y x^2 2 8.74 149.95 76.3876 3 9.42 150.05 88.7364 4 10.2 136.3 104.04 5 6.81 129.25 46.3761 6 15.31 177.18 234.3961 96.32 37.6996 7 6.14 LLI LC

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

Step 3: Select Input Y Range: 'y' column, Input X Range: 'x^2' column then OK. The screenshot is shown below,

The result is obtained. The screenshot is shown below,

B A C F F G 1 SUMMARY OUTPUT 2. Regression Statistics 2 4 Multiple R 5 R Square 6 Adjusted R Square 7 Standard Error 0.89216 0.79595 0.793708 41.55414 8 Observations 93 10 ANOVA 11 gnificance F df SS MS 12 Regression 1 612941.4 612941.4 354.9689 3.65E-33 13 Residual 91 157133.9 1726.746 14 Total 92 770075.3 15 Lower 95 %Upper 95 %o 16 Coefficientsandard Err P-value t Stat 17 Intercept 18 squared 91.37113 6.614738 13.81327 4.65E-24 78.23176 104.5105 0.410597 0.021793 18.84062 3.65E- 33 0.367307 0.453886

The regression equation is,

y91.3711+ 0.41062

For X=18

y91.37110.4106 x 182 224.4044 224