Recommended model-will best forecast sales

Question

Assignment:

THE 23^rd STORE (based on a problem by Alan L. Sarpe)

A retailing company owns and operates 22 home-building-supply stores in its various metropolitan areas across the country. Management is currently trying to pick the location for its 23^rd store. Two locations have passed through the initial screening phase and are under intensive study by the Management Committee: one in Toronto and one in Ottawa.

Pleased with both operating cost and sales performance under existing policies, the main criterion in location selection is to be expected sales volume. For this reason, the Management Committee has all but decided on the Toronto location, believing that sales would be higher there. The Committee has reached an impasse, however, trying to decide upon the amount of parking facilities to provide at the store. The poles of opinion have taken the following form:

"Since land prices are so high let's keep parking space to a bare minimum."

"Good sales depends heavily on adequate parking facilities. Let us not hold back on our capital expenditure and risk poor sales performance."

The Management Committee is seeking an objective opinion to help resolve their location selection decision.

The file "23^rd store raw data" contains data about the existing 22 stores, including last year's sales as well as the demographics of each store's area. Additional information is provided about the potential store locations in both Toronto and Ottawa for comparison purposes.

YOUR ASSIGNMENT:

Analyze the accompanying data and provide your recommended model that will best forecast sales.

Provide a thorough assessment of your recommended model.

Summarize your findings in a maximum one page report to the Management Committee. In addition to clearly addressing their concerns regarding both location and parking spaces, highlight any other insights or findings from your analysis. Where possible, provide clear quantitative direction.

Submit an Excel file containing any models used in arriving at the insights for your report. DO NOT include every single model you may have built - only ones that are relevant to your discussion. [Hint: in order to fully address management's concerns you may wish to rely on more than one model - you are not limited to using only one model's results in generating insights.   But you MUST clearly identify ONE model that you believe is best for PREDICTING SALES.]

VARIABLE DESCRIPTIONS:

Variable Name	Description
Sales	Last year's sales in $'000s
Store Size	In 100s of square metres of floor space
Parking Ratio	Number of dedicated parking spaces per 100 square metres of floor space
Population in Store Vicinity	Number of people who live within a circle of radius 5 km, centred at the store location, in '000s of people
Average Earnings in Store Vicinity	Average earnings per family living within a circle of radius 5 km, centred at the store location, in $'000s
Housing Split in Store Vicinity	Percentage of people within a circle of radius 5 km, centred at the store location, who live in single family dwellings
Population of Metropolitan Area	Population of the metropolitan area in which the store is located, in '000,000 people
Competition Ratio	Number of similar retail establishments per 100,000 people in the metropolitan area in which the store is located
Average Earnings in Metropolitan Area	Average earnings per family living in the metropolitan area in which the store is located, in $'000s

Data is

Store #	Sales	Size	Pk Ratio	Pop Vic	Earn Vic	Hous Spl	Pop Metr	Comp Rat	Earn Metr
1	389	10	3	3330	7.99	35.3	2.5	2	5.82
2	492.6	13	3	300	7.99	42.2	2.5	2	5.82
3	641.4	15	5	350	8.12	47.7	2.5	2	5.82
4	505.2	11	4	300	7.97	39.6	2.5	2	5.82
5	364.8	9	3	300	8.01	35.5	2.5	2	5.82
6	464.9	10	4	160	7.84	63.5	1.2	3	6.02
7	343.2	8	2	140	7.93	58.8	1.2	3	6.02
8	416.1	10	3	150	8.19	63.9	1.2	3	6.02
9	542.1	14	5	170	7.99	68.8	1.2	3	6.02
10	678.5	12	3	350	8.06	40.3	2.5	1	5.91
11	799.8	15	4	350	7.84	50.3	2.5	1	5.91
12	654.4	10	3	300	7.98	53.2	2.5	1	5.91
13	710.1	10	3	275	8.07	57.1	2.5	1	5.91
14	872.1	16	5	300	7.95	49.1	2.5	1	5.91
15	753.8	12	3	90	8.06	60.2	0.8	0.5	5.72
16	696.7	10	4	80	7.97	50.7	0.8	0.5	5.72
17	567.9	9	3	70	8.19	49.3	0.8	0.5	5.72
18	901.9	14	4	85	7.99	63.4	0.7	0.2	5.73
19	608	9	2	65	7.98	49.5	0.7	0.2	5.73
20	734.7	10	3	70	7.97	69.6	0.7	0.2	5.73
21	674.8	9	2	55	8.11	70.6	0.6	1.2	5.69
22	653.3	9	2	65	8.04	75.2	0.6	1.2	5.69

Proposed Location	Store size (100 sq m)	Number of Parking Spaces	Total Land Cost	Annual Property Taxes
Toronto	15	30	$     1,00,000.00	$                                                12,000.00
Toronto	15	60	$     1,40,000.00	$                                                16,000.00
Ottawa	15	30	$        80,000.00	$                                                  9,000.00
Ottawa	15	60	$        95,000.00	$                                                11,000.00
Proposed Location	Pop Vic	Earn Vic	Hous Spl	Pop Metr	Comp Rat	Earn Metr
Toronto	280	7.92	40.7	2.5	2	5.82
Ottawa	80	7.97	62.3	0.8	0.5	5.72