Purchase/Stock Report

[tonyt198]

I am trying to write a report which links stock allocated against stock on purchase orders and will show the relevant due date for that product.

At the moment I get all purchase orders being printed that has the product on, whether on order or cancelled.


Basically the information I'm trying to compile is:

Sales Order Number
Product outstanding quantity
Product on order quantity
Product due date

Will this be possible or am I going round in circles?

Thanks

[brucedenney]

Not possible in the report designer BUT...

Here is the kind of issue

Imagine the scenario where you have 10 items on a Sales order, you have 3 in stock, 3 on order 123 due on the 1st and 6 on order 124 due on the 16th.

What is the due date?

The answer is we don't know because we don't know if there is another sales order before this one for 90 and that is going to use all the stock and all of the next 2 orders scheduled for delivery.

I have done this sort of thing in the past by creating the purchase orders from the Sales order as a "back to back" order, we automated it so that the sales order got written on the pop and the pop got written on a sop. We repeated items on the pop so the same item could be listed multiple times on multiple lines each line relating to one sop. We knew which items were on which order it was really tied down but this wouldn't work in a scenario where not everything was ordered "back to back".

You can do it in Excel.

You would have to have a way of defining the order in which stock was used. Oldest Due Date first would be easiest.
Where stock from 2 different orders was needed, you would only show the later dated items.

So in excel you need to link data using ODBC you need the sop tables, the pop tables and the stock table, you can filter to only show live "on order" orders and not any quotes, tentative, cancelled or complete.

So in the main sop sheet you would list all the outstanding items on all the orders, sorted by due date, the trick is to calculate cumulative qty of each product code on each line as you go. So against each item, rather than knowing how many you need for that order you know how many you need to complete this order and all the ones prior to it.

On the pop sheet you would do the same sort of thing listing the cumulative qtys of product. So it would list the cumulative number available at each due date, rather than the the number on that order.

In the sop sheet you look up the cumulative qty of product, less the qty in stock, in the pop sheet, that will give you the line where the qty is available and then you can offset that to get the due date from the pop.

You need to have some moderately advanced Excel skills and have you head round the data, it is a bit complicated. To make it slick you need a bit of VBA experiance to do all the refreshes and to make it so you can change version of Sage or the Data location easily. I reckon it would take me an hour to and hour and a half to do.

Once you had it you could go further build a Pivot report off the sop sheet, so as to see just the order and due date of the last item. You could calculate the overdue days against each product to see the pinch point. Graph the overdues against date to see when things will be at their worst. etc etc etc.