RETHINK Retail

The Art & Science of Forecasting

When you’re building a business case for a new store, the Year 1 Sales forecast is arguably the most difficult line item to pin down. It’s also one of the most influential variables in the model that can make or break the business case. Most of the expenses shouldn’t vary from your business case because they are, in theory, almost all controllable, and the uncontrollables typically aren’t so large it swings the business case one way or the other.

A lot of forecasting for new store “pro formas” (another word for business case) is shockingly done with little science, and more often based on gut or irrelevant past experiences.

Using Your Finger Like a Weathervane
Using the “finger in the air” forecasting technique

 

In many organizations, there’s a retail leader who says “we did really well at [my last irrelevant/incomparable brand], so we should do well.” The “finger in the air” method almost always leads to overly optimistic/pessimistic bets based on irrelevant, or a lack of, information. They key is to find the benchmark data points that are relevant, and build a data set to extrapolate trends from.

As you forecast, you need to establish your goal: is it to get
every single store right, or get the portfolio right (the aggregate)?

It really depends on where you are in your journey. If you’re just beginning to test into your first few, then each forecast needs to be met/exceeded. If you’re in scale mode, then the portfolio approach lends you a little more flexibility and is the more important output as you answer to your investors (“is this working?”).

Opening stores is not too unlike being an investor: you raise funds, invest in risky assets (stores/stocks/companies), and you optimize for the portfolio ROI, not necessarily for every individual investment. Put another way, the successes or failures of individual bets isn’t as important (but they’re still important!) as long as the total portfolio achieves the target ROI.

The portfolio met expectations; the 40% of missed bets were offset by the winnings of the 60% who exceeded expectations.

This method is great to evaluate your overall ability to (a) forecast and (b) prove out the channel, but can also lead to misguided conclusions. See the below example:

One major beat made up for a lot of bad bets.

This is less likely, but can be possible. The important thing is to evaluate your store pro formas both individually and as a whole. The opposite can also happen and is arguably more common: a few big, terrible bets’ losses threw off the total portfolio:

Two large bets that missed threw off the entire program’s returns.

This example tends to be the common outcome of investing in flagships, or super expensive real estate that doesn’t actually produce sales (“high street” retail).

For what it’s worth, and just to beat a dead horse, the portfolio approach is also how a lot of venture capital investors approach their investments: they just need a single 100x investment to make up for all the duds, so that their portfolio returns 10x overall to the fund owners.

I describe all of that because it’s impossible to get every individual bet right, especially if you’re in scale mode. The most important thing to keep in mind is to avoid extrapolating from individual results and instead focus on analyzing where the beat or miss came from. The beauty of the methodologies outlined below is that they also enable you to break down where your pro forma went wrong, because they all require some type of math to calculate their outputs.

  1. KPI Build: As I mentioned in a prior post, you can break down sales to its underlying components:
    Sales = AOV x Traffic x Conversion
    For sales forecasting, you can compare existing locations’ metrics (or even competitors if you have the intel), to draw it out.

     

    I personally like to compare similar stores: if my new store opportunity is in an indoor mall, for example, I’ll pull in all of my existing indoor mall stores as benchmarks. Typically the store format (street, indoor/outdoor mall, lifestyle center) is highly correlated with some of these metrics: street locations have lower traffic but higher conversion, and non-street locations have higher traffic but lower conversion.

    I’ll usually set my pro forma target by using my “gut” to determine each input. So for example, if I’m a formal shirt brand 👔 and the new store opportunity is in midtown Manhattan, I would think about each of the metrics as such:

  2. AOV: NYC stores average $X for AOV, but given Midtown is office heavy, and therefore likely more penetration of workwear, I might set this slightly higher than the average NYC store that are less visited by these professionals. They’re more like to either buy more expensive items, and/or more items.

  3. Traffic: If this is a street location, I’d benchmark the traffic against similar street locations in NYC, and maybe set it lower than the more tourist-trafficked locations. At a minimum I’d likely set the traffic lower than non-street locations. That said, a counterpoint could be that this is such a concentration of target customers, the traffic should be set higher.

  4. Conversion: As a street location, my conversion will likely be higher than non-street (where there is more unqualified traffic like children or spouses), and as a Midtown location where office workers are dropping in to buy shirts to replace the ones they spilled coffee on (ie a more intentional visit vs a browsing mission), this will likely sit above the more touristy locations.

    By comparing similar cohorts of stores, I can land on metrics that “feel right” based on how similar or different my new opportunity is compared to my existing stores.
    If you don’t have existing locations, you can also create a range of scenarios (eg instead of listing Store 1, 2, 3, etc, list out your Low/Medium/High cases).
    I’ve even gone as far as asking everyone who has an opinion (ie the Real Estate Committee) to back them up with a KPI breakdown, and then used the average of everyone’s estimates as the pro forma. There have been a lot of studies on the power of collective wisdom that support the idea that the average of estimates could be quite accurate, especially if you know how to adjust for biases. My general observation is that those who will be held accountable for the store sales (eg the sales team) will lowball the forecast, and those who are overly excited by the opportunity (eg the person whose hometown the new opportunity is in) will give a wildly optimistic forecast — so be sure to keep those contextual points in mind if you go down this route!

  5. Competitor / Peer Crossover Locations:
    Bust out your middle school math book, because you’ll need to brush off your ratios here.
    If you have existing stores, then another great method is to use peer/competitor sales data and leverage the ratios of existing crossover locations (can be same market or ideally same center/destination) to predict your new store opportunity.
    Let’s say you’re examining an opportunity at Mall A, and that you already have a store at Mall B. And then let’s say your Peer is at both Mall A and Mall B. If you can understand the ratio of your peer’s sales between Mall A and B, you can apply that same ratio to your sales using your Mall B’s sales. See below:

     

    However, this ratio methodology isn’t perfect: if you’re looking to put your first store in the market at Mall A, but this is 1 of 5 stores for your competitor, it’s not quite apples to apples since all of your demand will be concentrated in one location vs theirs spread across five

    One way to mitigate that risk is to look at multiple crossover peers and use the average of all the forecasts from crossover peers. So if the Peer crossover based on the above image forecasts $4,000,000, but another crossover peer’s sales implied $3,500,000, I’d set this method’s forecast at $3,750,000 (the midpoint or average).
    If you’re an omnichannel retailer with wholesale distribution, then you can even replicate this methodology with your wholesale sales. In other words, instead of comparing the multiple of your sales relative to a specific peer, you can look at the multiple of your stores’ sales relative to how much you’re generating at a wholesale partner’s locations. This is one of the powers of being truly omnichannel.

  6. Internal comparables: This is similar to the KPI method in that you’ll pull from your existing locations, but different in that you’ll go into less detail. When I’ve shown the Year 1 Sales Forecasts in most Real Estate Committee Meetings (RECOM), the first question I get is usually “what do other stores in [this market/this cohort/this format] do in sales?”

     

    I’ll often have a few versions of this handy:
    – Stores in the same market
    – Stores of the same format
    – Stores of the same size
    – Stores with similar populations in their trade areas

  7. Trade area penetration: Much like the other methods, this one also relies on historical store opening data. The focus here is to identify the likely penetration rate of a population based on previous openings’ results.
    In this example we assume that stores either drive similar increases in penetration rates, or result in consistent levels of post-opening penetration rates. Once you have a resulting customer count, you can multiply it by their 12 month lifetime value (ie how much your average customer spends in their first 12 months) to compute a forecast (or whatever method you most prefer to translate customer counts into sales).

 

There are a number of other methodologies I’ve used, but these are the four I almost ALWAYS use in some way.

And as with all of my prior posts about how to do one thing or another, each of these has its own set of pros, cons, and caveats. Which brings me to the ultimate tool: “the football field.”Before I joined the retail world, I practically lived in my cubicle 100-120 hours per week (no exaggeration) in investment banking. While I didn’t do new store sales forecasting, I did company valuations which is a form of forecasting. And much like the above methodologies for new stores, there are a number of well known methodologies, each with its own pros, cons, and caveats. The solution: the football field:

 

The idea was built on the notion that there’s an infinite number of ways to value a company, and you should take ALL methods’ outputs into consideration. And so I took the same principles and applied it to retail, but with end goal to effectively do a “valuation” defined as a new store’s Year 1 Sales, which looks like the below as a football field:

 

Each methodology will have its own range, and when you put them side by side you’ll see a natural overlap in a tighter range. Here’s how I interpret the “football field” forecast:

  • Method Ranges: each method has its own range, and the diamond inside of each represents the average of that method’s range.
  • Suggested Pro Forma: illustrates the average of averages, which is usually where I set a pro forma (sometimes I’ll eliminate or override it if I think one of the methods is not valid for one reason or another).
  • Min Required: if I know the Year 1 Sales that is required to achieve a GO decision (eg IRR of 20%), then I like to overlay it to show how realistic or unrealistic it is based on the methods’ outputs.
  • Actual Pro Forma: This is what you think the store will actually do. In theory, you should never bring a deal to committee for approval if this line sits below the Min Required, but I just left it because I’m lazy. In a perfect world, the Actual Pro Forma will sit closely to the Suggested Pro Forma, and both would sit above the Min Required. Alternatively, you can use this model to illustrate why you should NOT open a store somewhere. Unfortunately for many DTC brands, this might be a common defensive tactic to cover your butt if a store did horribly — you can point to this and say “I told you so.”

This is the SEC-mandated disclaimer for investors in stocks and other securities, and very much applies to this same context.
Most of these methodologies rely on past openings and their data to forecast future events, and as you open more stores you’ll get better at identifying trends and patterns. But this type of modeling won’t get it right every single time; there are always exceptions and outliers that buck the trend.
These methodologies are just using trends and averages, which might work over time and at scale, but also don’t take into account a lot of other variables that influence sales:

  1. The team: Great managers make great teams, and great teams make great customers. I’ve seen stores beat pro forma because they had a kickass GM, and I’ve seen stores miss pro forma because they had a terrible GM. The same could be said of the broader sales team, and even of the RMs.
  2. The space: You may have noticed that the football field framework might look the same if you’re looking at space A or space B in the same center. Theoretically the way you account for this is by setting different traffic counts to space A and space B’s individual KPI builds, but it won’t be reflected in any of the other methodologies.
  3. Marketing support: Even if the metrics indicated that an opportunity is perfect on paper, you still need to support the store with marketing. The store will take too long to ramp if the customer never heard it was opened.
  4. Migration patterns: This can theoretically be accounted for in the population counts of the market penetration or in traffic in the KPI build method, but is not explicitly reflected here. This can refer to the 2020 migration patterns that decimated CA markets, or even refer to new developments in a market where customers might need some time to condition themselves to shopping.
  5. Competition: Generally I’ve found competition to be good: the better a competitor is doing, the better I’ll be doing. But that might not be the case if a direct competitor later moves in right next to you and takes your customers. These methods do not account for that explicitly.
  6. Shopping landscape: In markets where there are limited places to shop (ie 1-2 destinations that your customer typically shops), these methodologies tend be more predictive. But in markets where there are a LOT of options for the customer, that also means there is a lot more noise in the data. Comparing crossover sales in Denver, for example, will likely be more accurate than crossover sales in NYC.
  7. And so much more

I spent years trying out various other methodologies and across numerous brands, and these are just the four methods that worked most consistently for me.
At a minimum, part of your annual strategy sessions should include a look-back at all forecasts to analyze the accuracy of:

  1. Your ultimate Year 1 Sales forecast: how accurate were YOU?
  2. The Suggested Pro Forma: how accurate was the MODEL?
  3. Each individual methodology: how accurate was each METHOD?

You should refine these methods regularly, and even experiment with others. Not only will this make you better at forecasting, it will also help you understand which internal and external variables are actually correlated with your business.

TRI HUB BOUT THE AUTHOR