Trade Mix Optimizer

ImageTMO is easy to use. It presents results in a simple to understand Excel workbook where the sales rep. can try different trade plans and see results immediately.

TMO takes into account all the causal factors affecting the business: displays, features, price, FSI's (for both your product and the key competitor items). It also include holidays, seasonal factors, events such as the Superbowl, even the weather if that is appropriate. The business model figures out the correlations between sales and each causal fact and rejects those that have no impact. It then tells you the dollar impact of each fact, per point of ACV distribution of that fact or per dollar of price.

Run TMO at market or account level, and at SKU or promo group level on the product side.

It is a planning tool for sales people, not a trade management tool or a short term demand planner. By default, it plugs in the same trade plan as the previous 52 weeks, and the user can easily change it and see the results instantly. For example, you may see that in aisle displays have a significant impact on sales. Switching my trade dollars from a less effective medium or simply doing more of them or increasing their %ACV distribution has a great benefit to the sales forecast. The product quantifies the sales impact of each trade medium.

Once a sales person has figured the trade plan, the results can easily be consolidated for demand forecasting.
The primary source of data will be sales and causal information from IRI/ACNielsen and FSI's usually from Adview. We provide holidays, events and weather in a spreadsheet that you are free to modify.

Demand Forecasting Model

Purpose and Scope: The purpose is to model our company's retail business and produce strategic demand forecasts. The result of the model can be either a dollar sales forecast, or a volume (cases) forecast. Strategic forecasting looks across the entire company's business and months or even years into the future. Tactical forecasting, on the other hand, is used for forecasting short term sales on a customer by customer basis and for specific product lines. Tactical forecasting is used by a sales rep when taking orders to say "this is what you ordered last year for this event, these were the terms, and this is what we predict sales will be." Strategic forecasting is a tool for long term planning: "what product lines will be in our mix?"; "what new manufacturing capacity will we need?" and so on. We can operate the model at any level where we have historical data available. For geography, division or key account; for product, brand, package or item. If the business purpose is to schedule the plants, then the forecast would volume, by plant, by item, by week. Sales data from a syndicated provider is required for long term planning because we want to include competitive activity in the model. For strategic forecasting, market level data is adequate. Products are promoted at different levels, sometimes it is by brand, sometimes by a package, so to handle all combinations, item, or UPC must the product level. Weekly data is adequate - monthly is not because holidays and events play an important part in most business' sales planning activity. (There is no business reason to attempt a daily forecast).

Methodology: This will be a multiple linear regression model with net sales and case volume as the dependent variables and the business drivers as the independent variables. Technically, a regression model has only 1 dependent variable, so this is in fact 2 side-by-side-models. To be effective, the model requires 3 years of weekly history.
Business Drivers (independent variables)
These are the factors that drive consumer demand.

    1. Price.  As an example, in the carbonated soft drinks category, with two dominant competitors, the price differential between them is an important driver. 

      To work in all categories, better than one price differential variable will be 4 variables:  the price at our brand, and the 2 top competitors, then ‘all others’.

      Promotion (other than price based).  These are the types of promotions:
    1. Features: this is a print ad, one of these:
    1. FSI: (full supplement insert, usually a Sunday newspaper insert)
    2. In Store: some print ad in the store such as a banner or hand out.
    3. Inserts: These are ‘blow in’ single sheet ads inserted into a newspaper.
    4. Mailer: Ads sent through the mail, usually including a coupon.
    5. ROP (run of print): an ad inside the newspaper alongside the articles, more targeted, but smaller than an insert.
    6. Coupons.
      Features are classified by size (A,B or C)
    1. Displays: this is an in-store display, either in the lobby, in an aisle or on the end of an aisle (‘endcap’).
    2. Frequent Shopper Programs: in-store price discounts available only to members of the frequent shopper program.

    1. Holidays/Events.  These are a major driver of consumer demand.
      1. New Year’s
      2. Superbowl
      3. Easter/Passover
      4. Memorial Day
      5. Fourth of July
      6. Labor Day
      7. Thanksgiving
      8. Christmas/Hannukah

        (Halloween?, Baseball playoffs?, NASCAR?)

    2. Seasonality. This is the general difference between seasons and is obviously geography dependent.  The average daily high temperature is probably a good variable.

    3. Weather events.  Different from the seasonal trends, this could be specific events such as heat waves, cold snaps, hurricanes(?)

    4. Demographics.  With data only at Key Account / market level, demographics will be of limited use since any particular key account/market combination has all or most demographics within it.  If we have data at a lower level, this would be a meaningful driver.

    5. External factors.  Possible external factors are a product recall scare (ours: bad, competitors: good!), news reports (energy drinks are good/bad for you!)

 

Mathematical Model
Since regression is a mathematical process, all variables must have numeric properties.  For sales, volume and price, these are already in the appropriate form.
Usually this numeric property is a quantity, or a value such as price.  In this model, many variables will have a ‘on / off’ or ‘presence / non-presence’ value. 

Each type of promotion is a separate independent variable and its value is the percent of stores in the key account/market that sold that item under that promotion situation in that week. 

      1. Promotion variables are:
      1. FSI feature
      2. In Store feature
      3. A size insert feature
      4. B size insert feature
      5. C size insert feature
      6. Mailer feature
      7. ROP (run of print) feature
      8. Coupons
      9. Lobby display
      10. Aisle display
      11. Endcap display
      12. Frequent Shopper price discounts
    1. Holidays and events are a simple ‘1’ or ‘0’ in the appropriate week.  Although holidays and events are discrete (do not overlap) we should represent each as a separate variable rather than one ‘Holiday’ variable because each will have different impacts on demand:
      1. New Year’s
      2. Superbowl
      3. Easter/Passover
      4. Memorial Day
      5. Fourth of July
      6. Labor Day
      7. Thanksgiving
      8. Christmas/Hannukah
    2. Seasonality: the average high temperature for the market is a good quantitative value for seasonality
    3. Weather factors:  this is a subjective variable, we could use 1/0 values for each type of weather event or assign a +1/-1 value for good/bad events.
    4. External Factors: similar values as for weather events.

 

Outputs
Regression is a backward looking method:  it models the relationships between the dependent variable (sales) and the independent variables (price, promotion, holidays etc.) back over time.  To make a forecast, we predict the independent variables as best we can and combine them using these relationships in an equation that results in the sales forecast.

Simple Linear Regression, as its name implies, tries to find a straight line relationship between the dependent variable and one independent variable.

A straight line has the equation:

y = i + cx

where   y   is the dependent variable
i   is the intercept, that is the value of y when x is zero
c   is the gradient of the line or the ‘coefficient’)

x   is the independent variable.


In soft drink terms, the relationship between 6 pack sales and the price differential between Pepsi Cola and Coke Classic 6 packs would look like this:

            [Pepsi sales]= [base] + [coeff] * [price differential]
where   [base] is the average sales when there is no price differential
[coeff] is the increase in sales for every dollar that Pepsi price is lower than Coca Cola’s
[price differential] is the difference between Pepsi price and the equivalent Coca Cola product


The regression model will tell us that the [base] was '$x' millions , that is the average sales per week when there is no price differential; the [coefficient] was 'y' which means that for every dollar that our price is to our advantage, we can expect to make $'y' in extra business.

Regression produces a second statistic called the correlation.  If the correlation is 1.0, then the dependent and independent variables are in direct proportion (or perfectly synchronized if you will).  A correlation of -1.0 means they are in perfect inverse proportion.  0.0 means there is no correlation between the variables.

There is one more important statistic: the significance.  This tells us whether there are enough data points and whether we can depend on the correlation.  (It is possible to have a high correlation by accident, that is, the data have nothing to do with each other even though we can fit a good straight line through them.)


Building the model
The model is a table with a column for the dependent variable and columns for each independent variable.  We have identified what we think are the drivers (independent variables) of the model.  IRI has most of the data we need for pricing and promotion.  Weather we can get from the national weather bureau.  Holidays, we know.

We need 3 years of weekly history to have a significant number of data points.  These are the rows of the model table.  Excel will be adequate for running preliminary regressions while we tune the model.

Tuning the model
When we run the model, first we should look at the correlation of each variable and its significance.  We may find poor correlations as we did with price differential and need to replace it or use a different variation.  For example, maybe actual price is a better driver than price differential. 

We need to understand the time lag between orders and consumer purchases, for example, most buying is done the weekend before a holiday, we may have better correlation setting holidays to 2 weeks, not one.

When we have found the best set of independent variables, we ‘regress’ the model.  This means using the model to make a forecast for last year.  This is the ultimate test when we see how close we came to a good prediction.