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. Akumen’s Driver Models do not support circular references, except for Prior Value Nodes.

There are two ways to handle Circular References in Value Driver Models. The first is to use the Optimiser. Under the hood, Excel basically iterates through a number of times, and when it gets to the end of the iterations, that’s your solution.

Optimiser

You can use the Optimiser to in effect perform circular calculations if there is no mathematical way of solving the problem using standard nodes (within the current time period). Take the example below.

There is a circular reference in that the donation to charity is based on the after tax profit, but the profit also includes the donation to charity as a component of revenue - other expenses - charity.

The design pattern in Driver Models looks as follows:

Lets step through this node by node:

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
Charity	This is an interesting node, in that we do not set a value. It is, however, required to exist to be used by the optimiser.
Initial Profit	`([Revenue]-[Other Expenses]-[Charity]) * (1 - [Tax])`
Calculated Charity	`[Initial Profit] * 0.1`
Profit (Second Iteration)	`([Revenue] - [Other Expenses] - [Calculated Charity (10% of Profit)]) * (1 - [Tax])`
Delta Profit	`([Initial Profit] - [Profit (Second Iteration)])^2`
Optimise Profit	`optimise(15, 20,[Delta Profit], 0, 9, [Charity], 0, 2000)`
Error	`optimiseerror([Optimise Profit])`
Final Charity	`optimiseinput([Optimise Profit], [Charity])`
Final Profit	`([Revenue]-[Other Expenses]-[Final Charity]) * (1 - [Tax])`

The idea is that we calculate an initial profit. Note that the entered charity can be any value - this value will not get used, but the node reference will be used by the optimiser. We then calculate charity, and the second iteration for profit based on this charity. Once we have the second iteration, we can calculate a delta. Note that we raise to the power of 2 to avoid issues with negative numbers.

This is where the optimiser comes in - we want the delta between the initial profit and the second iteration of profit to be 0, by modifying the charity. We can take a guess at the range the charity should be to set the lower and upper bounds. If we hit 0, we’ve got an optimal solution. We can double check that using the error node - this should also be 0. We can then use the optimiseinput calc to retrieve the input charity value, and finally calculate a final profit.

Notice that the Optimise node is highlighted red. Hovering over the node shows that the number of iterations defined (20) is not enough to solve to 9 decimal places. Given this is currency, we can change the optimise node calc to solve to 2 decimal places. Turning Automatic Evaluation on and off will continue to solve - you’ll notice at this point that the final profit changes slightly when this occurs. This is because there could be multiple solutions to the problem, resulting in slightly different results.

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.