Need help to calculate Purchasing Requisitions backlog by end of each month

hi! I need help to calculate an indicator related to the purchasing requisitions backlog at every month end. We receive purchasing requisitions that we need to convert in purchasing orders in SAP. The objective is to calculate how much requisitions are pending at end of month and track the evolution of the indicator.

Today I calculate the status of each requisition based on two columns: requisition creating date and purchase order date. If the purchase order date has no date, requisition status is “Open”. If there is a date, requisition status is “closed”.

I can track today the end of month picture, but I cannot calculate the previous months indicator.

The data model is pretty basic:

Requisition Number - Requisition date - Purchase Order number - Purchase Order date

Any recommendation to calculate this indicator?

thanks!

@tomyrcm Welcome to the Forum.
Could you share your data (anonymized if necessary)?

2 Likes

@rfeigel please find it attached. thanks!
PR file.xlsx (11.6 KB)

Hi @tomyrcm

Can you please draft your desired output as well based on this?

Because the data only shows month 5 but in your first post you mention wanting to calculate the previous month indicator so I assume your actual dataset covers the current month as well? This matters for the proper calculation method.

Requisition Number - Requisition date - Purchase Order number - Purchase Order date

This is not reflected by the file you uploaded correct? So the records that you uploaded reflect either of the two?

You mention wanting to calculate the pending records, but that’s different than filtering on Status = Open?

Anyway, please help us helping you by providing more details about your input, logic that should be applied and desired output. The more you can provide, the better the help will be :wink:

2 Likes

@ArjenEX I am attaching a new file with more data.

Yes, I received purchasing requisitions every month. I hope the new file provides better perspective.

I simplified the model in my original request. The file I use is the one attached.

Yes, it is different. I.e: if a requisition does not have a PO by 31/5, it will be shown as open by 31/5. If a PO is created by 2/6, when I run the report by 30/6 it will be shown as “closed”, which is okay, but I cannot compare the backlog by 31/5 and 30/6.

If I had 30 requisitions as “open” by 31/5 and this requisitions are closed by 2/6, when I try to recalculate the backlog by 31/5 the backlog will be shown at cero. I need to somehow store or calculate wh
PR file v2.xlsx (3.5 MB)
ich was the backlog by 31/5 in the future (to compare purchasing order creation date vs. the end of month date could be an option but I don’t know how to do it).

please chech this file instead.
PR file v2.xlsx (3.5 MB)

Try this. Select year and month of interest. It provides a monthly summary, year to date summary and current status of individual requisitions. I haven’t QAed it very carefully. If you find bugs let me know.

4 Likes

Found an error in the Monthly Summary Report. Corrected workflow uploaded.

Have you looked at the workflow I posted? The courtesy of a response would be nice.