Stock Purchasing and management Issue

Hello team,

I have been doing spare parts since 1979 (yes I am a greybeard) yet I have no qualifications or training in proper stock inventory control.

I have written various purchasing suggestion things in Excel before but I have never been sure that I was on the right track, self doubts etc etc.

Well, now I am spare parts manager with a company and we are handling server/laptop/notebook/desktop and printer parts for one of the biggest names in IT hardware. We are a distributor, not the OEM.

Please see attached.

Now, this was not written by me, but by their "IT Guru" who has some sway in this company. I hope the formulas copied across.

As you will plainly see from rows 6/7/8 there are problems with it. I invented 6/7/8 data for demonstration and made comments.

The rest of the data is real, except for the part numbers and vendor name.

Well, 1st off my observations (I may be wrong and will be happily corrected)...

1/ It can't handle 1 off big sales.

2/ It can't handle slow declines or slow increases

3/ There is no lead time.

4/ There is no min/max

5/ There is no pallet discount factor

6/ What else is wrong with it???


So, what do I need?

Let’s pretend the lead time is 30 days.

I would like something that can handle points 1~5 at least. Plus I would like to see some sort of forecast for the next 2 months, not just what I have to order now for 30 days time, I would like to know 60 and 90 as well.

If necessary there may be a warning calculation that flags that whilst there is a suggested order for the next 30 days, there may be an anomaly in the figures for that SKU that requires me to actually read the transactions more closely.

Am I asking for too much here?? Probably, but I have always helped people where I can, Scout Master and Rural Fire Service etc etc.

Please, just Excel methods, no programming, I just do not need any programming at present.

Yes we do have Access here, but I need to hit the ground running and also sidestep the "IT Guru" with something I understand and just get on with my job.

Yours kindly


Jeff Harper
Australia.