Site Selection - Analysis

Part 1:

2. Before joining these 2 databases, they were compared so that duplicate and empty records were eliminated and corrections to street names were made. Note that, for approximately ½-dozen records, an address was present but data on "Salary" and "Occupants" were missing (e.g.: 541 Baseline Road). This would normally cause bias in calculating statistics, because a "$0" in the "Salary" field would artificially depress "Average Income" per buffer zone. In fact, a "$0" entry under the "Salary" field probably represents <NO DATA>, rather than an actual income of $0, since even unemployed persons will have some income. However, most of these records were the locations of the pizza outlets, existing and proposed. As well, phone numbers and postal codes were missing for these same records, but these are not required in calculating statistics nor in joining the tables.

Another anomaly discovered is that a fair number of residences have unusual high numbers of occupants. In particular, there are a number of dwellings along Baseline Road where the number of occupants is over a dozen. At the same time, the salary listed is almost the same as in dwellings with 4 or 5 occupants. The extreme case is 430 Emerald Avenue, which has 65 occupants and only $58,150 as the salary. These anomalies are of concern, as they may well be data errors, yet affect the calculation of average household size, and, ultimately, the measure of demand for pizza.

Total records in "BaseGraphic" = 850

Total records in "BaseTable" = 858

After "cleaning" the data, a total of 850 records were in each table. A new field called "Address" then joined them

Ques 1:

Where buffers overlapped, I allocated the dwellings based on delivery parameters. That is, to avoid confusion for the drivers, I would chose a set of houses up to the end of the street to be in a particular buffer, even if it appeared on the map that one or two of the houses was closer to the centroid of the adjacent buffer. This is deemed more desirable than telling drivers that they can only deliver up to the 7^th out of a row of 10 houses. It also creates less confusion for customers, who know that they can phone the same pizza outlet as their adjacent neighbour. This is much the same principle as setting boundaries of, say, political ridings or zoning districts, where similar neighbourhoods are kept "clumped" together, rather than splitting on a street, a decision that would seem arbitrary to those living on the street.

A problem area was dividing the territory between the existing Baseline and proposed Granton outlets, since these two outlets are closest to each other. Baseline residents would identify with the Baseline outlet, so all Baseline dwellings that were in the overlap, up to Bassano, were left in the Baseline catchment area. Granton and all other streets south of Baseline (except those west of Cordova) were given to the proposed Granton outlet, as Baseline is a relative barrier to traffic, both for delivery cars and customers, making the Granton outlet more attractive to all dwellings south of Baseline even though they may be physically closer to the Baseline outlet.

Average number of people and income per household per catchment (buffer zones - MAP 1):

NOTE: "HHLDS." – households "AVE." – average per household "OCCS." – occupants per household

Ques 2:

I did not find a relationship between income and pizza purchases. However, I did find data from Statistics Canada that may be used to estimate total revenue for each pizza outlet per area.

In the "1996 Canadian Global Almanac" (J.R. Colombo, Macmillan Canada, 1997), page 81, a chart is presented showing average per capita expenditure of Canadians on various categories of goods and services. In 1994, the latest data available in the chart, $1,005 were spent annually, per person, on the "Restaurants and Hotels" category, representing 6.5% of "Total Expenditure". To use these data to estimate revenue potential for each pizza outlet, the following steps were taken:

• estimate disposable income per household (after tax),
• deduct estimated savings rate to estimate expenditures, and
• estimate proportion of "Restaurant and Hotel" expenses represented by pizza purchases.

According to the Colombo (1997, p.237) Statistics Canada data, average "Personal Disposable Income" represented 76.8% of "Total Personal Income" and "Total Personal Savings" represented 7.8% of "Personal Disposable Income" for Canadians in 1994. Deducting estimated "Personal Savings" would yield an estimate for the average "Total Expenditure" per household per catchment area.

Applying these estimates against the catchment area data above yields the following table, where "Average Income" less taxes equals "Disposable Income" less savings equals "Total Expenditures":

NOTE: "DISPOS." – disposable "EXPEND." – expenditures

The next step involves estimating, out of the total amount spent by an average household on "Restaurants and Hotels", how much is actually spent on pizza. An examination of the Ottawa Yellow Pages reveals that:

• 4.5 pages were devoted to "Hotels",
• 5 pages were devoted to "Pizza", and
• 15.5 pages were devoted to "Restaurants".

Applying these ratios (5/25 pages) to the above estimate of $1,005 (6.5%) spent on "Restaurants and Hotels" yields an estimate of 1.3% of "Total Household Expenditures" spent on pizza orders by the average Ottawa household. Applying this factor to the average expenditures for the above catchment areas, and multiplying this by the number of households in each catchment area, yields the following estimate of total revenue per pizza outlet:

REPORT:

The client has determined that all stores must have minimum potential revenue of $50,000 annually. Thus, the existing Baseline and proposed Granton outlets are considered to be "not viable". Considering that Baseline Road itself is a "relative barrier", it was decided to consolidate the market territory south of Baseline (currently split between the Baseline and Granton catchment areas) into one pizza outlet.

Thus, it was decided to close the existing Baseline outlet of Wheelhouse Pizza, and open a new one at the proposed Granton location. The part of the Baseline catchment area depicted on Map 1 that is south of Baseline Road is to become part of the new Granton territory. This gives the Granton outlet a "Potential Annual Revenue" of just over $62,000 annually. This revenue represents only part of what the new Granton location could eventually generate, since it will be able to expand its market to the rest of the "St. Claire" neighbourhood as far south as Meadowlands Drive (not depicted on the maps).

That part of the Baseline catchment area depicted on Map 1 that is north of Baseline Road is to be split between the Hanbury and Erindale outlets. Map 2 depicts these final catchment areas with their data.

Finally, an explanation is required for why the new Erindale outlet has a "Potential Annual Revenue" that is markedly lower than that of the existing Cannon and Hanbury outlets. As with the Granton outlet, the new Erindale outlet has the capacity to increase its "Potential Annual Revenue" since it will be able to expand its market to the rest of the "Bel-Air Heights" neighbourhood as far east as Maitland Avenue (not depicted on the maps).

Step 1: Examining tables

Both BaseGraphic and BaseTable are retrieved in order to examine structure of the tables, number of records in each tables, etc. In all, there are 858 records in BaseTable and 850 records in BaseGraphic.

Step 2: Concatenating columns

An extra column is added into each table by going into Table>Maintanance>Table Structure... Subsequently, "Number" + "St_Name" is concatenated into "No_Street" for both BaseTable and BaseGraphic by using Table>Update Column....

Both tables were ordered by "No_Street" in order to check for any blank records. Four blank records were discovered in BaseTable, and were subsequently removed and packed. None was discovered in BaseGraphic.

To find duplicate records in each table, SQL statement was formulated for each table using the count function. One duplicate record was discovered for BaseGraphic (i.e., 144 Navaho Drive), but none was discovered for BaseTable. One of the records was later changed to 146 Navaho Drive.

Step 5: Checking for non-matching records

Changes were made to those un-matched records retrieved from step 4, so that a one-to-one relationship can be established between those two tables. In addition, there were five Postal_code noticeably missing from BaseTable.

Step 6: Checking for missing information

# LOG SHEET OF EDITS