Problem working out a couple of formulas

[Pluribus]

Hi

I am having a complete mind blank and was wondering if someone can help me.

I am working on a forecasting spreadsheet to work out the daily budget of a client. His budget is worked out on a day by day basis over the course of a month. Most of the formulas for this are simple and I wont embarrass your intelligence by detailing them.

However, I cant work out a formula.

1. His daily spending money is £50. Any underspend is carried over to the following day.

2. His daily budget for food is £50. If he does not spend the entire £50 the underspend is added to his spending money the following day. Example, he spends £14 on monday so £36 is added to the spending money on tuesday giving a total of £86.

3. His day trip budget is £200. Any underspend is added to the spending money on that particular day.

I need a formula to calculate the spending money available every day. I also need to calculate the total amount spent daily. I have tried a couple of ways but the formulas are circular so dont work...

Formation of spreadsheet:

Please Login or Register  to view this content.

If anyone can help I would appreciate it.

Thanks

Ian

[Rahbee]

How many days does the money for 'Spending Money', 'Food', and 'Day Trip' rollover (i.e., does it rollover indefinitely?)

[Pluribus]

The spending money rolls over daily, food rolls over to the following days spending money and the day trip rolls over into the same days spending money. I hope that makes sense...!

[Rahbee]

Pluribus:

I am sorry I was not clear with my question. When is the end of the fiscal period for this budget? Is it every week, month, year, etc?

This is necessary to determine how to best create a solution (i.e., is this for tracking money over the course of a week or month..so on...)

[SPNesteby]

Pluribis

Don't know if this is what you're looking for. This is my 1st shot at doing something like this. Anyway, It's color-coordinated for ease of reference.

Notes:
1. In the sheet I created, the 1st day spending money and rollover total is a bit awkward if left alone, as it really isn't 450(It's only 250 if he doesn't spend a dime). It works itself out if you start filling in values for day 1 though, I believe.
2. In the following days, the new "Budgeted" amount isn't always 50 for spending, as it'll change to the "current day spend," which is $50, plus any rollovers from the previous day.

Hopefully this might give you a start, if any of this is wrong. I'm a bit tired myself, and it's the best my fatigued, excel amateur brain can come up with.

HTH