Quick Sales Analysis Using Pivot Charts
By Bill Kennedy - Some people’s eyes glaze over when you present them with a table filled with numbers, but if you show the same information in graphic form, they “get” it instantly. Particularly in an organization where the sales data get analyzed more than pitching statistics in the World Series, you want people to get it fast.
Here’s a quick way to put some accounting power in the hands of the sales team with a Microsoft Excel Pivot Chart. Use your accounting system or report writer (e.g. Crystal Reports works well for this) to create a large unformatted table of your sales information. You want to include all the sales data you can, such as:
- Sales Amount
- Sales Quantity
- Sales Person
- Sales Territory
- Item Number
- Item Description
- Customer Number
- Customer Name
- Customer Country
- Customer Province / State
If you have summary information such as Item or Customer groupings, throw that into the pot as well. When you have every possible statistic in the file, open it with Microsoft Excel. Now you’re ready to create the Pivot Chart.
If you’ve never seen a Pivot Chart, you’re in for a treat. It looks like a normal graph, but it isn’t. A Pivot Chart lets you drag and drop any combination of those statistics you just gathered and get an instant view of the results. You can see monthly sales by salesperson, sales by item or territory or my favorite: show me the people who bought last year but haven’t bought this year. For a primer about Pivot Charts, Jon Peltier has a nice tutorial in TechTrax.
Try it out. They are really simple to use and the best part is that you can save time by letting the sales team do their own analysis. Just create the spreadsheet and let them have it!
For other tips on energizing your accounting system, go to Energized Accounting.