OpenboxKB Archive - Page 10 of 11

Create or change a calculation

Iain Morrow on 20 Oct 2022

Each calculation (or input) in the model is represented by a box in the main part of the screen.

Inputs are in yellow, and calculations are on a white background.

The icons in the lower part of each box show you more information about each item. The balance for example has a small balance icon, whereas “this is a calculation” does not. “This is a constant input” has a dot showing that it is constant, while “This is an input” has a line showing that it is a series that varies over time.

Calculations can either be standard calculations or “Balances”. A balance is a balance sheet item or a stock of something. For example, a company’s cash balance, or its inventory, would be stocks.

Standard calculations are just that: they take some input values and calculate a result. Balances work differently. They take an initial value (an opening balance) and track the flows into and out of it. Those flows add to or subtract from the initial balance. For example, with a bank account, the initial value would be the opening account balance, and the flows would be payments into and out of the account, such as purchases of assets

To decide whether something is a balance, just ask the question: “Would this appear on a balance sheet or inventory”? If the answer is yes, it is a balance. When converting to Excel, Openbox inserts standard corkscrew formulas for each balance.

Creating a standard calculation in Openbox is much like writing a formula in Excel. Simply select the box in the ‘calculation’ window that represents the item you want to write a formula for, then either click in the formula bar at the top of the screen or press F2. Then type the formula you want.

Openbox recognises standard Excel syntax, so you can type a formula that looks similar to one in Excel. The difference is that instead of cell references, there are references to the names of other calculations. It also recognises most common Excel function names, and when you start typing it will suggest from a list of available functions that include the letters you type. If you type ‘I’ for example, it will show you functions such as IF and SUMIF.

The names of the other calculations are shown in square brackets. When you type a left square bracket (‘[‘) you will see a dropdown list of all the other calculations already in the model. As you’ll see in the animation above, if you continue typing, Openbox will search for all items that include the text you type. So, typing ‘cash’ will find all items that contain the word cash or a word containing cash, such as ‘cash balance’, ‘opening cash’, and ‘cashflow’. When you find the one you want, click on it or press ‘Tab’ when only one item remains in the dropdown list.

If you type a name that does not exist already, Openbox will ask you if you want to create a calculation with that name, and whether it should be a constant or not. It will link it to the calculation you are editing. Initially, the calculation will be a placeholder, with no formula. But you can then edit it just like any other.

If you need to add a new placeholder directly, click on the “New Item” button, or press Ctrl+N (N for new). If you want to add a new balance placeholder, click the “New balance” button beside “New item”, or press Ctrl+B (B for balance).

If you want to see how the calculations and inputs will look as a spreadsheet, press F9 to see the preview.

Add inputs to the model

Iain Morrow on 20 Oct 2022

Adding simple inputs

For inputs which are constants, the easiest way to add them is just to type in the Openbox formula bar. The approach is just like in Excel: press F2 or click in the formula bar, type the input, and then click the tick or press Enter/ Return.

Input sheets

When looking at the preview, you might have spotted that there’s more than one input sheet.

What do these mean? Well, Openbox lets you separate different types of input. Some people like to have all inputs on a single sheet. And in the open box layout options you can choose that.

But other people like to separate constants, series and table inputs, because those inputs are naturally laid out very differently.

Let’s look at these input sheets.

First, InpC. This sheet contains only constant items – ones that don’t change over time. It comes with a scenario selector that allows you to specify different values and switch between them. These can be thought of as key levers that you can pull and change the model’s results.

Then, InpS. This is the Series sheet, which is where all inputs that vary over time go. Examples might include number of employees, inflation and so on.

Note that there are two of those sheets. Remember that a model can have two timelines. If you’re modelling the construction and operation of a new road, for example, you might have the construction on a monthly basis and the operations on a quarterly basis.

You might need inputs on a monhtly basis (say, capex for the construction period) and on a quarterly basis (say, number of cars that pay the road toll, per quarter, during operations). It would be confusing to have these on the same sheet,

Finally, there is InpT for table. Often you have a table of data that you want to include in your model, and that doesn’t fit well with series data or constant data. So open box puts it on a separate sheet, which means it’s clearer to see what’s going on.

And crucially. you can edit the inputs in any of these sheets directly – see below.

Adding more complex inputs

OK, so we have seen how to add simple inputs (a constant tax rate or date for example). Now, let’s see how to add some more complex inputs.

Maybe there’s a time series, for example, that we’ve got from the client and we want to put it into the model.

The simplest way to do this is just how you would do it in Excel – copy and paste it in. Let’s suppose we have this Excel file from the client. We select the input data, press copy, then switch to the preview. Select the corresponding cell of the input sheet and press Control V to paste. Done.

Add a new sheet

Iain Morrow on 20 Oct 2022

You can create a new sheet by clicking the ‘add group’ button or pressing Ctrl+G. It will appear beside the “Time” sheet. Double click on the name of the sheet to edit it.

Sheets can be moved around by dragging and dropping them, or by right clicking and selecting “Move”. For more on moving items, see Move things around in the model.

Sheets will appear in the Excel model in the same order as in the Openbox screen.

Add a new section

Iain Morrow on 20 Oct 2022

Select the calculation or calculations you want in the section. You can select several calculations using Ctrl+click, just like selecting several cells in Excel.

Then press Ctrl+Shift+G or click the “New section” button in the Ribbon.

The calculations will be put into a new section. Double click on the name of the new section to edit it.

Sections can be put inside one another, so you can have sections, sub-sections, sub-sub-sections and so on.

If you drag a section out, between two sheets, it becomes a sheet itself. You can also do this by right clicking, selecting “Move” then “(make into sheet)”.

If you drag a sheet inside another sheet or section, it becomes a (sub)-section of that sheet or section.

For more on moving items around in your model, see Move things around in the model

Move things around in the model

Iain Morrow on 19 Oct 2022

In the main model view, you can see the overall structure, with sheets, sections, and calculations/ inputs within the sections.

This view is good for restructuring the model – moving things around to be where they should be. There are three ways to do this.

First of all, you can drag and drop calculations, sections and even whole sheets. If you move a sheet into another sheet, the first sheet becomes a section. And a section that you move out, becomes a sheet.

Second, you can use arrow keys. Ctrl + up/down/left/right moves an item one step in that direction. When moving sections, Ctrl+Shift+left/right moves them out to become a sheet. When moving sheets, Ctrl+Shift+left/right moves the sheet into the neighbouring sheet as a section.

Thirdly, you can use the right click menu. There is a “Move” menu in the right click menu which shows every sheet and section in the model. This can be handy if you are moving items a long way, and dragging might be slow or awkward.

If you move something by mistake, or to the wrong place, just press Undo (Ctrl+Z).

Add a component/ module to my model

Iain Morrow on 19 Oct 2022

While every financial model is unique, there are standard parts that appear in many models. For example, most financial models will have a fixed asset and depreciation section. Rather than rewriting each section each time, Openbox allows you to save common model parts, or components, and import them into new models. That way, you can focus on the more bespoke parts of the model.

What is a component?

A component is simply part of a model that might be used again in the future. There are two types: a report component and a calculation component.

A report component is just a list of the items that a report should contain, and the order they should be in. It does not say anything about how each item should be calculated.

A calculation component contains formulas, and possibly inputs, but unlike a full model does not have any time settings or reports. You can think of it as a part of a model that calculates something useful.

Openbox saves calculation components as OBC files, and report components as OBR files, to distinguish them from OBZ files which are full models.

How do I get a component into my model?

Components are just files that can be stored on your PC or in a file sharing system such as Dropbox. You can select them by clicking the “Import Component” button in the Ribbon.

You will then see a window allowing you to select any OBC file on your computer. Note that when you click on one, you will see a preview of it on the right-hand side, as well as a short description written by the person who made the component. If you move your mouse over the preview, you will see the formulas for each item.

You can also ask Openbox to suggest an appropriate component. Right click on any placeholder and then click “Suggest”. Openbox will look for a component that calculates a result with the same name as the placeholder. For example, if you right click on a “Revenue” placeholder, it will suggest all components that calculate revenue. Just click on the one you want.

Note that suggestions are only for components in your Openbox library.

Merging

Once you have selected a component, it needs to be connected to the rest of the model. There are two steps to this process.

First, you need to tell Openbox which calculations in the component connect to calculations already in the model. For example, if you’re importing a component to calculate “Revenue“, you want the “Revenue” calculation in the component to replace the “Revenue” placeholder or calculation that’s already in the model.

Openbox will ask you how you want to do this, in the following screen:

This screen allows you to connect the component to the model in any way you like. Openbox suggests, first of all, connecting calculations of the same name. But you can override this, by clicking the dropdown beside any calculation you are importing and selecting another item. In the example above, we’re connecting “Revenue” to “Solar revenue” in the model manually. But Openbox has automatically identified that “Periods per year” needs to be connected.

The two calculations will then be replaced by a single calculation, which is linked to the model and the rest of the component.

If one of these items is a placeholder, there is no more to do here. But sometimes both items have a formula. In this case, Openbox will ask you which formula you want to keep in the merged item, as shown in the example below.

Add a flag, index or discount factor

Iain Morrow on 19 Oct 2022

Openbox makes it easy to add pre-built components to your model, so you can include whole sections with a few clicks. Some parts of financial models are so frequently used that the ability to add them is built into Openbox: flags, indexes and discount rates.

Flags

When you think about the things that models represent, like projects or companies, they typically go through different stages. A project for example will go through a construction period, and then an operational period, with possibly a post-operational/ clean-up period afterwards. Corporate models can have ‘historic’ and ‘forecast’ periods. There can also be key events, such as the drawdown of key debt instruments, or the project’s start of operations, or the date at which a key contract (such as a PPA) ends.

A common way to manage these stages and events is through flags – calculations that give the value 1 at the date of the key event, or during the key period, and zero otherwise.

Openbox makes it easy to add flags automatically. Click ‘Insert’, then the ‘Flags’ dropdown, then ‘<new flag>’.

Openbox will ask you what type of flag you want. There are five basic types:

On a date. A flag that triggers on only one date e.g. a drawdown date or end of life date
Until a specified date. A flag that is active up to a given date e.g. an asset lifetime flag.
After a specified date A flag that activates after some date – e.g. a commissioning date
Between two specified dates. A flag that starts inactive, then activates for a period, then is deactivated – e.g. an asset that is commissioned and then decommissioned
Periodically. A flag that is active once a month, quarter or other period – e.g. a payment period flag

Select the type, enter the relevant dates, name it, and click OK. Openbox then adds a new block of calculations to the model.

You can then apply that flag to any item by selecting the item, then clicking ‘Insert’ then the ‘Flags’ dropdown again. Click the name of the flag you want to apply. Openbox will modify the item’s formula to include the flag.

Indices and discounting

Models often include indices – factors showing how much a price or payment grows over time, relative to a base value. A typical case would be inflation.

Openbox lets you add indices easily. Click ‘Insert’ then ‘Indexes’, then ‘Add new…’. .

You will be asked for the key dates, and whether you want to calculate the index at the start, beginning or end of the period. Openbox will then insert the necessary calculations.

Discounting works in a similar way.

To apply an index or discount factor, select the item you want to apply it to, then click ‘indexation’ or ‘discount’ and the name of the index or discount factor you want.

Deal with units marked in red

Iain Morrow on 19 Oct 2022

Openbox checks all units in the model, so it can warn you about formulas which make no sense because they combine inputs in a way that “adds apples and oranges”. For example, there might be two calculations in the model, one in Euros and one in US dollars. These should not be combined without applying an exchange rate.

Units are calculated for each formula, based on the calculations that feed into it. For example, if a calculation takes a number in Euros, and divides it by a number in tonnes, the result must have units of “Euros per tonne”. Openbox will warn you if this is not the case by highlighting the units in red.

The first thing to do if the units are shown in red is to check that the calculation does make sense. For instance, have you applied all necessary conversion factors and exchange rates?

You have three options for fixing units in red:

Correct the units, if you decide they are wrong
Tell Openbox to try and correct them itself by clicking the units text, then clicking “Use expected”
Tell Openbox to stop flagging this unit as wrong. Click the units text and then tick the “Ignore issues” box, then click “OK”.

You can also simply ignore the red text. It will not stop the model being built, but it should be treated as a warning, so we do not recommend this approach.

Change the units for calculations

Iain Morrow on 19 Oct 2022

Each box on the diagram shows the units of that item, centred in the lower half of the box. While units are optional, they are highly recommended because they are another way of avoiding model errors. They allow Openbox to warn you about equations which make no sense because they combine inputs in a way that “adds apples and oranges”. For example, there might be two calculations in the model, one in Euros and one in US dollars. These should not be combined without applying an exchange rate.

Move your mouse over the units text to see a description of what the problem is. In this case, it would be something like “Expected units are EUR per tonne, but actual units are EUR”.

If you double click on red units text, Openbox will bring up a list of possible units.

You can also click “Use expected” at this point to have Openbox set them automatically to the correct value.

This is not a complete guarantee that a formula will be correct – there are other types of error that can occur. But it does prevent many common ones.

Setting the units for one calculation

To set units, just click the units text on each item, or press Ctrl+Shift+U. You can use any text values you wish for units, or pick one from the list of those already in the model.

Conversely, if you prefer not to enter the units yourself, you can ask Openbox to work them out for you using the ‘Auto Units’ menu. Just set the units for all inputs, and then press the ‘Units – Auto Units’ menu. Openbox will set the units for everything that it can.

Default units

Each model has a ‘default’ unit, which will be applied to all new calculations when they are created. Usually, this is the currency of the model.

You can also apply the default unit to any calculations in the model that do not have units, or just to the selected calculations. Click ‘Units’, then ‘Apply Default to Selection’ or ‘Apply Default to Entire Model’.

Managing units

Often you want to review all the units in a model and change, merge or delete them. To do this, click ‘Units’ then ‘Manage units’.

You will see a list of all the units used in the model (except a few built-in units such as ‘%’). The default unit will be highlighted in bold.

You can rename any unit by clicking on it and editing the name. This will be applied everywhere in the model that that unit is used. Note that you can change the name to the same name as an existing unit and this will effectively merge those units. If you have, for example, USD 000s and USD000s in your model, and you want to standardise on USD 000s, change the USD000s unit name to USD 000s.

You can also delete units by ticking them and pressing the ‘Delete’ button. Any calculations with those units will now have their units set to ‘<none>’.

Finally, you can change the model default unit by ticking it and pressing the ‘Default’ button.

Create an Excel model from your Openbox model

Iain Morrow on 19 Oct 2022

Click “Spreadsheet” then “Build model”, or press Ctrl+Shift+F9.

Openbox will then create an Excel model that does the same calculations as the model you have just created. Depending on the size of the model to be created, this may take a few seconds or several minutes.

During the process, it will run the standard validation checks, and you may see one or more warning windows. For more on validation, see this page: What checks does Openbox do as part of validation?.

You will also be asked for a filename for the Excel model. By default, Openbox puts it beside the Openbox OBZ file, and gives it the same name, just with a different extension (e.g. xlsb). But you can change that if you wish.

Once the model has been created in Excel, it is a completely standard Excel model, with standard formulas. It can be shared, modified, and used just like an Excel model created by hand. There is no need for anyone except the person who created it to have Openbox.

You can change the type of model that is created (xlsx/ xlsm/ xlsb) by clicking “Help and settings” then “Openbox settings” in the Openbox window.

Select the file format you want from the dropdown, then click “OK”.