Pivot Tables

Excel pivot tables provide an excellent way to analyze data. There are hard to construct but easy to use. Motive Power does the hard part for you. The sample shown will let you analyze sales using any of 30 fields to sort and subtotal the data. This table requires Microsoft Excel 2000 or later. Let's see how it works.

Click here to download a sample of the Invoiced Sales Pivot Table. You may prefer to print this page using File|Print before looking at the model if you have a small screen.

When the Excel workbook is open, click on Pivot on the lower left. (If you run the sales analysis from within Motive Power, this will be done for you.) We see the default Motive Power pivot table. You can see that the data is broken down first by job title, the vehicle make and then vehicle model.

How do we do a different analysis? To to see sales for each technician, scroll down the Pivot Table Field list on the right until we see Tech. Drag the word Tech to the first position on the left, to where it now says Title. Your sales figures now show, using Tech as the primary sort. If we want to see how each Tech does on each make we can see it easily. Drag the Title and Make heading and drop them back in the Pivot Table field list. Now we see how our dollars break down for each tech for each makes of vehicle that tech worked on for the period being analyzed.

Let's add Operations to the analysis. Find Op in the Pivot Table Field list and drag it over and drop it between Tech and Make. Operation is more of a skill level breakdown that you set inside Motive Power to tell it which Techs should be assigned to which jobs. We can look at sales for a single day by clicking on the date paid dropdown. Pick the first date, 5/22/01 to see what happened that day.

Look at the items in the Pivot Table Field list and think about the combination you might be interested in. You could check your information to see how a service writer does in different zip codes. There may be a difference by age of vehicle. Perhaps someone is really good with pickup owners but does not get the same profits from sedan owners.

No one but you, the shop owner or manager, can know what you want to figure out. The Pivot Table gives you a really good way to do that. In addition, your data is in Excel. It can be graphed by clicking on the graph icon. You can also share the data with your accountant, advisor or twenty group by saving it in any of the over 30 formats offered in Excel.