ADP Weighted average vs Straight Line

[JSR]

I want to get a consensus regarding ADP users related to your setups. What is the consensus regarding the use of weighted average versus straight line calculations for generating stockorders?

[Chuck Hartle]

Hi JSR,

I could spend a lot of time explaining the whole process. But, to keep this short, I am going to assume that this is for your captive inventory that is moving.

Straight Line Averaging is just what it claims to be. For example, you have a 30 day supply and you selling 12 during this period at any time you have a BSL of "2". It is 6/12 = 2....

The weighted average is a little more complicated. Several people have given me different calculations, but David Summers with Toyota gave me the weighted average and I have to concur with his findings. Using the weighted average over the past three months gives you a weighted average of approximately the following criteria:
Current Month = 50%
Month Two = 30%
Month Three = 20%

For Reynolds users a three month average is supposedly
Current Month = 44%
Month Two = 33%
Month Three = 22%

I have gotten this from several people but I have not been able to see this happen. I have seen a straight division on ERA systems.

Bottom line, for my opinion, is stay away from both EOQ and Straight Line Averaging where you can. SCS and ABC Sourcing for ADP and ERA systems work much more acurately and use either 3 months or 6 months for your calculation period in ERA program 2324.

Chuck Hartle'

[David S]

JSR

Spending a lot of time talking about this is no joke. I truly think I could build an entire seminar around how the various DMS systems calculate Best Stocking Levels. As I was developing an interest in understanding these systems I found that there were many misconceptions. There are people that have based their entire Inventory Control Theory on these misunderstandings. From thinking that the computer systems store historical data in weekly buckets to believing that the two time period related questions in STK dont make any difference.

Because I felt that none of us could maximize the use of our DMS systems without truly understanding how they operate, I started a personal crusade last year to discover their secrets.

Chuck is on the money with the straight line averaging. As for the weighted averaging, I am afraid that the last time we talked I neglected to share my latest findings with respect to ADP. There is no way I can explain with the written word how the entire formula works. Here is the simplified version though.

When using the weighted average calculation, the system will always weight the current months sales at 40%.

The previous months weighting is variable. Based on how you answer the 2 questions in STK, HOW MANY DAYS THIS MONTH and WHAT DAY OF THE MONTH IS THIS. The weighting will vary anywhere from 29%, if you answer that its the 22nd of 22 working days, to 67% if you answer its the 1st day of the month.

The previous or third month of the calculation will always be weighted 20%.

My experimentation has led me to believe that in a traditional weekly order environment it is best to use the system as designed. Since I dont personally get much time dealing with weekly stock orders maybe Chuck can give us some further insight here. But, when you are trying to stabilize the supply chain, and normalize your inventory levels, changing your answers in STK each day causes problems. Changing the weight of the previous months sales has a significant impact on your BSL. As a result your system suggested inventory levels are artificially increased at the beginning of the month and artificially decreased at the end of the month. Al the while we are trying to maintain our inventory at a constant depth in relation to our sales demand. Therefore, I have been recommending that in a daily order environment that Parts Mangers always answer that it is the 15th of 30 days. This provides a 49% weight. Then, with the help of tracking RO Fill Rate manipulate the days supply to attain the correct level of inventory.

Why use weighted vs. straight line? Here is an excellent example. To keep it simple Ill use a calendar year. With the following sales history: Aug 2, Oct 3, Nov 3, Dec - 4. Using straight line averaging you have a total of 12 sales divided by 12 months. Using a 30-day supply your system would recommend you stock 1 piece. Even though the demand is actually increasing, straight line averaging does not take into account this trend.

With the same scenario, using a weighted average assuming today is the 15th of 30 days, the system is going to recommend a BSL of 3. In effect the system recognized the increasing demand and calculated for it. In addition using these formulas the systems will recognize a decline in demand thereby reducing BSL and reduce your exposure to obsolescence.

[Rex]

David and Chuck,

I would be interested in both of your perspectives on the next part of this same subject: seasonal parts. Neither weighted or straight-line will handle the initial demand for 4WD actuators during the first 3-4 weeks after the first snow. Do you like the systems' seasonal setups or do you have a different idea on this?

[David S]

Rex:

Knowing that you are living and working in midnight sun of the summer and daylight darkness of the winter in Fairbanks Alaska you face the frustration of seasonal sales more so than most parts managers do.

You are absolutely correct. The first 3 or 4 weeks is a potentially dangerous time but once we work through the initial demand the weighted average method of stock order calculation is exactly what you want.

We must begin by recognizing that there is no one stocking formula that is going to handle 100% of the possible situations. However there are ways to reduce your exposure to lost sales on these exceptions. When that first seasonal sale hits you want to make sure that you have the parts available. So the first step is to make sure that you have at least an on-hand quantity of 1 for any parts that are stocking status.

In R&R you have question #7 in 2324 to address this, Minimum Order Quantity of 1 For Stock Orders. By answering this line with a Y the system is suppose to recognize any parts that are of stocking status with 0 on-hand, 0 on-order and 0 on backorder and recommend a quantity of 1. I have also been led to believe that ADP does this automatically. Unfortunately, for unknown reasons I have found parts that meet these criteria that were not recommended in some stores with both systems. As a result I recommend running a zero on-hand report with similar criteria through 6910 or with ENG/RPG to prevent all stock out situations.

Using these tools will make sure you have inventory for the first demand of the season. Hopefully, your stock order frequency and lead-time then provides you the opportunity to replenish the inventory prior to the next demand. If this process can continue for the next couple cycles of demand the weighted average method of stock order calculation will work as intended and increase your BSL according to the increasing demand.

I mentioned the stock order frequency and lead-time above. Truly this is the last key to dealing with seasonal parts. Assuming a 30-day supply, weighted average, with no sales history in the recent past it will take 3 sales in the current month to generate a BSL of 1 on a stocking status part. If you have weekly orders, customers will likely be left waiting. If you have daily stock orders, unless all demand occurs in 2 days or less, you will likely be able to replenish prior to the next demand.

[Cam Klem]

Just a quick note to let you know I've tried your system of using 30/15 for current month business days and which day it is.

I like it a lot!!! My inventory is about 7400 active GM, movement based with some pricing tweaks for straight list and discount items. Program stuff, all that. The system as described does an excellent job of damping out the 'start of the month go nuts' syndrome. I've tried it on short 30/30 BRP/BSL up to big simulations like 100/100 for quarterly program buys. My staff have played back ond forth - and we would add one tweak - on our system, after the middle of the month we tried adding 1 or 2 days to the actual business day, and found that worked better on high velocity stuff.

I'm sorry - I can't give you the math, you guys seem way better at that - but the effect is fairly clear.

With or without the after the middle of the month stuff though, this is one of the nicest tips for ADP STK I've ever seen.

Thanks very much.

[jimm]

how do you tell your system to use straight line avg, or weighted avg? where in R&R do i set this up?

[David S]

Jimm:

To apply the weighted average in R&R go to screen 2324. At the top of the screen you will see your choices listed on the line "Sales search period for stock orders". The choices available are 3 months, 6 months, 9 months and 12 months. Setting this parameter to 12 will give you the closest thing to "straight line" averaging.

Actually, setting this to 12 months does not give you "straight line" averaging. Straight line averaging would take the total demand over the past year and divide by 12 giving you an average month of demand. R&R actually takes the past 12 months worth of history and divides it into quarters. There is then a complex calculation that actually weights the most recent period heavier than previous periods to determine what R&R calls the WAMS or weighted average monthly demand.

We recommend to our Toyota dealers that they use a sales search period of 3 months. We have found this to be very effective.

If you have any further questions, please feel free to drop me a note.

Thanks
David Summers

[Chuck Hartle]

As a follow up on this discussion, we have found that if you are a GM or Toyota dealer with daily stock orders, the 30/15 works wonderfully.... Thank you David.

However, with weekly stock orders I would suggest the following to 'level out' some of the short fall and overage you may experience when calculating your stock orders:

First third of the month = 30/10
Middle third = 30/15
Last third = 30/20

This is working great for those on weekly stock order programs. The problem occurs early in the month when the weighted average is considered based on the previous month. In the first couple of days of the month, with hardly any sales and a 30/15 application, it puts about 20% weighted average on the current month with no sales. The result has been a 60% to 70% reduction in stocking levels in the first week. This may be too much for weekly stock orders, especially if your manufacturer's fill rate is not so good.

Chuck Hartle'