Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula calculation
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula calculation
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 "Excel GuRu" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula calculation
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 "Iteration" 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. "Charles Williams" wrote: 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 "Excel GuRu" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula calculation
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. Pete |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula calculation
The 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 http://www.decisionmodels.com/calcsecretsk.htm Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com "Excel GuRu" wrote in message ... 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 "Iteration" 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. "Charles Williams" wrote: 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 "Excel GuRu" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
Again-revealing the contents of formula | Excel Worksheet Functions | |||
Initial Formula Calculation | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |