Hi all
Firstly hello! I'm new. I do touch on Excel for project management, test logs etc in my job, but this rarely involves anything technical. It's great to see the forum here is so active.
Secondly, I have an idea for a tool to calculate a required bank balance on any given date, based on indexes of predefined debits and credits; their amounts and frequencies. When I say idea, I believe this may be possible in Excel, but it is beyond my technical ability and know-how. I'm hoping an advanced user may be able to chime in and give some direction.
To skip to the detailed requirements, continue reading below the italic text -
For additional context - This is for tracking the "required balance" of a joint expenses bank account on any given day. My partner and I pay proportions of our individual and shared expenses in advance (sometimes with a different % share) into a joint account.
Our proportional $ credits to this account are either weekly or fortnightly, however nearly all debits are monthly. Due to this, as you may have guessed, at certain times of year over-payment occurs where the weekly/fortnightly schedule gets ahead of the calendar month dates. Excess is also accrued where we do not spend the full amount of estimated expenses.
Over the years we have 'audited' this account, and have been able to skim off extra in to savings or to cover unexpected outgoings. Doing so can be quite a painful, manual process though, and our debits index changes and grows all the time. The last couple of times I've audited manually, I've exclaimed to myself: "there's gotta be a way to set this up in Excel!"...
The Challenge! (Detailed requirements)
Ultimately I'd like to have a calendar sheet with any given date populated with a $ amount balance calculation value ('Required Balance'). Another way of serving this could be to have a date input cell or field which churns out a $ amount in another cell - I'm not to picky about how it's presented, if it works!
The data for the calculations could (i guess) take the forms of two index sheets - Debits and Credits.
Debits is obviously an index each of the scheduled outgoings from the account, and so far I have plotted the following as an attempted 'index sheet':
My partner and I would need to edit or add to this index throughout the year if necessary, and so the solution would ideally be dynamic.
Credits (you've guessed it) is an index of the regular credit amounts in to the account, and so far I have plotted the follow as an attempted demo 'index sheet':
Remember these are paid in more frequently as proportions of the total debit amounts, and not always an equal proportion across Payees.
Something extra to note here, is:
'Required Balance Calculation' (the function)
The function would perform a calculation using (presumably) a running calendar, ideally one that continues across years. This would sum the total $ amount of all credit proportions plotted, taking in to account only those credited in the period after the full amount for their corresponding debit was last taken from the account (as indexed in Debits). Ultimately, displaying a $ amount for the "current required balance" for any given date, or rather: the expected dollar amount to cover upcoming debits in the time frame.
I hope this is OK to follow. Although I have a glimmer of hope that this may be possible, my brain is on the verge of exploding trying to work it out.
Should I make any new in-roads, i'll continue to share below.
Sheet attached with small about of mock data to demonstrate above.
Thank you in advance to any kind experts who are up for sharing my challenge and chiming in!
Firstly hello! I'm new. I do touch on Excel for project management, test logs etc in my job, but this rarely involves anything technical. It's great to see the forum here is so active.
Secondly, I have an idea for a tool to calculate a required bank balance on any given date, based on indexes of predefined debits and credits; their amounts and frequencies. When I say idea, I believe this may be possible in Excel, but it is beyond my technical ability and know-how. I'm hoping an advanced user may be able to chime in and give some direction.
To skip to the detailed requirements, continue reading below the italic text -
For additional context - This is for tracking the "required balance" of a joint expenses bank account on any given day. My partner and I pay proportions of our individual and shared expenses in advance (sometimes with a different % share) into a joint account.
Our proportional $ credits to this account are either weekly or fortnightly, however nearly all debits are monthly. Due to this, as you may have guessed, at certain times of year over-payment occurs where the weekly/fortnightly schedule gets ahead of the calendar month dates. Excess is also accrued where we do not spend the full amount of estimated expenses.
Over the years we have 'audited' this account, and have been able to skim off extra in to savings or to cover unexpected outgoings. Doing so can be quite a painful, manual process though, and our debits index changes and grows all the time. The last couple of times I've audited manually, I've exclaimed to myself: "there's gotta be a way to set this up in Excel!"...
The Challenge! (Detailed requirements)
Ultimately I'd like to have a calendar sheet with any given date populated with a $ amount balance calculation value ('Required Balance'). Another way of serving this could be to have a date input cell or field which churns out a $ amount in another cell - I'm not to picky about how it's presented, if it works!
The data for the calculations could (i guess) take the forms of two index sheets - Debits and Credits.
Debits is obviously an index each of the scheduled outgoings from the account, and so far I have plotted the following as an attempted 'index sheet':
Debit | Debit amount | Debit frequency | Weekly/fortnightly day | Monthly/quarterly date |
Debit A (unique name) | $ amount | [Weekly/Fortnightly/Monthly/Quarterly] | [If weekly/fortnightly - day of the week] | [If monthly/quarterly - number 1-31] |
Debit B (unique name) | $ amount | [Weekly/Fortnightly/Monthly/Quarterly] | [If weekly/fortnightly - day of the week] | [If monthly/quarterly - number 1-31] |
My partner and I would need to edit or add to this index throughout the year if necessary, and so the solution would ideally be dynamic.
Credits (you've guessed it) is an index of the regular credit amounts in to the account, and so far I have plotted the follow as an attempted demo 'index sheet':
Remember these are paid in more frequently as proportions of the total debit amounts, and not always an equal proportion across Payees.
Payee | Towards which debit? | Credit amount | Credit frequency | Weekly/fortnightly day |
Payee A | Debit A | $ amount | [Weekly/Fortnightly] | [Day of the week] |
Payee B | Debit A | $ amount | [Weekly/Fortnightly] | [Day of the week] |
Payee A | Debit B | $ amount | [Weekly/Fortnightly] | [Day of the week] |
Payee B | Debit B | $ amount | [Weekly/Fortnightly] | [Day of the week] |
Something extra to note here, is:
- Payee A frequency will always be always 'Weekly, Tuesday'.
- Payee B frequency will always be always 'Fortnightly, Thursday'.
'Required Balance Calculation' (the function)
The function would perform a calculation using (presumably) a running calendar, ideally one that continues across years. This would sum the total $ amount of all credit proportions plotted, taking in to account only those credited in the period after the full amount for their corresponding debit was last taken from the account (as indexed in Debits). Ultimately, displaying a $ amount for the "current required balance" for any given date, or rather: the expected dollar amount to cover upcoming debits in the time frame.
I hope this is OK to follow. Although I have a glimmer of hope that this may be possible, my brain is on the verge of exploding trying to work it out.
Should I make any new in-roads, i'll continue to share below.
Sheet attached with small about of mock data to demonstrate above.
Thank you in advance to any kind experts who are up for sharing my challenge and chiming in!
Attachments
Last edited: