Spar InfoTech, Inc.
Promotion Profit and Loss Report
Initially a P&L by promotion sounds very simple. Calculate the incremental dollars from a Trade Promotion, apply the margin, subtract the cost and get net profit. But a more careful review shows this will not give a comprehensive answer or be useful for an historical analysis or comparisons across brands, sizes and markets. The following is what I have found can used to analyze the results of trade promotions and why these particular calculations make sense. Not everything is applicable in all situations but it is likely that at some point in any analysis questions will arise which will need these numbers. The builder of a promotion evaluation system has to decide for himself what he needs but most of the numbers I recommend can be done with simple calculations so expanding the P&L to include everything is not too difficult.
Heading. Product description, geography and or chain the promotion covers (e.g. national, Denver or Publix).
Number of post promoted weeks. This is necessary to allow for a record of how the trade or pantry loading occurred and to understand how much of the total sales during a promotion period is from post promoted sales.
Promotion description. Since promotions can be complicated the description should be limited to a number of characters with standard abbreviations. Some are obvious such OI and 1 free with 1 (e.g. 1W11) but many others will be company specific.
Number of shipping or sales days the actual promotion covers. Since the volume of sales is related to the number of days it is necessary to know if this is a 1 week, 4-week or 6-week promotion.
1. Periods to analyze. Without exception the promotion needs to be broken into a pre, promotion and post promotion time period. There will be situations where the promotions are sufficiently close together that a pre or post period cannot be shown. Rules need to be established for what takes precedent when that happens. I am not going to go into rules SPAR has found to be most effective here but will handle them separately.
a-Pre-Period. The pre-promotion period can be set for 1 week and in the case of scanner data it is not necessary. There are situations where the impact of a Trade Promotion on the week before the promotion starts is minimal but then actual sales and the SPARLINE will be very close so the calculated impact will be close to zero. Not having a pre-period can call into question the credibility of the analysis in some situations since with shipment data the retailers will hold off ordering the week before a promotion in order to take advantage of the discount in the following week.
b-Promotion Period. This is self explanatory and needs to be the actual promotion dates and not the nominal dates of the promotion. The actual promotion dates can be different than the documented promotion dates since things change in the real world and are not always adequately documented. If there is any uncertainty the data should show the actual dates and these should be used.
c-Post Promoted Period. This is very critical and something ignored by the more simplistic models. For shipment data retailers have financial formulas taking into consideration the discount from a promotion with the cost of holding the product. The amount of lost sales in the post promoted period is usually material and for individual retailers with a high discount, low bulk, non-perishable product can extend for two, three or more months. Post periods are difficult to calculate when using scanner data but very clear when using shipment data. The post promoted period should be as long as it takes the actual sales to reach normal sales (the SPARLINE) so every promotion will have a different post promoted length. For shipment data this is usually 2-6 weeks but can extend longer. For scanner data it is much shorter and often hard to calculate by the standard technique of seeing how many weeks it takes for actual sales to come back to a level of the SPARLINE.
2. Unit of measurement. If the product is sold by the case, then use cases, if in dozens use dozens and if in other units use that. But not dollars as dollars can be impacted by price increases and other factors which make a consistent measurement across years difficult.
3. Measures. For the pre, prom and post period actual units shipped, the SPARLINE, the difference between the two (to show the negative or positive results in each period). The difference should be shown in units, dollars based on the current price, and percentage increase for each period, and the total. A total for the pre, prom and post period is also necessary.
4. Promotion costs. This is the most complex (other than the calculation of the SPARLINE) because promotion costs come in so many different ways. Some examples are:
a-Straight OI either in dollars or percentages. The most simple to use and calculate for cost.
b-Free Goods. 1 free with 11 or 2 free with 10 is a form of OI and also simple to calculate.
c-Alternative discounts. All discounts need to calculated. This is done from the Promotion file and Sales file which show how many units are sold at each discount. Examples are 10% OI but an extra 2% for volume, 10% OI and 5% incremental for performance, a coupon overlay and special allowances for almost anything.
d-Extra costs. When on-pack incentives or packaging with extra product the cost of production needs to be factored in on a per unit basis to adequately calculate the true cost of the promotion over and above any trade discount.
e-Flat costs. These can be for anything such as a flat payment for inclusion in circulars, in store demos and the cost of building special elaborate promotion displays or just the cost of promotional activity.
4. Profit margin and gross incremental profit. This is a simple calculation which applies the incremental gross margin to the incremental dollars from the promotion to calculate incremental gross profit. This needs to be incremental and not average as the units sold are incremental units.
5. Net profit. The total cost of the promotion is subtracted from the gross incremental profit to get Net Profit.
6. Net profit adjusted for inflation. This is a price adjustment. While not necessary for an individual promotion this number is useful for comparing promotions over time particularly for commodity based products or products where the underlying costs of the product can vary significantly over time. I recommend using a 5-year database and in 5 years the price of some products (e.g. coffee, cooking oil, products based on agricultural pricing) can vary by as much as 50%. A method is needed to normalize the results from each individual promotion to allow for comparisons over a number of years to see which promotions were the most effective.
7. Other calculations. Over the years when doing analysis or presenting to senior business management questions come up about the accuracy of the SPARLINE and how can promotions be compared across different brands, sizes or categories when deciding how to allocate spending. These are the measure I recommend be included with every P&L.
a-The break-even SPARLINE. The SPARLINE is a calculation and hence has a margin of error. By showing what the SPARLINE has to be to have the promotion break even it is possible to judge if that is possible. In almost all cases the SPARLINE would have to be so low for a promotion that is losing money to make money that it is clear the promotion is unprofitable and while it could be more or less unprofitable, it is unprofitable. The same is true for profitable promotions. If the break-even SPARLINE is so high that it cannot be reached then the promotion made money and it is just a question of how much. If the break-even SPARLINE is very close to the actual SPARLINE then the promotion is Break Even.
b-Break even incremental volume. This is just another way of giving decision makers comfort that the analysis is correctly showing which promotions are profitable and which are not. By comparing the BE incremental volume with the actual incremental volume you can determine if a an unprofitable promotion could have been profitable or a profitable promotion unprofitable.
c-Cost per incremental unit. This is one of the best measures to compare promotions across brands, sizes and markets that have different volumes by comparing the cost per incremental unit. With this it is possible to allocate promotional spending with the lowest cost per incremental unit first and spending until the BE point is reached. This is an excellent tool for allocating promotional spending over brands, sizes, markets and retailers.
d-Percent Promotion Effect. This is the lift from the promotion during the promotion period. Since post periods can vary from 0 to double digit weeks calculating lift over the pre, prom and post period makes it difficult to compare promotions over time. But just using the lift during the promotion period is one more way to analyze the Trade Promotion spend. This is only one measure and should not be used by itself but as a useful measure in conjunction with other data. This is particularly helpful when one of the objectives of a promotion is to gain market share, load consumers’ pantries or otherwise drive volume instead of profits.
e-Number of weeks of incremental sales. This is a way to normalize the impact of a promotion across different markets, sizes, seasons, and brands. Larger brands, sizes and markets will usually yield more sales but by measuring the weeks of incremental sales it is a way for the results to be normalized.
f-Weeks of incremental profit. This is the same concept as the Number of Weeks of Incremental Sales but using profits instead of sales.
g-Weeks of inventory loading. This is an important measure to see where trade loading is occurring. Trade loading can result in many hidden costs for production facilitates who have large swings in volume, are an indication that the retailer views the promotion more as a buying opportunity than a sell through opportunity and in combination with the incremental sales during the promotion period serve as a measure of sell through.
Not all of these measures are needed for every analysis and there may be some others that specific companies would like to have. But having evaluated millions of promotions across almost every category sold at retail and having presented 1,000’s of analyses these are the one that I found most useful, almost always addressed any questions or issues and serve as a basis for further analysis of many promotions across time, markets, brands and sizes.