Openbox performs a number of checks on each model before it is translated into a spreadsheet. They are designed to catch many of the most common errors in models. 

The checks run whenever you build a model. You can also force them to run by pressing “Validate” on the Spreadsheet menu, or pressing Shift+F9. 

Of course, this does not remove the need to check any results manually.

Validation warnings

If the validation process finds any issues, it will warn you in a pop up window. You have the option to ignore the issues, or to stop validation and fix them. If you choose to stop, the validation warnings will appear in a new window to the right of the main Openbox window, as shown below.

Example of validation window

Each row is clickable, and when clicked the main window will scroll to show where the issue is. 

You can show or hide the validation window by clicking “Spreadsheet” then “Show/ Hide issues”.

Let’s now look at the key validation checks that Openbox does.

1. Missing calculations

Sometimes, you can have a formula that refers to a calculation that doesn’t exist. Mostly, Openbox prevents this, adding and removing placeholders to keep the model consistent. However, it is possible to delete an item from the model, and then anything that depended on that item will have an invalid equation. This is the equivalent of a #REF! error in Excel. Openbox will warn you when deleting items, and will ask you if you want to add a placeholder, if it encounters this situation during validation.

2. Incorrect Units

Openbox highlights where your model has inconsistent units, unless you choose not to use units at all. 

Units are shown in red if they appear not to be consistent. This is only a warning and will not prevent Openbox building the model. But Openbox will warn you during validation. If you click ‘Cancel’ at this point, you will get a list of inconsistent units in the Validation issues window. See Deal with units marked in red for more information.

3. Embedded constants

Any numeric values in formulas should be made into separate inputs instead. This is a key source of model error.

The classic example is a tax formula of the form: ‘=B17 * 20%’ where 20% is the tax rate in operation at the time the model was created. If the tax rate ever changes, the formula will be wrong. The formula should be ‘= B17 * A12’, where A12 has the value of 20%.

Openbox enforces this by displaying a warning during validation if any formula contains a numeric constant. You can choose to ignore this 

However, Openbox does allow ‘0’ and ‘1’ as constants in a formula.

4. No repeated names

Each calculation in an Openbox model must have a unique name. You cannot have two calculations called ‘cash’ for example. This avoids errors caused by having two versions of the same calculation in a model. It also forces the modeller to be clear about exactly what an item in the model represents.

5. Circular references

Openbox will identify and report circular references in your model. It will show you the circular calculation, and ask if you want to break the circle at a particular point. See the article on circular references for more detail.

6. Too many headers

All Excel models that Openbox creates for you are based on an Excel template file. That file specifies the number of items that can be in the header rows in each sheet. For example, the standard “Gridlines template.xlsx” that ships with Openbox has four header rows, as can be seen in rows 2-5 of the spreadsheet below.

Anything in the ‘Headers’ section of the Time sheet will be put into the header rows. Openbox checks that there are the right number, and warns you if there are too many.

7. All necessary sections and time blocks

There are certain sections that every model should have: an inputs sheet, a time sheet and sections for model checks, alerts and constants. Openbox makes sure that they are included.

It also checks that the model has a start date, model period start and end and other standard time inputs and calculations.

8. Two calculations with very similar names

It’s easy, when creating a model, to add the same calculation twice by mistake. Openbox won’t let you give two calculations exactly the same name, but if you include an extra space or % or dash, it will not warn you right away. 

During validation, it will look for names which are the same except for spaces and symbols, and confirm with you that these are really distinct calculations. You will be given the option to combine them into one, if not.

9. Reports with same name as sheets

Every Openbox report, and every sheet in the main window, will ultimately become a sheet in an Excel model. So you can’t have a report called say “Income“, and an Openbox sheet called “Income” because they would both become an “Income” sheet in Excel. Openbox will warn about this and ask you to change one of the names.

10. Flags that aren’t on the time sheet

All flags, indices and discount rates should be on the time sheet. Openbox doesn’t enforce this while you are working on the model, but it will automatically move them during validation.

11. Copy/ paste blocks not on the optimisation sheet or invalid (different arrays)

In the same way that flags should be on the time sheet, copy/ paste blocks for resolving circularities must be on the optimisation sheet. Openbox will fix this automatically for you.

12. Balances with invalid or unusual formulas

Formulas for balances must include the BEG() function. Openbox will warn you if they won’t.

Balance formulas should also be BEG(), plus some inflows, minus some outflows, possibly with a flag. If they are not of that type, Openbox will warn you, and ask you if you are sure that the formula is correct.

13. Calculations on the input sheet

Separating inputs, calculations and outputs is part of modelling best practice. But it’s easy, when working on a model, to add a calculation to an input and forget to move it off the input sheet. Openbox will automatically move any calculations on the input sheet into “Unallocated“, so you will be notified to move them to the correct sheet or section. 

14. Unused items

All inputs and calculations should be used somewhere in the model – if not, they should be removed to avoid confusion. Openbox will give you a list of any unused items.

15. Empty sheets or sections

Openbox will offer to delete any empty sheets or sections.

16. Adding balances and non-balances

Openbox will warn you if a calculation adds a balance to something that is not a balance. While this is sometimes OK, it often indicates an issue with the model logic.

17. Functions that are applied to the wrong things

Some functions don’t make sense when applied to certain inputs. For example, trying to get the PREVIOUSVALUE of a constant, or using BEG to get the opening balance of something that isn’t a balance, or using an array function on something that doesn’t have arrays.

Openbox will warn you about all of these.

18. Problems with timelines

You can have more than one timeline in an Openbox model, but there are some things to watch for when building multi-timeline models. These include using calculations that are on (say) a quarterly basis in a monthly calculation.

19. Anything unallocated

The “Unallocated” sheet in the main view is intended as a temporary place to put calculations, before they are moved to the right sheet. In the final model, there should not be anything in “Unallocated“. Openbox will warn you if there is. It’s not a major issue while working on drafts, but should be fixed before final release.