Circular References
Although Excel supports Circular References and there are times when it must be used, Circular References in Excel are not recommended due to the possibility of errors throughout the spreadsheet. Under the hood, Excel basically iterates through a number of times, and when it gets to the end of the iterations, that is your solution. Akumen’s Driver Models do not support circular references, except for Prior Value Nodes.
Prior Value Nodes are used to handle Circular References in Value Driver Models.
Prior Value Nodes
Prior Value Nodes can also be used to, in effect, create a recirculation, as shown in the example below.
In this case we are not relying on iterations to give us the correct result, we are recirculating the charity back into the profit in the next time period.
At time t = 0, we have no profit, move the timeslider to t = 1 and we have the initial profit as in the example above using the optimiser. At time t = 2, the profit has circulated through and gives us our final after tax profit.
The calculations used in this are:
| Node | Description |
|---|---|
| Tax | A global node, used only as the tax rate for the profit calculations |
| Revenue | A fixed input node (though we could flex this through scenarios to do a what if on profit) |
| Other Expenses | Again, a fixed input node |
| Before Tax Profit | [Revenue] - [Other Expenses] - [Charity] |
| Profit Closing Balance | This is the prior value node - note there is no initialisation value for this for t = 0 |
| After Tax Profit | [Profit Closing Balance] * (1 - [Tax]) |
| Charity | [After Tax Profit] * 0.1 |
Note that in this example, Charity is calculated based on After Tax Profit, and it’s value is then used in Before Tax Profit.