Openbox

Sell my own components through the Openbox site

Iain Morrow on 04 Jan 2023

Openbox makes it easy to create your own components, either from Openbox or Excel models. Section 6d: Creating your own components in our course shows you how.

We offer the ability to sell your components through our website, as a service to our users. If you are interested in doing this, please click here to email us. You will need to sign our standard component seller agreement. This requires you to confirm things such as that the IP in the component is your own, that it doesn’t infringe anyone else’s rights, and that you accept that we take no responsibility, and accept no liability, for any components you sell.

“Triangle” roll out blocks

Iain Morrow on 23 Dec 2022

Example triangle layout

Often, when you have several cohorts, or rounds of capex, or multiple projects with different start dates, you end up with a “triangle” block in your model. This has one row for each cohort/ round of capex/ etc. and the costs start at different times for each row. 

Openbox can create this for you. The attached file shows a simple example where you have a number of assets that are in service at different times. Deprecation starts at the given time for each one. Try playing around with changing the asset life for each project, and see how the results in the Openbox preview window change.

Example triangle layout file – click to download

 

Displaying scenarios on charts

Iain Morrow on 14 Dec 2022

You can display charts in the Openbox view by clicking the chart icon at the bottom right of each line item, or by pressing F11.

By default, this only displays one line – the base case. But if you are using the “Constants/ Scenarios” layout option for Inputs (that is, you have InpC and InpS sheets), you can display several scenarios at once.

In the “View” menu, click “Scenarios”. 

Openbox will show you a list of the scenarios on the InpC sheet. Check the ones you want and click OK. The scenarios are those highlighted in the red box in the picture below.

Openbox will ask you if you want to rerun the model. Click “Yes”. It will then calculate the results for all selected scenarios in the background and display them on any charts you display.

You can also compare two scenarios – for example, budget and forecast. Just click the “Compare” button beside scenarios, and choose the ones you want. 

Why is my report repeated for everything in an array?

Iain Morrow on 13 Dec 2022

If a report contains any line item that varies by an array, Openbox will create a copy of that report for each item in the array.

Let’s see what this means with an example. Suppose you have a simple three financial statement model – income statement, balance sheet and cashflow. The first line on the income statement is revenue, calculated as [sales] * [price]. Let’s suppose that you track sales separately for each country, and you’ve created a “country” array with a list of the three countries you operate in.

Then Openbox will create three income statements for you, one per country. In many cases, this might be what you want. But sometimes, Openbox will create several reports for you when you are not expecting it. This is because something has an array that applies to it. 

The easiest way to find this is to look at the labels on one of the reports. Look for items that end with something in brackets, like this “Revenue (USA)”. The item in brackets is the item in the array, and so it implies that Revenue varies by some array.

To fix it, ensure that you consolidate across arrays (using SUMOVER, for example) for anything on a report.

How do I enter my license key?

Iain Morrow on 05 Dec 2022

  1. Open Excel
  2. Click the “Openbox” ribbon
  3. Click the “Licence” button
  4. Paste your key into the “Licence key” box
  5. Click “Activate online”
  6. Wait a few seconds for the key to be checked
  7. Once the key has been checked, close and restart Excel.

LOOKUP function

Iain Morrow on 02 Dec 2022

As you’ll see in the pages on arrays,  you can tell Openbox to repeat calculations by things like scenarios, locations or business units.

Sometimes, you will want to pick out a single one of these repeated calculations. LOOKUP (and its sister function SELECT) let you do this. The syntax is:

LOOKUP(<line item>, <value to lookup>, <array to look up>)

The <array to lookup> is only needed if the line item varies by more than one array.

EXAMPLE

Suppose you have a model with ‘Sales’ as an input, and this varies by location – so you might have sales in Europe, sales in USA and so on.

Suppose also that you have ‘Revenue’ calculated as ‘Sales’ * ‘Price’. Since ‘sales’ varies by location, ‘Revenue’ will as well. If you just want to look at revenue in Europe, you can use the LOOKUP function like this ‘LOOKUP([Revenue],’Europe’).

 

BEG function

Iain Morrow on 02 Dec 2022

If you refer to a balance in a formula, Openbox assumes you mean the closing balance.

So the formula [Senior debt balance]+[Junior debt balance] would give the total debt balance at the end of the period.

If you want to refer to an opening balance, use the BEG (for ‘beginning’) function.  In the closing balance formula, you can refer to the opening value for that balance by using BEG() with nothing in brackets. For example, BEG() + [Senior debt drawdowns] – [Senior debt repayments] could be the formula for the senior debt closing balance. Balance formulas must have BEG() in there, or Openbox will give a warning.

EXAMPLES

BEG([Senior debt]) is the senior debt balance at the start of the period.

SELECT function

Iain Morrow on 02 Dec 2022

Often, when you have something that varies by an array, you want to choose just one item. The LOOKUP function lets you choose by name, whereas SELECT lets you choose by position. With SELECT, you can say ‘give me the first/ second/ etc array item for this calculation block’, whatever they are called.

The syntax is ‘SELECT([Formula to look up over],[position])’.

EXAMPLE

Suppose you have a line item called “Revenue”, which varies by the array “Product”. You sell 5 products. You want to get the revenue for the 2nd product. This would be:

SELECT([Revenue], 2)

PREVIOUSVALUE and PREVIOUSVALUES functions

Iain Morrow on 02 Dec 2022

PREVIOUSVALUE

Gives the value of the item in the immediately preceding period – that is, lagged by one.

Note that PREVIOUSVALUE() – that is, with nothing in the brackets – means the previous value of the line item you are calculating. In Excel, this is a formula that refers to the cell immediately to its left.

EXAMPLE

PREVIOUSVALUE([Cash flow]) is the cashflow last month, if the model is on a monthly basis. In Excel the formula in say K10 would look something like “= J10”

PREVIOUSVALUES

Gives the total for the current calculation in all previous periods (not just one period period).

EXAMPLE

IF(PREVIOUSVALUES() >0, 0, [Construction period flag] would show zero up until the column where Construction period flag is 1, then zero afterwards.  So it would be 1 only in the first construction period.

In Excel the formula would look something like “= IF(SUM($J10:M10) > 0, 0, N8)” (assumes the formula is in cell N10 and the construction period flag is in row 8