- Open Excel
- Click the “Openbox” ribbon
- Click the “Licence” button
- Paste your key into the “Licence key” box
- Click “Activate online”
- Wait a few seconds for the key to be checked
- Once the key has been checked, close and restart Excel.
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.
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’).
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.
BEG([Senior debt]) is the senior debt balance at the start of the period.
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])’.
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:
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.
PREVIOUSVALUE([Cash flow]) is the cashflow last month, if the model is on a monthly basis.
All the values of that particular line item, in all periods – the ‘whole row’.
You need to apply another function to turn those values into a single number. Most commonly, you’d use SUM or SUMPRODUCT.
SUM(ALLVALUES([Cash flow])) is total cashflow over the lifetime of the model – i.e. what would be in the row total.
SUMPRODUCT(ALLVALUES([Operational period flag]), ALLVALUES([Cashflow])) is the total cashflow in the operational period
In the “Openbox” ribbon in Excel, there is a “Check for new version” button. If you click it, Openbox will either tell you that you have the latest version, or tell you that there is an update and offer to install it.
Note that you need to be connected to the Internet to receive updates. If you have issues with receiving them, please check your connection and try again.
The short answer is that you don’t have to. Openbox will automatically convert inputs on calculations that are on (say) a monthly basis to (say) a quarterly basis. It will also convert calculations into the basis required by the reports.
Suppose you have a model that has monthly construction, quarterly operations and financial statements that are annual. You don’t need to do any time conversion yourself – Openbox will handle it for you.
It also respects whether it is working with balances or not. If you take a monthly balance and convert to quarterly, the quarterly figure is the last monthly figure in each quarter. If it’s not a balance, the quarterly figure is the sum of the monthly figures. Openbox takes this into account when consolidating.
Normally, each worksheet has a timeline across the top, and each column represents the same amount of time – a month or a quarter for example. But you can have two timelines on one sheet, where (for example) the first few columns represent months and the rest represent quarters. Openbox calls this a “hybrid” timeline.
To get it, click “Time” then “Settings” and check the “Hybrid timeline” box, as shown below.
This page shows the changes, including new features and bug fixes, in each version of Openbox, starting at v1.9.0. Changes are shown newest first (i.e. the newest version is always at the top).
- Formula help now displayed below formula bar at all times when editing. Includes help on function syntax and arrays
- Add “online help” button in formula help bar which takes you to the function help page on this site
- Add “unpin all charts” functionality
- Fix bug which caused duplicate labels for some constants on two timeline models
- Include copy paste block check in overall check
- Tidy up Equation charts
- Fix issue with financial close date calculation for new models
- Improved chart display in Focus Mode
- Improved file name suggestions for saving
- Fix issue where there are reports with different arrays, and the user chooses to put all reports on one page
- Fix issue where pasting beyond column IV in the preview
- Copied and pasted items in the main view are now put beside the original, not in ‘Unallocated’
- OBXValues function now correctly deals with array elements in the second argument
- Counterflow arrows now refreshed on validation
- Fix bug where array formulas in the template would cause an error
- Improved Focus Mode layout and redrawing
- Fix bug where a template with automatic calculation would cause a crash