How Can We Help?
< All Topics

Create or change a calculation

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.

Leave a Reply