Openbox

Find error

If you have an error cell in the preview, Openbox can help you find the source of it. Simply select the cell and press Ctrl+E (for ‘error’). 

Openbox will analyse the model and work back through the formulas until it finds the reason for it. For example if you click on a #DIV/0 error, Openbox will find the formula where the original division by zero happened.

Why is my calculation shown as grey in the Focus mode?

Sometimes when you go into Focus view, some of the calculations are shown as grey boxes, as shown in the example below.

This is because they are sub-totals. They are calculated as the sum of the items above them on a report. This means that you don’t need to specify the calculation explicitly. Openbox will work it out for you, based on how the report is arranged.

How do I get rid of the green “change” markings in my Excel model?

When you are working in Excel, you can ask Openbox to keep track of any changes, so they can be written back to the Openbox window. This means that each changed row will be marked with green highlighting in column A.

If you don’t want this marking, you can remove it, and prevent any more marks appearing, by taking these steps.

  1. In Excel click the “Openbox” tab in the Ribbon
  2. Make sure that “Auto mark” is not checked
  3. Click the “Unmark all changes” button
  4. Click “Yes” when asked if you are sure

All the green markings will be removed.

Example of a model with several timelines

This article shows you how to create a basic model with multiple timelines in Openbox.

  1. Open Excel
  2. Click “Openbox” then “New”
  3. Select “Standard project finance”
  4. Select “Quarters”
  5. Change the start date to January 2024 and financial close to February 2024
  6. Set the construction duration to 12 months and operations to 20 months.
  7. Click OK

When the Openbox window appears, press F9 to see the preview.

You’ll see a model with two time sheets, one for quarterly and one for monthly. There will be a copy of each report on a financial year basis, as well as on a quarterly basis.

The OBZ and Excel files are attached here.

OBZ file: multi timeline model

Excel file: multi timeline model

If you want to change the time settings, these articles show you how:

Error 2349 when installing or updating

Sometimes when installing Openbox, or updating it, you might see a message about an unexpected error or a file error, with the error code 2349.

This means that for some reason the installation file is damaged. Most likely, it failed to download correctly. We suggest you download a new copy from www.openboxmodels.com/download-page/ and try again.

If the issue persists, it may be because of anti-virus software. You may want to turn this off temporarily while you download and install.

What functions are available in Openbox?

Openbox recognizes 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 ‘F’ for example, it will show you functions such as IF and SUMIF. 

Here is a list of all the functions Openbox recognizes.

FUNCTIONS THAT WORK LIKE THOSE IN EXCEL

ABS

AND

AVERAGE

CEILING

COMBIN

CONCATENATE

CONVERT

COUNT

COUNTA

COUNTBLANK

DATE

DATEDIF

DAY

DAYS360

EDTAE

EOMONTH

EXP

FIND

FLOOR

GETPIVOTDATA

IFERROR

IFNA

INT

INTERCEPT

IPMT

IRR

ISBLANK

ISERR

ISERROR

ISNA

ISNUMBER

LEFT

LN

LOG

LOG10

MAX

MIN

MMULT

MOD

MONTH

NA

NORM.DIST

NORMINV

NOT

NPV

OR

PMT

POWER

PPMT

PRODUCT

RAND

RANDBETWEEN

RIGHT

ROUND

ROUNDDOWN

ROUNDUP

SLN

SLOPE

SUBTOTAL

SUM

TIM

WEBSERVICE

WEEKNUM

WEIBULL.DIST

XIRR

XNPV

YEAR

YEARFRAC

 

OPENBOX SPECIFIC FUNCTIONS FOR SELECTING SOMETHING

SWITCH

SWITCH(<value to switch on>, <value 1>, <result 1> <value 2>, <result 2>, …)

Match the value to switch on against the list of values, and return the corresponding result

CHOOSE

CHOOSE(<value to switch on>, <option 1>, <option 2>, …

Pick one of a number of values, based on the first value

SUMPRODUCT

SUMPRODUCT(<value 1>, <value 2>, …) – multiplies each element of value1 and value2 and returns the total. Often used with ALLVALUES e.g. SUMPRODUCT(ALLVALUES([Some flag]), ALLVALUES([Cash flow])) returns the total cashflow on the dates that the flag applies

INDEX

INDEX( <item>, <row number>, <column number>, <area number> ) – returns the value at the given location for the value

MATCH

MATCH( lookup value, lookup array, match type) – looks in the given array for a value, and returns the position in the array {example}

LOOKUP

LOOKUP(<line item>,<lookup value>,<lookup array>) – looks for the lookup value in the first array of the specified item, and returns the value for that item. Optionally, you can specify which array to look in

https://openboxmodels.com/documentation/lookup-function/ 

SELECT

SELECT(<line item>,<index of array to choose>,<array name> – look for the value in array name that is at the specific position (index) specified. The <array name> parameter is only required if there is more than one array

https://openboxmodels.com/documentation/select-function/

INTERSECT

INTERSECT(<item>,<row label>,<column label>) – choose a value from a table, specifying the row and column label

SUMIF and SUMIFS

SUMIF(<item>, <criteria>, <sum item>) – as for SUMIF in Excel, although you might find the ARRAYVALUES and ARRAYELEMENTS functions useful here. <example>

COUNTIF and COUNTIFS

COUNTIF(<item>,<criteria>,<item to count>) – as for COUNTIF in Excel

COUNTIFS(<item>,<criteria 1>, …) – counts the number of cells that match the given criteria, across several different cell range

OPENBOX SPECIFIC FUNCTIONS THAT RELATE TO TIME

SECONDARYAXIS

SECONDARYAXIS(<item>) – gets the values from the secondary axis version of this item

PRIMARYAXIS

PRIMARYAXIS(<item>) – gets the values from the primary axis version of this item

DELAY

DELAY(<item>, <length of delay>, < value before start> ) – returns the value of the given item a number of periods ago (based on length of delay). <value before start> is used when the delay would take you to before the start of the model. If you leave out <item>, the current item, that the formula is for, is used

PREVIOUSVALUE

PREVIOUSVALUE(<item>) – returns the previous value of the given item, or the previous value of the current item if no item is specified”. See https://openboxmodels.com/documentation/previousvalue-function/

NEXTVALUE

NEXTVALUE(<item>) – returns the next value of the given item, or the next value of the current item if no item is specified. Note this can easily lead to circularities. E.g. NEXTVALUE([Cashflow]) is the cashflow in the next

BEG

BEG(<balance name>) – returns the opening value of the balance. If you don’t specify the name, it returns the opening value of the balance it is is. https://openboxmodels.com/documentation/beg-function/

PREVIOUSVALUES

PREVIOUSVALUES() – returns the cumulative total of all values to the left”

PASTVALUES

PASTVALUES(<item>, <include current period>, <number of periods to include>) – historic values for the specified item. If Include Current Period=1, the current value will be included. If you don’t specify number of periods to include, all historic periods will be included

e.g. PASTVALUES([Cash flow],1,6) returns the cashflow for the 6 months leading up to and including this period

PASTMIN

PASTMIN(<item>, <include current period>, <number of periods to include>) – the minimum historic value for the specified item, looking over the specified periods. If Include Current Period=1, the current value will be included. If you don’t specify number of periods to include, all historic periods will be included

PASTMAX

PASTMAX(<item>, <include current period>, <number of periods to include>) – the maximum historic value for the specified item. If Include Current Period=1, the current value will be included. If you don’t specify number of periods to include, all historic periods will be included

PASTMEAN

PASTMEAN(<item>, <include current period>, <number of periods to include>) – the average historic value for the specified item. If Include Current Period=1, the current value will be included. If you don’t specify number of periods to include, all historic periods will be included

SUMOVER

SUMOVER(<item>,<array to sum over>) – – gets the total over all items in the named array, or over all arrays if you don’t name one.

MAXOVER

MAXOVER(<item>,<array to look for the maximum over>) – gets the maximum over all items in the named array, or over all arrays if you don’t name one

MINOVER

MINOVER(<item>,<array to look for the minimum over> – gets the minimum over all items in the named array, or over all arrays if you don’t name one )

MEANOVER

MEANOVER(<item>,<array to look for the average over> – gets the mean (average) over all items in the named array, or over all arrays if you don’t name one

FINALVALUE

FINALVALUE(<item>) – the value of the line item in the last time period

FUTUREVALUES

FUTUREVALUES(<item>, <include current period>, <number of periods to include>) – future values for the specified item. If Include Current Period=1, the current value will be included. If you don’t specify number of periods to include, all future periods will be included.

ALLVALUES

ALLVALUES(<item>,…) – the value of the specified line item(s) for all time periods https://openboxmodels.com/documentation/allvalues-function/

OPENBOX SPECIFIC FUNCTIONS RELATING TO ARRAYS

ARRAYVALUES

ARRAYVALUES(<item>, <array>) – returns all the values for the specified item, for the specified array, as a range. Useful with SUMIF

ELEMENTNAME

ELEMENTNAME(<array>)

Makes the function vary by the specified array, and returns the name of the element, one per row

OTHER FUNCTIONS

RECURSE

RECURSE(<arguments>) – applies a function to itself. Used only for creating Lambdas

LAMBDA

LAMBDA(<parameter list>,<function description>) – create a LAMBDA function to include in Excel

https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67

LABEL

LABEL(<line item>) – returns the name of the item e.g. LABEL([Revenue]) returns ‘Revenue’

How do I set a calculation to be on one timeline or another?

By putting it on a sheet with that timeline. 

Each sheet in Openbox has a timeline associated with it. You can see this by the letter at the top right of the sheet. In the image below, the “Ops” sheet is on a Quarterly basis, and the “Construction” sheet is on a Monthly basis. 

If no letter is shown, your model only has one timeline.

If you put a calculation on a sheet that has a monthly timeline, that calculation will be done monthly. If you move the calculation to another sheet with (say) a quarterly timeline, the calculation will now be done quarterly.

The easiest way to set a sheet’s timeline is to right click on it and choose “Put on secondary axis” or “Put on primary axis”. 

For inputs, you can set the timeline individually. Just right click on each input and choose “Flip to primary axis” or “Flip to secondary axis”.

How do I change the file that Openbox starts a new model with?

When you click “New” in Openbox, you get a window with a dropdown labelled “Choose template”. Each Openbox model starts with a template with some basic calculations. But suppose you always build models for one sector. Why not start with a template that has standard calculations for that sector?  You can create a new template and add it to this list as follows:

  1. Create a new Openbox model
  2. Add the calculations you want.
  3. Save it in your Openbox component library, in the main folder. This will usually be the “OBXComponents” directory in your Documents folder.
  4. Close and restart Excel

Now when you click “New” in Openbox, the model you created will be available as a starting template in that list.

What does the setting “show formula name on all rows” do or mean?

Suppose you have a calculation block that varies by an array. Maybe you have “Revenue by product”, and it shows five rows, one for each product. Some people like to show “Revenue” on each of those five rows, whereas others like to show it only in the top row, and in the rows below, just show the names of the products.

Openbox defaults to the first approach (show name on all rows) but you  can switch to the second by going to the “Layout” table of Openbox settings and unchecking “Show formula name on all rows”