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.