LinkBack URL
About LinkBacks
Hello,
I am using Circular Referencing to calculate a few values and it is giving me blatantly incorrect values. See attached workbook - Book1.xlsx. Any thoughts?
Thanks!
Hello,
I am using Circular Referencing to calculate a few values and it is giving me blatantly incorrect values. See attached workbook - Book1.xlsx. Any thoughts?
Thanks!
What does "blatantly incorrect" mean? It suggests to me that you must have some idea of what the "correct" answer should be.
When I need to understand an iterative calculation, I like to program it first in a non-circular way so that I can fully understand and appreciate the behavior of the algorithm. One way I do this is illustrated in the attachment. Set up the initial values in one column. In the adjacent column, use formulas to calculate the next iteration based on the previous column. Then copy across (See columns K:AE). Note that, over 20 iterations, all we are seeing is an oscillation. If you set your max iterations to an odd number, you should see the same oscillation in your circular reference cells.
As one who does not know what "a correct" answer would look like, I can only say that your algorithm appears to be doing exactly what you are telling it to do -- oscillate between two values. I would suggest that you need to go back to the "pre-Excel programming" stage of this and think through the quantity you are trying to calculate and formulate a better algorithm for it.
Originally Posted by shg
I'm sorry if I wasn't sufficiently clear in what I need. I am creating a balance sheet. I have a lot of other values on the sheet. I have "summarized" them in my hard coded values (colored blue in the file linked below). These values cannot change. In addition, there is no way to iterate like you have done in your file. My problem is that, according to accounting rules, Assets = Liabilities + Stock Holder's Equity. So for each year, I need to have some sort of "balancer". This is where Circular References come in.
Unless I am missing something, I do not see a way to program the non-circular way. I would much prefer this if it is possible though.
Book1.xlsx
It looks like you trying to calculate Equity, which you are calling the "balancer". Is that correct?
If so, you should only need the "balancer"/equity calculation in your Liability section, not the Asset section.
I can write you a formula to do that, if that's what you want : )
Lol I'll just post a dumbed down version of my file. See Sheet 2.
Book1.xlsx
I pointed that "iteration" out as a way to analyze the algorithm. It may not work in the final spreadsheet, but it seems like a useful way to analyze the circular analysis during development of the spreadsheet.These values cannot change. In addition, there is no way to iterate like you have done in your file.
Is it really as difficult as you are making it out? I do not understand accounting, but, simple algebra suggests that, if I know assets and liabilities, and equity is unkown, equity=assets-liabilities. Circular logic is not even a part of the analysis. D8 is the formula =D$7-D$16 (note absolute reference on row, or use =D$22, since that is already calculating this difference), then copy to E8 and D17:E17. However, you are the expert on accounting, and I am not, so you would have to decide if it is really that simple or if I am not understanding how this should work.My problem is that, according to accounting rules, Assets = Liabilities + Stock Holder's Equity.
One way or another, you need to apply what you know of proper accounting practices to develop a suitable algorithm. This might be a question to put to more experienced accountants rather than a bunch of nobodies on an Excel forum. How do other accountants perform this kind of "balancing"?
I think I see what you are trying to do is kind of reverse calculate a figure. That "balancer" is a calculated figure that when added to your other assets, will give you a correct "total assets" figure. Is that correct? If so, which numbers are known figures? You cannot calculate your "balancer" using any formula that evaluates to a CIRCULAR REFERENCE.
Exactly! All numbers that I have given are know except for the "balancer" lines. Why can I not use circular references here? In theory, that is what should be happening.
You are using a formula for TOTAL ASSETS and that total sums specific subtotals, so it isn't a KNOWN value. When you use the cell for TOTAL ASSETS in your formula for the "balancer", the formula for it is causing a circular reference (which should be telling you that you have a problem!). Do you under stand what a circular reference is? You are using your calculated TOTAL to calculate another figure ("balancer").
You need to independently come up with your TOTAL ASSETS figure or come up with another way to find your "balancer".
Last edited by candybg; 01-21-2016 at 01:21 PM.
I understand how a circular reference works, at least I thought I did. I have used them before. See Sheet 3.
Book1.xlsx
Here's a simplified example of what you are currently doing:
1 + 2 + 7 = 10
The total of 10 is a formula adding 1,2,7.
If these figures are in the first few cells in col A of a spreadsheet, the formula in A4 could be =SUM(A1.A3) or =SUM(A1,A2,A3)
Lets say we are missing the A2 value (of 2) and want to calculate it. If we delete the "2" from cell A2, then the total formula would only show "8" not "10". How could you calculate the value of that cell without knowing what TOTAL you should have? You need an alternative method to calculate it, *if* the TOTAL is a formula, not a known (typed) value.
Does this help?
I see. Thank you clearing that up. Do you have any suggestions for an alternative?
Perhaps it is oversimplified, but the obvious solution to candybg's simplification is algebra.
a+b+c=d
If I am given a, c, and d and need to find b, then simply solve for b
b=d-a-c
Is there more to the problem than that?
You have to start with the "knowns" - do you KNOW the TOTAL ASSETS without summing the individual parts? Do you have a report to run straight out of your accounting system that will give it to you? Otherwise you will have to find another way to get the total for FEDERAL FUNDS SOLD. By the way, is the FEDERAL FUNDS SOLD under Liabilities the same figure?
MrShorty:
I do not believe that is correct. There are two equations and two unknowns. a+b=c (asset side) ; x+y=z (L&SE side). b only shows when z>c and y only shows when c>z.
Candybg:
I do not "know" any values. These are all projections. Theoretically, one Fed Funds side should be zero when the other Fed Funds side is acting, hence the IF statements
Well, yes and no. Using a spreadsheet to get the total (d), then you can't solve for b when your total changes each time you put a number in there. That's what is causing the circular reference. You need to determine your total without a formula and then you can solve for b.
Then I can't see a way to "solve" for your "balancer" because every change in that number would affect your calculated total. There has to be an independent way to calculate it (or total it).
That's what I conclude as well. Time to goal seek! Thanks for your help
I think I can explain why your examples don't work.
To simplify your spreadsheet formulas in words:
You want to calculate your liability "balancer" amount (b) - and are asking excel to comparing the sum of abc (assets) to the sum of xyz (liabilities plus equity). If the sum of abc is smaller than xyz, you want to add the balancer figure to it. So the first time it compares assets to liabilities/eq, assets are smaller by, say, 1000 so you want to change "b" from zero to 1000. As soon as you do this, the sum of assets changes (upwards by 1000) so now assets equals liab/eq., so your formula says to use a zero.
I think there may be a way to do this - I'll get back to you tomorrow
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks