Openbox

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.

Set missing units

Iain Morrow on 24 Nov 2022

When validating or building a model, Openbox checks for any calculations without units. If it finds any, it will offer to work out, for each one, what the units should be. For example, if there is a calculation which adds two others with units of $, Openbox will set the units to $.

If it cannot work out what the units are, either because the formula is too complex or does not make sense, it will leave them empty.

‘Constants on reports’ warning

Iain Morrow on 24 Nov 2022

This is very much a warning rather than a definite problem.

Openbox does allow constants on reports, but to avoid confusion it does not include them in any totals or sub-totals. This message simply reminds you of that.

‘Items on unallocated’ warning

Iain Morrow on 24 Nov 2022

Every Openbox model has a sheet called “Unallocated” on the far left of the main window. This is where new calculations go, and is a handy place to keep calculations that you are not yet sure where to put in the model.

But it is designed to be a temporary thing, used when drafting the model. In a finished model, every calculation should be in its right place and “Unallocated” should be empty. 

One way to think of it is as a “to do” or “to do later” list.

Openbox will warn you (unless you ask it not to) if there’s anything in the unallocated list when you validate or build.

 

Linking from a dashboard or other sheet to the Openbox part of a spreadsheet

Iain Morrow on 24 Nov 2022

Openbox lets you link from cells on a sheet in the Excel template file to something on a sheet that’s created by Openbox itself.

For example, you can have a “Dashboard” sheet in the Excel template file, which has a “Revenue” chart on it. You can tell Openbox to link that to the “Revenue” lines in the preview or built model, once they are created.

The way to do this is with the OBXValues function. In your Excel template file, put = OBXValues(<name of item>) in the cell where you want the first value to go e.g. = OBXValues(“Revenue”).

You only need to put it in one cell (the first time column –  usually column J) and Excel will automatically copy it to the right.

If you want to bring in a constant, put the OBXValues formula in the constants column (usually column F).

If your model uses Openbox arrays, you can tell Openbox which element of the array you want, by specifying it as the second argument to the OBXValues function. For example = OBXValues(“Revenue”, “USA”) will include the revenue from your US sales only. 

‘Named range not valid’ warning message

Iain Morrow on 24 Nov 2022

Openbox can add named ranges to your models, if you want, by ticking the appropriate option in the “Settings” window.

It tries to add a named range that has the same name you used for the calculation or input. In some cases, that isn’t possible because the name contains characters that aren’t valid in the name of a named range.  Excel Help says:

“The first character of a name must be a letter, an underscore character (_), or a backslash (\).
Remaining characters in the name can be letters, numbers, periods, and underscore characters.

You might want to change the name you use in Openbox and try again.

Adding an issues log

Iain Morrow on 11 Nov 2022

On the Openbox Ribbon in Excel there is a section for creating and adding to an issues log, as shown below.

This is intended to be a simple and flexible log for keeping track of live issues, when they have been cleared and who cleared them.

To add a log to any Excel model, click “Create log”. A new sheet called Issues will be added – this is taken from the Excel template so you can customise it with your own colours and fonts as you wish.

You can then add a new issue to the log by selecting the cell in the model that has the issue and clicking “Add issue”. A new row will be added to the log, with a link to the cell, a date, and you will be prompted for a description.

The “Show log” button simply jumps to the “Issues” sheet at any time.

Produce model documentation

Iain Morrow on 11 Nov 2022

Openbox can produce documentation for any model produced by it – even one you did not make yourself.

In the Openbox part of the Excel Ribbon, there are three buttons: Document in Excel, PPT and Word respectively.

The Document in Excel button will add a sheet to the model with a table listing all inputs and calculations, and their units and formula. 

The Document in Word button does the same, except it creates a table in a new Word document.

The Document in PPT button creates a new set of PowerPoint slides, one per calculation, showing the formula, precedents and dependents. These are hyperlinked and can be saved as a clickable PDF that allows you to explore the calculations in the model without Excel.

You can change the format of the PowerPoint slides created by editing the “documentation template.pptx” file in your OBXComponents folder.

What information does Openbox send to the Internet?

Iain Morrow on 11 Nov 2022

Openbox sends your licence key to our licensing server, at least once every 30 days, to check that your licence is still valid. No personal information is sent, only the key.

Openbox also sends anonymous telemetry data to allow us to diagnose any issues or bugs that arise. This includes a report on the type and location of any unexpected error. 

You can turn this off by unchecking the “Allow Openbox to report unexpected errors to us” box in the Openbox Settings window.

By default, Openbox won’t send your username with any error reports. If you want us to notify you when any bugs you report are fixed, check the “Include user name so we can tell you when the issue is fixed” box.

Turn on autosave?

Iain Morrow on 11 Nov 2022

Open the “Openbox settings” window and click “Other”. The fourth checkbox from the top allows you to turn on autosave, and set the frequency in minutes.

 

Note that this will save over the existing file, rather than create a new file for each version.

But if you save your files in a file sharing tool such as Dropbox or OneDrive, they will keep track of versions, allowing you to go back if you need to.