Charts should tell a visual story of the imperative aspects of a company’s business questions including what the results and conclusions are and how the company’s journey looks. Most managers and CEOs do not want to waste their time looking at endless data or complicated charts. Most senior executives want their employees to understand the business and what the relevant business decisions are enough to determine the pertinent aspects of the company and be able to display them in simple charts.
Charts can also be used to summarize data, see whether there are outliers, and determine if there are problems with the data and how to go about fixing them.
This chapter will review how to create simple pie charts and bar charts in Excel using frequencies and percentages. Although Excel is a powerful tool, its charts are basic; for more elaborate charts, use the open source software R or Python.
You saw real-life probability questions in Chapter 3, so now it is time to see how a small data set using two variables can be transformed into a visual representation. The first thing to do is to tally up and then summarize the data in a frequency table. We will use Excel, but this time we will let the data tell a simple story from each chart. However, before pictures are created, the formulas for frequencies and then percentages must be run and understood in order to know how to explain the business questions and the story’s journey.
The book uses small databases to demonstrate the ideas; these techniques can calculate the formula and create charts but can easily be generalized and used with a larger database as well. In fact, small datasets should be used to test to make sure the data looks correct and you know how to use the techniques, but the real charts should utilize large datasets.
This chapter will first explain frequencies and percentages and then put them in charts.
Frequencies: How to Use Percentages
Data and count of the month and shop mode
The example scenario is that a small business is having an email campaign for loyal customers and wants to know, based on subjects for two months (January and February), which method of purchase is used more often. The method of purchase is referred to as the store (in-store or Internet), which is the type of buyers’ shopping patterns. Both January and February have 15 buyers, so there are 30 shoppers in total. Nine shopped in-store, and 21 on the Internet. Half of the shoppers shopped in January, and the other half shopped in February. Shoppers were equally likely to shop in January as in February. The difference in size of these groups and the fact that these are small groups may make it hard to compare shoppers who shop in the store compared to those online. Figure 6-1 displays the data and Excel code to tally and calculate the frequency for each group.
Total number of subjects = 30
Total number of 1 (Month = January) = 15 subjects
Total number of 2 (Month = February) = 15 subjects
Total Shop 1 (Shop = Store) = 9 subjects
Total Shop 2 (Shop = Online) = 21 subjects
Calculating the number of shoppers in January and February
Once the frequency for store and Internet are tallied, take the total number in the store, which is 9, and divide it by the total number in the sample, which is 30; then multiply that number by 100. Here, the percentages of 30 percent and 70 percent are calculated, respectively. Again, the cumulative percentage is initially 30 percent for stores but then add 30 percent and 70 percent for the store and Internet. Always remember that a percent is between 0 percent and 100 percent, but always sum up to 100 percent. Remember, round shoppers to an integer because there is either a person or not.
The percent column has to add up to 100 percent, while in the cumulative percent column the individual percentages add up one at a time until all the percentages are included, which will include 100 percent of the data. If this does not happen, then there is a calculation error. If the calculation is very close to 100 percent, then there may be a rounding error.
Frequencies, percent, and cumulative percent in Excel
Excel code of frequencies, percent, and cumulative percent
Although frequencies can explain data, they are not always so clear to the viewer. Horizontal, vertical, and pie charts explain data as well and can tell a story in just a glance. Let’s review them now.
Simple Charts: Horizontal, Vertical, and Pie
To create horizontal, vertical, or pie charts, go to the Insert tab in Excel and review the percentages you created from the raw data. There you will find a variety of bar charts and pie charts. Excel also has a recommendation icon that can help you to figure out which type of chart to use. Although there are no absolute rules for which chart to use, a key to remember is that each chart should be as simple as possible and present a clear and precise statement about your findings; it should tell the story that is important for the business to understand.
In the following case, the horizontal and vertical charts and pie chart are used to tell an understandable and uncomplicated story. The pictures of the pie chart may display the most poignant and effective chart. Let’s see why, but first let’s go over the data and the storyline questions.
For the most part, most of the charts that will be generated to tell the company story will be from the statistically significant results.
As can be seen from the data, the number of shoppers for the variables Month and Store are very different. For Store, the count of data points for in-store is 9, and for Internet the count of data points is 21. Again, the book is demonstrating these concepts using small groups, but samples 30 or more should be used, or a more sophisticated statistical analysis determining the sample size should be used. Also, the sizes of these two responses are disparate, and both samples are smaller than 30. Caution should be used when comparing these groups because having very different size datasets can skew the results and lead to spurious conclusions. In such cases, it may be better to just state the probabilities with their associated numbers without claiming statistical significance.
For the shoppers for the Month variable, both January (15) and February (15) have the same small count or number of subjects. In this case, an independent t-test can be used to compare the data, and this statistical test will be explained in a later chapter.
Here are some ideas that may be derived from the data to create a story. There is a larger percentage of the shoppers from a store shop on the Internet (70 percent) than in the store (30 percent). Loyal customers buy about the same whether it is January or February (50 percent). Are these results significant? We could have added to the story by including what it would say about revenue generated.
The next part is to create the type of chart that displays the story you are trying to tell. It is important to look at each and see which type of chart would accurately and simply show the story you are trying to tell at a glance.
Horizontal and Vertical Bar Charts
Pie Charts
Pie chart of percent of subject per month
Pie chart of percent of subject per type of shop
Summary
This chapter illustrates how to create frequency tables and use them to create percentages. You can insert either frequencies or percentages in the charts depending on which is more powerful. If there is a small sample, then numbers are recommended. At that point, you can decide which types of chart should be used and if the data warrants recognition enough for the employee to create charts from it. A simple and clear chart can tell a story at a glance in a way that a frequency table or data never could.
To elaborate and tell a more elaborate story about a business problem, the reader may want to determine if these results are significant. In this case, what would the chart say about how much revenue is generated in a store or online senario?