Forum Discussion
Formula to add to running totals
Are you open to a different approach?
I think, to be more specific, you'd be better served by a single transactional table that just begins with a row of opening inventory levels in the two spots. (That's what you have now as a separate table, which I'm suggesting is adding an unnecessary complication.)
With a single transactional table, you can handle transfers, incoming shipments, outgoing, and resulting balances, all in one single database.
- Yes, I am open to a different approach.
Could you kindly edit the workbook and show me with one table.Could you kindly edit the workbook and show me with one table.
I can do that. First let me ask what rules or assumptions have you built into this? For example:
- What is the relationship between Storekeeper and Main Store? Is the former the warehouse for the latter? Or vice versa?
- Are there other entities--other stores?
- When something is "Issued" what does that mean? Just goes out of your area of concern?
- You have one column headed "Minimum Transfer Size" for example; I can safely assume that means that when a transfer of stock is called for, the minimum is 1,000.
- But to which site does that apply? It clearly isn't a limit applied to "Issued" for example.
- It appears to apply to transfers from MainStore to Storekeeper: is that correct? Does it apply anywhere else?
- I see under "Issued" various quantities...I presume that just covers shipments out, but where are they going?
Finally, are there any other factors that I've not asked about but that you take for granted?
Could you kindly edit the workbook and show me with one table.
I can do that. First let me ask what rules or assumptions have you built into this? For example:
- What is the relationship between Storekeeper and Main Store? Is the former the warehouse for the latter? Or vice versa? The Main Store is a controlled internal warehouse that issues items to the Storekeeper (Issuing Store)when they have exhausted or are unable to fully issue items to end users. They can only get the minimum supplied quantity needed to fill their gap. E.g. If chemical XYZ is delivered by the supplier(purchases) in 1ltr packaging and the issuing store have a shortage of 2500ml, then they will get 3ltrs full and be left with 500ml.
- Are there other entities--other stores? No other except Main store as explained above and the Storekeepers (Issuing Store)
- When something is "Issued" what does that mean? Just goes out of your area of concern? Yes. Let me add a point. The end users will request for items from the storekeepers. The Storekeeper will check their balances and if it they have enough stock, they will issue. If they have a shortage, then they will request from the Main Store. For example, End users will request for 200ml of chemical XYZ while the storekeepers ((Issuing Store) have 100ml in stock. To cover the shortage of 100ml, they will get a full 1ltr bottle (1000ml) from the Main store which is the minimum quantity supplied by external suppliers(Purchases). They will issue the 200 and be left with 900ml till further request in future.
- You have one column headed "Minimum Transfer Size" for example; I can safely assume that means that when a transfer of stock is called for, the minimum is 1,000. This one is dictated by the quantities supplied to the Main Store by external suppliers. A supplier could supply his products in 1ltr packaging, or 20ltr packaging. Etc. So when a transfer is called for, then the Main Store will issue what is required in the original packaging quantity. In the above example, the issuing store needed 100ml but because the Main store has 1ltr bottles as the smallest unit, they will issue it in full while the storekeepers will keep their remaining balance. Hope to have clarified.
- But to which site does that apply? It clearly isn't a limit applied to "Issued" for example. There is no limit to issuance as long as they have enough stock.
- It appears to apply to transfers from MainStore to Storekeeper: is that correct? Does it apply anywhere else? It does not apply elsewhere. Mainstore is there as a controlled warehouse and transfers to the storekeepers only when they have shortages.
- I see under "Issued" various quantities...I presume that just covers shipments out, but where are they going? This is a farm and the requests are either chemicals or fertilizers done on a daily basis. Once released, it goes to be used in their activities. I used one item as an example but they issue tens of items every day.
Finally, are there any other factors that I've not asked about but that you take for granted? For me, what is important is to know the running totals of each items per store and the quantity that needs to be requested from the Main store by the storekeepers.
I hope to have shed more light.
