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.
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.
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.
All the green markings will be removed.
This article shows you how to create a basic model with multiple timelines in Openbox.
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:
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.
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.
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
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(<value to switch on>, <option 1>, <option 2>, …
Pick one of a number of values, based on the first value
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( <item>, <row number>, <column number>, <area number> ) – returns the value at the given location for the value
MATCH( lookup value, lookup array, match type) – looks in the given array for a value, and returns the position in the array {example}
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(<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(<item>,<row label>,<column label>) – choose a value from a table, specifying the row and column label
SUMIF(<item>, <criteria>, <sum item>) – as for SUMIF in Excel, although you might find the ARRAYVALUES and ARRAYELEMENTS functions useful here. <example>
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
SECONDARYAXIS(<item>) – gets the values from the secondary axis version of this item
PRIMARYAXIS(<item>) – gets the values from the primary axis version of this item
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(<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(<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(<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() – returns the cumulative total of all values to the left”
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(<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(<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(<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(<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(<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(<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(<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(<item>) – the value of the line item in the last time period
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(<item>,…) – the value of the specified line item(s) for all time periods https://openboxmodels.com/documentation/allvalues-function/
ARRAYVALUES(<item>, <array>) – returns all the values for the specified item, for the specified array, as a range. Useful with SUMIF
ELEMENTNAME(<array>)
Makes the function vary by the specified array, and returns the name of the element, one per row
RECURSE(<arguments>) – applies a function to itself. Used only for creating Lambdas
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(<line item>) – returns the name of the item e.g. LABEL([Revenue]) returns ‘Revenue’
No, this is not yet possible. It is on our product roadmap for the future, so watch this space.
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”.
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:
Now when you click “New” in Openbox, the model you created will be available as a starting template in that list.
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”
