We Don’t Plan That Way!

A client of ours heard from their sales team that they have never planned promotional events to the product SKU or product group level; in their words “We don’t plan like that!”. That comment led me to think about why a consumer goods manufacturer deploys a trade promotion management solution, and should it conform to the yesterday process or build to the tomorrow process. Further, what are the objectives of the various stake holders in the organization and are they at cross purposes? This article attempts to explore these issues which ultimately lead to change management issues…or not.

More and more, trade promotion management solution initiatives are being driven by the finance department because, in part, trade promotion spending is growing steadily higher with return on investments growing steadily lower. A noted observer of the trade promotion space recently noted that the average trade dollars expended by CPG manufacturers has exceeded 20% of gross revenues, and that number is approaching double that 20% range in some categories and geographies. This pundit went on to declare that TPM solutions were no longer a source of competitive advantage, but TPM solution are, in fact, a necessity. Why should a trade promotion management solution be deemed a necessity, and why are finance departments driving these projects?

Finance departments are responsible, in part, for knowing how much money is being spent on the trade spending line for each product for each week, or at least for each month or fiscal period. In addition, more and more companies are creating sales finance departments to do more return on invest analyses, and to determine whether each SKU is paying its way. But wait! Your company’s trade promotion process has always just done trade spending at the brand level, and have depended on Excel formulas to estimate how much is being spent for each SKU. That process has not even allowed for an understanding of what actually happened financially versus what was planned. The level of financial understanding is greatly enhanced by a good trade promotion management solution that stores good financial data down to the account, SKU and week level. Measures such as manufacture profit, retailer profit, total OI spend, total BB spend etc. all provide great line of sight to important analyses such as assortment optimization, trade promotion post analyses as well as the financial fundamentals like accruing the right amounts to the general ledger.

So why is the fuss coming from the sales department? Perhaps, there are conflicting objectives. When sales finance is held accountable for getting the right trade spending accrued to the right month and SKU, and to determine whether trade spending has been profitable; sales may have a different agenda. Sales may want to be able to go spend their trade funds at the account without the onus of being profitable held over their head, and they may be concerned that not only will they be held accountable for hitting a volume objective, but also be profitable in the pursuit. This is a Yin & Yang that needs to be addressed, because the goals of each department are simply not aligned. The sales team will fight the movement to a trade planning tool that will bring more efficiency, visibility and at very least sell more goods for the same money. There are numbers that suggest a comprehensive trade promotion management system can drop as much as 10% to the bottom line by just cleaning up unprofitable and inefficient trade spending.

The finance team needs to know how profitable the company’s customers are, and to insure the company’s trade spending dollars are as efficient as possible. Finance also needs visibility to the deduction settlement process to insure deductions and the spending they represent are being properly accounted for. Additionally, finance needs to insure future trade spending liabilities are clearly identified and the proper amount of funding is being accrued. As companies deploy sales finance departments these financially astute people can also help the sales team analyze the financial viability of their promotional plans.

However, the sales team cannot be allowed to fight the effort to move to a more granular planning process; just because “We don’t plan that way”. In order for the tomorrow process to work, everyone from the senior management team to the rank and file account managers must be on board. Often times the account management teams fear that someone is scrutinizing their trade funds expenditures, and sometimes the account managers do spend money unwisely. The fear of big brother watching over their plans or those unwise spending practices can become an obstacle to the account managers willingly adopting the tomorrow process. A trade promotion management solution cannot be viewed as a sales system or a finance system; a TPM solution must be viewed as a company business management solution.

What are some of the ways changing the culture can be accomplished?

  1. There must be senior management ownership, engagement and mandate that there is a new way of doing business at your company.
  2. Deploy a stakeholder team that is purposely cross functional, and insure that team has the time to drive the project through to completion.
  3. A gradual shift from yesterday’s process to tomorrow’s process will help the sales team make the process adjustment.
  4. Aligning sales’ objectives with those of the sales finance team may help move sales from the idea of selling volume at any price to selling profitable volume.
  5. Imagine how a sales person might respond if part their compensation package included a percentage of the trade dollar savings they might gain by planning more profitable promotions.
  6. Find the promotable people in your organization to champion the new way doing business with tomorrow’s process.
  7. Adopt the same language up and down the organization so that everyone views the same KPI’s in the same manner.

Providing visibility to certain key metrics might also help the sales team understand a good promotion outcome versus a bad one. What percent of dollar revenue, for a promotion, should be spent? How much per incremental unit should be spent? Is $1.54/incremental unit for a $5.99 every day retail priced item too much? Here is a Promotion Scorecard from our Trade Promotion Management solution:

Can your sales and finance teams see this level of information, and react to it with a plan of action driven by facts? A promotion scorecard can bring together metrics such as profit, % of spend to total dollar sales and cost/incremental unit. If your sales team cannot see these types of metrics, then we should make plans to discuss how CPGToolBox.com can help your team drive more profitability into your trade spending practices.

CPGToolBox.com, LLC is a CPG sales and marketing solutions provider building tools on the Salesforce.com platform. We can be reached at 678-503-5001 Ext. 301 or email Rick Pensa at rpensa@cpgtoolbox.com. See more information about the CPGToolBox Trade Planner by clicking on the following link https://appexchange.salesforce.com/listingDetail?listingId=a0N3000000B42zhEAB .




TPM Systems: Connect Corporate Planning and Business Intelligence to Event-Level Planning

TPM Systems: Connect Corporate Planning and Business Intelligence to Event-Level Planning

By Tim Vollman, President S3 Mobility and Rick Pensa, President/CEO CPGToolBox.com, LLC

Many companies today perform high level corporate planning (customer P&L planning) and tactical Trade Promotion Management (TPM) event level planning in two different systems. This is a major factor in the ‘disconnect’ between original budgets, revised planning, and the detailed planning that exists within the TPM system. In most cases, once the detailed plan is created, there are no controls or processes to ensure that it is connected to your overall corporate plan. The breakdown between these two systems can often result in TPM overspends and plan shortfalls.

Guiding Principles to Planning

One of the keys to effective planning is to have a system that permits you to establish goals, and then set the related tactical event plans to execute your plan objectives. This would include goals such as market share by brand, customer profit margin, and other similar metrics.

Another critical key is to connect the corporate plan to the tactical event plan, which requires that you first decide on the level of planning for both corporate and the event level planning. Typically, your corporate plan is performed at a customer and product summary level. This allows for more of a macro planning approach, in which you can perform ‘what ifs’ and quickly determine the changes in pricing, costs, and the impact to overall profit. This permits users to set goals, and then create tactical event plans to meet these goals. Top down planning using allocation methods are essential to create quick directional views that subsequently provide for an iterative process.

Clearly, setting your customer and brand-level tactics is not something you want to first perform at an event level, as you must first determine if your volume, pricing and related costs will derive your desired results.

Once your goals have been set, and you have created your plan, the next step is to ‘connect’ this plan to your detailed tactical event level plan. A seamless process must also exist so that base volume, lift volume and related spending at the corporate level are truly connected at the event level. This means that downstream tactical event level changes are managed, recorded and summarized back to the corporate planning level, thus keeping the respective systems synchronized.

Additionally, trade spending controls are implemented at the tactical event level providing the account manager and senior management with an understanding of how the tactical plans will impact the higher level strategic planning goals and KPI’s. This is done through an effective trade funds management (TFM) process that assigns tactical event funding from one or more trade spending budgets/funds.

Building in the Proper Controls

Proper controls must be implemented throughout the planning process. Controls such as spending limits, spend per unit, and related lift-to-cost ratios are all essential to forcing compliance and approvals throughout your process. This reduces the work flow time from contract initiation through approval and finally through resolution.

Best practices such as…

  • Having a rule-based system, allows for a streamlined process, where only outliers require oversight.
  • Having process driven oversight capabilities allowing visibility to non-compliant events
  • Knowing planned spending by event or product visible up the hierarchy
  • Utilizing cost information to review margins and contribution
  • Providing visibility to historical pricing and to protect against margin erosion
  • Connecting actual settlement spending with specific tactical events

…all provide important processes, metrics and overall controls to connect Corporate and Event planning, while providing the efficient workflow processes.

About the Author:

Rick Pensa is the president/CEO of CPGToolBox.com, LLC and has more than 23 years of focus on the Trade Promotion Management process, TPM analytics and system implementation in the CPG industry. CPGToolBox.com, LLC is focused on providing sales and marketing tools built on the Force.com platform.

Tim Vollman is the president of S3 Mobility and has spent more than 25 years focused in Corporate Planning, Trade Promotion Management, and related Business intelligence systems. Mr. Vollman has founded three software companies in the Planning/ Optimization sector.

Trade Promotion Management Change Management: How To Gain Sales Team Acceptance

So you have made the decision to leave spreadsheets behind, and you are moving to a trade promotion management software solution. What will it mean to your sales organization to get out of spreadsheet hell?

The whole issue of change management is a huge one that will impact the success or failure of the TPM project. Let’s look at the issue from a couple of perspectives – Sales, Supply Chain, Finance, Management and IT. Each of these departments may see a benefit, threat or a little of both.

Sales may see the departure from Excel as a potential increase in workload, and that there is a new piece of software that they will have to learn and manage. Many companies adopt the mantra that they want to see their sales teams “selling” rather than sitting behind a computer; yet they want those same sales people to evolve to be thoughtful, proactive, collaborative ‘business managers’. It is a paradox in that many Sales Managers don’t understand or fully realize the upstream and downstream implications (and importance) that their business plans have on the broader organization, and often times they view a TPM tool as just ‘too much work’:

Consider the following:

  • The sales account manager creates a new promotional plan in a TPM tool…what happens? The combination of base volume and incremental volume is captured in a common tool and format that helps drive the demand and production plan.
  • When the system calculates the planned spending and that planned spending gets assigned to a fund, the finance people suddenly have visibility to future promotional liabilities and the ability to real-time actualization.
  • The Marketing departments now have a repository to view customer plans, planned promotional activity, new distribution, and brand-specific spending. All of which are critical for profitably managing and building a brand!
  • Sales leadership now has a common view into their business by channel, customer and by brand, and they can easily identify gaps and proactively course-correct as necessary.

All of the above information is coming from the person who is closest to the account and should know more than others in the organization. However, because of increased visibility and change away from the ease of a spreadsheet; sales people may find the new TPM tool too invasive of their day to day activities.

The Demand Planning and Supply Chain group sees a tool that will give them a much more definable look at volume causality to help them know when and why volume will spike on each SKU (and at which account) well in advance of the order. They have a single source of information they can review prior to locking down their forecast. They will see a TPM tool as a significant benefit over spreadsheet hell, the lack of any visibility…or the “just trust me” approach.

Finance sees a solution to the question of ‘when’ and ‘how much’ money should I accrue to an SKU or brand for a given period or month rather than a “peanut butter” spread approach of spending to the GL. In the spreadsheet world, some SKU’s get too much spending accrued to them and other SKU’s don’t get enough funds accrual assigned. Insight into this sort of real time information will provide finance with a much better appraisal of the profit and loss situation for each SKU, brand, customer and channel, and they will surely find the new TPM system to be a benefit over the static spreadsheet approach.

Many companies manage their trade investment fund balances, based on variable rates, in spreadsheets; so the ability to see accumulated trade funds for all 150 spreadsheets is huge. Not to mention seeing what has been planned against those funds all prior to the events happening, or what has been spent against those funds as a result of the settlement process. Sales management has a difficult time trying to synthesize all of the trade investment accrual funds, the commitments and spending against those funds in a static spreadsheet environment. Therefore, sales management will see great benefit from a dynamic TPM system that provides them drill-down functionality and real-time insights into their volume and spending.

Of all groups, IT may have the most reservations when implementing a new TPM simulation tool. If the TPM installation is an on premise tool, then IT has a major stake in the maintenance of hardware, software, infrastructure and data. However, with a cloud-based solution such as CPGToolBox Trade Planner, most of that responsibility is shifted into the cloud, and IT is responsible for moving data into and out of their ERP systems to interface seamlessly with the tool. Certainly a cloud-based solution is less invasive and burdensome on the internal IT staff than an on premise solution, and generally the initial investment in resources, training and implementation are far less for a cloud-based tool.

So if the Sales Team and account managers are the critical lynchpins for accurate, timely, reliable customer shipment and planning data; how does the organization gain their acceptance and endorsement of the new TPM tool?

The following are a few considerations that might address the issue with your organization:

  1. Are you doing any trade planning at all? If you are not doing trade planning today, or if it is not happening on the back of a cocktail napkin, then any new software solution for Trade Promotion Management would require a change in organizational process and Sales behavior. A TPM tool is a terrific facilitator to help your organization review, change and perfect your promotion planning process, but senior management must clearly define the process, expectations and hold to those expectations throughout the whole change management process. While change and enhancement is a given, senior management cannot relent on the journey through the process of changing the organization.
  2. Is your current trade planning process too complex? Spreadsheets can accommodate all sorts of exceptions to the rule and different twists to the rule, but a software tool may not be able to accommodate all of those possible exceptions to the rule without being heavily customized; but that costs money and takes much more time to implement than a relatively out-of-the-box solution. Get the basic process grooved in before you start layering on all of the exceptions and accommodations for promotional contract variances. You may find out that all of those complex exceptions were not really needed in the first place.
  3. Find a champion for the TPM tool in the ranks of the sales team. That sales champion will be the best person to explain how the job can be made easier using the new TPM tool, and his/her peers will sit up and listen. Your sales champion will show the rest of the sales team how to use the TPM tool to shorten the planning process by cloning or copying previous promotional plans into the new planning period, how to use in joint business planning sessions with their customers, and other uses and short cuts they can learn.
  4. Plan many incremental training events and follow up training approaches. Don’t rely on just one massive training session to instill the new process and software tool into the sales organization. Utilize your first set of training efforts to insure the key steps in the process are fully learned i.e. deal with promotion planning first and then come back and deal with settlement training. Training should be considered an ‘evolution’ and not a ‘revolution’, with many iterative opportunities to enhance and improve the planning process both internally and externally. Use technology such as web meetings and “lunch & learn” sessions to cover areas that may require remedial training. Plan a brief training session at all sales meetings for the first year or so to insure cross pollination of ideas from power users to struggling users. Use videos of training sessions and actual point & click videos that are 3-5 minutes induration to provide for training on demand.
  5. Hold the course and don’t relent! Too often, sales management will not hold their ground on a well thought out process when the sales team chafes at the requirement to move off of spreadsheets and into a TPM tool. You must make the TPM tool become oxygen to the sales team, in other words, sales cannot do its job without using the TPM tool. There can be no exceptions or work around options to the TPM tool, and your organization’s success depends upon its successful implementation and ongoing management.

With commitment, preparedness, and a little ‘luck’ you WILL change the culture at your organization. Experience has shown that a TPM tool that provides process, visibility and accountability can drop as much as 10 points off your trade spending budget. This is money that can be reinvested to build brands, or dropped directly to the bottom line! Inefficient trade planning is costly, and getting the process under control can pay for the TPM tool in a matter of months.

CPGToolBox.com is a trade promotion management company that has built a cloud-base full function Trade Promotion Management solution on the Salesforce.com platform. CPGToolBox Trade Planner will help your sales team manage fund allocation, accrual funds, trade promotion planning with predictive modeling, settlements and reporting. Please contact Rick Pensa at rpensa@cpgtoolbox.com or call at 678-503-5001 Ext 301 or check out our website at www.cpgtoolbox.com .

Excel 2010 & 2013 Data Slicers Taking Pivot Tables To A New Level Of Control

If you are a Microsoft Excel power user then you have probably been using pivot tables for quite some time. Pivot table functionality is among some of the most powerful features within Excel because it can turn a flat table of raw data into a powerful database with the ability to move data from rows to columns and to the page by position with just a couple of clicks of a mouse. In addition, a good pivot table user can do some interesting things such as creating formulas that exist within the pivot table or taking a field such as dollar sales and creating a dollar share with that same field. If you are a really creative Excel pivot table user then you have probably created various templates using, but hiding, pivot tables so that you can arrange your data in many creative ways such as a dashboard, a controlled tabular report or even a combination of both of these elements.

In Excel 2010 a new functionality was added… Data Slicers. Slicers are one-click filtering controls that narrow the data shown in PivotTables and PivotCharts. Slicers can be used interactively to display data changes when you apply filters. For example, you can create a PivotTable report or chart that shows sales by year, and then add a Slicer that represents promotions. The Slicer is added as an extra control in the PivotTable or chart, and lets you quickly select criteria and instantly show the changes. You could also embed the breakdown by promotion in the report itself, by including the field in the row or column heading, but Slicers do not add extra rows to the table, they only provide an interactive view into the data.

Data Slicers

The new Excel Data Slicer functionality really opens up how you can build a more intuitive templates that clearly identify which items are included or excluded from a particular pivot table view. Formerly, using only pivot table fields it was not as easy to see which items were included or excluded from a particular filtered view. In the following example it is easy to see which sizes, type, product category, flavors, geography and periods are included in the data portrayed on the left side of the report.

Data Slicers are available only in Excel 2010 and Excel 2013 and are very easy to use and create as the following steps will demonstrate:

  1. Create your pivot table as you normally would do
  2. Layout a consolidated report such as the following –
  3. Click anywhere in the pivot table and click on the Pivot Table Options (Excel 2010) or Analyze (Excel 2013), and click on the Insert Slicer option –
  4. Place a checkmark beside each pivot table element you want to have displayed as a Slicer, by left clicking in the box to the left of the element and click the OK button when done –
  5. Excel will insert a Slicer for each of the pivot table elements you placed a checkmark next to –
  6. The Slicers can be moved by dragging them around on the screen and grabbing the handles on the sides, top, bottom or corner to change their size. You will note in the example above that all of the elements for each Slicer are highlighted so the data display on the left has no filter and is displaying all data for all geographies, time, sizes, type and product category at one time.
  7. By clicking on the following criteria – Sizes = 16, Type = Half & Half, Geography = Denver and Time= Latest 52 Weeks…the data will be filtered now to display those specific settings, and you will see only two items in the Product Category are now highlighted –


These days everybody boasts of a massive spreadsheet. But almost no one needs all the data at same time. We are always filtering data for the latest quarter, 6 months starting Mother’s day or 8 weeks from November 1st etc. Of course, you can use auto-filter and select all the dates. But it is a pain. Thanks to Timelines, filtering for dates is a breeze. You can add timelines for any date column in a pivot table / pivot chart. I am sure your internal clients & bosses will love it.

Really, the Timeline (only available in Excel 2013) is just another type of Slicer that only operates on date formatted fields. You can set up and use the Timeline Slicer in the following manner:

  1. Set up your pivot table and Data Slicers in a manner outlined above
  2. Click anywhere in your pivot table and click on Pivot Table Options (Excel 2010) or in Analyze (Excel 2013), and click on the Insert Timeline item in the Filter section of the ribbon. If you have pivot table fields that are date formatted those date fields will be presented as optional fields to create a Timeline from
  3. Select the date formatted field you wish to make a Timeline from; click on the box to the left of the field to place a checkmark and then click on the OK button –
  4. Excel 2013 Will Pl. a timeline Data Slicer onto your spreadsheet and you can move it around and change the size of it as we discussed above and the Data Slicer section. When the timeline is displayed all available fields are highlighted, however you can adjust and select only the periods that you want to display in the pivot table report.
  5. The Timeline slicer can be viewed in year, quarter, months or day aggregations to limit the data viewed in the pivot table report. For the purposes of this example I will select the month option. In the following example we have filtered on one market and set the geography slicer and one product in the product slicer, and we have selected the months of May and June. We can control the slicer by grabbing the ends of the slide along the timeline so we could click on May and then grab the right end of the slider and slide it to June –

As you can quickly see, the Microsoft Excel Data Slicer and Timeline filter functionality provide a powerful user interface to allow users with little to no pivot table experience to quickly and intuitively filter on the data they want to see.

Insight, Information & Consulting Services, Inc. is a data visualization consulting firm that helps its Consumer Package Goods clients better “see” their data. We deliver data visualization tools from cost-effective Excel & PowerPoint templates to a turnkey data visualization tool that will allow your users to see their data in PowerPoint templates using only a web browser. For more information, please visit our website at www.insightinformation.net or call Rick Pensa at 770-425-4243.


Passing A Date Parameter

I recently had an interesting challenge that required the passing of two date parameters from an Excel spreadsheet over to an Access database in order to return data that met the parameters of data between the first date and the second date. The Excel spreadsheet template reported the data in a variety of static reports based on the date range of the data returned from the Access database. The solution to this problem was to use the Microsoft Query software available in the Excel spreadsheet. The following is a demonstration of how you can pull data from a Access database back into an Excel spreadsheet.

The following is an example of the report we want to populate from the Access database with data that meets a certain date range:

Our Access database contains over four years of data at the weekly level, but we will only want to pull back certain weeks of data to populate our report. The first step in our process will be to set up an external data pull using Microsoft Query:

We simply need to follow the prompts to find the appropriate Access database:

The next step is to select the table we want to extract our data from and select the fields from that table that we want to display in our data extract:

Then we follow the prompts through the Microsoft Query Wizard until we get to the point where the Wizard asked us where we want to return the data to and select “View data or edit query in Microsoft Query”:

In Microsoft Query click on Show/Hide Criteria to make visible the criteria fields and then select from the criteria field the desired field that we will want to pass a parameter to:

In our example query we have selected the week ending field for which to develop our parameters to be passed and we will enter the Access formula for Between to enter the start date and the end date for our query to select data from the associated Access database table:

Please note that we used the opening and closing square brackets to indicate that the query will expect a parameter that we are naming Start and a second parameter that we are naming End as our starting and ending date parameters. When we have completed our parameter formula simply click on the File menu at the top and then click on the bottom option “Return data to Microsoft Excel” and we will be presented with the only option available which is to return the data as a Table. Select OK and the table will be populated in the Excel spreadsheet.

Now we are ready to set up our date parameter fields to pass to the access database, and we do this by navigating to the data tab and clicking on the Connections menu item. Select the two feels that we want to identify as our Start date and our End date at a convenient and strategic spot in the Excel spreadsheet; in our example we will go to Sheet2 and use cell A1 for our Start date and cell A2 for our End date. In our example we want to pull data from the Access database between the dates of October 9, 2010 (10/9/2010) and April 23, 2011 (4/23/2011).

The final step in the process is to now tell Microsoft Query which of these fields is related to the start date and which is related to the end date. We do this by navigating to the Data tab and then select Connection menu option:

From the Connections wizard click on the properties button on the right side of the wizard, select the Definitions tab and click on the Parameters button at the bottom of the dialog box.


In the parameters dialog box first select the Start date parameter and identify the cell associated with it (in our example cell A1 on Sheet2) then select the End date parameter (in our example cell A2 on Sheet2).

Click on the OK box and the processes now completed. There are a couple of caveats with this approach; for example you can only deliver the data back into a table and not a pivot table, however there are various formulas which we will cover in subsequent newsletters that will allow you to aggregate the data using SUMIFS and COUNTIFS formulas.

Insight, Information & Consulting Services is a data visualization consulting firm that can help your company set up Excel templates that connect to Access databases, SQL databases as well as other data sources to provide your field sales and marketing teams with actionable data delivery tools. Please contact Rick Pensa at 770-425-4243 for further information.

TPM In The Cloud

Trade Promotion Management (TPM) in the cloud! The holy grail of a trade promotion tool that is ubiquitous, easy to use and easy to afford is now here. Salesforce.com has been a driving force in the cloud computing wave that is sweeping through the computer world. Cloud computing removes the need for expensive hardware, network infrastructure, data management and software maintenance because these issues are all handled through Salesforce.com in the cloud. Clear Task has been in business for six years building applications that fit on top of the Force.com platform to extend the Salesforce.com rich functionality and capabilities.

Now Clear Task has built a Trade Promotion Management application that fits on top of the Force.com platform. Clear Task set out to develop a full function TPM tool with the easiest to use planning interface in the business. They knew that consumer package goods account planners don’t have time to learn how to navigate complex menu and user interface screens to plan a simple promotion, and they set out to develop a one screen planning screen and it looks like they got it right.

The Clear Task TPM tool’s functional capabilities include:

  • A robust sales target and promotional funds allocation tool to allow for the input of one sales target and one promotional spending target and then allocate those sales targets and funds down to the account level. All sales targets and funds are allocated to account business plans at the business unit or product group and for a planning period.The allocation module provides a way for marketing or headquarter planning to distribute sales targets and promotional funds quickly and effectively in advance of the planning effort. Those funds are then tracked within the Clear Task TPM tool funds management module to insure clear visibility to allocated, planned, spent and remaining fund balances.
  • The Clear Task TPM planning tool is built on the basis of containers that hold information starting with an account business plan down to each individual promotional tactic:
    • Account Business Plan – Built for an account, planning period and business unit holds the weekly level metrics such as base units, planned units, actual units, planned spending and actual spending. All promotions and tactics for a planning period are contained in the Account Business Plan.
    • Promotions – Information containers that set the beginning and ending ship dates for a given promotion and contain all of the tactics for those dates.
    • Tactics – These are the basic building blocks for the planning effort as tactics contain data such as the type of promotional execution (Feature, Display, TPR etc.), the level of price discount and promotional lifts calculated from these elements.
  • Funds Management – Each tactic is associated with one or more authorized funds for that account planning period and the Clear Task TPM tool allows for an easy assignment of funds to each tactic. The settlement process depends of these funds assignments in order clear and open deduction or submit for a check request.
  • Clear Task TPM makes use of the Salesforce.com workflow engine to manage the trade plan & deduction approval process and the assignment of tasks within the tool based on business rules that control each workflow process.
  • Deduction Management – The Clear Task TPM deduction management approach is for a simple drag and drop approach to clearing an open deduction by dragging one or more open tactics on top of an open deduction which appends certain pieces of information to the deduction record and send that record back to the accounts receivable group to clear the invoice dispute.
  • Payment Request – A payment request is initiated by opening a check request form the then user drags and drops any open tactic on to the check request. Certain pieces of information are appended to the check request record and sent to the accounts payable department to send a check to the recipient.
  • Reporting – An advantage of building a TPM application on top of Salesforce.com is the ability to leverage the rich reporting functionality native to Salesforce.com. Clear Task has leveraged these capabilities to produce a suite of promotional analyses reports as well as dashboard reports to keep the user up to date on the impact and affects of their accounts’ business plans.

Perhaps the most interesting and exciting thing about the Clear Task TPM tool is the price. You don’t have to buy Salesforce.com to purchase the Clear Task TPM app because it has a runtime version of Salesforce.com called Force.com embedded in the TPM application. You don’t have to buy hosting space or bandwidth and you don’t have to buy hardware and increased network infrastructure to use the Clear Task TPM app. The costs associated with the implementation of the Clear Task TPM tool are pretty straight forward:

  • The TPM app costs $99 per user per month
  • There is a $15,000 to $30,000 consulting fee for the upfront TPM process consulting, setup and training effort to get the Clear Task TPM app up and running within your organization…By The Way the implementation timing is within 4-6 months depending on the scope and complexity of the implementation …BUT THAT’S IT!

If you organization has 30 account planners that on-going cost is $35,640/year versus costs of $200,000 + and 20% annual maintenance fees.  See a quick 5 minute YouTube video demo of “TPM in the Cloud”…YouTube Preview Image

If your organization is doing trade promotion planning with Excel spreadsheets, on cocktail napkins or with nothing at all; you need to see the Clear Task TPM application.


Conditional Average

Have you ever found yourself needing to average 1,000 lines of data on a spreadsheet with a ton of zeros across a number of ranges such as price gap ranges? Maybe you are trying to figure out volume responses based on certain price thresholds or maybe the average lift at various promotion prices.

You can do this with some new functions in Excel 2007 or by simply creating a custom function in earlier versions of Excel (I will provide the syntax later in this article). Suppose you start out with 1,000 lines of data in a spreadsheet that looks like the example in Exhibit 1:

The left column is the price gap between your brand and the Private Label and the right column is the volume you achieved at that gap for that week. We want to know on average what the volume expectations might be at various gap levels i.e. 10¢, 20¢, 30¢ etc.

You can't do a simple average but there is a way to accomplish this calculation using Excel 2007's Sumifs and Countifs. If we want to know the average sales for all instances when the price gap is between 10¢ and 20¢ the formula will look like this:

=Sumifs(Volume, Pr Gap, ">=.10",Pr Gap,"<.20")/Countifs(Pr Gap, ">=.10",Pr Gap,"<.20")

Exhibit 1
This formula could be copied and you would simply adjust the bottom and top ranges of the price gap for each gap threshold i.e. 20¢ to 30¢, 30¢ to 40¢ etc.

If you are still in Excel 2003 or earlier versions you might want to try the following custom function syntax (CountBetween and SumBetween) entered in the Excel Visual Basic editor; simply cut and paste the following sentences into your Visual Basic editor:

Function COUNTBETWEEN(rng, num1, num2)
COUNTBETWEEN = Application.CountIf(rng, "<=" & num2) – Application.CountIf(rng, "<" & num1)
End Function

Function SUMBETWEEN(rng, num1, num2, rng2)
SUMBETWEEN = Application.SumIf(rng, "<=" & num2, rng2) – Application.SumIf(rng, "<" & num1, rng2)
End Function

Then you can enter the following formula in any cell in the Excel worksheet:


The resulting matrix will provide you with an understanding of potential volume responses to a range of price gaps:

Insight, Information & Consulting Services, Inc. is a consulting company dedicated to providing data visualization tools to clients in the Consumer Goods industry. Please visit our website at www.insightinformation.net or call Rick Pensa at 770-425-4243.

Location Intelligence – The Power Of Where

I have been talking about the power of seeing data on a mapping tool, and this month I would like to highlight a powerful data visualization tool which can turn your thousand line spreadsheets into powerful data visualization tools.

The aWhere CPG tool has been developed with the Consumer Goods Company in mind, as the next level of reporting for Category Management projects. The Category Management capabilities built into the aWhere CPG mapping tool include the following:

  • See store level sales information such as high performing or low performing stores instantly with multiple colors and store icons.
  • See store data such as out-of-stocks (OOS), segment or SKU sales depicted with map graphs which are a pie chart/bubble chart combination and/or bar charts.
  • Build custom consumer profiles from retailer store level POS data and the US census demographic data included in aWhere CPG.
  • Cluster stores on the basis of demographics, indexes, sales thresholds or any combination of these to identify stores that are over or under performing their sales opportunity.
  • Evaluate store coverage or in-store conditions reporting impact on POS sales by seeing where demo activity, store coverage, plan-o-gram type or in-store merchandising is driving sales (or not).
  • Map together syndicated data such as IRI or Nielsen with retailer store level POS data to see a true picture of product sales and shares by market.

Seeing data from a 1,000 line spreadsheet mapped suddenly allows even the least analytical user to quickly see trends and gaps within the data. We called it making decisions at the speed of sight!

Recently, I was asked to work on a hardware project where I developed store level consumer profiles in order to build demographically based merchandise assortments. My client was having problems with out of stocks. It was clear that brass hardware sold better in certain markets/stores and brushed nickel sold better in other markets/stores. The trick was to predict where the best assortment of price tier, finish and styles would sell by store. This would be especially important for new store openings so that the new store had the best mix of price tier, finishes and styles in the highly stylistic bath hardware category.

Here is how we did it:

  1. We knew the store level POS sales for each SKU stocked, so we segmented the SKU's by price tier, finish, style and other characteristics.
  2. Each of these product characteristics were built into a matrix across all stores, and the sales for each characteristic were indexed to the chain average sales. The result was some stores indexed very high for each characteristic grouping and some stores indexed low.
  3. Each store was scored with a demographic profile using the aWhere CPG multi-layer mapping and layers intersect capabilities. Some stores were blue collar/low style stores and some stores scored as high income/high style stores and other stores scored in between across 4 key demographic profile consumer segments.
  4. The demographic profiles for each characteristic grouping were weighted for the high sales index stores and a profile for each characteristic was built.
  5. We then went to the map and clustered all stores that matched the profile for each product characteristic, and evaluated their sales index for the product characteristics.
  6. The final step was to build store cluster assortment recommendations for each of the store cluster groups.

The results were amazing and they highlighted the consumer driven nature of the bath hardware category. Our project resulted in a 20% reduction of out of stocks and 10% increase in sales by simply having the correct assortment on the shelf.

To learn more about CPG applications of Location Intelligence, please register for our free webinar that will show you how to leverage the power of mapping in your Category Management analyses. If you are in a data poor category, you need to see this.

Monday, November 16th 11am EST


Wednesday, November 18th 11am EST


For more information please contact Rick Pensa at bpensa@insightinformation.net or call him at 770-425-4243.

Is the Price Right?

In the current economy, everyone is talking about retail price. Many sales people subscribe to the philosophy that if low price sells a lot of goods, then a lower price will sell more. However, that is not always the case. We need some caution and a little scientific evidence before driving retail prices to all time lows. Product pricing cannot be developed in a vacuum and there are many variables to consider. Today, we will discuss price gap strategy. Look for upcoming articles on EDLP strategy and on price elasticity.

Price Gap Strategy – What is your product’s price gap relative to Private Label and/or other sub-segments (such as the value versus premium segment) of your category? A viable pricing strategy must consider price gapping between segments as well as between Private Label and competing brands. If the price gap dynamics are not understood, both the retailer and the manufacturer risk lost sales revenue.

For example in the dairy category, there is very little perceived quality difference between branded and Private Label milk by the average consumer. In some cases, the store brand quality and taste might even be as trusted as the leading brand. In lieu of a perceived product differentiation by consumers, branded products are forced to maintain a narrower price gap relative to Private Label products.

In the Orange Juice category, the product dynamics are different than the milk category.

  • Private Label sells nearly 51% of the Reconstituted Orange Juice (Recon-OJ) segment’s gallon sales.
  • Private Label sells 8% of Not From Concentrate Orange Juice (NFC-OJ) segment’s sales.
  • Both products are located next to each other in the refrigerated case.
  • NFC-OJ is perceived as a premium product appealing to high income, older, more educated urban and suburban consumers.
  • Recon-OJ (and frozen) Orange Juice appeals to blue collar, lower income, and rural consumers.


Consider the level of interaction between these Orange Juice segments and how their interaction impacts the price to value relationship perception for the consumer.


Let’s look at a consumer decision hierarchy. When a consumer walks into a store; what triggers the purchase decision?

  1. The first decision trigger is quality: Do I want to buy a quality product? Yes, of course!
  2. The second decision trigger is brand: Is my brand available?
  3. The third decision trigger is price: Is my brand on sale? What is my brand’s price relative to other products in the same category and to Private Label? Are there more economical alternatives available to meet my needs, outside the direct category?



If a non-price sensitive Orange Juice consumer is shopping for Orange Juice, the decision sequence might be;

  1. Is this a quality product? NFC-OJ tastes better and the national brand consistently delivers a higher quality product.
  2. Is my brand available? Yes
  3. How much does it cost? The price is more but it’s worth it.


NFC-OJ consumers are less likely to switch between branded NFC-OJ and Private Label NFC-OJ unless the price gap becomes drastic. How drastic that gap is can be determined through consumer and statistical analyses. NFC-OJ manufacturers can afford a larger gap between their brand and Private Label alternatives. Their consumers are not as price sensitive.

If a price sensitive Orange Juice consumer, who is not convinced of the perceived benefits of NFC-OJ, is shopping for Orange Juice, the decision sequence might be different.

  1. Is this a quality product? There’s no difference in quality.
  2. What brands are available? Any brand will do.
  3. How much does it cost? The NFC-OJ national brand is $2.99 for a half gallon of and the store brand is $1.99 for a half gallon. The Recon-OJ is for $3.99 for a gallon. The store’s brand saves me even more at $3.79. There’s no difference, so I will pick the least expensive product.


The economy segment is shopped by consumers who are more price sensitive, so the price gap to Private Label must be more narrow or they will switch. The concept of price gap is an important consideration for a complete price strategy. The impact of segment interaction needs to be quantified in order to predict volume response. You must understand how to structure the gap between your product and related products and segments by understanding consumer interaction. How interchangeable are competing products in the consumers’ mind? With a lower consumer interaction between products a higher price gap can be tolerated with minimal volume loss. If you know the volume response for each 1% of price gap between competing products, you can predict how much volume will flow from higher priced products to lower priced products.

Contact Rick Pensa at 770-425-4243 to schedule a free 1 hour consultation on ways to get a better handle on your pricing. Visit our website at www.insightinformation.net to browse our services and deliverables.


Pivot Tables to the Rescue: Formula Finesse

One of the biggest frustrations with pivot tables is the notion that your formulas need to be built outside the main body of the pivot table…the good news is you can put formulas in your pivot tables. Excel 2007 supports a wide range of Excel functions within the pivot table formula wizard. In this article we will show you how to build formulas in your pivot tables and look into some interesting formulas you can use in pursuit of Category Management analyses.

Just to be clear, Excel does not support array formulas which are formulas that affect a range of cells i.e. sum(A3:A34) or countA(B4:B25). However, most any function that deals with a number in each cell can be employed in your pivot table formulas including logical formulas such as If(UNIT>100, UNIT/100,0). Here is how it works:

  1. Create a pivot table by placing your cursor in the cell “A1” position of your data and select insert Pivot Table option. Then and add (drag) at least one field in the Row or Column section and add (drag) a field in the Values section of the pivot table wizard.
  2. With your cursor anywhere in the pivot table (you are in the Options menu), find the Tools section select Formulas to open the Formulas Wizard (Excel 2007); this Wizard can be accessed in the pivot table tool bar in Excel 2003.

  3. Then select the Calculated Field option from the drop down menu under Formulas.

  4. The Formula Wizard consists of the following fields:
    1. Name – Use this field to type in the name of the field. Caution – Do not use the a name that you will eventually use to display in the pivot table as Excel puts the accumulation method as a part of the name i.e. “Pct Chg” will look like “Avg of Pct Chg”. Recommendation – Use a more cryptic name (like $ Pct Chg) in the Formula Wizard so you can reserve the naming description for use in the pivot table (more on this later).
    2. Formula – Enter your formula in this box. For purpose of this example we will build a simple Dollar Percent Change Versus Year Ago calculation. You type the operands, and select the fields. Specifically you type =) then you select Dollar Sales from the Fields selection tool. You type – then you select YAG Dollar. Now you close the parenthesis with )/ then you select YAG Dollar (again). Now your formula will look like the one below.


    3. Once the formula is finished click the add button to add the newly developed field to the pivot table field list. Now the field can be added (dragged) to your pivot table Values section.

  5. The newly created field can now be adjusted by creating a more readable name and set the number type etc. from the Value Field Settings option. This can be accessed by right clicking on the Sum of $ Pct Chg tile.

  6. In the Value Field Settings Wizard –

    1. The name can be changed to any unique field name (there can’t be two fields with the same name)
    2. The field summarization method can be set by selecting Sum, Count, and Average etc.
    3. The number format can set by clicking on the Number Format button and selecting how you want numbers to be displayed
  7. Click OK when you have completed your formula and add it to your pivot table.

While we have only explored a simple formula such as Percent Change, but more complex formulas such as the following example can be used as well –
= IF(‘Unit Sales’ >100,’Unit Sales’ /100) to identify stores with unit sales over a certain threshold. The pivot table could be sorted on this calculated field to cluster high or low performing stores.

Insight, Information & Consulting Services, Inc. can help your company develop a data visualization strategy leveraging pivot table technology to display information not just report data. Call Rick Pensa at 770-425-4243 to set up a complementary needs assessment, or visit our website at www.insightinformation.net .

The Power of Where – Location Intelligence

Have you ever run a Google map from your location to the nearest Starbucks, and listed the driving directions? Well you just ran some location intelligence. Location Intelligence is the visualization of relationships between facts and data points about a specific location or set of locations in order to identify opportunities take action and measure the results of those actions. When you see 1,000 store locations and the specific data such as Out of Stocks (OOS), existence of a merchandising rack, or Point of Sale (POS) sales data in a spreadsheet it is difficult for the normal person to perceive gaps, regional trends, and opportunities/threats. That is where Location Intelligence comes in; when you can see those same 1,000 store locations on a map, the regional trends, relationships between OOS and POS sales, and opportunities/threats become obvious.

The power of location intelligence has come to category management by leveraging location information your company is already collecting to learn the why behind the what. When you can see threats and opportunities before your competitor sees them, you can act before they react. You can quickly see which stores are underperforming or over performing and see the underlying influences in a manner that would be nearly impossible in a spreadsheet. 

Figure #1 Sales Data in Spreadsheets

AWhere data in spreadsheets

Figure #2 Performance by Store Location

AWhere Performance by Store Location  

The human eye can see visual patterns 65,000 times faster on a picture, like a map, than in a tabular form such as a spreadsheet. The data in Figure #1 is difficult to interpret. The map displayed in Figure #2, visually represents the data on the spread sheets, so that the relationships become apparent. Here, the red stores are underperformers, the yellow stores are average performers, and the green stores are high performers. The high performing stores can be easily and quickly viewed in relationship to their location and the associated demographics. While location intelligence provides excellent data visualization capabilities; there is so much more that can be applied to category management.

Location intelligence allows users to see all the data points mapped to a particular address, like a store location, trading area, or region, in graphic representations such as pie charts, bubble charts, and bar charts; all displayed on a map. By using mapping representations, the user can perform all types of category management analyses, such as distribution analysis, out of stock analysis, regional differences in POS sales, and cluster analyses of stores that meet certain criteria within the data. A multi-layer mapping tool, allows the user to link together map layers to produce a consumer segmentation analysis that would be difficult in an Excel spreadsheet.

An example would be a store trading area layer linked to a block group layer containing demographics to develop store trading area demographic profiles. In Figure #3, a store trading area was developed for each of the stores in the mapping tool (i.e. a 2 mile radius around the address). Then the underlying layer, containing consumer demographics was connected with the trading area layer to produce a demographic profile for each store. The process of assigning store demographic profiles can be quickly and easily accomplished for thousands of stores; thus allowing a user to identify and target stores that meet a certain demographic profile. For example, a user might want to identify all stores that have a high index of families with two or more children under the age of 18. Stores matching that profile will be highlighted on the map and easily recognized, identified, and targeted for promotional execution. In this example, we will look at the racial profiles in each trading area.

Figure #3 Demographics by Store Location

In Figure #2, we saw that the stores in south side of Chicago were high performing stores for our product. In Figure #3, we are looking at demographic profiles for each store. Compared to other parts of Chicago, these stores have a higher incidence of African American consumers in their trading area. This gives insight into who's buying our product without extensive consumer research. This information is not easily visualized from a spreadsheet. This is the power of WHERE!

Location intelligence is not a difficult discipline to bring into your organization, but certain elements need to be in place to be successful in you implementation. A successful location intelligence implementation will require:

  • Tools – Select the right tool(s) to facilitate the power of location intelligence within your organization.
  • Data – Identify, organize, source, and structure geographic data sets within your organization.
  • Data Visualization – Craft the best mapping tools and analytics to reveal the most about your data.
  • Human Resources – Identify and train the right people with analytic skills to advance and evangelize the organization for location intelligence.

Insight, Information & Consulting Services, Inc. can help your organization craft a location intelligence strategy, identify data sources, organize your data, and develop hard-hitting, information packed data visualization mapping tools. There are many GIS, location intelligence tools on the market, and they run the gamut from basic to extremely complex. A basic tool such as Map Point by Microsoft, is a good starter tool for visualizing location intelligence data, but it lacks multilayer mapping and important location intelligence analytics tools found in the AWhere CPG location intelligence software. There are many professional GIS tools that require individuals with highly skilled GIS training to operate. The GIS tool I am using for examples in this article, come from AWhere CPG, from the AWhere Company, which delivers multilayer mapping capabilities, sophisticated geo-demographic analytics tools, and it has been designed and optimized to facilitate category management within the CPG industry. For more information on Location Intelligence or to sign up for our webinar on Location Intelligence, coming up on October 1st, 2009, please email us at www.insightinformation.net.

“Who Moved My Cheese?” Change Management & the Challenges of Implementing a New Trade Promotion System


"Today more than ever business environments are rapidly changing, requiring your company to continuously adapt. If environmental complexities including customer demands, regulatory requirements, and competition are not adequately addressed, businesses run a high risk of failure." Clarkston Consulting, www.clarkstonconsulting.com.

Change is natural and can be very positive. Staying competitive in these challenging economic times requires change. Managing change means managing people's fears which can be unpredictable. We all seem to be trying to figure out how to manage change and there are many bestselling books are out there on the topic to help. Implementing a new Trade Promotion Management (TPM) process and tracking tools can usher in some of the most difficult change management challenges imaginable. However, if properly approached change management can be successfully implemented, but there is a right way and a wrong way to approach such a daunting challenge as a new TPM process or a new planning tool.

"Who Moved My Cheese?," written by Spencer Johnson, M.D., is a #1 best selling story of four characters living in a maze. Each one faces unexpected change when they discover their cheese has disappeared. Each one adapts to change in the maze differently. One doesn't adapt at all. Metaphorically, the maze represents our organizations and communities and the cheese represents our careers, relationships, wealth, etc. This timeless allegory reveals profound truths to individuals and organizations dealing with change. Moral: Don't get upset by change. Adapt for long term success.

According to the Graziadio Business Report from Pepperdine University, there are some basic principles to remember when implementing change:

  1. Do no harm. Implementing change poorly is sometimes worse than not implementing change at all.
  2. All change involves personal choice. Any organizational change is preceded by personal change.
  3. The relationship between change and performance is not instantaneous. As far as humans are concerned, there is so such thing as an instantaneous transformation.
  4. Connect change to business strategy. Change should only be pursued in the context of a clear goal.
  5. Involvement breeds commitment. Managers who do not involve their workers in decisions run the risk of stalled change efforts.
  6. Any good change effort results in increased capacity to face change in the future. It's one thing to "install" change and another thing to "implement" change.


Managers and leaders sometimes forget these principals in their haste to adapt the "latest and greatest" ideas. Although managing change is difficult, remembering the basics can help ease the transition. One change some progressive companies are making is to implement a Trade Promotion Management (TPM) system to improve the efficiency of their forecasting and trade promotion spending. When change is connected with a clear business strategy, it is easier to obtain buy in throughout the organization. The process, however, is not without its unique set of challenges.

Challenge #1: Getting buy in throughout the organization.

How do you select and implement rather than "install" a system? The very first step is to collect the stake holders (sales, marketing, finance, logistics, etc.). This group should consist of cross-functional managers who are peers and will actually be using the new process or system. Remember, involvement breeds commitment. These cross-functional representatives, as well as all those who will be involved in the process, need their success tied to the success of the implementation. Establishing quantifiable group goals and measuring the successful accomplishment will help keep the team focus on obtaining results. The next step is to gather and carefully consider the input from the groups and address concerns as they relate to process. This takes more time and coordination, but will pay dividends with the support it garners. The entire process needs to be tied to some level of satisfaction within the organization and the question; "What's in it for me?," needs to be answered. The job can be done faster, easier, more accurately; the individual may be able to make more money, etc., because of this new process and software. Make them see the value as it affects them personally. The new system should be "sold" throughout the organization by showing people what they have to gain. Personal change precedes organizational change.


Challenge #2: Getting the right process.

In the case of a TPM process, a series of sales planning and trade spending decisions must be made and communicated throughout the organization. The question is how to best streamline the flow of information from the trade spending planning process, to the approval process, to the execution process, to the evaluation process.

The team must evaluate and perhaps re-write the TPM process. Perhaps there was no formal process and one must be invented. Perhaps the process evolved around systems that no longer exist and is no longer effective. Careful consideration should be given to avoid the "not invented here" syndrome and once again, input from all functional areas must be collected and considered. If the process is broken, a software tool wrapped around a wrong process will actually inhibit good change management. If the process is intuitively smooth, clean, and well thought out, then a software tool wrapped around that new process will actually enhance change management.

The implementation team should establish a timeline to implement the process with success milestones along the way. These success milestones should be communicated to everyone in the organization to keep each person focused on the achievement. This also serves to re-focus if the group gets off track. In a large organization, it's better to select a small test group and make sure the process flows, before extending the new process and system to the entire company. Incremental change is important when adopting a new process. Do one thing well first. Then move on to other, more complicated areas.



Challenge #3: Getting the right tools.

Finding a tool that is easy to implement, gets the job done, and is affordable is a huge accomplishment. Often CPG companies will spend more than they need to just to get to a viable trade planning tool. The bell curve in how people learn technology will impact project implementation. Some learn fast, some never learn, and some learn what they need to accomplish their jobs. The user interface must be simple, easy to learn and intuitive to the user. If not, it's an uphill battle.
Fox diagram.jpg

There are many companies selling software solutions for demand forecasting, supply chain management, retail and warehouse inventory control, and logistics planning, etc. One such integrated Sales & Operations Planning tool built for the consumer products industry comes complete with the added benefit of an integrated trade spending management component, is the Fox Collaborative Planning System.

This tool allows an account manager to build a forecast based on his individual accounts and product group promotions, using historical volume & base volume, promotional tactical lifts, and spending budgets. The system communicates volume, spending, and approval status throughout the organization. Forecasts are literally built from the ground up, one product group at a time, account by account based on factual lift calculations resulting in a one number forecast tool. Spending authority and budgets come down from management, get allocated, approved, presented to customers, approved again, and are officially "planned" and in the system.

Gaps between business objectives and forecasts are easily visualized and understood. Red flags are flown when inventory issues arise and spending goes above budget. One of the major strengths of this system is the ability to plan and evaluate and to use this information in future planning cycles.

Challenge #4: Getting used to new levels of accountability.

When you go from a forecasting and planning approach that uses a bit of mystery, smoke, and mirrors to a logical, fact based system, you will likely ruffle some feathers. The sense of threat, fear, and loss of control increases when you're under the microscope and all your numbers can be questioned, analyzed, and monitored. Some sales managers get defensive and are uncomfortable losing their proprietary knowledge set (their "job security"). They like keeping total control over their key accounts. This can be overcome with time, involvement, training, reassurance and consistent objectives, that is, their personal success tied to the success of the implementation.

Senior management must stay the course as the new trade planning process and tools are implemented. Volume at any cost, quarterly loads, and mortgaging volume must be replaced with fact based planning, event based forecast numbers and a "one number" system that is accepted across the organization.

Although there are challenges to implementing any type of change, you can be successful implementing a new trade promotion management system. Keep in mind the pitfalls, set goals, and establish accountability, take baby steps, and go.

Insight, information & Consulting Services, Inc. has the experience and the knowledge to help your company navigate the Trade Promotion Management water. We can help your company develop the right processes, select the best and most efficient tool; and put in place the right components to help minimize the impact of change management on your TPM project. Please call us to discuss your needs and ask for Rick Pensa at 770-425-4243 or on the web at www.insightinformation.net .

Insights From Malaysia

I was fortunate to be invited to conduct a two day seminar in Kuala Lumpur, Malaysia on Trade Marketing In An Economic Downturn. The group of delegates consisted of 32 consumer goods professionals from a variety of CPG companies. We discussed the relationship of the consumer to the Trade Promotion Management (TPM) process. In many Asian countries and even here in the US, the consumer is not considered in the trade promotion planning process. We explored ways to bring the consumer into the equation when planning events, as a means to gain strategic advantage over competition. Many Asian countries do not have the rich panel data that we in the US can boast, but there are still ways to engage the consumer using custom surveys, Consumer Relations Management (CRM) interaction on the brand's own website and other creative means designed to understand the who, what, and where of the consumer's experience with a brand.

Penetration x Purchase Frequency x Buying Rate = Sales Volume

Let's look at a simple equation for sales volume. Consumer Penetration (new consumers through trial) x Purchase Frequency (how often purchased) x Buying Rate (amount purchase/occasion) = Sales Volume. Impact any one of these measures and volume grows or contracts. Trade promotions that cannot be tied to one of these measures are wasted effort. If a category or brand has a very low consumer penetration index does a Temporary Price Reduction (TPR) or an Every Day Low Price (EDLP) strategy really make sense? Could those promotional funds be better leveraged by funding promotional tactics that bring new users into the brand franchise or category segment? When promotional tactics are designed to drive one of these purchase dynamic measures a clear marketing strategy emerges. Let's look at our three sales drivers and how to impact them with trade marketing promotions.


  • Feature Support
  • Displays
  • In-Ad Coupons
  • FSI Coupons
  • In-Store Product Demonstrations
  • Cross Ruff Couponing

Purchase Frequency

  • Feature Support
  • Displays
  • Secondary Locations
  • CRM interaction with the consumer delivering targeted marketing communication messages, recipes/usage advice, etc.

Buying Rate

  • Multiples Pricing
  • Temporary Price Reduction (TPR)
  • Feature larger sizes
  • CRM interaction with the consumer, delivering targeted marketing communication messages, recipes/usage advice, etc.

When trade marketers fit their promotional arsenal into the purchase dynamics equation, holes in their promotional strategy become clear and the right tactics can be developed to drive the right purchase dynamic metric. Low penetrated brands can develop promotional strategies that attract new users. Strategies aimed at increasing purchase occasions will drive purchase frequency, and specific measures aimed at giving the consumer incentives to buy more products for pantry load or other usage opportunities can drive buying rate.

In Malaysia, we dug into the development and use of a lift matrix to improve promotional planning. A lift matrix helps determine the optimal combination of tactic and price discount. We developed simple baseline calculations using data from retailer Point of Sale (POS), shipment, and syndicated data, when a pre-calculated baseline was not available. Here is an example of how to develop a baseline and lift matrix:

  1. Use at least 52 weeks of POS, shipment, or syndicated data at the UPC level, and take away the weeks with known promotional tactics.
  2. Use the remaining weeks of volume develop a forecast of what sales would have been in the absence of those promotional tactics. You can see our baseline volume is around 98 in this example.

  3. Once a baseline has been established, you can determine the lift over baseline for each promotional tactic, and express that lift as an index. The formula for a Lift Index is ((Total Sales-Base Sales)/Base Sales)*100. In week 11 (see below), Total Sales = 200, Base Volume = 98, so ((200-98)/98) *100 = 104 lift above base for that display.

  4. Lifts can be determined for each combination of tactic (Feature, Display, Feature with Display, and Temporary Price Reduction (TPR) and price discount levels, and the resulting lifts can then be used for future promotional planning exercises.

The benefits of a lift matrix are many, and here are just a few:

  • You can determine which tactics drive the most volume lifts. Does one week of feature support drive more sales volume than six weeks of TPR support?
  • You can determine which price discount levels drive the optimal volume lifts. For example, does a TPR at 15% discount drive the same volume as a 20% price discount? If the 15% price discount drives nearly the same volume as a 20% price discount then 5 point of discount savings can be saved and used to leverage another promotional event.
  • You can calculate the promotional cost per incremental unit using a baseline and lift matrix.
  • A credible lift matrix employed in a Trade Promotion Management tool will always deliver a fact based forecast of promotional lift and ultimately a more reliable sales forecast.

In Malaysia, it was interesting to learn that the companies represented at the conference did not use TPR as readily as we do in the States. If used at all, the deal levels were much lower. While we deal in levels of B1G1F, their promotional discounts were in the 15% range, and there was much less reliance on EDLP pricing action. They were more likely to spend for displays and in-store activities.

The above article is meant to give the reader a high level understanding of how a baseline calculation is developed and to help the reader understand the value of a lift matrix to forecast volume lift for any combination of tactic and price discount level. A much more sophisticated discussion of the development of predictive baselines such as cross-sectional baseline to account for seasonality, will be handled in a future Insights newsletter. If your company is seeking to reengineer your trade promotion process and you want to put the consumer at the center of your trade promotion strategy, call us at Insight, Information & Consulting Services, Inc. Perhaps you are a small to mid-size FMCG company and you don't want to fork out a million dollars to install a trade promotion planning and tracking tool. Call us at Insight, Information & Consulting Services, Inc. We can be reached at 770-425-4243 or visit our website at www.insightinformation.net . We look forward to speaking with you about your trade promotional needs.