How do I add blank or spacer rows to a report?
Click the report then click “Insert” and “New heading”. Then edit the name of the heading to be a space. This will show up as a blank row. Then drag it to the place you want it to go.
Why aren’t the styles in my template being applied?
This may be because Openbox is set to ignore these colours and styles and use the FAST standard ones instead.
To check this, in the main Openbox window, click “Help and Settings” then “Openbox settings”. Click the “Standards” tab then make sure that “Apply FAST…” is unchecked. Click OK to close the settings window.
How do I get rid of the input scenarios?
In the main Openbox window, click “Help and Settings” then “Openbox settings”. Click the “Layout” tab then select “One sheet” in the Input Layout section. Click OK to close the settings window.
Can I show the preview spreadsheet on my second screen?
You want to see the main Openbox view on one screen and the preview spreadsheet on another. To do this:
- In the Openbox window, click “Help and settings” then “Openbox settings”
- Click “Other”
- Make sure the box “Show preview in new window (requires restart)” is ticked
- Press OK.
- Close and restart Excel
When you next create the preview it will appear as a separate window to the main Openbox window. This means you can move it around, including to a second (or third) screen if you have one.
Why am I getting an error about “secondary items on a report”?
Sometimes Openbox will give you a warning message of this form: “You cannot use an item on a report because it is on the secondary axis”. Why does this occur and what can you do about it?
This is a known limitation of Openbox that we are working to remove. It occurs when you have a model with two time lines. Openbox calls these “primary” and “secondary”, but you might think of them as “operations” and “construction”.
Normally when you put a line item on a report, Openbox automatically converts it to the right time step for the report (e.g. annual). But it cannot yet do this for items that are on a secondary (e.g. construction) timeline.
The workaround is to create a new item in your model that is on the primary timeline, and put that on the report instead. For example, suppose you have [Construction cost] on the secondary time line.
Add a new sheet to the model, called “Report items”. Add a new item to that called [Construction cost quarterly]. Make the formula [Construction cost]. Remove [Construction cost] from the report and add [Construction cost quarterly]. Then press F9 for the preview and the issue will be resolved.
The short video below shows how to do this
Show, hide or resize the preview window
When you press F9 or the “Preview” button in Openbox, the preview spreadsheet window appears.
You can hide or show this window by pressing Ctrl+Shift+P (P for Preview). You can also use the toggle on the menu. Click “View” then the three dots at the end, and then the “Preview” button.
You can resize the window by moving your mouse over the black line at the top of the preview and dragging it up and down.
This short video shows you how:
Do all inputs have to be on an input sheet or tab?
Separating inputs, calculations and outputs is a core part of spreadsheet good practice, and Openbox enforces that. In the worksheets that it creates, all inputs will always be on an Input sheet. This will always be true of FAST models.
However, some other standards allow you to have inputs on other sheets. You can tell Openbox you want to do this by:
- In the Openbox settings window, going to “Standards” and unchecking the “FAST Block structure” option
- Leaving any items you want to be inputs on other sheets as placeholders.
This means that they will be highlighted in bright yellow.
You can also have inputs on sheets you add yourself, e.g. as part of a dashboard. See the articles on dashboards for more information.
Add a total or sub-total to a report
Any item in a report can be defined as “the sum of the items above it”. This means that you don’t need to specify the formula directly. Also, as you add or remove items above it, the item will change just like a SUM function in Excel.
To make say [Gross Profit] into a sub-total, right click on it and choose “Sum of above”. You will see that it becomes bold. If you right click and choose “Sum of above” again, it will go back to a normal report line.
Making an item on a report negative
All calculations in a model are “positive by default” – that is, they will be positive whether they are costs, revenues or something else. That means that when something in the model is negative, it is probably a mistake.
But often you want to have items on a report shown as negatives – such as costs or outgoings.
To make an item like [Operating costs] negative, right click on it in a report and choose “Negate”. You will see that brackets appear around it.
Note that the same item can be negative on one report and positive on another.
Sales growth example
I want to have sales in the model, and I want it to grow by 5% per year. How do I do that?
The correct answer is: 1. Add [Sales growth factor] and [Annual sales growth rate] to your model.
2. Set [Sales growth factor] = IF([Period number]=1,1,PREVIOUSVALUE() * (1 + [Annual sales growth rate]) ^ (1/[Periods per year]) )
3. Set [Annual sales growth rate] to 5%
4. Set [Sales]=[Base period sales] * [Sales growth factor]