Avoiding Circular References (Excel 98/2000)

Topic

New Reply

I have to build a budgetary spreadsheet that will be updated throughout the year as actual spend becomes known.

Here’s the problem. Lets say I have $12,000 available in a particular category that I account for on a monthly basis. Before the year starts I have $12,000/12 ($1,000) per month in the appropriate cells across the sheet.
After 3 months (lets say), I’ve spent $3,300. I still have to account for $12,000 at year end, so my revised monthly spend for the remaining 9 months is $12,000/12 (baseline) minus 1/9 the overspend in the first 3 months ($33.33 per month). Also, the over/under spend divisor has to decrease as time goes by – so after 6 months I am dividing the overspend by 6 to apportion to the remaining months.

If I try to do this with formulas, I wind up with a circular reference as each cell (in future months) references to itself in a Total amount that has to be subtracted from the baseline total to generate the incremenat portion for future months. (My brain hurts even trying to explain the circularity).

I can’t be the first to want excel to do something like this, can I. Has anyone out there got any neat tricks (or macros etc.) that might take care of this.

Going round in circles

Keith

Reply | Quote

Replies

See if the attachment does what you want.

Reply | Quote

John

Close!! I’d like to be able to see the future months available however, and putting zero in the future spends doesn’t add up. See my attachment as a purely graphical explanation.

Keith

Reply | Quote

[Edited, one day I’ll learn to say what I mean.]

If you don’t zero the future budgets, they incorporate future months as zero spends, which accelerates the remaining budget. I had thought that through before but didn’t know what you were looking for. This is fixed in my earlier attachment by replacing the formula in cell D4 with this, copy right to end of year.

=IF(ISNUMBER(C5),$A$2/12+($A$2*MONTH(C3)/12-SUM($C$5:C5))/(12-MONTH(C3)),C4)

Reply | Quote

John

Closer still – however, there’s still an error in the math. The attachment is your modified sheet, with a yearly total added. I’ve underspent by $600 in the first 4 months and the annual budget total shows 12,666. This figure should remain at 12,000. I’d love to be able to create money this way but sadly it doesn’t happen.

Reply | Quote

Hi Keith,

The problem is in your formula in O4. What your formula is doing is adding up the progressively adjusted monthly averages, which can’t be expected to equal the annual budget. For example, if you spent the whole $12,000 budget in the 1st month, your formula would give you only $1,000.
To keep O4 the same as the annual budget you only need:
=A2
Alternatively, to show how much budget is remaining, you need:
=A2-O5

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Keith,

Look at this method. I use it a lot in my job. It uses this formula:

[Budgeted Amount – Amount Spent To Date / Remaining number of months]

Just replace the formula each month with the actual spending and the subsequent months will
average out the remaining available balance.

HTH
Ken

Reply | Quote

Keith, Macropod’s explanation is correct, and I have added a row to show you how “my version” works.

I’m assuming that we think like accountants (I can’t help it) so that we are only adjusting the budget months AFTER the most recent spending month.

Therefore, the proof that my formula works is that in the sum of actual expeditures to date plus the sum of the remaining future budget should always equal 12,000, which it does … in my tests so far.

Let me know if that is not the way you want the logic to work.

KJToo’s method is very nice and you should look at it, except that it leaves the remaining budget calculation in the spent row, which is odd to me (sorry, KJToo). I’m going to try to spend some time on his/her version, because I keep thinking there should be a way to simplify my formula.

Hope this is all helping you get where you need to be.

Reply | Quote

Thanks to all for helping untangle my tangled brain!

Reply | Quote

Having looked closely at the revised sheet, I find that I can now get the formula to work in a single line, which was my original goal. I named $A$2 as “budget” and $C$4 as “month1” just for clarification and modified the formula in all but the first cell to be

=IF(ISNUMBER(C4),Budget/12+(Budget*MONTH(C3)/12-SUM(month1:C4))/(12-MONTH(C3)),C4)

This avoids the circular reference by building the sum spent each month throughout the chart. As real $$ are known, I just overwrite the formula with the hard number. Incidentally, the final sum in O5 now works and always shows $12,000.

I guess I could also make this work on a weekly budget, I’d just have to have a hidden row with week numbers in it as there’s no Excel formula for getting week numbers like the “MONTH” formula we use here. Easy enough to do once you work out the logic.

Again – many thanks to all loungers for the assistance.

Reply | Quote

Week numbers are easily derived using formula, see also http://www.cpearson.com/excel/weeknum.htm%5B/url%5D.

Reply | Quote

Hi Keith,

For a one-line solution on row 5, in which you overwrite the formula as each month’s results are known, you could use:
=Budget/12
in C5,
=(Budget-SUM($C$5:C$5))/(12-COUNT($C$5:C$5))
in D5 and copy this formula across to N5.

Alternatively, if you could be sure that B5 won’t have a number in it, you could
=(Budget-SUM($B$5:B$5))/(12-COUNT($B$5:B$5))
in C5 and copy this formula across to N5.

Regarding your musing about the use of week numbers, if your report is going to remain month-based, you’ll have some issues to work through where the month end doesn’t correspond with a week end. For example, do you round up/down, and what about the 365th/366th day?

Another approach might be to base your pro-rata monthly budgets on the number of days in each month. To do this, you could use:
=(Budget-SUM($B$5:B$5))/(DATE(YEAR(C3)+1,1,1)-C3)*(DATE(YEAR(C3),MONTH(C3)+1,1)-C3)
in C5 and copy this formula across to N5. Also, to make the month dates in row 3 work correctly with this or a weeks-based formula in leap years, you could put the required 1 January date in C3 (as you have now), and the formula:
=DATE(YEAR(C3),MONTH(C3)+1,1)
into D3 and copy this across to N3.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote