Formula to Calculate Daily balances

Question

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 and would appreciate to be assisted.

I am using office 2019.

mtarler · Answer

Starting in row 4 and filling down it looks like you could use:
=P3+5000*N4-I4
and I would further recommend replacing the 5000 in this case with a fixed location where the Transfer Size is stored, so something like $Z$1 if you store that in cell Z1

a_sirat · Answer

Thank you.My main problem is that I have filled in the quantities in columns N &amp; O manually. Would it be possible to have a dynamic formula in this cells that would update as items are being issued.

peiyezhu · Answer

Item Code store qtyCHEM1 main 10CHEM1 issuing 965CHEM1 main -400CHEM1 issuing -1300how about keeping record in one sheet and then pivot table?

a_sirat · Answer

peiyezhu&nbsp;I have edited the file again and I think if I could have the closing balances of the previous day become the new Opening balances of a new day, then I can manage to move on from there. Please see attached updated file. The file can be changed completely and not a must to appear the way it is. Important for me it to use the closing balances of the previous day as the new balances of the new day.thanks

mtarler · Answer

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 . . . Amount2023-02-26 . . 123. . . . . . . . CHEM1 . . . . . used . . . . . . 4002023-02-26 . . 123 . . . . . . . CHEM1 . . . . . . add . . . . . . 50002023-02-26 . . main . . . . . . CHEM1 . . . . . add . . . . . 100000this 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 Sizeyou could have other info like Min Qty that could be used to have a NEED TO ORDER table/reportThat 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 &gt; need) then return 0.

Forum Discussion

Formula to Calculate Daily balances

Share

Resources