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,

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

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

The result is obtained. The screenshot is shown below,

The regression equation is,

For one additional employee sales will increase by $11.3718

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,

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,

The regression equation is,

For X=18