Regression Analysis: Cross-Sectional Analysis; Calculation of a Regression Equation Jim Manzano is the general partner of an investment group that owns a number of commercial and industrial properties, including a chain of 15 convenience stores located in the greater metropolitan area of Cleveland, Ohio. Jim is concerned about the recent increase in inventory theft and waste (he calls it “spoilage”) in his stores. Spoilage has increased by more than 20 percent in each of the past two years. In some stores, the main reason is theft; in others, it is damage and vandalism; and in still others, merchandise actually does spoil and must be thrown out. Jim has collected data on spoilage at each of his stores in the recent month and is looking for patterns of spoilage relative to store size (measured by square feet of floor space, number of employees, and total sales) and to the location of the store (location 1 is an area where few arrests for theft, disorderly conduct, or vandalism are made, and location 3 is for areas with high arrests). Jim is not sure, but he suspects, based on his experience managing convenience stores, that a relationship exists among these factors. A colleague told him that a type of regression called “cross-sectional” regression would suit his needs. The cross-sectional regression takes data from a single time period and determines predictions for the dependent variable at different cost objects (in this case, different stores). The objective of the cross-sectional regression is to compare the actual known value for the dependent variable to the predicted value as a basis for assessing the reasonableness of the actual value. This approach is often used in cases similar to Jim’s in which the accuracy or reasonableness of the reported dependent variable is a concern. In effect, the cross-sectional regression develops a model that represents the overall patterns in all the data, and the unusual stores will be identified by the largest error terms in the regression. The following data are for the most recent month’s operations:
Store Number | Inventory Spoilage | Square Footage | Number of Employees | Location | Sales |
1 | $1,512 | 2,400 | 8 | 1 | $312,389 |
2 | 3,005 | 3,900 | 10 | 2 | 346,235 |
3 | 1,686 | 3,200 | 12 | 1 | 376,465 |
4 | 1,908 | 3,400 | 12 | 1 | 345,723 |
5 | 2,384 | 3,750 | 9 | 2 | 453,983 |
6 | 4,806 | 4,800 | 10 | 3 | 502,984 |
7 | 2,253 | 3,500 | 8 | 1 | 325,436 |
8 | 1 ,443 | 3,000 | 10 | 1 | 253,647 |
9 | 3,755 | 5,550 | 15 | 2 | 562,534 |
10 | 1,023 | 2,250 | 15 | 1 | 287,364 |
11 | 1,552 | 2,500 | 9 | 1 | 198,374 |
12 | 2,119 | 3,500 | 16 | 2 | 333,984 |
13 | 5,506 | 7,500 | 15 | 3 | 673,345 |
14 | 3,034 | 5,700 | 16 | 2 | 588,947 |
15 | 772 | 2,200 | 8 | 1 | 225,364 |
Totals | $36,758 | 57,150 | 173 |
| $5,786,774 |
Required
1. Using Excel or an equivalent software program, prepare a regression analysis that predicts inventory spoilage at each of the 15 stores. Use any of the four potential independent variables (or a combination) you think appropriate and explain your answer. Also evaluate the precision and reliability of the regression you select.
2. Using the regression equation you developed in requirement 1, determine which of the 15 stores might have inventory spoilage that is out of line relative to the entire chain of stores. Explain your choice.
We need at least 10 more requests to produce the solution.
0 / 10 have requested this problem solution
The more requests, the faster the answer.