All posts by Rick Pensa

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. 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 in the cloud. Clear Task has been in business for six years building applications that fit on top of the platform to extend the rich functionality and capabilities.

Now Clear Task has built a Trade Promotion Management application that fits on top of the 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 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 is the ability to leverage the rich reporting functionality native to 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 to purchase the Clear Task TPM app because it has a runtime version of called 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 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 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 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 .

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

“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,

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 .

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 . We look forward to speaking with you about your trade promotional needs.

Pivot Tables To The Rescue-Part II: Custom Market Shares

Did you know you can produce custom market shares in an Excel pivot table which reflect how you identify the category segment? Last month, we learned what a terrific tool Excel can be for data analysis, by building a pivot table to display a pie chart for brand share and a bar chart to depict percent change vs. year ago. This month, I want to expand upon the data manipulation capabilities of a pivot table, by showing how we can produce a custom market share. It is often difficult to create dynamic brand shares based on segmenting a category. For example if we want to show Private Label's share of Low Fat Milk for Large Sizes (half gallons & gallon sizes) and then quickly see Private Label's share of single serve milk. With a pivot table we can set the size and fat level characteristics to create the dynamic segment definition but how can we create a dynamic brand share calculation to work just as quickly?

The following examples shows you how to build a dynamic share calculation in a pivot table:

Step1: Start your pivot table with a list style "flat file" as the following example illustrates.

Step 2: In the upper left hand corner (cell A1) of the data set, click on the Insert tab in Excel 2007 (pictured below) and in Excel 2003 (Data/Pivot Tables) to produce the pivot table (not shown). Excel will automatically select the data range for you. Select "New Worksheet" and "OK" from the radio buttons to get to the section where you arrange the data in the pivot table.

Now, build the pivot table using the market and some characteristics from your data. This segmentation is determined by how your company views the category. For example, the dairy industry might consider the milk category to be segmented by fat content (Whole Milk vs. 2% Milk vs. Skim Milk), size (Gallons, Half Gallons, Quarts, Single Sale), and then by brand. In this exercise, our objective is to report the brand share for each segment as we drill down through the category definition.

Step 3: Select "Market" and "Fat Content," and "Size Category" and "Period" by dragging them into the Report Filter box on the right side. Drag "Brand" into the Row Labels box and "Dollars" into the Values box as illustrated below.

Step 4: In Column B1, select Charlotte from the drop down menu. In B2, select Fat Free from the drop down menu. In B3, select Large from the drop down menu, and in cell B4, select Period-12 weeks ending June 2009 from the drop down menu.

We want to know the brand dollar share of the Fat Free (Skim) Milk segment of the Large Size (Half Gallons & Gallons), in the Charlotte market. Excel allows us to use the Dollar Sales measure again in the Values box and make it a Dollar Share measure rather than a Dollar Sales measure.

Step 5: Drag a second "Dollars" into the Values box (lower right).

Click on the second "Dollars" drop down menu and select "Value Field Settings." Select the "Show Value As" tab and change the "Normal" setting to "% of Column," as in the following screenshots. Excel automatically converts the dollars to percent of total. (By clicking on the second "Dollars" listed, you will get dollars on the left and percentage share on the right. You can also use the "Value Field Settings" screen to change the number format to reflect currency (not shown) by clicking on the "Number Format" button. You can also change the column headings in the Pivot Table on the "Value Field Settings" screen (not shown) by typing in the Custom Name box.)

Excel displays the share value instantly for the brands that make up whatever segment we have defined.

In our example, you can easily see that Private Label dominates Large Sized, Fat Free Milk with an 83% share of Fat Free/Large Milk. Using the drop down menus in Column B to select Single Serve and All Fat Contents, you can view your data in a new way.

Now you see that Private Label is dominant, but not as dominant in the Single Serve/All Fat Contents segment with a 39% share of category. You can see what a powerful tool Excel Pivot Tables can be to analyze your company's information. You can look at your data from many perspectives with the click of a button.

Insight, Information & Consulting Services, Inc. can help your company develop a powerful data visualization strategy using simple readily available tools such as Excel or we can help your company deliver data in a powerful thin client tool designed to build and update thousands of charts with the click of a button. Visit our website at or call us at 770-425-4243.

Pivot Tables To The Rescue

Did you know that you can develop a Microsoft Excel template that you can distribute to your extended users such as the field sales force? You can leverage Pivot Tables within Microsoft Excel to develop impactful tools to drive actionable insights to the ultimate end users in your sales and marketing organizations.

Let me show you how to build an Excel template to distribute syndicated data to your sales team who don't have access to the data. Since most people have Excel on their computers this provides an easy way to get specific issue driven templates and information into the hands of the end users of the data.

The first step is to organize your data in a flat file. A flat file is defined as an unformatted square "chunk" of data as the following example will show:

We can now go to cell A1, from the insert menu select the pivot table option and chose the entire set of data to build a Pivot Table (I am using Excel 2007 to build our example). We can choose to create a new worksheet or we can decide to place the pivot table on the current worksheet. If you select ok, it will automatically define the range it interprets and default to a new sheet within the same Excel file:

We can drag fields from the pivot table field list to the various elements of the pivot table report to build our report layout:

  1. Drag Markets (MKT) and Periods (PER) in the Report Filter section of the report so we can select one or more markets to view and a time frame (4 Weeks, 12 Weeks or 52 Weeks).
  2. Drag the Products (PROD) field to the row labels box and since we want to see dollar shares and the impact of trended dollar sales.
  3. Drag Dollar Volume ($ VOL) and Dollar Volume Change versus year ago (DOLL VOL CHG VS YAG) to the values box.
  4. The Pivot Table will be automatically created.

Our report output needs to be presentation ready so we will want to have two separate graphs populated when we select a market. For example I want a pie chart to show the shares of the brands in a selected market and the bar chart to show the dollar trend changes for each brand versus the same period one year ago. To do this we can build data ranges outside of the pivot table to be charted in our template. First, let's set up the table for our pie chart:

  1. Move to a separate place on your worksheet, beneath or beside your Pivot Table.
  2. Use the "=" function to reference the data from the pivot table and fill the table you are creating.
    1. Place an "=" in the new "pie chart" table, in the product name cell (A15 in my example) and click in the cell of the first product (A6 in my example) in the pivot table.
    2. Repeat until all products are represented.

Next, we will reference the dollar sales fields in the same manner

  1. Place an "=" in the dollar sales value cell (B15) and then clicking on cell B6.
  2. Repeat until all of the product's dollar sales are represented.

Now, let's create the table to be used for the bar chart.

  1. Reference the percent change in dollar sales like we did in the pie chart, starting with the product name.
    1. Place an "=" in the new "bar chart" table, in the product name cell (A23 in my example) and click in the cell of the first product (A6 in my example) in the pivot table.
    2. Repeat until all products are represented.
  2. Now, reference the percent change in dollar sales.

We will then produce each of two graphs (a Pie Chart and a Bar Chart) on one page of our template in the following manner:

  • Highlight the area to be graphed
  • Select the insert menu and pick the pie chart option
  • Format a pie chart as you normally would
  • Create a second chart in the same manner selecting the bar chart option

Now, for with a little cleanup effort such as:

  • Allowing hidden data to display in the graph
    • Right mouse on the graph object and pick "Select Data" for the menu
    • Click on "Hidden and Empty Cells" (bottom left in the dialogue box)
    • Click "On" the "Show Data In Hidden Row & Columns"
  • Align graphs under the pivot table drop downs
  • Hide the rows that show the data set for the pie chart and bar chart
  • Turn off gridlines and headings on the Page Layout Ribbon (Excel 207)

Your template is now completed and you can see market shares and volume trends in one easy to use template that can be printed off and presented to your customers:

The above template is a very simple example of creating an Excel tool to better visualize your data as real and actionable information. Additional levels of sophistication can be added such as intelligent text bullet points, dynamic rank reports, navigation buttons to navigate the user between multiple charts and tabular data visualization charts. In future editions of the Insight newsletter we will demonstrate how to build these enhancements into the template.

Insight, Information & Consulting Services, Inc. is dedicated to delivering better data visualization tools to its clients. We develop data visualization tools for Trade Promotion Analysis, Category Management, Consumer Analysis and sales & marketing business analysis templates. Please contact us at 770-425-4243 or visit our website at

In Search Of Better Baselines

Why search for better baselines? Brands promote their products — to deliver incremental sales and bring consumers to the brand's franchise. There is no way to understand the impact of a promotion if there is no reliable understanding of the volume we would've received in the absence of that promotion. Therefore a baseline, which is really nothing more than a forecast of the sales we would've received in the absence of a promotion, provides an estimate of sales we would have expected based on historical sales when no promotions were in effect.

For many years, we have relied on syndicated data services, such as Nielsen and IRI, to help us understand our promotion effectiveness. However, syndicated data suppliers do not collect 100% of scanner sales data from all stores in the channel or do they collect data from all channels. Data suppliers cannot provide data for retailers, channels, and product segments that they do not collect. Now there is a way to develop baselines and lift benchmarks for accounts that are not reported in syndicated data services.

A baseline is a benchmark of your expected sales volume in the absence of any promotions. Baseline sales are sales generated by your product with no in store promotional efforts. Baseline calculations are required to develop a lift matrix of promotional response for each tactic by price discount levels. A lift matrix is a measurement system to determine how much additional or incremental sales volume resulted from specific promotional tactic and price discount combinations. Fusion Point, a data measurement and modeling company, has built a set of models that build "custom" baseline volume estimates from shipment and/or retailer point of sale (POS) data sources.

Another reason that "custom" baseline volume estimates are important, is because syndicated data suppliers' baselines tend to be inadequate for today's promotional evaluation needs. These general baseline models do not handle seasonality, long term on shelf price reductions, or non-measured promotional tactics (Loyalty Cards, In-store TV/Radio) very well. Fusion Point is able to process syndicated, shipment and/or retailer POS data with its new baseline models to include many external variables such as weather, seasonality, etc. to generate a set of reliable baseline and lift matrixes; even for accounts that are not reported by the syndicated data services.

A reliable baseline is crucial to a fact-based approach to Trade Promotion Management (TPM). Without a reliable baseline, there is no way to develop lift matrixes which help account planners to determine the most productive and profitable promotional executions. Most CPG companies rely too heavily on flawed syndicated data suppliers' baselines to power their TPM systems and to perform promotional analyses.

The following chart is an analysis of the anatomy of an in-store trade promotion tactic (such as feature, display, feature with a display, on shelf price reduction):

Anatomy of an In-Store Promotion:

Without a reliable baseline, we don't know how much of our volume was incremental, how much we subsidized, and how much lift each combination of tactics & discounts generated. Look at the 4th week (5/10/2009) in the chart above and you will see that the lift over the baseline (narrow orange bar) is very small versus the week of 5/31/2009 when volume over baseline is nearly doubled the base volume projection.

The amount of promoted volume (red bar) under the baseline is "subsidized" volume (narrow yellow bar). Subsidized volume is the volume you would expect to have without any promotion. The volume generated below the baseline level receives promotion funding; and is a necessary expenditure in any promotion. However, the trick is to generate enough incremental volume to offset the cost of subsidized volume.

"Incremental" volume (narrow orange bar) is the volume sold over the baseline. Incremental volume usually is generated by a promotional tactic such as a print feature, an in-store display, a print feature with an in-store display, or an on-shelf reduced price. We gauge the sales over baseline as a "lift" over baseline. The lift calculation is as follows and is usually expressed as an index:

(Total Units – Base Units)/Base Units.

Lifts can then be combined with the level of price discounts for each tactic to generate promotional lifts thereby creating a lift matrix. A lift matrix might look like the following example:

Example Lift Matrix

In the example above, a feature ad with a 30% price discount below normal pricing generates twice as much lift or incremental volume as a 30% on shelf price reduction only (TPR=temporary price reduction). This information might guide the account manager to try to sell more feature ads than on shelf price reduction events at a discount level of 30%.

This type of analysis cannot be performed without a baseline calculation, and most trade promotion management tools require a lift matrix to forecast promotional sales. Armed with the new Fusion Point baseline model, any CPG manufacturer that can supply retailer POS or internal shipment data can develop a base volume estimate that will fuel a lift matrix which can be an effective trade promotion planning tool.

Check out our blog and our website at

Trade Vs. Shopper Marketing…Which One Drives Brand Strength?

The buzz word of the day is Shopper Marketing and today's discussion is whether Shopper Marketing is replacing Trade Marketing.  Do you know how to optimize both disciplines to drive your brand's strength?   A.G. Lafely's (CEO of Proctor & Gamble) latest philosophy is to move away from traditional trade spending in favor of shopper marketing as the best approach to a higher trade spending ROI. P&G has taken the approach to reduce or do away with trade spending over the past 30 years.

Several years ago, P&G made a bold industry announcement that they would suspend or greatly reduce trade spending in favor of a lower FOB price only to have to reinstate trade spending against their lower delivered price – within the same year.

From my observation the human nature is to act on a purchase decision when certain basic factors (or some combination) are met. For example:

1. I am sick and I need flu medicine.
2. We are out of OJ and I need to get some (or will there be some interchangeable alternative?).
3. Hey there is a new and different item I just tripped over in that display – let me try some.
4. My favorite yogurt is on sale I think I will buy 10 instead of 1.

How does a Fast Moving Consumer Package Goods (FMCPG) Company communicate its solution to basic purchase demand signals? In most cases brands rely on trade spending in the retail store to attract the consumer, and far too often those funds only buy down the price at the shelf. But does a temporary price reduction at the shelf really attract new consumers to your brand? How does a brand capitalize on consumers' demand signals apart from a high media budget?

  • It could increase reliance on couponing.
  • It could leverage In-Store marketing tools such as In-Store Radio/TV
  • It could increase In-Store product demonstrations to capture shopper attention
  • It could look for ways to differentiate their product in the eyes of the consumer by building a one on one relationship with the consumer using emerging e-marketing tools (Facebook, Twitter, your own CRM site, etc.).

I am seeing a marked movement away from quality performance vehicles such as feature and displays to short term on shelf price reduction (TPR) or basic price reduction tactics. From my research, it seems TPR tactics generate lower lifts versus higher quality executions such as a feature, display or some sort of In-Store Radio/TV blast that encourages the trial or pantry load decision.

Some are saying that Trade Promotion is not the optimal way to reach a consumer and grow your brand's position in the market place and Shopper Marketing is the new mantra on everyone's lips.  However, execution is the key missing ingredient.  Often, large companies cannot execute Shopper Marketing well because they have separate Trade Marketing and Shopper Marketing departments that rarely if ever collaborate on a holistic consumer/trade promotional strategy.

The best way to market to the shopper is to start small and build trust through frequent (but short) consumer relationship building interactions. Also, brands need to answer the trade Promotion questions such as, "Do more infrequent high quality Features capture more consumer attention and drive more trial and pantry loading events than long term reduced price events?"

A well integrated Shopper and Trade Marketing strategy combined with the proper measurement tools will provide the performance insight that these integrated programs have on purchase dynamics metrics such as penetration, purchase frequency and buying rate to build sustainable brand equity.

Visit us on our website at

Trade Promotion In An Economic Downturn

In the FMCG/CPG industry, one of the last bastions of efficiency is the trade promotion budget. Over the years, trade marketers have been spending more and getting less return on that spending investment. In an economic downturn, every penny counts. You have got to improve your bang for the buck on your marketing spending. The easiest way to get more efficient is to improve your trade promotion effectiveness. You can drive better ROI on your trade promotions, but it will require a paradigm shift to a consumer centric and fact based process.

In tough economic times, companies that hone their consumer segmentation and targeting skills will come out of the economic slowdown with an increased consumer base. The trick is to break down who your best consumers are; find out what motivates them to purchase, and measure the key purchase dynamics (penetration, frequency, and buying rate).

Promotion metrics need to be carefully planned and monitored as well. Do you know the "lift" or incremental volume your brand generates by tactic/discount/timing combination? Do your on-shelf price reductions generate the same lift at a 15% discount as at a 25% discount? Do features once per quarter on a payday week generate more incremental sales than 6 weeks of on-shelf price reductions?

When evaluating promotional lifts, are you calculating profit generated by incremental volume against the subsidized costs to get to base volume levels? Subsidized volume is volume with promotional dollars spent just to get to base volume.

For example, if your base volume is 750 units per week on a promoted SKU and you are spending 50¢/unit on all units in the ad or on TPR for that SKU, then $375.00 is a cost of achieving base volume; sales you would have generated without a promotion. If your promotional efforts deliver total sales of 900 units for that SKU, 150 incremental units were sold. What is your cost per incremental unit?

Your promotional costs are $375 base + $75 incremental, for a total of $450, to deliver 150 incremental units or a cost per incremental volume of $3.00 per unit. If your objective is 75¢/incremental unit sold you would need to sell 1,500 incremental units to offset the $375.00 of subsidized volume. In reality, you also need to include the fixed cost of the ad to obtain a true cost of the promotion.

Are you calculating the impact of cannibalization effects on the non-promoted SKU's in your brand or promoted group to determine a true incremental volume for the brand or category? Retailers that can grow their categories on the backs of well thought out and profitable brand promotional strategies and begin to look to those manufacturers for more profitable promotional strategies.

As the state of the newspaper industry begins to crumble and the traditional print media cease publication, how is your company's account sponsored print feature strategy changing? That strategy will need to include more in-store media and web advertising on your accounts' websites, and an increased reliance on web delivered coupons targeted for usage at a specific retailer. In addition, CPG manufacturers will need to forge a better and on-going relationship with their consumers through a Customer Relationship Management (CRM) approach from your company's website.

Developing and maintaining a database of consumer names and addresses and a record of on-going communication with these consumers will insure your brands are foremost in their minds and on their shopping lists. As you start and pursue a dialogue with your brands' consumers you begin to know where they live and shop, and you can easily find out where other consumers that look exactly like them live. A targeted approach allows you to leverage your company's marketing funds directly at the consumer. By utilizing Location Intelligence tools, pockets of consumer opportunities can be identified and strategies/tactics developed to deliver your brands' marketing messages directly to those consumers.

I will be conducting web seminars on the subject of Trade Promotion Management and will address these and other relevant TPM issues. These workshops will address a real world and hands on approach to the Trade Promotion process in an increasingly tougher economic environment. We will show you how to set up a process that will:

  • Help you organize your internal resources
  • Cause your company to change your thinking about the trade spending process
  • Show a cost effective tool to facilitate trade promotion planning/tracking/evaluation
  • Drive more efficiency into the trade spending process
  • Improve your trade spending bottom line

This workshop will help your organization drive more efficiency into your trade spending budget and this will equate to a higher bottom line. Our agenda will be one of real world practices and hands on application for Trade Marketers and account management teams to learn how they can take advantage of economic downturns drive market share and consumer penetration.

We will be conducting the above content in a series of 2 hour Webinars starting later this year. If your company would like this seminar presented at your site for your team please contact Insight, Information & Consulting Services, Inc. – Rick Pensa at 770-425-4243 or Visit our website at .