Openbox

Can I bring a whole model from Excel to Openbox?

Iain Morrow on 06 Jan 2023

Openbox lets you bring in pieces of your existing Excel models by copying and pasting (Make a new component from an existing Excel model). But what if you want to bring in an entire sheet or even an entire model?

First, open the Excel model. Select the sheet or sheets you want to bring into Openbox. Then click “Openbox” and “Import selected sheet(s)”. 

Openbox needs full access to the model for this import, and so it has to be closed in Excel. It will be re-opened later.

The process will proceed much like the process for copying and pasting,. You will be asked:

  • What each column represents
  • Whether you want to revalidate the model after bringing in the sheet from Excel. You may wish to do this for small sheets although we recommend reviewing the imported sheet first.
  • Whether you want to merge the imported items with anything of the same name already in the model.

Once the sheet has been imported, it will have the same name as the sheet in Excel. So if you import “Construction”, you will see a new sheet called “Construction” in your Openbox model.

IMPORTANT: This only allows you to import calculation or input sheets. To import report sheets, follow the instructions here.

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)