Case—Forecasting at FoodMart
1. Introduction
Tanisha Davis, a Demand Planner at FoodMart, is looking over historical sales data for a number of grocery items when she notices something odd. The last five years’ sale of Mexican Coke, Coke made with real sugar, show an unusual pattern There is a spike in sales each year at some point in the spring, but not in the same week each year. Because the software has calculated seasonal factors based on five years of history, it increases the forecast for several weeks out of the year, though historically there has only been a one-week spike. This means that the forecast for week 13 is more than 20% different from last year’s actual sales in the same week. Tanisha knows she’ll have to do a deep dive to understand the historical data before she can create an effective forecast.
2. FoodMart
FoodMart is a medium-sized grocery chain operating 107 locations throughout the Northeast United States and Southeast Canada. About 30% of products are replenished through direct store delivery from the vendor. These products, in categories like dairy, snack foods, and bread, are managed largely by the vendors themselves, who keep track of inventory on store shelves and replenish accordingly. The other 70% of products pass through FoodMart’s network of distribution centers. These products are managed by the central planning team, responsible for forecasting, determining optimal inventory levels, and placing purchase orders on thousands of items.
3. Demand Planning at FoodMart
Tanisha Davis works on the central team as a Senior Demand Planner. She is currently responsible for approximately 5,000 grocery items at eight stores in the state of Maine, for a total of about 40,000 item/location combinations. Forecasts are created at a weekly level, resulting in over two million forecasts for which Tanisha is responsible each year.
Given this huge volume, Tanisha’s typical process is to “manage by exception.” This means she automates as much of the process as possible, and spends her time addressing special circumstances. To begin each week, she uses FoodMart’s statistical forecasting software to create forecasts based on historical sales data. The software looks at both trends and seasonality in past sales data, fits the historical data to one or more statistical models, and creates forecasts for future weeks. If the historical sales data for a given item fits a known model at least 90% of the time, that forecast is passed along to the procurement team without any human intervention. If, however, the software is unable to find a good fit for the historical data, a notification is generated and Tanisha will look at the sales history for the individual item/location combination. Using her knowledge of statistical methods, any outside information, and her best judgment, she will either confirm the statistical forecast or make adjustments before it is passed along. For example, Tanisha may know that a certain item was on promotion at a specific time last year, and that caused an atypical spike in demand. She might also know something about associated factors, like the fact that sports drinks sell more in warmer weather. She may also compare different items in the same product category to see if patterns affect multiple items, or see if the pattern is occurring at multiple stores. One year, for example, a predicted snowstorm in the Portland area caused a lot of consumers to stockpile bottled water in the previous week. This is the type of thing that the statistical forecasting software cannot know without some type of human intervention. However, Tanisha also knows that sometimes sales patterns cannot be explained by anything other than random fluctuations in consumer behavior, and too much time spent looking at a single item/location combination could turn out to be wasteful.
4. The Current Problem
Tanisha sees strange patterns in the “Mexican coke” sales history, as well as that of several other items. She decides to call Brad McCray, Marketing Coordinator, and the two have the following exchange:
Tanisha: Hi Brad, I’m seeing some unexplained spikes in “Mexican Coke” sales the last few years. Have we run any special promotions on that item?
Brad: No, I don’t see anything like that in our promotions spreadsheet.
Tanisha: What about a manufacturer’s coupon? Or maybe a sale on a complimentary item?
Brad: Hmmm…I don’t think so. Give me a minute to look. In what month is this happening?
Tanisha: Either March or April. It moves around a bit each year.
Brad: That’s odd, but no, I don’t see anything. Sorry, I wish I could be of more help.
Tanisha: Okay, thanks for your time; have a great week!
Having ruled out sales promotions and coupons, Tanisha was more puzzled than before. What could be the root cause of the historical sales data she was seeing? Should she spend more time investigating, or accept the computer-generated forecasts and move on? As she stared at the historical data, Jamie Gold, another Demand Planner, interrupted her thoughts:
Jamie: I overheard you talking to Brad, and I have an idea what might be going on. Why don’t you send me the spreadsheet so I can take a look?
Tanisha was a bit surprised, because Jamie was very new to the Demand Planning Team, and Tanisha had been the one to train her. What did Jamie know that Tanisha didn’t?
4.1. The Data
In the attached spreadsheet, each tab contains five years (2018–2022) of historical sales data for a different product: orange juice, Medjool dates, Mexican Coke, and an unnamed fourth product. The sales data are broken into weekly buckets, from week 1 through week 52. Also given is the 2023 forecast that has been created by FoodMart’s statistical forecasting software. The unit of measure is cases for all products. Begin by graphing the data in Excel, and then answer the following:
4.2. Questions
Looking at sales history for each of the products, in what phase of the product lifecycle is each of these products (introduction, growth, maturity, or decline)? Does what you see in the data align with what you know about each of these products from your own experience?
What patterns do you see in the historical data for each product? What are the underlying reasons for these patterns?
How did the software use these patterns to create forecasts for each product? Do you think the forecasts are correct, or would you make changes? Why or why not?
Based on the sales patterns, what do you think Product 4 is?
