Forum Discussion
Need Expert Help - Inventory
Anonymous
Just for the sake of clarity, I've drawn up a visual representation of the maths involved. In doing so I realised that I was inaccurate in the concept of "previous month balance", since the measures I posted actually include new stock added in the month (which I believe is what you are after; if not, the equations below provide the answer to showing the previous month balance, excluding any stock added in the month):
(For the sake of others who might read through this thread, I am going to correct the previous posts to reflect the correct concept being calculated)
Hi Paul,
Ok, so bare with me as Algebra was not my strong suit! Here is a question I posed to them, and her replies. Plus a little more context:
1. She runs her inventory report once a day. From there, she generates two reports out of ERP...the Flu Plan Pegging, and the Items. (the sample tables in my report). The "Last Gen Date" field from the Items table is the date the beginning inventory for the whole process is based on. It's a singular date (the date that the report was generated). Whatever the qty on hand was at the time of the report is what the starting point for the inventory is.
2. The Pegging report provides the context for the Demand (Outstanding Requirement).
I think we're very close, but I did not quite follow your table and which formulas I would need to change to adjust, based on the comments below.
"The on hand qty from the Items table does not take into account any outstanding requirements. So if the Items report was generated on Feb 2 with on hand 206,656, but there was still an outstanding requirement from Jan, your actual beginning inventory for Feb should the 203,080 you stated below."
"You will need to take the cumulative of any “past due” outstanding requirements + outstanding requirements for the current month to get end of month inventory."
"My question is really about beginning month inventory. Using my example below, the “last gen date” of the Items report was Feb 2 and the on-hand amount was 206,656. But there was an outstanding requirement in January of 3,576. In that case, is the “last gen date” of the Items report (and specifically the on-hand qty), already taking this into account? Or, is the Feb 2 on-hand inventory really 206,656 minus 3,576 (203,080)?
Anonymous
Bear with me since I am a little lost at the moment with the process you have described:
1) She runs the report once a day. When she does so, a new calculation date is created (defined as "Last Gen date"), correct?
Now is when I am not quite understanding the context...
You mention: Whatever the qty on hand was at the time of the report is what the starting point for the inventory is.
And then say: "The on hand qty from the Items table does not take into account any outstanding requirements. So if the Items report was generated on Feb 2 with on hand 206,656, but there was still an outstanding requirement from Jan, your actual beginning inventory for Feb should the 203,080 you stated below."
What values does the report generated each day contain? Does it have data from the dates previous to the report creation (given that she runs the report each day)? if so which? (If the "quantity on hand" must take account any outstanding requirements (from Jan in the above example) and the quantity on hand from the Items table does not take into account any outstanding requirements, you need data from the previous month -specifically the cumulative outstanding requirements-, right?) Is this [cumulative outstanding requirements] included in the daily report and if so, which field is it?
2) Does the report include a value for "Quantity on hand" for upcoming months?
Depending on how this value is generated, & what values the report contains, we need to use either the value itself or cumualtive. So, if the report only contains values for the current month, we would use the cumulative value; if the report has "Quantity on Hand" values for future months (to cater for product ordered and delivered in the future months), we would use the SUM of the values.
3) does she need the closing balance for each month? (as I see it, there are two "closing balances":
a. The closing balance of physical stock: [stock - fulfilled demand] (ie physical stock remaining once products have left wharehouse)
b. The closing stock taking into acount demand which has not been fulfilled: [stock - fulfilled demand - non-fulfilled demand] (so, physical stock - products ordered but haven't actually left the wharehouse.) This value is what I understand should be used in the actual calculations for the starting point or opening balance for the following month.
4) Assuming that what we use for the calculations for the starting point or opening balance for the current month is point 2.b. above, do you need the new demand for the current month to be part of the calculation)? ie: [2.b. - current demand]
or do you need to show the calculation [2.b.]
Hi Paul - Getting down my thoughts and will reply on this tomorrow. Thanks for sticking with me on this!!
Hi PaulDBrown
Using Item 20303A as our example.
The tables below are indicative of the change that can occur from one period to another. In this case, one report ran on the 12th, and one today, on the 20th.
I'll try to answer your question by way of example:
Feb Mar
Qty On Hand Prev Demd Crnt Demd Cumu Demd Surp Qty on Hand Prev Demd Crnt Demd Cumu Demd Surp
206,056 10,200 3,804 14,004 192,052 192,052 0 33,954 47,958 144,094
My interpretation of how this should work involves a couple of assumptions:
1. The "previous demand" is any demand prior to the Last Gen date (in the context of the current month of the report). That is why I show 0 previous demand in March. Current demand is today + future demand (again, in the current month context).
2. One question is what is the best way to calculate the very beginning Qty on Hand in the event that there is outstanding demand from the month prior to the Last Gen date. For example, if there had been leftover January demand of 56, should the Feb Qty on Hand be 206,000 - or would it be included in Cumulative Demand?
3. There is no injection of new stock factored in. Not sure why they've not asked for that, but not something I am looking at.
4. Previous outstanding demand is just inventory that has not shipped yet for whatever reason.
Hopefully this helps! And thank you again for your assistance.
Items Table Feb 12:
Quantity On Hand Last Gen Date 206056 2/12/2020 Pegging Table Feb 12:
Due Date Outstanding Requirement 2/10/2020 5400 2/10/2020 4800 2/12/2020 1182 2/12/2020 1182 2/28/2020 1440 3/2/2020 12 3/2/2020 12 3/2/2020 6 3/2/2020 6 3/2/2020 12 3/2/2020 6 3/2/2020 6 3/2/2020 6 3/4/2020 12 3/6/2020 480 3/13/2020 240 3/13/2020 72 3/13/2020 1800 3/13/2020 300 3/13/2020 990 3/13/2020 300 3/13/2020 120 3/13/2020 822 3/13/2020 480 3/16/2020 504 3/16/2020 3960 3/20/2020 3780 3/20/2020 2520 3/20/2020 1080 3/20/2020 108 3/20/2020 72 3/20/2020 480 3/23/2020 900 3/23/2020 108 3/27/2020 180 3/27/2020 1440 3/27/2020 96 3/27/2020 108 3/27/2020 48 3/27/2020 12 3/27/2020 48 3/27/2020 720 3/31/2020 6000 3/31/2020 4200 3/31/2020 1800 3/31/2020 108 Quantity On Hand Last Gen Date 202816 2/20/2020 Pegging Table Feb 20:
Due Date Outstanding Requirement 2/10/2020 5400 2/10/2020 1560 2/20/2020 1182 2/20/2020 582 2/20/2020 600 2/21/2020 84 2/28/2020 360 2/28/2020 48 2/28/2020 1980 Anonymous
Based on the examples, my take on calculating the Quantity on Hand taking into account the leftover demand is:
[SUM of Qty on Hand] - [Prev Demd]