PARTS YEAR END INVENTORY – AVERAGE COSTS and SALES to PURCHASE RATIOS

A well-run Parts Department should begin preparing for their year end inventory on Day One of the New Year. Conducting an efficient, meaningful, and accurate inventory requires that sound parts handling procedures be followed year-round. The following information will provide you with suggestions and guidelines which will help you attain your goal of an accurate year end parts inventory.

There are some key concepts and terms used by your ASTRA software which everyone in the Parts Department should understand. We will begin by reviewing these items.

Average Cost:

A part’s average cost is calculated by the system based on initial value and subsequent purchase order receiving. This will be the cost used as your Cost of Sale when this part is sold. It will not necessarily be the same as the cost charged to you by the vendor. The formula used to calculate average cost is as follows:

(Old ON.HAND qty X old AVG.COST) + (New QTY.RECD X New COST)
divided by: (Old ON.HAND qty + New QTY.RECD)

For example, part# ABC has qty. three on hand at an average cost of $2.00. You receive a PO for five more at the vendor cost of $2.25:

(3 X 2) + (5 X 2.25) divided by (3 + 5) =
6 + 11.25 divided by 8 =
17.25 divided by 8 = $2.16 < new avg.cost

Incorrect average costs are most commonly caused by receiving errors and/or incorrect parts setup in 3-1-3. In most cases, these errors can be corrected by reversing the original PO/Receiver and then issuing a corrected PO/Receiver thereby correcting the average cost. Because an average cost adjustment (3-10-4) creates a GL transaction affecting the parts inventory account, this method should only be used as a last resort. If a part has zero on hand, you can change the average cost in 3-1-3. Never manipulate the system to allow yourself to do this when you really do have items on hand (No, I’m not going to tell you how!).

Sales to Purchase Ratio:

This is by far the leading cause of problems involving on hand quantities, average cost, and receiving errors. The sales to purchase ratio is part of the setup for a part in 3-1-3: (15) Sales/Purch: The system default is one (with blank or null also meaning one).

Before we explain what a sales to purchase ratio means and how it is used in the system, do yourself a favor and forget you ever saw the following in 3-1-3:

(2) Case Qty:
(13) Selling U/M:
(14) Purch U/M:

These three fields are informational only and the system could not possibly care less what you put in those fields. You can put anything you want in these fields – your grandmother’s girdle size, your dog’s name, your age, or your lucky lotto numbers – the system will never, never, ever look at this data. The system will totally ignore these fields. Field 15 is where you should focus your attention.

What is a Sales to Purchase Ratio?

The sales to purchase ratio compares how you sell a part to how you must purchase it from your supplier. For example, you buy one pump from the supplier and you sell one pump to the consumer; you buy five pumps from the supplier and you sell five pumps to the consumers. Such a part will have one sale for every one purchase. The sales to purchase ratio, then, would be one to one and the value you enter into Field 15 in 3-1-3 would be ‘1’.

Now suppose you must purchase weather stripping by buying one 1,000 foot long roll. You, however, sell this to the customer by the foot. In this case, you would have 1,000 sales for every one roll purchased. Thus your sales to purchase ratio for this part would be 1000 to 1; you would put ‘1000’ in Field 15. Likewise with a quart of oil – you buy it by the case with each case containing 24 quarts but you sell it by the quart. Purchasing 4 cases would give you 96 quarts to sell or 24 sales for every one purchase. Field 15 for the oil would be 24.

But that’s only half the story!!

*** It is very important that you remember how you purchase an item that has a sales to purchase ratio greater than one. ***

In Field 18 of the part’s setup in 3-1-3, your supplier cost is the cost you must pay to make one purchase!!

In our weather stripping example, one 1,000-foot roll costs you $500.00. This is your supplier cost in Field 18! Do not make the mistake of thinking “Well, then it costs me 50 cents per foot” and so you put 0.50 in Field 18. No! No! No! This is not correct!!! You pay $500.00/roll so your supplier cost is $500.00.

So how does the system keep track of your cost of sales when you only sell one foot? When the system calculates your average cost, it first looks at the sales to purchase ratio from Field 15. It takes your supplier cost from the PO Receiver of $500/1000 feet and calculates that as 0.50/one foot. Before it plugs the numbers into the average cost equation outlined previously, it converts the amounts based on sales to purchase ratio.

So what can go wrong (because it will!)?

1. You could put in a supplier cost of 50 cents. Your average cost, which also determines your parts value and your Cost of Sale, would become 0.0005 cents. This will look great on the P & L Statement but if your place ever burns down and you have to collect your asset value from the insurance company, you will likely find yourself looking for another job.
2. You could forget to put in the sales to purchase ratio in Field 15. This would result in an average cost (and value and COS) of $500 per foot of weather stripping. This will not look good on the P & L Statement and you will not have to wait for the place to burn down before you need to find another job.
3. You could do everything correctly in 3-1-3. You even make your purchase order correctly, ordering a quantity of ‘3’ so that you receive 3 rolls and therefore 3000 feet of weather stripping. But then along comes Untrained Parts Person to receive that PO. They say “OK, I have 3000 feet of weather stripping here” so they receive qty 3000 of the PO. The system knows you have an S/P of 1000 so it takes that 3000 and multiplies it by 1000 and suddenly you have (apparently) three million feet of weather stripping. That should last a good, long while! And think back to the average cost equation, plug in 3,000,000 instead of 3,000 for the on hand qtys and you can see what a mess this can create.

Lessons to be learned

• Understand average cost and the sales to purchase ratio.
• Understand the relationships between average costs, sales to purchase ratios, supplier costs, on hand quantities, and parts value.

Why?

Average cost times on hand quantity equals your parts value. A Year End inventory is done so that you can maintain and correct your on hand amounts and have a correct asset valuation. The Tax Man appreciates such trivialities and the Auditor is a happy camper if (s)he has meaningful data to look at when examining your Parts GL Account.

Are you ready to do a Year End Parts Inventory?

Ask yourself these two questions:

1. Do I understand average costs, sales to purchase ratios, and how they relate?
2. Do I at least understand that I do not understand these things?

If you answered ‘No’ to either question, do not schedule a year end inventory or involve yourself in one. Seriously! If you don’t know what you are doing, don’t do it. If you answered ‘No’ only to the second question, you can get by if someone at your dealership who will oversee the inventory can answer ‘Yes’ to the first question. Either become someone who can answer ‘Yes’ to the first question or blindly do what the ‘Yes’ person tells you to do – don’t guess!

What else can (and should) you do before starting a year end inventory?

Anything that makes the actual counting process easier and less prone to errors will make a big difference when it comes time to head out with the count sheets.

1. Cull your PARTS file by deleting part numbers you do not have and will never order again. Use the Parts Deletion routines in 3-8 and the Obsolete Parts Report in 3-3-32-7.
2. All stocked items with the same part number should be in as few actual physical locations as possible. Label locations and input these locations into Fields 7 and 12 in 3-1-3.
3. Review the reports in 3-3-32-6 (parts with a negative on hand amount) and 3-3-32-8 (parts with a cost and/or average cost of zero). Use these reports to spot problems and correct them before you start Year End.
4. Create custom reports in 11-12 to help you keep ahead of potential problems. For example, a report displaying parts with a sales to purchase ratio that also displays vendor cost and average cost will help you spot probable errors. If you see a part with an S/P of 24 and the average cost is the same as the supplier cost, you know something is wrong. (If you don’t, stop reading now and go back to page one!) Likewise, a report showing parts that do not have a description would point out part numbers that are probably invalid and which could be deleted. Create reports for anything that you think will help you keep your Parts Department organized and well maintained.
5. Conduct monthly Cycle Counts. If you have release 7.04.0 or above, use the Cycle Count Routines in 3-13. If you have an earlier release, print count sheets from 3-3-7. Answer ‘N’o to this prompt - Display On Hand Only (Y/N): If you do print the system on hand amounts on a count sheet, I guarantee someone will write that qty as the count at least once instead of actually counting! Research differences between what you count and what the system shows, focusing on possible receiving errors. Receiving errors should be corrected through a reversing PO/Receiver and a new correct PO/Receiver. Use Stock Adjustments in 3-1-10 to enter your counts only if you cannot find any logical reason for the discrepancies. In a perfect world, one would only make a stock adjustment if, for example, a part was stolen or broken and thus unsellable.
6. Do all of the above at least every month. Two months prior to doing your Year End inventory, do them in earnest! In addition:
   1. Every month you should reconcile the parts value report to the parts inventory account and address all discrepancies. We strongly encourage this! If there are problems and you are not addressing them monthly, these problems will be 12 times as big when you have to address them at year-end. Using the RBNI feature will make this process much simpler. (RBNI is explained in your release notes.)
   2. Review 3-3-32-9 (parts committed to a work order). All parts committed to work orders should be moved to ‘parts used’ on the work order (4-1-2, P vs. 4-1-2, PR). Make sure these parts are also separated out from the rest of your stock and not counted.
   3. All parts that have been physically received should be received in the system on purchase orders. Make sure Accounting has all of the packing slips/receiving reports so that invoices can be entered before you start to count.
   4. “Tidy up” your storage areas. If parts are stored in cases, stack them neatly so that they will be easier to count. Don’t just throw them over in the corner. Likewise, pre-count small parts that you have in large quantities. If you have an old cardboard box full of half-inch screws, count them when you have enough time to do so accurately, not when The Boss is screaming “Are you done yet??”. If possible, find smaller old cardboard boxes and divide them up into, say, one hundred screws each.
7. Once you have initialized in 3-7-2, we recommend that you do not sell or receive parts until you have completely finished the entire YE procedure. The system is designed to allow you to remain open but it requires careful supervision to be successful. More on that in a bit. I can already hear many of you saying that you can’t afford to stay closed long enough to do all your counts. You would be surprised how quickly you can do a Year End if you have been following all these recommendations throughout the year. I have seen facilities where, yes, I would look and say “This will take you several lifetimes – as is” but I have never seen one yet that would take more than two days – three, tops – if these recommendations are followed throughout the year. If your Parts Department is well managed year round, counting can go surprisingly fast.

So what do you do if you cannot shut down during the counting and you must use the system to sell/receive parts? Make sure you have some system in place so that your people doing the counting will know what is going on. Suppose you’ve run 3-7-2 and a part had five on hand at that time. Before that part is counted, you have to sell two to a customer. Place a label, note, taped message, or neon sign with the remaining parts telling the counters to include those two in their counts. In other words, they will still count five even though they only see three in front of them. For parts received on a PO in the system (3-6-2), keep them in a separate place from the parts that are being counted but do count them. If you have physically received the parts but have not received them in the system (3-6-2) then do not count them and keep them out of the way until your YE is finished.

Saving Time

If you feel you simply do not have enough people to finish your YE within 2-3 days, consider hiring an inventory company to do the counting for you. IDS has worked extensively with RGIS which makes their living counting other people’s inventories. They can be contacted for further information at 800-521-3102 or at www.rgis.com. We will work with them to help you achieve an accurate YE inventory in a timely fashion. IDS sells handheld barcode inventory scanners that can be used to assist in completing your year end parts inventory. These eliminate the need for manual count update entry in menu 3-7-4 and speed up the entire year end inventory process.

Practice before you do. If you have a DEMO account, use that. If not, look over all the steps in the 3-7 menu. If you have any questions or doubts or problems, call IDS Support at 800-962-7872 (or 727-578-1440) before you actually start. Regardless of what questions you might have or what problems you might encounter, you will have a bigger mess if you wait until after running 3-7-2 to call Support.

Year-End Preparedness Checklist:

Don’t start until you can honestly check off all of these boxes:

• Do I understand average costs and sales to purchase ratios?
• Do I really understand average costs and sales to purchase ratios?
• Have I been truthful thus far?
• Have I reconciled my parts value to my parts GL on a monthly basis?
• Have I used menued and custom reports to help me maintain my parts records?
• Have I kept my physical storage areas tidy and organized?
• Did I become familiar with the YE routines at least two months before now?
• Have I had all my questions answered?
• Do I really, honestly, fully comprehend average costs and sales to purchase ratios?
• I am ready!!