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.

Leave a Reply

Your email address will not be published. Required fields are marked *

18 − 15 =