Circular References

(1) For Numbers, a circular reference _is an error_ and so, it is rejected.

(2) I'm unaware of spreadsheets accepting circular references.

Yvan KOENIG (VALLAURIS, France) lundi 7 septembre 2009 13:43:11

Numbers does not allow circular references. As you already know, Excel will allow circular references if the number of iterations is set to limited. I would assume this could create problems if you have functions that require more iterations to complete than you are allowing. Your answers might be wrong. Also, being a preference setting, it applies to Excel as a whole so if you forget to change it back, your other spreadsheets will also do limited iterations.

Typically a spreadsheet that requires iterations would be designed with multiple rows of identical formulas. Each row would pull one or more values from the row above to do the next iteration. So 100 iterations would require 101 rows (including the initial state).

I built a financial appraisal spreadsheet that I couldn't crack without throwing in a few circular references. Numbers didn't like it, but the spreadsheet within Open Office handled it no trouble at all.

Your appraisal spreadsheet either required some iteration to get to an answer or there were errors in the spreadsheet. Circular references are, in my opinion, poor spreadsheet design. Most can be removed by doing some algebra beforehand. As an example,

P1 = Profit before paying bonuses
P2 = Profit after paying bonuses = P1 - B
B = Bonuses = X*P2 (bonuses are function of profit after bonuses)

If you put that straight into a spreadsheet you'll get a circular reference. If you do the algebra and rearrange it as

P1 = Profit before paying bonuses
P2 = P1/(1+X)
B = X * P2

then you have no circular reference.

I know it needs re-arranging, but my modelling skills are poor compared to my other limited talents. I will sit down and have another go at it. The calculation relates to land value with land value being a function of profit less costs when the cost of the land is also an allowable cost (if you see what I mean). Iterating your way out of a circular reference isn't what you might describe as best practice, but it sure is handy when your head aches from trying to untwist the formula and failing dismally!

If you provide the algebraic equation(s) I'll see what I can do to untwist them for you.

That's very kind. I will note the formulae down when I am in the office tomorrow.

Thanks.

I hope I have managed to set this out in a sensible fashion. Here goes....

A = Sales value = £amount, or $ if you prefer.
B = Costs = £amount
C = Interest on costs including land = (B+G) * 5%
D = Cost including land = B CG
E = Return excluding land = A-(B+C)
F = Developer's return on on cost = D*15%
G = Land value = E-F

Does that make any sense? You can see that there are a couple of circular references. Good luck and thanks.

Adam

Okay, here it goes:

A = given
B = given
C=5% A/(15%*(1+15%(11/5%)))
D=C*(1+5%)/5%
E=A-(B+C)
F=15%*D
G=A-B-C (115%(11/5%))

It would be much simpler looking if I had done all the math but I figured you would want to see where the 5%'s and 15%'s came into play.

To ensure these are correct, put the above formulas in one column and then have another column of your original formulas. Use the results of the formulas above to feed your original formulas. The values in each column should all match. I did that and it worked but you should do it too just in case.

Message was edited by: Badunit

Badunit, that is incredible and incredibly kind. It works just perfectly, but I completely fail to understand why.

How on earth do you arrive at an interest figure for costs that uses sales prices as its starting point?

I am a humble architect by training. What on earth do you do?

Thanks again.

Adam

It was all algebra. The nature of the equations didn't matter, it was a completely mechanical process to rearrange them. The hard part when solving multiple equations is to find a good starting point. In this case I noticed that the equations for D and C were similar, both had "B+G". I rearranged the equation for C in the form of (D+G) = (1/5%)*C and I dropped that into the equation for D.

D=B + C + G = C + (B+G) = C + (1/5%)*C = (1+1/5%)*C

Then I moved on to G. I replaced E and F with the equations you provided for them. I then replaced the D with my new equation for D (the one above) and was left with G as a function of C and two known values (A and B)

G= E - F = A - (B + C) -15%*D = A - B - C - 15% (1+1/5%)C = A - B - (1 15%(11/5%))C

I then went back to the original equation C=(B+G)*5% and replaced G with my new equation for G and rearranged it to get C= on one side. From there it was pretty much done.

So it didn't really matter what the equations referred to, it could have been rainfall in the Amazon basin or rocket thrust factors for a trip to Mars. The process was just substitutions and rearranging of the original equations.

I wish we could go back and correct typos in posts long after we post. I noticed too late that, in the explanation of how I developed the solution, I typed a D instead of a B in the equation (B+G)=(1/5%)*C

Remarkable. I have an A Level in pure maths with statistics, but I shall have to sit down somewhere quiet and read this several times over.

Thanks again. Top work.

Adam