Question

Davis Stores sells clothing in 15 stores located around the southwestern United States. The managers at Davis are considering expanding by opening new stores and are interested in estimating costs in potential new locations. They believe that costs are driven in large part by store volume measured by revenue. During a discussion, one of the managers suggests that number of employees might be better at explaining cost than store revenues. As a result of that suggestion, managers collected the following information from last year’s operations (revenues and costs in thousands of dollars):

Store	Costs	Employees	Revenues
101	$5,727	52	$4,866
102	2,252	29	6,436
103	2,628	45	2,448
104	1,815	26	4,870
105	3,413	36	6,087
106	3,695	53	5,331
107	5,591	57	3,218
108	3,376	51	3,544
109	4,856	45	3,675
110	5,273	52	3,464
111	2,653	43	3,098
112	2,976	38	2,562
113	5,446	53	3,507
114	4,772	31	4,588
115	1,715	25	3,768

e-1. Enter the regression coefficients.

e-2. Estimate the cost of a store with revenues of $3.40 million and 51 employees using the results of a multiple regression of store costs on store revenues and employees.

Answer 1

We have used MS Excel for performing regression analysis. Following are the steps followed:

1. On the Data tab, in the Analysis group, click the Data Analysis button.

2. Select Regression and click OK.

3 In the Regression dialog box, configure the following settings:

Select the Input Y Range, which is your dependent variable. In our case, it's cost.

Select the Input X Range, i.e. your independent variable. In this case, it's employees and revenues.

• Check the Labels box if there are headers at the top of your X and Y ranges.
• Choose your preferred Output option, a new worksheet in our case.

4. Click OK and observe the regression analysis output created by Excel.

Answer e-1

Following is the regression analysis output:

From the output we can write regression equation as follows:

Cost = -1319.8726 + 101.3266*Employees + 0.1878*Revenues

Thus, regression coefficients are as follows:

Intercept: -1319.8726

Regression coefficient of Employees: 101.3266

Regression coefficient of Revenues: 0.1878

Answer e-2

Substituting revenues = $3.40 million = $ 3400 (in thousand dollars) and employees = 51 in the regression equation, we get:

Cost = -1319.8726 + 101.3266*51 + 0.1878*3400

Cost (in thousand dollars) = 4486.304