Question

Question1

You are working for a food retailer named Half-Foods. Half-Foods has been recently acquired by a large company, and the new management wants to review store sales and staffing in search for improvement opportunities. Half-Foods has 93 stores across the US.

From the analysis of historical data, you believe that annual store sales follows a Normal distribution with a mean of $70.1973 M, and standard deviation of $9.13183 M. Also, the number of store employees follows a continuous uniform distribution ranging from 10 to 104.

The new management states that any store with more than 80 employees is considered over-staffed.

Management also implements an incentive plan in which the stores with annual sales above $79.32913 M are recognized as high performing stores. All employees in high performing stores will receive a bonus.

Based on the distribution given above, what percentage of the stores are expected to be over-staffed?

Question 2

Based on the data from Question 1, under the new incentive plan, what percentage of the stores will receive a bonus each year on average?

Question 3

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 14.74 2.67 10.78 1.86 7.24 18.83 10.25 23.76 11.93 17.92 10.74 3.43 6.74 16.03 24.83 0.44 4.67 15.52 25.8 1.59 23.29 5.55 25.08 10.69 19.63 19.92 20.44 4.7 17.54 5.81 23.26 12.32 17.35 5.01 14.94 12.66 1.52 25.74 16.84 13.11 25.19 19.85 18.73 15.85 16.63 7.41 16.87 9.97 14.92 19.9 11.49 9.99 11.4 19.97 4.13 23.74 18.04 18.56 10.98 16.37 13.18 13.14 17.64 16.33 6.58 21.81 19.75 5.62 2.7 19.45 4.22 24.46 2.98 8.65 21.14 10.12 1.75 4.08 17.32 25.69 5.12 1.41 8.9 13.97 18.85 23.72 24.34 12.47 17.16 18.47 15.27 12.03 3.18

Sales ($M) 242.16 124 217.25 76.44 173.26 266.08 135.37 366.11 204.41 215.06 184.35 62.23 148.22 246.43 390.81 99.09 169.83 188.72 439.51 101.3 370.38 114.47 413.62 193.17 255.83 273.06 291.1 70.76 259.6 129.66 400.63 254.56 268.22 104.37 210.41 179.8 92.37 365.1 270.06 107.29 387.71 222.04 259.91 197.37 308.21 123.13 213.44 149.32 205.69 279.52 280.94 203.96 179.98 264.09 76.12 338.03 254.4 333.17 196.43 193.03 201.13 220.78 267.59 264.61 263 308.83 297.71 100.53 84.47 323.42 148.25 256.02 145.89 222.86 322.76 151.92 83.97 83.49 328.22 343.69 114.2 154.7 149.02 142.11 253.32 376.78 285.51 246.5 237.92 253.06 275.93 199.25 171.43

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?

Question 4

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 11 employees.

What are the expected sales for this store?

Answer 1

Question 1

Let X be the number of store employees. X follows a continuous uniform distribution ranging from 10 to 104.

The pdf of X is

The new management states that any store with more than 80 employees is considered over-staffed.  

The probability that a randomly selected store is over-staffed is the probability that X>80

ans: percentage of the stores are expected to be over-staffed is 25.53%

Question 2) Let Y be the annual store sales. Y follows a Normal distribution with a mean of M, and standard deviation of M.

Management also implements an incentive plan in which the stores with annual sales above $79.32913 M are recognized as high performing stores.

The probability that a randomly selected store is a high performing store is same as the probability that Y>79.32913

ans: under the new incentive plan, the percentage of the stores will receive a bonus each year on average 15.87%

Question 3:

Let  X=the average number of employees

Y=the sales for each Half-Foods store in the past six months

The regression model that we want to estimate is

where is the intercept, is the slope of the regression line and is a random error

Paste the data into an Excel sheet and use data--->data analysis--->regression to setup

get this

ans: The estimated regression model is

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

The estimated slope coefficient is 11.1254. This means that for 1 unit increase in X, the value of Y increases by 11.1254.

ans: An extra employee brings $11.1254 M in additional sales

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

The p-value of X from the output is 0.0000

ans:  the p-value of the coefficient of Average Number of Employees is 0.0000

Question 4

Regress sales on number of employees and squared number of employees. The regression model that we want to estimate is

Add a new column to the sheet as below

get this

set up the regression using data-->data analysis-->regression

get this

the estimated model is

ans:

Using this model, predict the sales of a store with 11 employees.

Substituting X=11 in the equation we get

ans: the expected sales for this store is $183.2494 M