Openbox

Function that is different in Excel

Iain Morrow on 25 Nov 2022

Some Excel functions have known issues where they give the wrong answer in certain rare circumstances.

One particular example is XIRR, which gives #NUM if there is no negative cashflow in the first period. That does not mean that there is no internal rate of return for the series of cashflows.

The preview window in Openbox uses an Excel simulator, rather than Excel itself, to calculate, and that simulator’s XIRR function gives a correct answer in the case where there is no negative cashflow in the first period. So in such cases, you may see a (correct) value in the preview, but when you build the model in Excel, you will see #NUM!

The solution here is to use the same workaround for XIRR as you would in an Excel model, e.g. force a small negative cashflow in the first period.

 

Corkscrew which is a sum of balances

Iain Morrow on 25 Nov 2022

A balance calculation is usually opening balance, plus inflows, minus outflows – the standard balance corkscrew. See Balance formulas for more.

It’s perfectly reasonable to add two balances together and that gives you a third balance. But it isn’t a corkscrew. Openbox will alert you to this and suggest not showing it as a corkscrew.

Autocorrect the BEG() function

Iain Morrow on 25 Nov 2022

A balance formula should include the BEG() function, as so. Sometimes it is accidentally set to BEG(<name of balance>). While this is technically not wrong, it makes the formula longer than needed and can cause issues if the name of the balance is ever changed.

Openbox therefore offers to correct BEG(<name of balance>) to just BEG() – which means the same thing – if it finds it.

Balance formulas

Iain Morrow on 25 Nov 2022

Openbox lets you specify whether a calculation is a balance (i.e. a corkscrew) or not. If it is a balance, then it will appear in the spreadsheet as an opening balance, plus or minus some flows, and a closing balance. This means that the formula needs to be structured in a certain way, or Openbox will give you a warning:

  1. It needs to include the BEG() function – this means the opening balance.
  2. The flows need to be added or subtracted from BEG()

For example the formula for cash might be:

BEG() + [Cash receipts] – [Cash outgoings]

On the other hand, the BEG() function does not make sense unless the calculation is a balance. Only balances have opening balances. So if you include it in another formula, Openbox will warn you about that too.

Show everything in the model?

Iain Morrow on 25 Nov 2022

To show everything on all sheets, click Ctrl+Shift+A (A for “All”). Or click the “View” menu, then the three dots and click “Show all“.

To show only the items on one sheet or section, right click that sheet or section and choose “Show all items“

 

Embedded Constants

Iain Morrow on 24 Nov 2022

Excel lets you put numbers into a formula, but this is rarely a good idea. To quote from the FAST standard:

“…Embedding commercial information, for example an inflation rate, is never a good idea, as both clarity of model assumptions and active parameterisation are lost.

However, not all embedded inputs are created equal. Embedding a 24 (for 24 hours per day), 12 (12 months a year), 1000 (dollars in $ 000s) is permissible, even sensible. “

Other standards and best practice guides take a similar approach.

Openbox therefore warns you about any formula that contains a number. You can choose to ignore the message.

Items with the same name

Iain Morrow on 24 Nov 2022

Sometimes it’s easy to accidentally add spaces, punctuation or other characters to the name of a calculation. This can cause confusion – are ‘Revenue’ and ‘Revenue ‘ different things?

Openbox will warn you about names that are similar in this way, and ask if you want to merge the calculations or remove one of them. You don’t have to – and sometimes, if you have just added say a % to a name, you want both  – but it can be confusing to leave them both in the model.

Openbox will also warn you if somehow there are two items with exactly the same name. It is designed to prevent this from happening, but as a fallback, it does check for duplicate names and warn before building.

You need to change the name of any duplicates before the model will build correctly.

Warning that some calculations vary by one array, but others don’t

Iain Morrow on 24 Nov 2022

Most of the time, Openbox works out automatically which arrays apply to which calculations. But sometimes, it is not clear what it should do.

This is particularly true for balances. If a balance has an opening balance input that varies by (say) region, then all inflows and outflows should also vary by region. If they do not, there is an inconsistency which needs to be resolved. 

Change the arrays for the opening balance and flows so they are consistent.

Adding new variables automatically

Iain Morrow on 24 Nov 2022

When you type a formula in Openbox, it can refer to calculations or inputs that aren’t in the model yet. But they need to be in the model, and so Openbox will offer to add them for you.

You can say no, but they will be added as placeholders at the next validation. Otherwise, there would be the equivalent of #REF errors in the Openbox model.

Openbox will also ask if you want these new calculations/ inputs to be series or constant items. If you tell it not to ask again, it will assume they are all series in future.