Retail location evaluation tool development

Project Overview

 

Mustache is a 40-year old ice cream, snack and drinks store chain founded in Hermosillo, Sonora; with currently 70 stores in four states of northwestern Mexico. This project included the design and deployment of statistical models to evaluate potential store locations –achieved by forecasting sales, given a set of variables e.g. location, size and demographics. Everything integrated in a tool for the end user.

Key-facts_mustache_01

Executive Summary

mustache_002

 

Mustache was founded 40 years ago and it’s owned by a family based in Hermosillo, Sonora. Still, as with other companies within the holding group; they’ve always thrived for the best business practices and to stay ahead of competition in every aspect of their operation.

With strong leadership and an ex-BCG in command, Mustache wanted to implement a fact-based decision making process in their analysis of potential new locations for stores. Our consulting team worked in a methodology that started with a data acquisition process for 70 stores in four states, and considering over 35 categorical and numerical variables for testing; which included e.g. menu items, location, demographics, size, competition, traffic, among others.

After data processing and analysis, our team worked in feature selection and design, followed by the model development process using statistical techniques. Finally, the resulting models were implemented in a tool with user interfaces easy to use, so the decision maker could make use of the results with no previous technical expertise on statistical methods.

This tool asked for specific questions regarding the new location being evaluated and provided a custom parametric form that showed the potential of that given location compared to current stores and other potential locations.

Analytical approach

mustache_03

 

The first step of the methodology was to define dependent and independent variables that would be used throughout the project. As mentioned, over 35 independent variables were listed as potential features what would explain sales in different stores operated by Mustache at the time. These variables were related to physical aspects of actual stores, to be extracted from the point of sale system, to be surveyed or sampled, or to be retrieved from public databases.

The second step was the actual data acquisition process for which our consulting team worked in different fronts to obtain data from different sources and to design sampling or surveying methods for specific variables. Part of the data acquisition process considered data processing and analysis to verify the available data and start understanding the relationships between the different variables and the stores.

The third part of the project was the design, training and testing of statistical models to forecast sales based on the previously mentioned features. Although different types of models can be developed, for this particular project linear regression was proven to be the best choice.

Finally, a methodology for the usage of models was developed at the time that resulting models were implemented using VBA and Microsoft Excel in an easy-to-use tool with user interfaces. This helped the final user to analyze potential locations without actual previous expertise in the usage of statistical models. The deliverable was also accompanied by a written technical report and training.