Part A: CFS Site Selection Case (Shell Canada Ltd.)

Problem Set 4

Ian Jacobs, Manager of Convenience Food Stores at Shell Canada Ltd., reviewed the store-site selection model that he was about to present to management. The model predicted sales of existing stores quite well, and identified those sites that should never have been built upon. He was concerned, however, about the utility of the model for the selection of future sites because, by 1985, convenience food stores (CFS) at gasoline sites had become the single largest capital investment budget item in oil products marketing at Shell Canada. In theory, CFS investment offered higher rates of return than any investment alternative in oil products marketing. But a post audit of Shell's experience with its 40 sites in Western Canada showed that they did not in fact generate a uniformly high rate of return. Indeed, a substantial fraction did not clear the corporate hurdle rate. Even more troubling, revenues varied by a factor of seven across stores of the same size and layout.

Top management at Shell had decided that continued investment in CFS should be suspended until a methodology for choosing CFS sites had been developed. During the past two years, Strategic Planning Associates (SPA), an international management consulting firm, had been conducting a marketing strategy review for Shell Canada in oil products marketing. As part of this broader study, SPA was asked by top management to assist Jacobs in developing a model for CFS site selection.

Canadian Oil Products Marketing in the 1980s

The early 1980s had been a disastrous period for oil refining and marketing companies throughout the world. In Canada, the five major oil companies had refining and marketing divisions that had averaged only 2.5% return on assets employed. Several factors contributed to this profitability problem.

The primary cause was the world oil shocks of 1973-74 and 1979-80. As oil-product prices skyrocketed, demand for oil products fell dramatically. Moreover, as volume fell and oil products became increasingly commoditized, the basis for competition in the industry shifted from service to price. Thus, gross margins shrank at the same time that oil-products volume fell.

This pressure on profitability coincided with a dramatic rise in the real-estate value of gasoline station sites. Sites that ten years before had been profitable as stations would in many cases become more valuable now if the gas station were torn down and the land developed for alternate uses. The industry, which heretofore had evaluated a location for its potential performance as a gasoline-only retailing site, now began to consider the optimal configuration of a site, which might include a number of ancillary retailing services such as CFS, car wash, and automotive lubrication.

In this highly competitive environment, businesses such as CFSs became economically attractive investments for a number of reasons. First, a CFS could be erected on an existing gasoline-station site without the additional purchase of land, giving a distinct advantage to CFSs at gasoline sites over stand-alone CFSs. Second, there was a significant synergy between food and gasoline sales at these hybrid sites: the addition of a CFS increased gasoline volume by ten percent, on average. Finally, a single employee could operate both the gasoline and the food pay points, so that addition of a CFS required very few incremental employees.

The prevalence of CFSs on gasoline station sites in some regions of the United States attested to the attractiveness of the CFS-gasoline link. Because Canada had significantly lower penetration of CFSs, an opportunity arose for oil-products marketers such as Shell to pre-empt the entry of CFS chains into the Canadian marketplace.

Convenience Food Stores at Shell Canada

Despite this window of competitive opportunity, Shell's late entry into the marketplace seemed to pose a problem. Shell's forty sites in Western Canada did not compare with the many hundreds of sites of several of the major Canadian CFS chains, four of which had more than 400 stores each. This lack of scale seemed to place Shell at a competitive disadvantage in purchasing, store operating experience, and consumer brand awareness.

Further analysis of the competitive environment by Jacobs and the SPA caseteam revealed that regional scale, rather than national scale, was the factor driving industry economics. Even though Shell was at a scale disadvantage at the regional level as well, this disadvantage would be overcome by rapid expansion of Shell's CFS network in the West.

Shell's Existing Site Selection Methodology

The success of the expansion strategy would depend critically on proper site selection. In the past, Shell had tended to select sites with high gasoline volume. Judgmental forecasts of sales showed almost no correlation with actual sales at maturity (3-5 years of operation). Furthermore, there was surprisingly little correlation between gasoline volume and food sales (see Exhibit 1), so that the strategy of choosing sites with high gasoline volume was ineffective. It was imperative to identify those factors that accounted for the seven-fold variation in sales volume across Shell CFS sites (all of which had the same square footage of retailing space), so that a site-selection methodology could be devised that would systematically choose sites with high sales potential.

Developing a Site-Selection Model

Jacobs and the SPA caseteam decided to take a two-pronged approach to studying the issue of CFS site selection. They would conduct research on Shell's existing CFS customers and then analyze the data generated by the post audit of Shell's existing sites. If a model that explained the historical sales variation could be developed, it could be applied to predict future sales of proposed sites.

The caseteam hypothesized that the variables that could explain this large sales variation would include: number and proximity of competitors; population, income level, and age distribution in the relevant trade area; traffic volume and gas volume at the site; age of the gasoline station; hours of operation; type of neighborhood; and proximity to schools and apartment buildings. Unfortunately, very little of this information was readily available. Moreover the caseteam was unsure of what constituted the relevant trade area for a convenience food store, nor did they have a good understanding of how competition affected CFS sales.

To answer these questions, they undertook customer research and competitor survey at thirty-four of Shell's forty sites. The first surprising finding was that only 14% of Shell's CFS food sales were to customers who purchased gasoline at the time of their CFS visit, which suggested why food sales and gasoline volume were uncorrelated. By asking customers to point out on a map where they lived or worked, the caseteam was able to determine the relevant CFS trade area. Nearly 2/3 of Shell's CFS customers lived or worked within a one-mile radius of the CFS, with about half living within a half-mile radius.

By placing schools, apartment buildings, competitors' locations, and major "physical barriers" on these same customer-research maps, the caseteam was able to judge how these factors affected sales. While anecdotal explanations could be found for almost every site's sales performance, no consistent patterns across the sites were discernible. The analysis depended on other factors not on the map.

Accordingly, data on trade area demographics, traffic, type of neighborhood, and competition were collected, compiled and entered into a computer for regression analysis (see Exhibit 2 for a description of the variables and the data). After a month of regression modeling, the SPA caseteam returned to Jacobs with a model that explained 69% of the sales variation using only five variables: population in the trade area weighted by distance from the site, type of neighborhood, and three dummy variables having to do with competition; complete absence of competition in the quarter- mile trade area, presence of competition directly across the street, and presence of competition within the quarter-mile trade area. (See Exhibit 3 for a full description of the five variables and the model results.)

Jacobs' Concerns

Jacobs was impressed by a calculation (see the end of Exhibit 3) that showed that if the model worked as well in predicting the performance of prospective sites as it had in explaining the performance of existing sites, Shell could increase its after-tax profit by $13 million. Nevertheless, several questions remained in his mind. Did the size and signs of the coefficients make intuitive sense? Did it even matter whether the signs made sense since this model was to be used purely for predictive purposes? Could a model developed from historical site performance be used to predict proposed new site performance? More immediately, should Jacobs apply this model to the three proposed CFS sites in Alberta and halt construction on any whose predicted sales volume fell below the level needed to clear the corporate hurdle rate? These were the questions that troubled Jacobs the most as he prepared to present the caseteam's findings to top management.

Exhibit 1: Relationship between Gas Volume and Food Sales (1984)

Source: Shell Canada

Exhibit 2: Description of Variables

Site Number: / Identifier coded with values from 1 to 34
City: / City code: CAL Calgary, EDM=Edmonton, SKN=Saskatoon, REG=Regina, VAN Vancouver, WIN Winnipeg
Sales: / 1985 Sales volume (thousands of Canadian dollars)
Population: / Number of people, classified into three clusters:
<¼: / within a circle of quarter mile radius centered on the site
<½: / within a ring greater than ¼ mile but less than ½ mile from the site
<1: / within a ring greater than ½ mile but less than I mile from the site
CFS Competition: / Number of competitive convenience food stores, classified into four clusters:
AS: / across the street
< ¼: / within a circle of quarter mile radius centered on the site, but excluding stores across the street
< ½, < 1: / rings, as above
Supermarket Competition: / Like CFS competition, but for supermarkets
No Comp. <¼: / Coded 1 if there is no CFS or supermarket competition within ¼ mile of the site, 0 otherwise
Gas Vol.: / Volume of gasoline sold at the site in 1985 (in thousands of liters)
<24 Hrs./Day: / Coded 1 if site is not operated 24 hours per day, 0 if it is
Suburb: / Coded 1 if the site is in a suburban neighborhood, 0 otherwise
Urban:
Res.: / Coded 1 if the site is in an urban residential neighborhood, 0 otherwise
Inner: / Coded 1 if the site is located in a downtown (inner city) urban business district
Comm: / Coded 1 if the site is located on an urban commercial strip ("strip mall")
Avg. Inc.: / Average per capita income (thousands of Canadian dollars per year), classified into two clusters:
< ¼: / within a circle of quarter mile radius centered on the site
< ½: / within a ring greater than ¼ mile but less than ½ mile from the site
Males 20-34: / Percentage of the population living within ¼ mile of the site that is male and between ages 20 and 34
Pop > 65: / Percentage of the population living within ¼ mile of the site that is over age 65
Traffic: / Number of cars/day traveling past the site
Age: / Coded 1 if the site is less than 3 years old, 0 otherwise
Pop gradient / The sum across the three population clusters surrounding the site of average sales per capita in each cluster, multiplied by the population in that cluster. Survey data showed that 26% of customers (and, presumably, 26% of sales) come from the ¼ mile circle. Thus, at Site #1, 26% of the $1,122,000 in sales was generated in the ¼-mile circle, or $291,720 in total, or $291,720/2,310 = $126 per capita. Averaging these per-capita sales across all 34 sites, the ¼-mile circle averaged $70 per capita, the ring from ¼ to ½ mile averaged $22.8, and the ring from ½ to 1 mile averaged 6. The population gradient was computed by multiplying these average sales per capita by the population in the appropriate cluster, adding these products across the three clusters, and dividing by 1000.

The figure below depicts the various geographic segments described above, and shows the estimated average annual sales per capita in each segment.

Exhibit 3: Variables in Model

Dependent Variable: / Sales
Independent Variables:
Pop Gradient: / See Exhibit 2
Across Street: / Number of competitive CFS stores plus supermarkets across the street from the site
Comp < ¼: / Number of competitive CFS stores plus supermarkets within ¼ mile of site (includes those across the street)
Pop < ¼ & no comp: / Number of people within a circle of ¼ mile radius centered on the site having no competitive stores within the circle (= pop < ¼ * no comp < ¼)
Sub ¼-1: / Number of people within a ring greater than ¼ mile but less than 1 mile from a suburban site, weighted by average sales per capita
[= suburb * (.0228 * pop < 1/2 + .006 * pop < 1)]
Observations in Model: / All except the two sites whose age was less than three years

Regression Number 1

Constant / Pop Grad / Across St / Comp<l /4 / POP<1/4&NC / Sub 1/4-1
Regr. Coef. / 241.9 / 0.3713 / -83.15 / 100.2 / 0.1702 / 0.8660
Std. Error / 59.3 / 0.1323 / 30.29 / 29.6 / 0.0318 / 0.3116
t value / 4.1 / 2.8 / -2.7 / 3.4 / 5.4 / 2.8
# of obs. = / 32
Deg. of F = / 26
R-squared = / 0.6905
Resid SE = / 106.7

Exhibit 3, continued: Model Results

If the model had been used before the CFS network was built, and only the sites with predicted sales over $600,000 had been selected, then the average store performance would have been $125,000 higher
If the model is applied to the next 50 stores, the potential benefit will be:

50 Stores * 125K Higher Sales * 30% Gross profit

= $2M of Increased Profits per Year

That cash flow translates into a Net Present Value of $13 M after-tax

The data for this case can be downloaded from the Web site as "cfsshell.xls".

Questions for Part A:

1. Obtain basic summary statistics (mean, variance, standard deviation, minimum, maximum, range) for the sales data (both food and gasoline). What interesting things stand out?

2. Find the correlation between food sales and each of the other variables. Which variables are strongly associated with food sales?

3. Make a scatter diagram, plotting Gas Sales versus Food Sales. What conclusions do you draw? Give appropriate statistics to support your view.

4. Is the relation you see in the previous question true if one considers the suburban sites separately from the urban sites? Give graphs and/or appropriate statistics to support your views here.

5. Perform a regression using Food Sales as the dependent variable and Total Population within 1/2 mile as the independent variable. Predict sales for a store with 25,000 people within 1/2 mile.

6. Perform a regression using Food Sales as the dependent variable and two independent variables: (1) Total population within 1/2 mile, and (2) Total CFS competition within 1 mile. Comment.

7. Replicate the results in Exhibit 3 of the case (set up a spreadsheet with variables to match the ones in Exhibit 3, and perform the regression).

8. Using the model of the previous question, predict sales for city 24.

9. Tabulate the errors that the model makes in predicting the sales of all 32 cities. That is, compare actual Food Sales with the amount of sales that is predicted by the model. What is the average error? What is the standard deviation of the errors? Estimate the likelihood that a prediction of food sales is within plus or minus 100 units of the true sales value.

Careful! There are two things to be careful about when replicating the results of Exhibit 3. They are the following:

The population variables are not inclusive. That is, to get the population between 1/4 and 1 mile, you need to add the variables Population < 1/2 and Population < 1.
When running the regression of Exhibit 3, two of the 34 sites are excluded.

Part B

Answer the six questions at the end of Analyzing the Analysts.

Managerial Statistics1Prof. Juran