Forum Discussion
Formula to Calculate Daily balances
I am struggling to create a stock balances file where I can easily know the closing balances PER DAY, and what I need to borrow from the Main store as explained in the file. I have attached the file...
so here is the problem, we don't know what data you get/enter.
Since the format isn't fixed I agree with peiyezhu and you should completely reformat it as an entry table and then report tables separately. So for example:
Date . . . . . . . . Store . . . . . Item Code . . . Tx Type . . . Amount
2023-02-26 . . 123. . . . . . . . CHEM1 . . . . . used . . . . . . 400
2023-02-26 . . 123 . . . . . . . CHEM1 . . . . . . add . . . . . . 5000
2023-02-26 . . main . . . . . . CHEM1 . . . . . add . . . . . 100000
this type of ledger style can then be used with pivot tables, power query, or excel formulas to calculate everything assuming you have all the needed entries. But what I'm unclear about is how does the pull from the main get documented? who is to say someone doesn't pull 2 units because they are sick of getting only 1 each time or thought they needed 2 because there was a request for a big amount. And then what about the main? right now you are down to 1 but what if that goes negative (which I assume it can't so how in the sheet is more added to main?)
think of it this way, there are data entry columns and calculated columns and the 2 shall not mix. The 'ledger' style above would all be data entry. Elsewhere would be a reporting table with calculated values. Another table could be all the Item Info like:
Item Code - Item Name - Transfer Size
you could have other info like Min Qty that could be used to have a NEED TO ORDER table/report
That said a formula for N4 (orig sheet) could be: =MAX( 0, ROUNDUP((I4-P3)/5000,0))
so basically if I4 (used) is bigger than what is left (P3) then you need more from store and in increments of 5000 (or whatever reference amt you use) and you round up to cover the amount needed. The MAX( 0, ...) means if the value is negative (i.e. supply > need) then return 0.
Since the format isn't fixed I agree with peiyezhu and you should completely reformat it as an entry table and then report tables separately. So for example:
Date . . . . . . . . Store . . . . . Item Code . . . Tx Type . . . Amount
2023-02-26 . . 123. . . . . . . . CHEM1 . . . . . used . . . . . . 400
2023-02-26 . . 123 . . . . . . . CHEM1 . . . . . . add . . . . . . 5000
2023-02-26 . . main . . . . . . CHEM1 . . . . . add . . . . . 100000
this type of ledger style can then be used with pivot tables, power query, or excel formulas to calculate everything assuming you have all the needed entries. But what I'm unclear about is how does the pull from the main get documented? who is to say someone doesn't pull 2 units because they are sick of getting only 1 each time or thought they needed 2 because there was a request for a big amount. And then what about the main? right now you are down to 1 but what if that goes negative (which I assume it can't so how in the sheet is more added to main?)
think of it this way, there are data entry columns and calculated columns and the 2 shall not mix. The 'ledger' style above would all be data entry. Elsewhere would be a reporting table with calculated values. Another table could be all the Item Info like:
Item Code - Item Name - Transfer Size
you could have other info like Min Qty that could be used to have a NEED TO ORDER table/report
That said a formula for N4 (orig sheet) could be: =MAX( 0, ROUNDUP((I4-P3)/5000,0))
so basically if I4 (used) is bigger than what is left (P3) then you need more from store and in increments of 5000 (or whatever reference amt you use) and you round up to cover the amount needed. The MAX( 0, ...) means if the value is negative (i.e. supply > need) then return 0.
I have tried to reformat the table though some areas are still not clear. Please have a look.
Let me explain the process.
1. The end users will request for items to be used the next day (upfront) from the Issuing store.
2. The issuing store will prepare a summary of all these requested items which will be entered into a spreadsheet.
3. Upon entering this data, it shall be known if the issuing store has enough stock or will require top-up from Main store. If the latter applies, then the item will be issued to them from the Main Store as explained earlier. Stock levels for both Stores will be affected. For depleted items, they end users will choose an Alternative item till it is replenished.
4. Every Month, they make a list of items that need to be procured. Of course.. assisted by the program in use.
What is not clear to me is the used and add tx type and where the data comes from. The program user is supposed to feed the data from end users and the program to do the calculations. They should be concerned with what the end users want and the program to calculate what is available from issuing store and if there is anything needed from Main store on a daily basis. May be I did not understand clearly your point.
running total？ or summary by now？
each items store Date Store Type Item Code Transaction Type Amount balance 2023/2/21 Issuing Store CHEM1 used 400 -400 2023/2/21 Issuing Store CHEM1 Added 5000 4600 2023/2/21 Issuing Store CHEM2 Added 5000 5000 2023/2/21 Main Store CHEM1 Added 50000 50000 2023/2/21 Main Store CHEM2 used 50 -50 each items Date Store Type Item Code Transaction Type Amount balance 2023/2/21 Issuing Store CHEM1 used 400 -400 2023/2/21 Issuing Store CHEM1 Added 5000 4600 2023/2/21 Main Store CHEM1 Added 50000 54600 2023/2/21 Issuing Store CHEM2 Added 5000 5000 2023/2/21 Main Store CHEM2 used 50 4950
