The Top 8 Must-Haves for More Effective Analytical Reporting Within Category Management
It's no question that analytical reporting is becoming more automated over time. Effective platforms exist to display category insights paired with potential conversion savings dollars. A healthcare organization can have spinal implant spend broken down by manufacturer market share on one screen and that same distribution of five separate like-sized healthcare organizations on another. With one click, that entire category can be broken down into a line-item detail and benchmarked against the industry on the 50th, 25th and 10th percentiles.
There are situations, however, that cannot be resolved through the use of today's solutions. Access to the data can be a significant roadblock — a GPO can provide insights on switching across its own internal contracts but often lacks visibility into local agreements put in place by the healthcare organization. A personal (least) favorite of mine is dirty data. While an analyst can deduce that MFG No. BHT-1998 is the same as MFG No. BHT1998 with accompanying data, automated systems struggle to confirm its consistency. This is where your analyst generally comes in: to implement processes and provide insights that cannot be consistently performed by an external system.
The tedious days of Excel reporting are nowhere near their sunset, and I've compiled a list of eight must-haves to provide clear, concise and consistent results to support effective decision making. While this post is geared toward supporting decision-making within category management, the core elements can be utilized in any form of manual reporting that requires the use of an analyst.
1. Standard templates
The goal of standard templates is to maximize efficiency, consistency and analytical confidence. Analyst A's product conversion report from Abbott pacemakers to Medtronic should look nearly identical to Analyst B's conversion from Medline underpads to Owens and Minor. The following benefits are realized once the reporting process is standardized:
- The sourcing manager knows where to look for what. They understand where the data came from and how the analyst came to that conclusion.
- Analysts spend less time deciding how to approach the analysis. The analyst knows that volume is pulled from the enterprise resource planning (ERP) on invoices paid over the course of a year, and to pull spend on the entire supplier to potentially locate SKUs that are off contract. They know that current pricing is pulled from the contract database and to spot check invoices paid to ensure the two numbers run parallel. They know that proposed pricing is generally provided by the sourcing manager or vendor, and to trust the numbers, not the text. Time spent making decisions is time directly taken away from value-added work.
- The development of subconscious red flags. Once the process is known, any variation in it by nature receives additional scrutiny. Potential issues and opportunities are more easily identified.
2. All-encompassing overview page
Consider this the one-stop shop that answers the question asked — the executive summary of the analysis. This page contains the core findings, data sources, date range, assumptions, date stamp and signature line. The overview page should give the decision maker all the information needed and show them exactly how that conclusion was reached. Where was volume pulled from? Data sources. Is a tier change going to affect pricing? Standard assumptions. This looks a little off — I need more information. Signature Line.
Pro tip: If you want this page to be read (and you do), do not include the results of your findings in the email.
3. Vocalized assumptions
Decision making only occurs where there is a lack of information — if all factors were known, it would be an equation. Arguably the most critical area of these eight must-haves is the utilization of vocalized assumptions. These are meant to cover areas where not all information is known and where we must assume the most likely outcome. Think of it as a disclaimer to potential events that could derail your findings. An assumption could be anything from "A meteor won't hit the nearest Medline DC, causing a massive service interruption" to "Pricing listed on the purchase order reflects the price paid due to minimal exception rates and prompt payments." As a protection, a great practice surrounding this is to formally write the assumption down on the overview page next to the core findings of the analysis. The assumption is the center of attention and read in parallel with the rest of the section.
4. Line-item detail
This is where the core analytical work is performed. In this section, data is centralized across varying sources and calculations are performed in minute detail to address the overall question being asked. Information on the overview page should be a direct roll-up of the calculations performed in this section. Spare no detail, as the section will be used to answer any questions derived from what is presented. Color coding (see more below) is your best friend here. Correlate the color of the column headers with the base dataset that the information was pulled from for both you and the strategic sourcing manager's reference. Designate a specific hue to columns that contain calculations that were performed. Conditionally format numbers to display green or red for savings or increases. A number looks off? Highlight it in yellow and leave a comment. You will forget the intricacies of each analysis. You will be caught off guard by the category manager. Create a consistent, visual process that allows you to field any inquiries that may come your way without throwing the "Let me circle back to you" line out there.
5. Formalized data sources
Listing your data sources is equally purposeful for the strategic sourcing manager as it is for the analyst. In this section, the report and price files utilized are listed alongside where they were pulled from and what columns that data was used to populate. Date ranges for the data are to be included as well. The goal of this is repeatability. When a mistake is made (it happens), a consistent data set is critical to determining the root cause of the inconsistency.
6. Consistent formatting
My general Excel formatting goes as follows:
- Font: Calibri, 11 point. Column headers are bolded.
- Worksheet headers: Located in cell A1, bolded, at 18 point Calibri. The data source is listed below each header in cell A2.
- Color coding: Each dataset is assigned its own column color. In the analysis detail, each column header correlates with the dataset it was pulled from. Calculated columns are assigned green.
- Worksheet placement: In this order — overview page, line-item detail, data source 1, data source 2. If alternative sheets were used to complete the analysis (a page for a pivot table to break out volume by MFG number, for example), hide them to avoid scope creep but ensure they are still available.
- Overview page: Ensure all information can fit on the overview page, legibly, without the need to scroll. No gridlines.
Digestibility is the name of the game for this section. An analyst can dump all the information in the world into an analysis, but it serves no point if the strategic sourcing manager can't understand what it says. An effective format corrals the reader from the overview page to the detailed analysis and on to the datasets, if needed.
7. Date stamp and signature line
One of the most important, and overlooked, areas of reporting is the date stamp and signature line. A signature line provides accountability to the analysis. It states exactly who performed it, when it was performed and how to contact them.
8. Simplicity
Remember, we're answering a question. The goal is to provide all details necessary for a sound solution. Outside information drives focus away from the core decision and risks credibility. If we're asking how little Jimmy got to school, we don't need to know what color his shorts were. And if we answer khaki shorts when he was wearing navy pants, credibility across the entire analysis is now shot.