sage 50 stock control monthly reorder and reorder level adjustments

After a long time search online for a solution for Sage 50 accounts to set stock levels according to seasonal demand and finding nothing I finally worked out how to do it myself.  Am i the only one who has ever wanted to do this?

I'm hoping this will reduce the amount of stock we carry.  Our current reorder amount and reorder level are based on annual sales to keep an average of 1 months stock of each product on the shelf but this neither allows for periods of high or low demand.

I wanted to allow for supplier delivery times and have an adjustment factor to allow for items I might want to put into a larger order to save on carriage (and as a general fudge factor). I'll call this the minimum stock level. The aim will be to run out of stock at the same time as the next delivery arrives

You'll need:
A previous years sales figures or an estimate of expected sales.
A good idea of how long it takes each supplier to deliver each product.

I used 2 of the web category fields in the product record to record the estimated delivery time and the minimum stock level. Unfortunately you can't use the batch change function on these fields so I exported the product details report as a csv, created 2 new columns for delivery time and minimum stock level. Sort by supplier name then it's easy to use fill down with the figures for each supplier. Delete all columns other than product code and the 2 new fields. Go back to sage, file> import and import to import these figures into the product records.  This is fairly self explanatory if you follow the instructions.

Now we need to export the sales figures for the first month and calculate the reorder level and amount.

go to customer invoices.  You need to create a report with the sales figures for the month plus your 2 new stock fields.  Go to reports>invoice detail reports>sales by product (summary) and edit it.  You'll see the product number, name, sales quantity and 2 price fields.  You can delete the 2 price fields and replace them with the 2 web category fields.  Save this and then you'll find it under "my invoice reports".  Run this report and create a csv file for the chosen month.

Basing this on invoices had a flaw.  If there was no sales of that item then there would be no adjustment.  I created a new report in product reports, based on the product details report.  Delete all unimportant field leaving stock code and description.  now insert the stock prior month qty for each month so you'll have acolumn for each month.  Also add the 2 web cat fields you're using for delivery time and min. stock levels.  I also added the BOM flag to make those easy to identify for manual adjustment later.

Open this then create 2 new columns.

Reorder level = monthly sales figure/30 x delivery time + minimum stock level.

Reorder quantity = monthly sales figure/30 x delivery time.

Once you have the base figures you can now adjust the reorder quantity to allow for any minimum order quantity you need to use.  you also need to manually adjust for any items that use BOM.

Save and  you can import into sage as before.  Only map the product number, reorder level and reorder amount.

You can now monitor how well it works and adjust the minimum stock level and delivery time to fine tune ready to repeat for the next month.