Following is an example of how graphing was a critical part of getting to the bottom of a minor crisis.
An agreement had been made based on numerical data and monthly averages. However, in the 3rd month of the agreement the numbers were bizarre and far outside of any reasonable expectation. It was panic time!
Data visualization proved to the most valuable tool where,
- Numbers in a spreadsheet weren’t good enough.
- A graph was used to dig into details, and not just as a general snapshot of high-level trends.
Let’s look at this, y’all!
♦♦♦
Make-U-Smart, Inc. was wrapping up the year and preparing for the next year. Budgets were being discussed, compensation plans and bonuses were being revised … all that year-end activity. One product line, XYZ, needed special attention.
XYZ consists of various products for preparing for a statewide exam and here’s how it’s been performing:
The problem with product line XYZ was the nagging pain it created for customer service. The orders were complex, the calls were long, mistakes meant that a customer might not be able to take their exam. And it took a long time to train a rep to take those orders.
AGREEMENT TO PAY TO CUSTOMER SERVICE
Rather than outsource the ordering, an agreement was made to pay Customer Service 6.3% of sales per month to compensate them for the hassle and maybe help pay for additional people.
No problem. Based on past data, 6.3% comes out to $4100 to $4600 per month. Allowing for reasonable fluctuations, it’s all good.
3 MONTHS INTO THE AGREEMENT AND “OH NO!”
January’s payment was $2200. February’s payment was $13,600 an amount triple the most generous estimates. This can screw up a department’s budget, cashflow, ability to set expectations. It’s a mess.
The first move is to start gathering data. Was the data right? Did a sales person strike a huge deal and cause a sudden rush of orders?
The numbers were right. But here’s where the visuals come in.
ANALYZING THE VISUAL DATA
Graph A Shows the monthly history of XYZ sales and they’re all over the place; ranging from as little as $2947 up to $227,399
Observation Had this visual been created and reviewed earlier, the agreement would probably be different.
But is there any kind of trend or predictability? Let’s look at a line graph instead of a bar graph and reduce the visual noise.
Graph B The same as Graph A but lines join the data points.
Observation There appears to be a consistent rhythm. We go from peak to valley, peak to valley and the time frame between the peaks and valleys look predictable.
We don’t see small dips and then a peak. We don’t see long trends in any direction.
Here comes the bombshell! Someone mentions that the exam is given 3 times per year: March, July and November.
♦
Graph C Includes the exam dates, represented as the orange bars.
Observation UH OH! Now we can see. There is a clear pattern.
- The month before the exam, sales explode.
- The month of the exam, sales freefall.
- Between exams, sales slowly ramp up.
♦
Graph D Highlights the basic sawtooth trend in yellow, and it’s simplified in the image below.
RESULT & CONCLUSION: “What are we dealing with?”
Having seen this clear trend, and verifying the accuracy of the underlying data, the payment to Customer Service was revised to be paid quarterly instead of monthly.
What’s interesting is that the analysis evolved without fancy Excel tricks. Also, adding a layer of visualization on top of mere rows and columns of numbers told a story that wasn’t easily understood by a pile of numbers.
We also saw that there was a difference between the bar graph and the line graph. The bar graph exposed wide variations but they still looked erratic. The line graph “connected the dots” eliminated the distractions of the individual bars and suggested that there’s a very predictable cycle. A cycle that was directly tied to the exam dates and people who cram in their studies at the last minute.
This kind of thinking and analysis is more valuable than writing monster formulas. But this is often missing in how we handle our data. It helps to stop everything, back up and ask 2 questions when the you-know-what has hit the fan:
- What are we dealing with?
- What tools do we have for answering question #1?
This time around, the tool wasn’t a pivot table or conditional formatting. Good ol’ garden variety charts soothed the widespread heartburn.
This is just one more reminder that our BI tools are just tools. We have to develop our abilities to know what to use, when and how. That, and a handful of basic technical skills will go far.
eye photo credit: michael.heiss via photopin cc
