Circular References
Info
This functionality is only supported in Calculation Engine v3.
The Optimiser functionality can be used as an option to handle Circular References in Value Driver Models.
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.