Schedule of Payments - Formula Puzzle

Good afternoon,

I am trying to create a spreadsheet (see attached) that automatically schedules the soonest I can pay my expenses in a list based on three elements:
- A list of expenses including date due, date paid, source (who I'm paying), amount due, amount paid, balance (difference between due and paid), description, and notes
- A list of assets (or income) including date to receive, source (who's paying me), amount to receive, amount paid, description, and notes
- A list of priorities. Basically I have listed each source I will have to pay and I assigned a priority number next to each one. A priority of 1 being the greatest importance (of course, this is arbitrary).

Each of the three elements are on it's own spreadsheet in the workbook, in addition to the schedule of payments. So, four worksheets total.

What I want the schedule of payments worksheet to do is check the list for unpaid expense balances in relation to their due date, compare the list of expenses to the list of assets in relation to their receipt date, check to see if any of the assets have been allocated to other expenses, check the list of priorities as to which expense receives the next asset while respecting the associated dates (as to say, I don't want it to hold onto all of my assets because something with higher priority exists 3 months later and not reasonably apply the assets to sooner expenses).

As you may notice in the attachment, there is an As Of date. This just allows me to add up everything in a given amount of time, but does not factor into what I'm asking you help for.

The other thing I don't want the schedule of payments to do is list empty rows (probably solved by conditional formatting) or expenses that have already been paid.

The idea is that as time goes on and I'm filling in the amount paid columns in both the expense and asset tables, the schedule of payments will always display a current schedule.

Hi neomoses

Welcome to the forum

Here follows my views that have no more weight than anyone else's.

I have had a look at your spreadsheet and I believe that you are trying to do too much at one go. (In my opinion!) there needs to be separation between history and future transactions. The problem is that the future cannot be accurately forecast - something unseen will come up - which will ruin all your nice plans.

Having said that, I believe it is an excellent idea to try to plan your finances, rather than bury the head in the sand. I have used cash forecast sheets in the past which have helped, but only because I have been able to be flexible with the payment dates. I attach a sheet to your workbook- not that it solves your question, but in the hope it may give you some ideas.

Let me know if I can assist further.

Regards
Alastair

Hello Neomoses,
Without having looked at the sheet that Alastair posted, I tend to agree with what he's saying. I've been playing with the sheet you posted and have come to the conclusion that I would need to remake the whole thing (which I've begun to do because this is what I like doing in my spare time). I'm having difficulty understanding what you want out of the list of priorities...

For instance,
You say: "I don't want it to hold onto all of my assets because something with higher priority exists 3 months later and not reasonably apply the assets to sooner expenses."

The trouble I'm having with this is that, I don't think Excel can reason. Just because a priority 1 payment is needed to be made in 3 months, how is excel supposed to know that that is or is not reasonable to withhold currently available funds that could be used to pay priority 2, or priority 3 debt that is due sooner? I think the parameters would need to be set very clearly.

What could be done, is to designate a debt limit to each priority. So, for example, priority 1 should never exceed 10% of your entire debt. If it does exceed that, then allocate payments to bring that amount down. With a little more exploration, I'm sure we could fine tune the parameters so that excel seems to be making reasonable judgements based on clearly defined rules.

I'm still going to work on your spreadsheet because I like the idea and I could use the practice, but what I produce might not be exactly what you're looking for. I'll post what I make here for you..

Regards,
Samuel M. Roth

Figured it out, finally. See attached .xlsm file. The way this works is: On the assets and expenses sheets, you add the dates amounts you will be paid or pay something. The Forecast sheet picks up on both sheets and auto-hides inactive rows. When you are paid or pay something, you need to fill the amount in the appropriate column on each of the assets and expenses sheets so that it drops off of the forecast sheet.

I think it's nifty You guys deserve credit. Couldn't have done it without you