USING PIVOT TABLES TO DETERMINE WHICH VEHICLES TO KEEP OR CRUSH
Idea designed/developed by Bill Stevens
1. Use the Find Vehicles function (F2 key) and using the “Location” field, search for all vehicles that are in
your yard. your post-processed boneyard. Either search for all of the vehicles, or limit the search to a
particular section of
At our facility, we have just two sections, “Left” and “Right”, designated by “YL” and “YR”. If I used “YR” I would
be shown all vehicles in the “Right” Section. If I used just “Y”, I would be shown vehicles in both sections.
2. Left Click in the “grid” of vehicles on any one of them to highlight a line, then press CTRL-A to select
them all.
3. Right Click in the “grid” to have the context menu appear, and select “T21 Remaining Parts Report”.
Allow Pinnacle to crunch the data and build the reports. This could take a bit of time if
you have a large number of vehicles selected.
4. Once the T21 Remaining Parts Report appears, click the
“Save to Excel” button at the top of the page.
5. Save the file to an easily found location, such as the Desktop, and use an easy to remember file name,
such as “CRUSH”.
6. Launch Microsoft Excel. Press “CTRL-O” to open a file. Navigate to where you saved the file and open it.
Once this opens, you will see a very large data set of all of the parts with a current status of “Y” that are attached to the
vehicles you selected from the F2 screen.
This is the raw output of the T21 Remaining Parts report. Each row represents an individual part. The column headings
are the names of the data fields that Pinnacle Pro uses with each individual part record.
We need to set our focus only on the vehicles that have been here at least 90 to 120 days and longer. The analysis of our
sales history shows us that by approximately 120 days, just about all of the “Y” status parts that were going to sell off of a
particular car, have already sold. Generally, what is still attached the car are surplus parts that likely won’t sell in the next
90 days.
But we don’t want to crush parts and vehicles that are in our selection mix that have been here less than 90 days (or 120
if you prefer). So let’s modify our data set to eliminate these parts and vehicles and only focus on the items that have
been here 91 days or older.
Note: The choice of setting your focus on 91 days, 121 days, or longer is up to you and how you run your facility. The
Inventory Evaluation Report (T16) will show you how your inventory performs in the 0-90 and 91-180 day buckets and
how sharply a decline in sales occurs after 180 days. We need to identify the non-performing cars and turn them back
into cash by crushing them so that we can replace these non-performers with fresh inventory.
7. Navigate to the column with the heading of “daysInStock”. Click any cell in that column.
8. In the Ribbon Bar at the top of the screen, click the tab labeled “Data”, then the icon representing Sort.
9. Using the Sort function, sort the data set by “daysInStock” from smallest to greatest.
The result will be that all of the parts are now sorted by how long they have been in your system. The first rows are
the items most recently added, the bottom rows are the Y-status parts that have been in your bone yard the longest.
10. Scroll downward until you find the first row of data that has a “daysInStock” value of 91 or greater (or 121
or 181 or whatever you chose).
11. Select (by highlighting) all rows above the row you identified in step 10. To do this, click on the row number
(far left of your screen) that has the first entry 91 days or less. This should select the entire row. Now scroll
nd
upwards to the top of the data. Hold down the SHIFT key, and click on the row number for the 2 row. This will
highlight all rows between the two clicks.
12. On the ribbon bar, click on the “Home” tab, the, click the Delete button to remove these rows from the
data set.
Now the first row of data should be a part whose days in stock is 91 days or older. And the other rows are parts that
have been here at least that long.
Now our data set contains just the vehicles that we plan on crushing. And we can proceed to creating the Pivot table.
13. Press CTRL-A to select all rows and all columns.
14. Click the “Insert” tab, then click the “PivotTable” icon to insert a Pivot Table.
15. For the “Create PivotTable” dialog box, leave the data range as it is, but make sure to select “New
Worksheet” on where to place the PivotTable Report. And click OK.
16. A new worksheet will appear. On the left side of the screen, you will see the initial frame
work of the PivotTable report
On the right side of the screen, you
should see a sidebar labeled “PivotTable
Field List” that is a list of fields with blank
checkboxes, and below that section titled
“Drag fields between areas below” with
some empty boxes.
17. Find “vstockno” (vehicle stock number) near the bottom of the fields list, and
drag that to the “Row Labels” box
a. Click on the downward pointing triangle to the right of vstockno (1)
b. Select “Field Settings” from the menu that popped up. (2)
c. In the Field Settings Dialog box, click the “Subtotals & Filters” tab (3)
d. Select “None”. Click OK. (4)
If you take a look in your work sheet, you will see that Excel has filled the rows with the stock numbers. As you drag the
fields from the list to the different boxes below, Excel builds the table for you.
18. Find “bin” (vehicle location) at the bottom of the list and
drag that to the “Row Labels” box.
You should now have 2 columns showing the vehicle stock
numbers and the yard location of that stock number.
(Remember, your stock numbers and locations are likely
different than the example here)
Now that we have the report table rows set up, let’s populate the PivotTable Report with some columns of data for
these stock numbers.
19. Find “bprice” (the B-Price assigned to each IC# and each part) and drag that to
the “∑ Values” box.
Note how the PivotTable report updates. Tthe number that appears under the
“Total” column of our report is the total quantity of parts still attached this vehicle.
While this is a useful value, this isn’t the value we need to make our decision. We
need the how much money is involved, not the number of parts.
a. Click on the downward pointing triangle and then click on “Value Field Settings”
b. change the Field Settings from “Count” to “Sum”.
The report now updates to tell us is the total dollar value of all of the parts still
attached to the car. This sum includes all of the statuses that the T21 report
calculated (Surplus, Pull, Pull Now, etc).
Again, this total dollar figure useful, but this isn’t the prediction of how much sales that vehicle will produce in the next
90 days.
Fortunately, Pinnacle has calculated that for us, and the data is there to extract with our PivotTable, so let’s use Excel to
pull out that information.
20. Add “calculatedStatus” to the Column Labels Box.
Excel splits the Total parts value into the “Surplus”, “Unbolt”, “Warehouse”, and
“Yard” categories.
This is the summation of the sales value for the different recommendations calculated
by the T21 report.
When you ran the T21 report, Pinnacle took the current demand statistics and
calculated which parts are going to sell quickly. Then assigned them a “W” or “Pull Now”
status. The parts that would sell eventually are assigned a “U” or “Pull Status”.
Parts that are not likely to sell are “Y” status. And finally, parts that will never to sell are labeled “Surplus” parts.
A quick look at the example above shows that the vehicle in line 6, while having $2321 of parts value, is unlikely to sell
anything within the next 90 days (there are no values in the “U” or “W” columns). And the vehicle show in line 8 has $827
of parts currently bolted to the vehicle that will sell over the next 90 days.
Since our focus is determining which vehicles we want to keep from the crusher, let’s continue to modify our PivotTable
report to show us which vehicles are going to produce the most sales. To do that, we want to focus on the “U” and “W”
parts only since these are the parts that Pinnacle says will sell in the next 90 days.
21. To remove the “Y” and “S” columns and only show the “U” and “W” parts value, look in the sheet and
click the dropdown menu arrow to the right of “calculatedStatus”.
22. You will see commands for sorting and a box to select Value Filters. In the Value
Filters, uncheck “S”, “Y” and “blank” and then click the Okay button. This
will leave you with columns for “U” and “W” parts only. The Grand Total column
also updates to show you the expected sales value of “U” and ”W” from that stock
number within the next 90 days.
We now have a good report that summarizes how much in sales each vehicle will generate over the next 90 days if
we were to leave it alone, based on the data from the T21 Remaining Parts report.
But not turning non-producing vehicles into cash isn’t the smartest business decision. Look at line 8 of the example
above, stock# 11D062. According to the T21 report, that car is only going to produce $27.00 in the next 90 days.
We need to use our PivotTable results to identify two categories of vehicles:
• those that will not produce enough sales to justify keeping (calculated sales value is less than what we would get
from the crusher/shredder); and
• those vehicles that still have a strong predicted sales value
Let’s continue to evaluate this data to determine which cars will produce enough sales (like 11A024) and which should
be sent to the shredder and converted into cash for fresh inventory..
The PivotTable as we have it now is very useful, but unfortunately we can’t easily manipulate the results without
messing something else up. So let’s copy the data to a fresh sheet and tweak the results there.
23. Click anywhere in the sheet with the pivot table
a. Press CTRL-A to select all. Press CTRL-C to copy it.
24. At the very bottom of the sheet, select the small tab with the orange star at the far right to create a new
sheet.
25. In the new worksheet, right click on cell A1 and select “Paste Special” from the context menu. Select “Values”
from the dialog box that appears and click Okay.
This will paste just the values of the cells that we copied, not the formulas or magic items that make the PivotTable
work.
26. Highlight the first three row by clicking the row label “1”, dragging down the “3”, then clicking Delete on the
ribbon bar. This removes the rows that are no longer needed like we did when we eliminated the parts that
were younger than 90 days. We only want the row with the column headings of “vstockno”, “bin”, “U”, “W”,
and “Grand Total”.
27. Click on any cell in the “Grand Total” Column. Then sort the data by “Grand Total” from smallest to largest.
You now have a list of boneyard vehicles sorted by how much sales they will produce in the next 90 days.
Any vehicles that with a sales value significantly less than crush value can be let go now without any additional handling
and you won’t miss those sales. Assuming about 1.0 to 1.25 tons per hull, Crush Value = Current Scrap Quote dollars x
1.0 or 1.25. For example, if the current quote from the local shredder was $275 per ton
Crush Value = $275 x 1.25 = $343.75 or $345.
Any vehicle with a Grand Total of $345 or less, or more conservatively $400 or less, needs to go to the shredder.
Any vehicles with a sales value significantly more than crush value can be set back or saved for another round.
Now here is where the money making comes into being. Say the section you were planning on crushing out had 120
cars sitting in it. By using this PivotTable technique, you identified 15 vehicles worth saving another round because they
would produce at least $500 of sales in the next 90 days. This leaves 105 vehicles you can immediately send to the
crusher.
Taking our estimated Crush Value of $345, multiply by 105 vehicles. This becomes $36,225. For many folks, this
represents a significant portion (if not all) of your buying budget for a month.
Next, let’s look how we can use this same technique to manage the parts already in our warehouse.
USING PIVOT TABLES TO MANAGE WAREHOUSED PARTS (W STATUS)
Idea designed/developed by Bill Stevens
A frequent complaint amongst auto recyclers is that we run out of space to store parts we’ve pulled in our warehouse.
Our racks have become so full, that the excess parts have become a “fungus” and are spreading onto the floor and into
other nooks and crannies.
This often occurs because we aren’t using a good technique to identify what existing parts that are on the shelf are and
are not selling.
My suggestion is to use the following PivotTable technique to identify which interchange numbers of your major part types
are not selling. Then do one of the following
1. Eliminate the parts by tossing them into a crushed car
2. Reduce the price drastically to gain some interest from both local and Car-Part.com customers
3. Reduce the price drastically and immediately list on eBay.
All three are pretty serious, but important to do. Numbers 2 and 3 are a bit more desirable, but you need to set a
maximum deadline (like 90 days) and eliminate any parts that didn’t sell. If they haven’t sold by then, the demand data
has proven that it is extremely unlikely they will ever sell. So let’s recover the space for parts that do sell.
So how do you determine which individual parts you should keep and which ones you should take action on? We will use
PivotTables to take the information from the “Shakedown with Recommendation” report (T4A) and narrow our focus to the
parts that will sell. And by deduction, this easily shows us the parts that are “dead” and are not selling, or won’t sell.
1. Select the T4A report by going into Pinnacle and following the menu path of Management >> Stock
Reports >> Shakedown with Recommendation T4A. You will see the following criteria selection box
a. View would be used to split the report between Used and New Parts. We will use the default of “Location of
Used Parts”
b. Locations we will leave blank to encompass all locations. Note: This will do Yard locations too, but the setting
we’ll make in Part Status Filter (f) will keep out any parts with a “Y” status. If you wish to focus on particular
areas or rows of your warehouse, you would put the range of locations here. Experimentation here is
encouraged to see the flexibility of choices you have in selecting the rows and locations.
c. Model will be left to “All Models”. But if we wished, we could narrow our focus to
specific models.
d. Switch the selection to “Specific Parts”
e. Now we’ll select the parts we want to run through our PivotTable.
i. To make individual part selection simple, first right-click on the button with the
ellipses (e) and make sure the radio button and the checkbox are set as
shown.
ii. Once that’s done, left click on the ellipses button and
select the parts you wish to run through the
PivotTable. To select multiple parts, hold down the
shift key as you click on the part names in the right
column. Once you have made your selection, click the
rectangle in the upper right to close this selection box.
iii. For my example, I selected multiple parts:
Transmission, L Headlamp, R Headlamp, L Taillight,
R Taillight, and Wheels.
f. Switch the Part Status to “Warehouse” to focus the report
on parts with a “W” status.
g. “Order By” can be left alone or switched to anything you desire. This is something we can manipulate later in
Excel.
h. Click Okay to run the report.
Once the report is up, the columns to pay attention to is on the right third of the page, labeled Recommend and RecQoh.
These are the calculated prediction of this individual part will sell and how many Pinnacle recommends you should have
on hand to meet 90 days worth of demand.
This report is useful, but in my example, I came up with 86 pages of information. And most of the parts have a calculated
recommendation of “Surplus”. 86 pages of report is too much to scan, too much to print out, and a waste of paper just to
find the small number that I should keep. So we will use the PivotTable to improve these results.
Before we move on, go to the last page of the report, take note of the total count of the parts and the sales value. This
can be a useful comparison when you’re done.
Click Save to Excel, select the location to save the file, and name it something you can remember, such as Pivot Part
2. Launch Excel, and open up the file you just saved
The file that opens is all of the individual part records (rows) and the data fields (columns) for these individual records.
Since we have already specified some filter criteria when we built the report, we don’t need to make any changes to this
sheet. We’ll move directly into building the report.
3. Press CTRL-A to select all rows and all columns.
4. Click the “Insert” tab, then click the “PivotTable” icon to insert a Pivot Table.
5. For the “Create PivotTable” dialog box, leave the data range as it is, but make sure to select “New
Worksheet” on where to place the PivotTable Report. Click OK.
6. A new worksheet will appear. On the left side of the screen, you will see the initial
frame work of the PivotTable report
On the right side of the screen, you
should see a sidebar labeled “PivotTable
Field List” that is a list of fields with blank
checkboxes, and below that section titled
“Drag fields between areas below” with
some empty boxes.
From here, there is many ways to make use of the information. It all depends on what answer you are seeking, and
where you put the data fields into the Filter, Labels, and Values boxes. I encourage you to play with the different
combinations to see what works for you. Let’s look at one example.
7. Find “itemname” and drag it to the Row
Labels. Then drag “recstatus” to both the
Column Labels and Values box. When I do this
on my part selection, I get the results shown to
the right.
It is interesting that out of 1185 total parts, Pinnacle is
predicting that based on current demand data, only 41 of
these will sell within the next 90 days. And on the lamps, very few of them. So here is confirmation that I need to get
aggressive on pricing or eBay listing to generate some sales. This also shows me that if I was to eliminate these parts, it
is very unlikely I’ll miss any sales dollars.
Look at wheels. Here is an opportunity to get some scrap money if the price of steel and alloy wheels are at a good level.
Now that we know just 4 of my R Headlights are worth saving, there is a quick way to see which ones they are. (This
same function works with any number in the table). Double click on the number and the PivotTable will expand to show
you those parts.
Or, I can click on the 206 surplus transmissions and have a sheet showing me which stock numbers need to be
aggressively re-priced.
There are many, other PivotTable combinations that will aid in sorting through the data to find the answer you need. I
encourage you to export other reports from Pinnacle and experiment with manipulating the data in a PivotTable to help
solve a question that the main Pinnacle reports do not answer directly.
If you figure out something very useful, be sure to share with me and everyone else.
If you have any questions, feel free to email or call.
--Mike Farlow
Denton County Auto Salvage
Denton, TX 76205
940-387-5202