|
CORKSCREW Function
The CORKSCREW function provides a method of doing simple roll-forward calculations with Dynamic Arrays without using circular references and Excel iterative calculations.
These calculations start with an opening balance, add positive and negative flows to arrive at a closing balance, which then rolls forward to become the opening balance for the next column. The flows can be independent of or dependent on the opening balance. When dependent on the opening balance (for example an interest calculation) only simple arithmetic operations are allowed (multiply, divide, add, subtract, power).
Note: In Dynamic Array Excel corkscrew calculations can be done using ordinary (non-spilling) references. Corkscrew calculations involving dynamic array references create a circular reference that can be calculated using iteration.
CORKSCREW Syntax
CORKSCREW (Opening_Values, Op_Flow1, Op_Flow2 … [, Op_Flow12] )
Opening _Values
One or more opening values to use as the starting Opening Balance. Can be a range reference, constant or array.
Op_Flow1
Each Flow parameter can refer to a range, array or spill reference. By default, all the Flow parameters are added to the opening balance to create the closing balance for this column, which then becomes the opening balance for the next column.
The Op_Flow parameter can also be a single string arithmetic operator (“*”, “/” , “+”, “-“, “^” ) that defines an operation on the opening balance and the succeeding Flow.
Flows which are independent of the opening balance will usually be dynamic array references to calculations elsewhere in the workbook.
Flows which are in any way dependent on the opening balance in any column must be created using the method shown above.
CORKSCREW example
Two rows in B2:G3 give Sales and Costs derived from elsewhere in the model.
The initial opening balance is in A8 and the interest rate is in A9 (note that the interest rate could be a row that varies by year).
The CORKSCREW formula is = CORKSCREW (A8,”*”, A9,C2:G2, “-“ , C3:G3)
The “*” parameter causes the next parameter (A9=5%) to be used to multiply the opening balance by 5%.
The next parameter C2:G2 has no preceding operator so Sales will be added to the opening balance,
The next pair of parameters “-“, C3:G3 subtracts Costs.
The closing balance is calculated by summing the opening balance and the intervening rows, and is used as the opening balance for the next period,
.png)
|
