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 www.insightinformation.net 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 www.insightinformation.net.

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 www.insightinformation.net

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 www.insightinformation.net

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 www.insightinformation.net .

Insight Is Seeing It First

What does it take to be a visionary? Does your company struggle with how to see the real nuggets of truth in your data?  Does your company know where the best stores are?  With the right tools you can see opportunities before they become threats.  If you see the threats or opportunities before your competition sees you win.

Seeing data better has now become a new paradigm for reporting called Data Visualization.  How much data does your company have and how many reports circulate around your department month after month usually ending up in the trash can with no business impact?  With the right tools and reporting strategy driven by a Data Visualization strategy your team can see data in a manner that drives results – Insight is seeing it first!

In the land of the blind the one eye man is king; when you can see the threats and opportunities in your data before your competition sees it; your team can react and change direction, strategy or tactics.  Seeing data on web browser based tools that can be quickly accessed from anywhere provides a quick a timely scorecard just in time for an account meeting.  The user does not have to be computer savvy or an analyst to pull rich analyses and impactful charts down for a customer meeting the next day. 

The results are:

  • More time selling for your account teams
  • Sophisticated analyses such as Price Elasticity, Assortment Optimization and Trade Analytics in a push button environment
  • Relevant business building presentations that drive customer decisons

  • The need for fewer analyst resources and more sales exposure to your customers

  • Your account management team become consultants to their customers

With today's tools spreadsheet reports become graphic icons on a map, hours spent over a Power Point deck becomes a push button affair and the analyses that went undone are now presented with impactful "ah-ha's" to your customers.  Specific issue oriented presentation decks can be developed at headquarters that are market relevant when they are downloaded by your account teams.

Let us do a needs assessment of your current reporting strategy and we can develop an approach to data visualization that truly help your sales and marketing teams see their opportunities better.  Insight, Information & Consulting Services, Inc. can help you develop a set of information tools that put everyone from the top executives to your account teams on the same information page.  We offer a full suite of tools to help you visualize your data on maps or deliver 1,000 customized presentation decks to account teams across the country with the click of a button.

Visit us at www.insightinformation.net

Thin Client Information Delivery Tool

Insight, Information & Consulting Services has developed an exciting new tool in the data visualization space working on the Fusion Point platform.  We have worked with Fusion Point to customize their basic information delivery platform to provide enhanced template and user experience functionality.  A recent development is the ability to dump data out of Fusion Point into an Excel template that takes the exported data and executes a macro from the assigned template to further enhance the data.

An example of this functionality is a macro that exports all accounts in a geography for a selected product, executes a macro that asks the user for the number of units to be allocated then prepares a number of columns and formulas to allow the user to allocate a special pack across those accounts.  The user simply places an "X" in the field beside the desired account to produce that account's fair share of the allocated units.  The template also allows the user to override the calculated number of allocated units with a manually entered allocation amount.

We build custom templates to work in Fusion Point that will deliver tools such as SKU Optimization, Trade Promotion Analytics or information templates with built in intelligent text.  Fusion Point can house any type of data:

  • Syndicated data such as Nielsen or IRI
  • Internal shipment data
  • POS data
  • Consumer and respondent data

Call Rick Pensa at Insight, Information & Consulting Services for more information – 770-425-4243 or email at bpensa@insightinformation.net .  www.insightinformation.net

Data Visualization – Location Intelligence

This is a new web log to address the exciting new concept of visualizing business intelligence data in easy to use tools even on a map.  Nearly 80% of business data is location driven, and it only makes sense to see data on the familiar metaphor of a map.  The focus of this "blog" is to deliver insight into tools that deliver information from your data.  We are pioneering the use of mapping tools to view information in the Category Management process in the Consumer Package Goods (CPG) industry.

We will publish tips and analytic techniques used in Excel, Access and using spatial analysis tools to expand Category Management data visualization with the use of maps.  The tool I use is the AWhere SIS spatial analysis suite of tools to produce output such as:

  1. Shopping mission driven trade areas
  2. Loyalty card shopper analysis
  3. Map causal impact on POS sales
  4. Develop consumer profiles using POS data

The following map shows how store coverage data collectedon handhelds can be mapped to show impact on POS sales at the store level:


Location Intelligence WEBINAR

We conducted a Location Intelligence WEBINAR under the auspices of CPGCatNet with some interesting discussion about which spatial data most CPG companies view as important to them and what drives their retail store clustering. 

While I would have thought that most CPG manufacturers would cluster based on demographics our on-line poll indicated more reliance on clustering with store characteristic data.  I like to emphasize the use of store characteristic data, sales rates (sales/$1 MM ACV), sales sq ft etc as well as demographics to cluster stores.  We were surprised to see very few mention consumer addresses as important sources of location intelligence.

The consumer address information is an important source of demographic information for profiling and targeting your consumers and the more you can use that data the more ROI that usage returns to your company.  Mapping consumer respondent data around stores can help foster better targeted local marketing efforts.  I still think the huge supply of Loyalty Card respondent data is a huge source of great intelligence that is not being fully mined and geo-spatial software can be a great tool to make sense of Loyalty Card data.

I encourage your input and thoughts…please let us hear from you!!

AWhere For Retail Link Data

Ever wish you could see Wal-Mart Retail Link store level data on a map?  The AWhere folks have developed a "Lite" version of AWhere SIS that only links to spreadsheet data.  We are in the process of developing Excel templates that are populated by Retail Link output, cleaned up with a macro and mapped in the AWhere SIS "Lite" tool.  The whole process is a clean end to end turnkey output of Retail Link data for your stores mapping Out Of Stocks, distribution, POS Sales, Mark Ups and basket information at the store level and aggregated by Wal-Mart DC.

Does anyone have any ideas or wishlist output that I can build into the templates?

Location Intelligence

This is a new web log to address the exciting new concept of visualizing business intelligence data on a map.  Nearly 80% of business data is location driven, and it only makes sense to see data on the familiar metphor of a map.  The focus of this "blog" is the use of mapping tools in the Category Management process in the Consumer Package Goods (CPG) industry.

We will publish tips and analytic techniques using spatial analysis tools to expand Category Management data visualization with the use of maps.  The tool I use is the AWhere SIS spatial analysis suite of tools to produce output such as:

  1. Shopping mission driven trade areas
  2. Loyalty card shopper analysis
  3. Map causal impact on POS sales
  4. Develop consumer profiles using POS data

The following map shows how store coverage data collect on handhelds can be mapped to show impact on POS sales at the store level: