Author Note: In this post I referenced a webcast that was upcoming at the time of writing. The screencast of that webcast is now up.
The title’s a lie. I am a math person. But I can’t begin to guess how many times I’ve heard this from people. Even now, when “science geek” is very much in vogue, a lot of people seem to shy away from equations, spreadsheets, and real quantitative analysis. I think the very fact you’re a person means you are a math person; I believe humans are born to quantify. It’s what we do. But let’s talk about that later.
For now, let’s talk about Excel modeling. Even as a simple calculator, Excel allows businesspeople (and many others) to construct a “numerical picture”, and with a few keystrokes, see the result of changes to input variables. Since that already sounds like a calculus textbook, which all those “non-math people” just hated to read, let’s stick with the word “model”.
“Model” is an analogy. As a kid, I built Revel models of airplanes. With injection-molded plastic, glue, and decals, I made planes that looked a lot like the real thing. But they didn’t fly. They were, after all, models.
Later, I took some interest in Radio-Controlled (RC) Modeling. I never got so into it that I was building scale models of F-14s that could actually fly, but those certainly exist. There are wildly sophisticated RC Models – some even simulate actual jet engines! (How they do that is way beyond the scope of this post.) In the end, though, they are still models – facsimiles that ape the real things.
There are other airplane models, built to suit a different purpose. Aerospace engineers build both physical and computer models for wind tunnel testing and real-world scenario testing, for example. But these, too, are still just models.
Now, a radio-controlled, turbofan-powered, to-scale model of an F-14 cannot accommodate a crew of two, nor can a similar model of a 747-400 ferry 360 passengers from New York to Tokyo. And they can’t fulfill the missions of the actual aircraft.
By no means does this make the models useless. There are a lot of lessons that can be learned from them; lessons that may be highly applicable to operating the genuine article.
Back to Excel. Microsoft’s off-the-shelf product is a powerful tool. Here is quite possibly the simplest example of what an Excel model can do:
It’s a simple Income Statement. This company has revenues of $100 in Year 1, growing at 10% per year. Costs are 65% of revenue, and therefore, profit is 35%. And as you know, the beauty of this model is that any change I make to a cell will be reflected in all of the dependent cells (provided I structured the model correctly).
And again, Excel, straight off the shelf, has hundreds of functions that allow me to refine this model in innumerable ways. BUT — and this is a huge but — each cell must contain one and only one number, or one and only one formula. Each cell will always read as a number. No matter how sophisticated my model becomes, I can only look at scenarios based on each cell holding one number at a time. Yes, that number can be the result of a formula. In fact, in my simple example, each cell in the cost line holds a formula that calculates 65% of revenue in the corresponding year. No. Matter. What. This is what mathematicians call discrete values.
In Excel, you can pretty easily build scenarios, and test for the best (“optimal”) solution to a given set of variables. A well-constructed model treats things managers can control as variables. Then the model can give a reasonable approximation of what the outcome will be as the manager changes the inputs.
Back to our simple model above, I may have control over costs, and may want to see what my profit will look like if costs are 70% of revenue instead of 65%. A simple change to the model, and Excel will automatically update the profit line. Right?
Let me change the channel now. I’m going to bring statistics into the discussion. (Pause for the groan.)
In the real world – the world of actual airplanes flying actual passengers – variables are rarely discrete. (That’s why they’re called variables!) Remember the “bell curve”?
The classic example is SAT scores. A small portion of test-takers — the right-hand portion of this curve — get very high scores. The vast majority are in that big central bulge. And a few do pretty poorly.
Well, suppose the company in our simple Excel example looked at costs over time, and they discovered that costs (as a percent of revenues) fit that curve. So now, instead of a discrete 65%, they found that the average cost was 65% of revenue, but like the students taking the SAT, there’s actually a distribution of costs. If we could build that distribution into the model, we could make the model better; we could make the model fly. But Excel can not put that bell curve into a single cost cell, could it?
It turns out that Excel CAN do that (with the help of a super-powerful add-in). And let me show you what we can do with that super-simple Income Statement model from above:
Without going into too much detail (yes, I realize the irony of this statement!!), I took the bell curve I showed you above, and built it into the cost cells. And then I ran a model simulation to see what would happen to the profits.
There’s a lot of data there — and remember, this is about as simple as models get! We can observe right off the bat that there’s a 90% chance that Year 5 profits will be between $45.19 and $57.24, on revenues of $146.41. (This is a range of about 31% to about 39%. That sounds more useful than just straight-up 35%, right?)
And there’s loads more we can do, just with this simple model. Imagine building a slightly more realistic model! The decisions you can make are supported with better analysis, and then — I hope — you make better decisions!
This Thursday (Jan 29) I will be giving a webcast on “Cost, Price, and Product Profitability: Using @Risk to Enhance Strategic Decision-Making“((The webcast took place in February 2015. You can see a screencast here: http://goo.gl/GMX0kw)). (@Risk is the add-in I mentioned above.)
Don’t let the jargon bother you: it is a straightforward and pretty basic example of how this tool can help you manage better. And whether you’re looking to learn how to do it, or simply learn how others can do it to help you, you will be able to follow along. I promise. I’m good at that.
I’ve adapted the somewhat-classic Harvard Business School Case Study, Destin Brass Products Co., for the webcast on Thursday. If you want to grab the case (which is not necessary to follow along), just click that link.
I hope to see you there, and we can start to talk about flying models. And of course, feel free to reach out beforehand if you wish. After all, you’re in my LinkedIn network!