Casey Hardware Stores Scenario

Casey Hardware Stores is considering a location for a new store. Examining the profitability of

its 20 stores for last year is a step that will help them make this decision. Leadership has

decided to use linear regression analysis methods to determine if a linear correlation exists

between the profits of its stores and the sales volume of its stores. It is hoped that this

information and additional analyses will inform a decision on where to locate a future store.

Part A:

The management team initially wants to use Excel Linear Regression Analysis to take the data

for the 20 stores that is in Table 1 to develop a best fit linear equation of the form:

Y = a + b X1

where Y is the profit and X1 is the sales level. They are interested in seeing if there is a

relationship between the profit and the sales levels of their 20 stores. Table 1 is a summary of

Casey Hardware stores’ performance, profits versus sales.

© 2021 Strayer University. All Rights Reserved. This document contains Strayer University Confidential and Proprietary information and may

not be copied, further distributed, or otherwise disclosed in whole or in part, without the expressed written permission of Strayer University.

Page 2 of 4

Table 1. Summary of Casey Hardware Stores’ Performance Profits Versus Sales

Store # Profits ($000s) Sales ($000s)

1 42.13 748.82

2 6.32 140.78

3 38.47 702.11

4 -0.32 41.54

5 3.65 96.85

6 7.77 166.93

7 4.31 109.05

8 4.53 263.92

9 -2.69 50.84

10 3.22 90.08

11 9.03 190.59

12 -2.59 91.75

13 6.39 141.57

14 24.39 377.04

15 13.93 198.69

16 2.13 62.78

17 17.48 265.28

18 7.21 91.8

19 15.62 231.6

20 33.61 548.31

© 2021 Strayer University. All Rights Reserved. This document contains Strayer University Confidential and Proprietary information and may

not be copied, further distributed, or otherwise disclosed in whole or in part, without the expressed written permission of Strayer University.

Page 3 of 4

Part B:

The management team thinks that the simple analysis completed in Part A is a good start—but

they decide that there are several other variables that they would like to consider in this

regression analysis.

The company currently has three alternative sites for the store under examination and is trying

to assess the likely profitability of opening a store on each site. Management wants to consider

the following additional explanatory variables that it feels might logically affect profit. These

include:

• The size of each store, X2,measured in thousands of square feet. The larger the store,

other things being equal, the more customers and the more profit Casey management

might reasonably expect.

• The number of different product lines carried by the store, X3. The more product lines

carried, the more popular the store is likely to be with customers.

• The distance from the nearest major competitor measured in miles, X4.

Casey Hardware wants to develop a multiple regression using the data in Table 2 and then

recommend one of the three locations to open this year based on the results of that regression

analysis.

© 2021 Strayer University. All Rights Reserved. This document contains Strayer University Confidential and Proprietary information and may

not be copied, further distributed, or otherwise disclosed in whole or in part, without the expressed written permission of Strayer University.

Page 4 of 4

Table 2. Summary of Casey Stores Performance Versus Sales, Size,

Lines, and Distance From Nearest Competitor.

Store #

Profits

($000s) Sales ($000s)

Size (000s sq

ft) Lines Distance (mi)

1 42.13 748.82 6.0 150 0.1

2 6.32 140.78 1.4 75 0.1

3 38.47 702.11 5.0 170 0.5

4 -0.32 41.54 1.0 75 0.0

5 3.65 96.85 1.2 75 0.2

6 7.77 166.93 1.5 75 0.5

7 4.31 109.05 1.3 75 0.3

8 4.53 263.92 1.1 80 0.4

9 -2.69 50.84 1.1 75 0.0

10 3.22 90.08 1.2 75 0.6

11 9.03 190.59 1.4 80 0.5

12 -2.59 91.75 1.2 75 0.0

13 6.39 141.57 1.4 80 0.3

14 24.39 377.04 3.5 160 1.2

15 13.93 198.69 1.5 100 0.7

16 2.13 62.78 1.3 75 0.1

17 17.48 265.28 2.1 110 0.9

18 7.21 91.8 1.3 85 0.3

19 15.62 231.6 2.5 120 0.9

20 33.61 548.31 4.5 200 0.5

Part C:

Casey Hardware is considering three different sites for a new store. They are constrained in

each case as to the size of the store and the number of product lines that they can carry in the

store. They plan to use the results of the regression analysis in Part B to select the most

profitable site.