All posts by admin

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 can help your team drive more profitability into your trade spending practices., LLC is a CPG sales and marketing solutions provider building tools on the platform. We can be reached at 678-503-5001 Ext. 301 or email Rick Pensa at See more information about the CPGToolBox Trade Planner by clicking on the following link .




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, 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, LLC and has more than 23 years of focus on the Trade Promotion Management process, TPM analytics and system implementation in the CPG industry., LLC is focused on providing sales and marketing tools built on the 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. is a trade promotion management company that has built a cloud-base full function Trade Promotion Management solution on the 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 or call at 678-503-5001 Ext 301 or check out our website at .

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 or call Rick Pensa at 770-425-4243.