• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with a idea: "Required balance" calendar calculation using predefined debit and credit indexes

millski88

New Member
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':

DebitDebit amountDebit frequencyWeekly/fortnightly dayMonthly/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.

PayeeTowards which debit?Credit amountCredit frequencyWeekly/fortnightly day
Payee ADebit A$ amount[Weekly/Fortnightly][Day of the week]
Payee BDebit A$ amount[Weekly/Fortnightly][Day of the week]
Payee ADebit B$ amount[Weekly/Fortnightly][Day of the week]
Payee BDebit 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'.
Because of this, there may be another way of defining the credit amounts and frequencies; potentially without using a separate sheet at all? They could be populated in-line with the debit amounts potentially? I'm not advanced enough to articulate this, but I'm definitely open to all types of solutions.

'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:

NARAYANK991

Excel Ninja
Hi ,

A lot of questions need to be answered.

The first one is why are there no dates involved in the setup ? Shouldn't there be a start date for every entry ? At present you have entries which might have originated some time back , but at any time in future , you could have new entries coming in. Such new entries will require a start date.

When the final output is a calendar dealing in dates , I think a start date for every entry is required.

Narayan
 

millski88

New Member
Hi ,

A lot of questions need to be answered.

The first one is why are there no dates involved in the setup ? Shouldn't there be a start date for every entry ? At present you have entries which might have originated some time back , but at any time in future , you could have new entries coming in. Such new entries will require a start date.

When the final output is a calendar dealing in dates , I think a start date for every entry is required.

Narayan
Hi Narayan

Yes good point and definitely a something i'd overlooked, whoops.

I've added in demo dates which align with the dates/frequencies. I back-dated as i thought, perhaps it'd be easier to count forwards from the last full 'cycle' for each debit?

Anyway, tweaked version attached.

Thanks for your reply.
 

Attachments

millski88

New Member
Hi Narayan

In terms of presentation, I'd generally be happy for it to be presented in any format.

https://www.vertex42.com/calendars/continuous-monthly-calendar.html

If i could choose, I feel a 'continuous' calendar similar to this template i found online would be super-ideal, with the dollar value displayed in a cell for each date.

With this template I especially like how you can continue forward to any future year using the control at the top. If my balance calculator was dynamic and could function with a similar calendar view control, that'd truly be an amazing outcome.

Still scratching my head on the mechanics of it!

Tom
 

NARAYANK991

Excel Ninja
Hi ,

Give me a day or two to come up with something. In case others pitch in , you can respond to their questions / solutions.

Narayan
 

NARAYANK991

Excel Ninja
Hi ,

This is just the calendar ; can you go through it , and see if the format is acceptable ?

If you say it is OK , then the rest of the job , which is to populate the output area with entries and totals can be done.

I have taken a calendar by Chandoo and modified it.

Narayan
 

Attachments

millski88

New Member
Hi ,

This is just the calendar ; can you go through it , and see if the format is acceptable ?

If you say it is OK , then the rest of the job , which is to populate the output area with entries and totals can be done.

I have taken a calendar by Chandoo and modified it.

Narayan
Hi Narayan!

Apologies, only just been able to sit down and have a proper look.

This format looks great! I'm guessing I can push the year further forward than 2022 if necessary?

Also got a couple of VBA errors whilst using. I don't know much about this, perhaps it's my version of Excel.

If this can be used as the base for the idea, then perfect!

Thanks so much for your help.
 

NARAYANK991

Excel Ninja
Hi Narayan!

Apologies, only just been able to sit down and have a proper look.

This format looks great! I'm guessing I can push the year further forward than 2022 if necessary?

Also got a couple of VBA errors whilst using. I don't know much about this, perhaps it's my version of Excel.

If this can be used as the base for the idea, then perfect!

Thanks so much for your help.
Hi ,

Please post screenshots of the error messages that you got.

Also which version of Excel are you using ?

Narayan
 

millski88

New Member
Hi Narayan.

I replicated an error just now, shown in screenshot. I was just playing with the Year/Month/Date sliders. A run-time error '13'. Hopefully nothing major?

Also, Excel 15.3.3 (Mac).

Tom
 

Attachments

NARAYANK991

Excel Ninja
Hi ,

Is this error message a one-off or does it come all the time ?

Going by your screenshot , is it possible that the Mac does not accept the Form controls ?

When the selected month is November , the maximum value for the Date slider should have changed to 30 , because November has only 30 days. Instead it has remained at 31.

I will switch to ActiveX controls and re-upload the workbook.

Narayan
 

millski88

New Member
Hi ,

Is this error message a one-off or does it come all the time ?

Going by your screenshot , is it possible that the Mac does not accept the Form controls ?

When the selected month is November , the maximum value for the Date slider should have changed to 30 , because November has only 30 days. Instead it has remained at 31.

I will switch to ActiveX controls and re-upload the workbook.

Narayan
It happened occasionally as a result of certain changes to the form controls, so this could have been an incorrect date selection as you've outlined? I can test again a bit later today.

On this subject, I had a thought about the dates input when adding monthly or quarterly debits. I thought it'd be worth mentioning that the date range need only run 1-28, as it is not possible to set a recurring debits for dates later than the 28th anyway. Hopefully this would negate potential complication with using the 29, 30 and 31 dates?
 

millski88

New Member
Excellent thanks @NARAYANK991

A colleague of mine told me today his solution to this would involve normalising each row of credit and debit in to a daily $ value amount first. Does this make sense?

Anyway, 'different ways to skin a cat' of course....

Tom
 

NARAYANK991

Excel Ninja
Hi ,

Let us keep aside the issue of the output display format , and come to the calculations.

Please go through the attached workbook , and see if the calculations are correct. These are all on the tab named calcs.

What I have done is :

1. Moved two of your worksheets from your uploaded workbook into this workbook.

2. Created a list of dates from the start date till 31 December 2022. This is in column W.

3. In columns X through AI , I have transferred your entered data for the credits in different columns for the different categories. These have been repeated at the frequencies you have mentioned in your original worksheets.

4. In columns BH through BU , I have transferred your entered data for the debits in different columns for the different categories. These have been repeated at the frequencies you have mentioned in your original worksheets.

5. In columns N through Q , there are formulas to add the credits and subtract the debits.

Once you confirm that the calculations are correct , we will be left with two tasks :

a. How to transfer the data you enter in your original worksheets into the calcs tab.

b. How to display the calculated values in columns N through Q on the calendar tab.

Narayan
 

Attachments

millski88

New Member
Hey @NARAYANK991

Apologies for the delay in my reply. Thanks a lot for your work on this! This is great.

I've had a look at it makes sense. The only thing i'd query is the frequency of the 'gym membership' example, which is shown as being fortnightly, but doesn't run fortnightly on the calcs sheet. Is this one just incomplete on the current document?

It's be interesting to know how this can be maintained. Is there much manual mapping of data on the calcs sheet, or is it intended to be dynamically mapped out based on the 'index' style sheets?

Thanks again!

Tom
 

NARAYANK991

Excel Ninja
Hi ,

Sorry. I had not repeated the amounts for the four fortnightly categories.

See the attached file.

Once you confirm that the calculations are correct , we will look at the output display. Maintenance will come into the picture only when you start adding more payments for the same categories , or more categories and payments.

One thing to remember is that if a frequency is changed half way through , then it will be a difficult job to remove the existing entries and put in the new ones. Is this likely to happen ?

Narayan
 

Attachments

millski88

New Member
Hi Narayan

This looks good! Makes send now, and yes this looks correct in terms of calculations. Thank you again for your work on this.

Regarding the frequencies - no, once an entry is added it is very unlikely the frequency will change. Maintenance generally refers to adding new debits and credits - will this be possible without a lot of manual work?
 

NARAYANK991

Excel Ninja
Hi ,

The purpose of the application is to avoid too much of manual work.

Once the data in manually entered in the Debit / Credit worksheets , it will have to be transferred to the calcs worksheet automatically. I will work on this later.

For now , I will complete the output display worksheet and then move on to the data transfer.

Narayan
 
Top