Final Report

Luxury Car Dealership Site Selection (Final Project)

Introduction

For this exercise, I was selected to find the ideal location for a brand new luxury car dealership in Dane County called Alexus.  The main objective in this exercise was to use demographics, trade area, market structure, ranking sites, and retail gravity theory to locate the best location for Alexus. The process included customer prospecting as well as geocoding other luxury car dealerships in Dane county for comparison and further formulation of ideas for proposed site locations.   

Methods

                

The first step for locating my luxury car dealership was to locate other car dealerships in Dane County and then geocode them through Business Analyst (Figure 1).  To do this, I went on google maps and located all dealerships in Dane County, took note of their addresses in an excel workbook, and then geocoded addresses in Business Analyst to create a map of the dealerships. Next, I ran an analysis for customer prospecting to determine where the potential market was.  Several analysis were conducted for customer prospecting. The first variable selected was 2015 Average Household Income, with a geography level of zip codes.  The floor value was set at $100,000, and then calculated to create an output map showing zip codes containing income of greater than $100,000.  This floor value was chosen because a luxury car dealership sells cars that are usually expensive (ranging from ~ $35,000 to $120,000) and I was looking for target consumers that have disposable income for a luxury vehicle. The next customer prospecting analysis conducted was based on age.  I chose the variable as 2015 Population Aged 40-49 and set the floor value to 1,500.  This was because people aged 40-49 usually have worked their way up in the career world and may be more knowledgeable with managing savings.  The last map created for customer prospecting contained the variables HH bought/leased most recent vehicle 5+ years ago and HH owns/leases 2 vehicles.  I set the floor value to 4,000 for vehicles bought 5+ years ago, and set the floor for HH owns/leases 2 vehicles to 4,000 Households.  These variables were chosen because we are looking for areas of households that may need a new car and houses with two cars purchased 5+ years ago will likely need to replace one of the two vehicles soon.  The next analysis completed was optimal location/mean center for businesses in Dane County.  I chose to output three clusters showing mean centers.  Following that analysis, it was time to choose three proposed locations for a luxury car dealership.  I went on to google maps and explored the county for locations based on all of my analysis.  After searching and analyzing surrounding areas and competitors, I chose 2766 U.S 51 McFarland WI 53558, 4104 Hanson Road Madison WI 53704, and 6563 Whalen Road Verona WI 53593.  I put those three addresses into excel and geocoded them in business analyst to create my shapefile of proposed locations.  It was now time to rank my sites.  I chose a 3 mile buffer.  My three variables to base my ranks were HH owns/leases 2 vehicles, 2015 HHs with income of $75,000 - $99,999, and 2015 Total Population 40-49 (it is technically two variables because it is under the category “2015 Age: 5 Year Increments”).  My top ranked site was 6563 Whalen Road Verona, WI 53593, followed by 4101 Hanson Road Madison, WI 53704, and lastly 2766 U.S. 51 McFarland, WI 53558. The final step was to calculate a gravity model/point of indifference.  I did this by using Verona as my central city, and choosing three cities surrounding Verona.  My three cities surrounding Verona were Middleton, Belleville, and Fitchburg. Using the equation for Retail gravity theory, I calculated three points of indifference in between Verona and each city.

Results

(Figure 1)

Figure 1 shows the types of major car dealerships around Dane County.  I did not include used-car dealerships.  It's important to note that all of the dealerships listed above are located right next to major highways.

(Figure 2)

Figure 2 shows the optimal locations (mean centers) for a business in Dane County. One optimal location is much further away from Madison than the other two.  One mean center near Madison is located on a major highway and one is closer to downtown Madison, but a little further from a major highway.  Existing car dealerships are in black. 

(Figure 3)

Figure 3 shows customer prospecting for age based on zip codes for geography level.  The green zip codes indicate that there are more than 1,500 people aged 40-49 in that area. There seems to be a pattern of 40-49 year olds living in suburbs outside of downtown Madison.  This is likely due to UW Madison and all of the young students.  Verona and Sun Prairie are large zip codes with plenty of space for a new car dealership, while the highlighted zip codes closer to downtown Madison may not be an ideal location for a new luxury car dealership.  Middleton is a smaller area, but still shows potential base on age.     

(Figure 4)

Figure 4 shows further customer prospecting based on an income variable this time.  The areas highlighted in green are the zip codes with an average annual household income of $100,000 or more.  The floor value was set so high due to the prices of luxury vehicles.  Downtown Madison is not highlighted at all, while a lot of the surrounding area is.  These seems to tie in with Figure 3 because their seems to be a mild correlation between people aged 40-49 and an annual average household income of $100,000.  This can be seen by comparing Figure 4 with Figure 3, specifically the west side of Madison.  Sun Prairie does not share both variables in figures three and four.  

(Figure 5)

Figure 5 shows customer prospecting for two very specific variables based on vehicles.  The areas highlighted in green show zip codes containing at least 4,000 households or more that own two vehicles and have not purchased a new vehicle in 5 or more years.  This analysis yielded much different results than Figure 4 and Figure 3. The highlighted areas are closer to downtown Madison than the previous customer prospecting analysis.  This could be due to the higher income 40-49 year old people who can afford to replace their car before 5 years.  Another idea is that wealthier people in the other surrounding zip codes may have children or larger families in general, creating a need for more than two vehicles, therefore may have not fit my criteria of two vehicles.  

(Figure 6)

Figure 6 shows the proposed sites, their rank from one to three, and already existing dealerships. The proposed sites were:

Rank 1

6563 Whalen Road, Verona WI 53593: I chose this location because it is located in an area that meets my consumer targets' age group and income.  There are no major competitors nearby and it is located very close to a zip code that has more than 4,000 households fitting the previous vehicle variables (Figure 5). 

Rank 2

4101 Hanson Road, Madison WI 53704:  I chose this site because it is in between two major highways, it is near an optimal business location (Figure 2), it is within a zip-code that fits my target consumer age group and the two vehicle variables from (Figure 5). It is also close to Easte Town Mall shopping center and residential establishments. Disadvantages to this location include being located near two competitors and its zip code does not fit the criteria for an annual household income of $100,000 or more. 

Rank 3

2766 U.S. 51, McFarland, WI 53558:  This location was chosen because it has plenty of space, it is down the road from shopping centers and is near an urban sprawl.  It is also not near other competitors, it is in between two optimal business locations (Figure 2), and it is very close to a zip code that fits the income criteria (Figure 4).

Points of Indifference

Despite the ranking sites, I still am going with rank number 1.  Therefore, my central city for the gravity model was Verona.  The three surrounding cities I chose were Belleville, Middleton, and Fitchburg.  The points of indifference were calculated and were as follows:

Fitchburg to Verona = 7.0 miles

Middleton to Verona = 9.39 miles

Belleville to Verona = 7.36 miles

The points of indifference represents a cutoff of consumers being indifferent to shopping in either of the two cities. For example, if I lived 6.2 miles away Verona (in between Verona and Fitchburg), I would be indifferent for shopping in either city.  On the other hand, If I lived 8 miles away from Verona (in between Verona and Fitchburg), I would likely go to Fitchburg to shop for a car.  The highest point of indifference I have is Middleton, with a value of 9.39 miles. This means customers living up to 9.39 miles away from Verona (in between Verona and Middleton), would be indifferent in shopping between the two cities.

Conclusion

Despite the ranks, I am still going with Verona as my number one spot for an Alexus dealership.  The dealership would potentially attract a lot of people in between Verona and Middleton, and it is the only site that fits both my desired criteria for age and income.  It is also not too close to other major competitors.  My chosen site's zip code may not fit the criteria for vehicles I desired, but it is located right next to a zip code that does fit my vehicle variable criteria(Comparing Figure 5 and Figure 6).  Ultimately, this lab refreshed me with the analytical tools associated with business analyst while forcing me to open my mind and formulate a study question with the increased freedom of this exercise.  I had a lot of fun looking for an ideal site location for Alexus, and I hope business there would do good.