|
The Brode Report | September 2010
|
|||||
|
Five Tips for Better Scenario Modeling Back to my roots: excellence in spreadsheet modeling. Recently I’ve been creating models with dramatically improved scenario features. You’ve been there before: you’re trying to compare among three (or ten) different options. It could be that you have three variables and three inputs for each, which gives you 3 x 3 x 3 = 27 different options. Or maybe you’re comparing what the model looked like last week to what it looks like today. In any case, this leads to all kinds of fun problems. In the last case, where you have different versions of a model over time, you wonder about what changed between the two versions. Most spreadsheet compare tools can’t handle the complexity of modern models, so it can become a lengthy forensic analysis to figure out what is causing the changes in the outputs. There is a better way! What if you used the exact same model for both this week’s and last week’s model? It’s long been the holy grail of spreadsheets that we could treat them like “real” software programs and separate the inputs from the code. In many cases that is now possible. Here’s how: 1. All inputs must be on one sheet. It’s OK for them to link to “input” sections on other pages, but the model as a whole should have all inputs on one sheet. 2. Each input gets its own row. There’s a temptation to create 2-dimensional input structures, like this:
But instead, to follow this method, these inputs would look like this:
3. Each scenario gets one column. That means that the only difference between this week’s and last week’s model is that their inputs are in different columns. 4. Changes to the logic of the model between versions are handled by switches. This is important for backwards compatibility. If you go from calculating revenue with a growth rate, i.e. 2012 Revenue = 2011 Revenue * (1+20%) to calculating revenue as Units x Price, that means you have two different methods. The inputs should reflect a method choice and inputs for each method. Then the revenue formula has an IF or CHOOSE statement to determine which method is active. 5. Finally, the model has a master switch to say which scenario is active. This methodology has numerous advantages. First, instead of constantly opening old files and comparing two spreadsheets, all the data is in one file. By setting up comparisons of two input columns, you can see exactly where the changes are (especially by using conditional formatting as a visual alert) and zero in quickly to answer the “what’s changed since last week?” question. Next, the resulting model lets you see the evolution of your assumptions over time. So when someone asks, “When did we say we were going to roll out 20 in 2014?”, right away you can see that on June 14 you changed it from 10 to 20. So this provides an audit trail of course. Like any innovation, there are times when this is appropriate and times when it’s not. A model in massive evolution, with huge structural changes happening mid-course, is not a good place for this method. But if your model is more stable, this is a tool I have found to be a fantastic innovation. If you’re interested in seeing an example of this in Excel, email me and I’ll forward one on.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Skype IPO: Where's the Beef?
The Internet was abuzz last week about Skype’s planned IPO. Sites from GigaOM to Tech Crunch to CNET and even mainstream press such as Newsweek and BusinessWeek had breathy articles about it. I found it amusing that some articles even attempted financial analysis. From my perusal of the S-1, we don’t have sufficient information to tell if this makes sense.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
Connect with me | | | Share your thoughts | | | Subscribe | | | See past issues | | | www.brodegroup.com |
| New York: +1 212.203.6974 | London: +44 020 8123.8053 | Vail: +1 303.444.3300 |