Where should Tractor Supply Company stock Arrowquip cattle chutes in the Rocky Mountain region based on cattle inventories and a lack of Arrowquip dealers?

Introduction

Tractor Supply could be described as one of the nation's largest and most easily recognizable farm store. With locations across the entire country, it is a place where farmers, ranchers, and the typical American household could gather for all things outdoors and DIY. Tractor Supply has a long standing tradition of upholding the values of America's farmers and ranchers by providing easy access to anything they could potentially need. Arrowquip holds many of these same ideals and values. Arrowquip is a Canada based company that manufactures some of the best cattle chutes and working facilities currently available to the market. Arrowquip prides themselves on keeping ranchers in business by allowing easy access to any one of their dealers across the country. 

A partnership between Arrowquip and Tractor Supply makes a lot of sense for both companies. Tractor Supply would be able to expand their current cattle working facilities line up and Arrowquip would be able to achieve their own company's goal of being easy access to all producers. To determine which Tractor Supply's should be targeted in the initial rollout, a cluster analysis was preformed on which counties have higher cow calf inventories and also have a Tractor Supply and no Arrowquip dealer. The results of this analysis are outlined below.

The data summarized above came from three different places:

1. Cattle inventories were pulled from the USDA NASS through their Quickstats portal. 

2. Tractor Supply Store locations were available through POI Factory. 

3. Arrowquip Dealers were found by hand using their websites dealer locator. 

The map above shows cow inventories according to the agricultural census that was conducted in 2017. The darker colored areas show a higher number of cows and calves where as the lighter colored areas show a lower number of cows. All numbers are represented per thousand animals. This information will be useful in answering our overall question because the more cows that are shown theoretically means there will be a higher demand for Arrowquip products in that region. The Rocky Mountain region has already proven itself to be viable to producing cattle and this graph does an excellent job at pointing out which areas of this region have higher cattle inventories as indicated by the darker colors. These would be of the greatest interest to both Tractor Supply and Arrowquip.

The above shows a general overview of where there are Tractor Supply locations in this region. In order for this partnership to work, there must be a Tractor Supply location in a county beforehand. We are not suggesting that Tractor Supply builds new locations based on cow calf inventories, we are only suggesting that they stock Arrowquip chutes where the cow calf inventories are higher. A clearly explanation of this can be found in one of the graphics below.

The variables within our cluster analysis are the number of cattle including calves per county, as well as the number of Tractor Supply Stores per county. This allows us to put our clusters on where market opportunities may be present in comparison to where we see current Arrowquip dealers within operation. The idea here is to assess whether or not there could be a demand for these products. We anticipate that more cattle inventories would necessitate a greater demand for handling equipment and if Tractor Supply stores are within dense concentration, this could lead to opportunities in these given markets for sales contracts. 

The results of our cluster analysis provided us with 4 distinct clusters, as can be identified in the below graph. Those counties as have been identified in red, are areas in which we see low numbers in cattle inventories while simultaneously also seeing low numbers of Tractor Supply stores. Additionally, we have the green counties. These counties are those that comparatively have higher cattle inventories while also having more Tractor Supply locations within that county. Thus, our green counties provide us with greater market opportunity than those of the red counties because we have existing infrastructure with many existing cattle producers within the area who could utilize Arrowquip products. 

Later by comparing our cluster analysis to that of current Arrowquip dealers in the Rocky Mountain region of TSC, what we can see here is that in primary, places that are reflecting as red, or where demand may not be as substantial, are where the majority of current dealers are operating. While some are located in green areas, such as in the western region of Montana, what this allows us to do is find places where we may be able to satisfy demand, not over saturate the local markets with Arrowquip dealers, and not overshadow the current dealers. Thus, for example, a location like Colorado's very own Larimer County would be a very appealing place for Arrowquip and Tractor Supply to partner seeing as cattle inventories are relatively higher than most other places and there is also several current stores within operation that could handle the sales requests. Wyoming poses a great opportunity throughout the state to supply Arrowquip chutes their Tractor Supply's mainly because cattle inventories are still much higher in comparison. There is also a market opportunity in western Montana. This information can be compared against the map of Arrowquip dealers that is shown below.

Assumptions

We are assuming that there is a direct correlation between cattle inventories and the number of Tractor Supply stores in an area that would need cattle chutes. We are also assuming that cattle producers need cattle chutes and are looking to buy Arrowquip chutes. This may seem trivial however when cattle ranchers buya chute they are expecting it to last many, amny years. They want to ensure that it is built correctly. Given this, the buying cycle of chutes is relatively short. Not every cattle rancher is in the market just because they have cows. On this, we also assume that the ones that are in the market for a new chute are in the market for a new Arrowquip chute as opposed to many of the other brands of chutes that are out there. There is a product marketability aspect that we are ignoring and financial aspects such as sell through and return on investment that will be a big factor for Tractor Supply. 

Limitations

There may be some areas where there are not Tractor Supply locations available but there is a need based on cattle inventories and a lack of Arrowquip dealers. This has already been discussed lightly but we are in no way saying that Tractor Supply needs to start building locations just based off where there are higher cattle inventories. This analysis was just used to determine which locations would pose the greatest interest for an initial rollout. There may be counties that already possess Arrowquip dealers where partnerships with Tractor Supply may seem unnecessary, however, demand for such products is there. We would assume this rollout would include all Tractor Supply stores, however, we need to know what counties would be good test subjects.

Future Work

We could look beyond just Tractor Supply locations to find places for Arrowquip dealerships. There are a multitude of  local farm stores that have been ignored in this analysis. Another place we may find interesting is local farms in these regions. Many Arrowquip dealers are actually cattle farmers that have the chute selling business as a side job. Larger, well respected and recognizable ranches in a region could pose a great market opportunity for Arrowquip. Other factors could be evaluated on where specific locations may demand certain products. For example, areas that contain more dense feedlot operations may be more apt to purchasing a hydraulic squeeze chute over the a standard style chute. This is because typically a feedlot would lean on the automation of a hydraluic chute due to ease on the user.

Conclusion

This information would be of great interest to both Tractor Supply and Arrowquip if they decide to go through with a partnership. Tractor Suppply would be focused on financial aspects such as sellthrough and return on investment whereas Arrowquip would know that in order to get their chutes into Tractor Supply and keep them there, a positive outcome from those financial aspects would be essential. In theory, a higher demand for products would mean a better outcome for the product and one way to potentially predict demand is by utilizing cattle inventories. Nearly all of Wyoming, Western Montana, southeast North Dakota or Northeast South Dakota, southern Kansas, and Northern Colorado would be of the greatest interest for an initial rollout due to its higher cattle inventories, presence of a Tractor Supply, and Lack of Arrowquip dealers.

Appendix

A copy of our R code behind the cluster analysis with reasoning. 

# Read in the csv file for Arrowquip Dealers and open it for review. 

Arrowquip_Dealer_Info <- read_csv("Arrowquip Dealer Info.csv")

View(Arrowquip_Dealer_Info)

# Read in the csv file for Tractor Supply locations and open it for review. 

Tractor_Supply_Stores_AutoRecovered_ <- read_csv("Tractor Supply Stores(AutoRecovered).csv")

View(Tractor_Supply_Stores_AutoRecovered_)

# Read in the csv file that contains cattle inventories and open it for review.

Plan_B <- read_csv("Plan B.csv")

View(Plan_B)

############################################################

#Aggregating the point data into counties for Arrowquip Dealers. 

# Arrowquip dealer locations are presented using longitude and latitude, so we need to convert the data frame to a points simple feature object. 

Arrow_geo <- Arrowquip_Dealer_Info %>%

  select(Latitude,Longitude) %>% 

  st_as_sf(coords=c("Longitude","Latitude"),crs=4326)

# We then take the Arrow_geo data and construct polygons that summarize the number of Arrowquip dealers per county. Here we are using a function called tigris that acts as an API to extract these poloygons, and/or counties.

co_co <- tigris::counties(state = "CO",cb=T,class="sf") %>%

  janitor::clean_names() %>%

  mutate(aland=aland/2.59e+6) %>%

  st_transform(4326)

# We need to set the option to make the mapview package and functions render the points in preview.

mapviewOptions(fgb = FALSE)

# Map our the Arrowquip dealers into a visual format. 

mapview(Arrow_geo)

# To construct a more productive data frame we use the st_intersection data to "merge" our new county information with our geographic Arrowquip data previoulsy converted. 

Arrow_co_geo <- st_intersection(Arrow_geo,co_co)

# Now we need to convert the spatial data into a nonspatial object and join it with our other datasets.

gs_co <- Arrow_co_geo %>%

  st_set_geometry(NULL) %>% # Converts the data back into a dataframe.

  group_by(geoid) %>% # We tell the dataframe to join data based off of the "geoid" variable. 

  summarize(stores=n()) %>%

  ungroup()  # Tell R to forget the grouping information otherwise it can affect the data later. 

######################################################################

#Aggregating the point data into counties for Tractor Supply stores. 

# Tractor Supply locations are presented using longitude and latitude, so we need to convert the data frame to a points simple feature object. 

Tractor_geo <- Tractor_Supply_Stores_AutoRecovered_ %>%

  select(Latitude,Longitude) %>% 

  st_as_sf(coords=c("Longitude","Latitude"),crs=4326)

# We then take the Tractor_geo data and construct polygons that summarize the number of Tractor Supply stores per county. Here we are using a function called tigris that acts as an API to extract these poloygons, and/or counties.

co_co <- tigris::counties(cb=T,class="sf") %>%

  janitor::clean_names() %>%

  mutate(aland=aland/2.59e+6) %>%

  st_transform(4326)

# We need to set the option to make the mapview package and functions render the points in preview.

mapviewOptions(fgb = FALSE)

# Map our the Tractor Supply stores into a visual format. 

mapview(Tractor_geo)

# To construct a more productive data frame we use the st_intersection data to "merge" our new county information with our geographic Tractor Supply data previously converted.

Tractor_co_geo <- st_intersection(Tractor_geo,co_co)

# Now we need to convert the spatial data into a nonspatial object and join it with our other datasets.

gs_co_2 <- Tractor_co_geo %>%

  st_set_geometry(NULL) %>% # Converts the data back into a dataframe.

  group_by(geoid) %>% # We tell the dataframe to join data based off of the "geoid" variable.

  summarize(stores=n()) %>%

  ungroup()   # Tell R to forget the grouping information otherwise it can affect the data later. 

###########################################################3

# Now having the Arrowquip and Tractor Supply data aggregated into county level information, we need to merge that data with our cattle inventories data for further analysis and review. 

# First, we start by constructing a name and land area reference to join to our cattle inventory dataset. 

co_name_land <- co_co %>% 

  st_set_geometry(NULL) %>% 

  select(geoid,aland,name)

# Now we conduct a sequence of inner join statements all based on the geoid so that for every county we have multiple data points. This set specifically looking at Tractor Supply stores and cattle inventories. 

analysis_ds <- Plan_B %>%

  inner_join(co_name_land ,by = "geoid") %>% #converting sq meters to sq miles

  inner_join(gs_co_2,by = "geoid") 

# We do this again and conduct a sequence of inner join statements all based on the geoid so that for every county we have multiple data points. This set looks at the same as the prior, but with the inclusion of Arrowquip dealers. 

  inner_join(co_name_land ,by = "geoid") %>% #converting sq meters to sq miles

  inner_join(gs_co_2,by = "geoid") %>%

  inner_join(gs_co,by = "geoid")

# Finally, we export the data for use within Tableau for some visualization. 

write_csv(analysis_ds,"stores_data.csv")

write_csv(analysis_ds_2,"stores_data_2.csv")

#################################

#Cluster Analysis 1

# Read in our recently aggregated data for cattle inventories and Tractor Supply locations. 

stores_data <- read_csv("stores_data.csv")

View(stores_data) 

# Here we select relevant columns and scrub any outliers within our data.   

data <- stores_data %>% 

  dplyr::filter(`Inventories`>0,`stores`>0) %>% # Apply the outlier filter stating that we don't want to look at any counties that do not contain Tractor Supply Stores or that have cattle inventories equal to zero. 

  select(Placekey,`Inventories`,`stores`) %>%

  mutate(across(c(2:2),log)) %>% # We then log transform the data because we want to capture absolute changes within our data using linear scale.

  drop_na() # Finally we drop all NA values. 

# We then scale the data by using the scale function which first substracts the data by the mean and then divides it by the standard deviation. 

data_scaled <- data %>%

  select(`Inventories`,`stores`) %>% # Subsetting and collecting only the quantitative data we desire. 

  scale()

# Then perform a k-means clustering with k=5.

set.seed(123) # Set a seed for reproducibility.

kmeans_fit <- kmeans(data_scaled, 

                     centers=5,  # We specify the number of clusters we would like to analyze. 

                     nstart = 25)

# We create a dataframe that collects data on what county and state the data may come from and use a placekey as the identifier. 

location_info <- stores_data %>%

  select(Placekey,State,County)

# Add cluster labels to the dataset and join it to the location_info dataset using the placekey identifier so that our data can match up to the relevant locations in which it was collected in. 

data_clustered <- data %>% 

  mutate(cluster = kmeans_fit$cluster) %>%

  inner_join(location_info,by="Placekey")

Lastly, we export the data into a csv file for use in Tableau. 

write_csv(data_clustered,"Tractorsupply_cattle_clustered_data.csv")