When we started building Openbox, we thought we were building a tool to create spreadsheet models. But as anyone who has started a business will tell you, customers and the market have a way of surprising you with what they really want! And one thing they want is help understanding what their model is doing. Or even better, what someone else’s model is doing. I’ve discussed that in a previous blog too.
Why can’t they just look at the spreadsheet to work out what it’s doing? Spreadsheets are great for examining the local detail of a model – what’s happening in one cell and the cells near it that you can see on one screen. But very soon, you are scrolling up hundreds of rows and flipping between sheets just to go from a calculation to (say) the inputs it depends on.
And here’s the thing: whenever you show someone a model, and the output of a model, they always ask a question like “Why did that IRR change from 12% to 11%”? Or “What exactly is included in operating costs?”.
Those questions should be easier to answer than they are. Yes, you can use the little blue tracer arrows, and step back through the model using Ctrl+[. But you quickly get lost because you can only see one step at a time, and you need to remember how you got there.
Our approach is to show what our friend Hjalmar from grid.is called the “dependency graph” in his “building a spreadsheet engine from scratch” article.
Use a map to avoid getting lost
In simple terms, a dependency graph is a map of the model. It has a box for each calculation item in the model and arrows between the boxes. An arrow to one box from another one shows that the first box/ calculation depends on the second box/ calculation. In Excel jargon, the arrows show precedents and dependents.
With this map, you can keep track of where you have come from, and see big chunks of the model all at once.
The problem is, as Hjalmar pointed out, that a dependency graph can “…get very complicated very fast…”. Financial models are complex – that’s part of what we get paid to deal with, as financial modeling professionals. But if you show the whole model as a graph (and we’ve tried, believe me), it is overwhelming and unreadable.
Step by step
So what did we do? First of all, we challenged the assumption that you need to show the whole model. Our experience is that people like the idea of seeing the whole model on a single screen, but for any serious model, it is too much information to take in. They instinctively pick one part to focus on first.
Our solution is to show the graph one step at a time. Start with a single calculation and its immediate precedents and dependents. Let the user add layers, one at a time, not adding more until they’re happy they’ve understood what’s there already. Let them hide parts that are not interesting, or that they are no longer interested in.
It’s a bit like when you use Google Maps to plan a journey. It doesn’t show you the whole world, with a tiny line representing your best route. It zooms in on the route – the part you care about – and you can then choose to zoom out if you want to.
If you do this, then in most cases, you’ll find that there really aren’t that many steps from input to result. You go from looking at a model with thousands of calculations to focusing on just a handful.
Laying it out
But even a model that is displayed step by step can be hard to read unless you are very careful about how you lay it out. People like to have things flow in one direction from inputs to outputs. For me, it’s left to right. And they don’t like to have lots of arrows that cross one another.
There also turns out to be a concept that we might call “level”. This is basically how many steps it takes to go from a particular item to the outputs. We’ve found that it is much easier to read the model if everything at the same level is shown in a column on the screen. For example, outputs should be shown on a column on the right of the screen, and then everything that is an immediate precedent of the outputs should be shown one column to the left.
Of course, it is not quite as simple as that!
Suppose for example you have a fixed assets calculation in your model, and a depreciation calculation. Fixed assets depend on depreciation, so depreciation should be to the left of fixed assets. But depreciation also depends on fixed assets, so depreciation should be to the right…
Or maybe a calculation is two steps away from an output, if you follow one path, and three steps away if you follow another path. Where should it go then?
There’s no perfect solution, so the layout approach we use tries to strike a balance. We have been very strict about the ‘left to right’ rule. And we try to avoid arrows crossing one another. Sometimes, there’s no right answer to which ‘layer’ a calculation should be in.
If you’d like to judge how successful we’ve been, you can try it out in the latest version of Openbox here.
Moving it around
The next challenge is that the instant you add or remove boxes/ calculations from the graph, you change what a good layout is. Where should the new boxes go? How should the new arrows be drawn so they don’t cross existing ones? Which existing boxes should be moved to make space for the new one? Do we need to revisit any decisions about which ‘level’ each item should be at?
Basically, you need to redraw at least some of the graph. And if you’re not careful, that can take a long time. We needed to ensure that each box is as quick to draw as possible and that we don’t try to redraw things that don’t need to be redrawn.
One trick here is ‘caching’ – we work out what a calculation should be linked to, and then store that rather than working it out again. Unless we have to – if for example its formula or the formula of its dependents change. Excel does much the same. If you change an input in Excel, it recalculates the rest of the model, but only those parts that depend on the changed input.
Show me the numbers
Finally, a dependency graph can be a little…well… abstract. Yes, you know the three drivers of revenue. But which one matters most for the scenario you are looking at?
One of the great things about spreadsheets is that they let you see the numbers, which gives you a real sense of whether the model is doing what it should be and what’s important.
So show the numbers on the graph, right? Not so fast. Just like when we discussed showing part of the map and not the whole, the best approach is to show the user as little as possible but to be ready to show them more detail if they want.
And that’s the approach we’ve taken here. The graph doesn’t show the numbers, but Openbox will display a spreadsheet beside it that does, and that moves around as you move around the graph. You can also draw an instant chart beside any item, by clicking F11.
In effect, you have a totally customisable dashboard, which lets you drill down into any calculation as far as you like.
What’s next
The new Super Focus mode was created to help people find their way around financial models. But we have big plans for making it even better. Ways to improve navigation by only showing the most relevant items, and greying out the others. Ways to have it display the changes between two scenarios, so you can see visually exactly why that change in an input affected the result. And many more.
If you’d like to be involved in deciding what these next steps should be, please get in touch!
