Here are some new functions you can use, in addition to Excel functions.

SUMOVER(ALLVALUES([x])) gives the total over all time periods of [x]. For example SUMOVER(ALLVALUES([Revenue])) is total revenue over all time

INTERSECT([Table],[Row value],[Column value]) gives the value in [Table] in row [Row value] and column [Column value]

Here’s another new function: DELAY. DELAY([Item],[Length of delay],[Value before delay]) gives the value of [Item] from [length of delay] periods ago. [Value before delay] is where the delayed value would be before period 0. For example, DELAY([Capex],12,0) returns the value of capex from 12 periods ago.

Here’s another function: PASTVALUES. The syntax is PASTVALUES([Item],[Include current period],[Number of periods to include[). [Include current period] is 1 if you want to include it or 0 otherwise. The [Number of periods to include] argument is optional. If you leave it out, it includes all past periods. For example PASTVALUES([Revenue],1) gives the current value of revenue and all previous values. PASTVALUES([Revenue],0,6) gives the 6 previous values of revenue not including the current one.

Here is another function: FUTUREVALUES. It works just like PASTVALUES except it looks at future periods, not previous periods.

Here is another function: SELECT
Where a formula varies over one or more arrays, you sometimes want to choose just one of these items. The LOOKUP function lets you choose by name, whereas SELECT lets you choose by position. With SELECT, you can get the first/ second/ etc element of the formula, whatever they are called.

The syntax is SELECT([Formula to look up over],[position])

Here is another function: BEG
If you refer to a balance in a formula, Openbox assumes you mean the closing balance. So: [Senior debt balance]+[Junior debt balance] would give the total debt balance at the end of the period. If you want to refer to an opening balance, use the BEG (for [beginning’) function. For example BEG([Senior debt balance]) gives the senior debt balance at the start of the period. In the closing balance formula, you can refer to the opening value for that balance by using BEG() with nothing in brackets. For example, BEG() + [Senior debt drawdowns] – [Senior debt repayments] could be the formula for the senior debt closing balance.

Here is another function: LOOKUP
Line items in Openbox can be set to repeat for a list of say scenarios, locations or business units. See section 7 for more details. Sometimes, you will want to pick out a single one of these repeated calculations. For example, suppose you have a model with [Sales] as an input, and this varies by location – so you might have sales for Europe, sales in USA and so on. Suppose also that you have [Revenue] calculated as [Sales] * [Price].

Since [sales] varies by location, [Revenue] will as well. If you just want to look at revenue in Europe, you can use the LOOKUP function like this: LOOKUP([Revenue],”Europe”)

Another function: ALLVALUES
All the values of that particular item, in all periods. So SUM(ALLVALUES([Cash flow])) is total cashflow over the lifetime of the model – i.e. what would be in the row total.

PREVIOUSVALUE
Gives the value of the item in the immediately preceding period – that is, lagged by one. For example, PREVIOUSVALUE([Cash flow]) is the cashflow last month (if the model is on a monthly basis).

Note that PREVIOUSVALUE() – that is, with nothing in the brackets – means the previous value of the line item you are calculating. In Excel, this is a formula that refers to the cell immediately to its left.

Another functon: NEXTVALUE
As PREVIOUSVALUE, except this looks one cell to the right/ into the future. Note that this can easily lead to circularity so should be used with caution.

More functions: SUMOVER,MEANOVER,MINOVER,MAXOVER
Calculate the sum/ mean/ min/ max over an item that varies by an array. For example, suppose the model covers a number of business units, and [Revenue] is split by an array called “business unit”. Then to get total revenue, you type SUMOVER([Revenue],”business unit”).