software

Is there a way to setup multiple formulas so they calculate in a specific
order?

I have a complicated financial model that we use for budgetting. After we
change assumptions, we get errors in our formulas. What we have to do, is
trace the cells back to the error (using the auditing tools) and delete the
formula calculating the error and then undo the deletion. Only trouble is,
you have to do that many times to get everything calculating properly.

Problem is created by the following: we are trying to calculate net income
after tax. We need to calulate interest income(expense) based on the monthly
cash position which in some cases is calculated on the cash position after
the tax payment. Only trouble is, tax is calculated on net income before
tax, which is after interest income. Understand the dilemma? I'm hoping
somebody has a solution they could suggest.

I see two possible solutions:

- use iteration (I do NOT recommend this, Excel Iterative calculations
should only be handled using a very long pole)

- 'unwind' the circular calculation by calculating interest on cash position
before tax, then calculating tax and cash position after tax etc.regards
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

quot;Excel GuRuquot; gt; wrote in message
...
gt; Is there a way to setup multiple formulas so they calculate in a specific
gt; order?
gt;
gt; I have a complicated financial model that we use for budgetting. After we
gt; change assumptions, we get errors in our formulas. What we have to do, is
gt; trace the cells back to the error (using the auditing tools) and delete
gt; the
gt; formula calculating the error and then undo the deletion. Only trouble
gt; is,
gt; you have to do that many times to get everything calculating properly.
gt;
gt; Problem is created by the following: we are trying to calculate net
gt; income
gt; after tax. We need to calulate interest income(expense) based on the
gt; monthly
gt; cash position which in some cases is calculated on the cash position after
gt; the tax payment. Only trouble is, tax is calculated on net income before
gt; tax, which is after interest income. Understand the dilemma? I'm hoping
gt; somebody has a solution they could suggest.
I am using iteration. The model doesn't work otherwise(lots of circular
references)

What is the problem with Iteration? Is there a source where I can read more
about it? I've found quot;Iterationquot; useful, although I don't know the drawbacks.

Also, I am trying a different form of calculation for the final month of the
year in which we assume we will pay the majority of the tax. I'm calculating
tax based on net income before interest for that month.

quot;Charles Williamsquot; wrote:

gt; I see two possible solutions:
gt;
gt; - use iteration (I do NOT recommend this, Excel Iterative calculations
gt; should only be handled using a very long pole)
gt;
gt; - 'unwind' the circular calculation by calculating interest on cash position
gt; before tax, then calculating tax and cash position after tax etc.
gt;
gt;
gt; regards
gt; Charles
gt; ______________________
gt; Decision Models
gt; FastExcel 2.2 Beta now available
gt; www.DecisionModels.com
gt;
gt; quot;Excel GuRuquot; gt; wrote in message
gt; ...
gt; gt; Is there a way to setup multiple formulas so they calculate in a specific
gt; gt; order?
gt; gt;
gt; gt; I have a complicated financial model that we use for budgetting. After we
gt; gt; change assumptions, we get errors in our formulas. What we have to do, is
gt; gt; trace the cells back to the error (using the auditing tools) and delete
gt; gt; the
gt; gt; formula calculating the error and then undo the deletion. Only trouble
gt; gt; is,
gt; gt; you have to do that many times to get everything calculating properly.
gt; gt;
gt; gt; Problem is created by the following: we are trying to calculate net
gt; gt; income
gt; gt; after tax. We need to calulate interest income(expense) based on the
gt; gt; monthly
gt; gt; cash position which in some cases is calculated on the cash position after
gt; gt; the tax payment. Only trouble is, tax is calculated on net income before
gt; gt; tax, which is after interest income. Understand the dilemma? I'm hoping
gt; gt; somebody has a solution they could suggest.
gt;
gt;
gt;

I have used User Defined Functions in the past in order to control the
order of a calculation and use iterative methods (eg constantly
reducing some value and adding it to some other variables as you go
round a loop, until the value gets to zero). I could never get the hang
of Excel's iteration.

Hope this helps.

PeteThe main problems with iteration a

- one intended circular reference masks all other unintended circular
references, so it is difficult to debug/validate the model unless you take
precautions.
- its slow

see
www.decisionmodels.com/calcsecretsk.htmCharles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

quot;Excel GuRuquot; gt; wrote in message
...
gt;I am using iteration. The model doesn't work otherwise(lots of circular
gt; references)
gt;
gt; What is the problem with Iteration? Is there a source where I can read
gt; more
gt; about it? I've found quot;Iterationquot; useful, although I don't know the
gt; drawbacks.
gt;
gt; Also, I am trying a different form of calculation for the final month of
gt; the
gt; year in which we assume we will pay the majority of the tax. I'm
gt; calculating
gt; tax based on net income before interest for that month.
gt;
gt; quot;Charles Williamsquot; wrote:
gt;
gt;gt; I see two possible solutions:
gt;gt;
gt;gt; - use iteration (I do NOT recommend this, Excel Iterative calculations
gt;gt; should only be handled using a very long pole)
gt;gt;
gt;gt; - 'unwind' the circular calculation by calculating interest on cash
gt;gt; position
gt;gt; before tax, then calculating tax and cash position after tax etc.
gt;gt;
gt;gt;
gt;gt; regards
gt;gt; Charles
gt;gt; ______________________
gt;gt; Decision Models
gt;gt; FastExcel 2.2 Beta now available
gt;gt; www.DecisionModels.com
gt;gt;
gt;gt; quot;Excel GuRuquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Is there a way to setup multiple formulas so they calculate in a
gt;gt; gt; specific
gt;gt; gt; order?
gt;gt; gt;
gt;gt; gt; I have a complicated financial model that we use for budgetting. After
gt;gt; gt; we
gt;gt; gt; change assumptions, we get errors in our formulas. What we have to do,
gt;gt; gt; is
gt;gt; gt; trace the cells back to the error (using the auditing tools) and delete
gt;gt; gt; the
gt;gt; gt; formula calculating the error and then undo the deletion. Only trouble
gt;gt; gt; is,
gt;gt; gt; you have to do that many times to get everything calculating properly.
gt;gt; gt;
gt;gt; gt; Problem is created by the following: we are trying to calculate net
gt;gt; gt; income
gt;gt; gt; after tax. We need to calulate interest income(expense) based on the
gt;gt; gt; monthly
gt;gt; gt; cash position which in some cases is calculated on the cash position
gt;gt; gt; after
gt;gt; gt; the tax payment. Only trouble is, tax is calculated on net income
gt;gt; gt; before
gt;gt; gt; tax, which is after interest income. Understand the dilemma? I'm
gt;gt; gt; hoping
gt;gt; gt; somebody has a solution they could suggest.
gt;gt;
gt;gt;
gt;gt;