Scenario Analysis

We’ve lost a lot of money in the last month, and the Boss calls me in for a meeting. I’m afraid he might ask me to cut my position, but he wants me to do a scenario analysis instead.

This is great news. I love doing scenario analysis. You can use scenario analysis to prove absolutely anything.

A scenario analysis spreadsheet, as the name implies, is a spreadsheet that analyzes various economic scenarios. For each scenario listed, there’s a qualitative description (for example, “the economy picks up steam, measures of inflation rise, and the Fed hikes interest rates”) followed by impressively precise predictions for various market variables (“10-year bond yields will sell off to 3.59%, the S&P will climb to 1182 and oil will rise to $72.35 a barrel”). Combine these predictions with your current portfolio positioning, plus any changes that you expect to make in the portfolio, and you get P&L outcomes for each scenario. Assigning probabilities to each scenario then gives you the “expected value” of your portfolio, which in turn leads to the most important statistic of all: how much you expect to get paid.

A well-crafted scenario analysis spreadsheet can be extremely impressive. The sheer quantity of detail crammed into the spreadsheet can easily overwhelm the unwary reader: examinations of every possible state of the world, with associated probabilities, market predictions and P&L projections all to three decimal places or more. But where do all these numbers come from? Easy: from a thorough examination of current and future economic conditions, coupled with a deep understanding of market dynamics.

In other words, I pull them out of a hat.

Let’s be honest. I might put hours or days or even weeks of research into building my scenario analysis spreadsheet, but even with the best intentions in the world, there is no way I or anyone else can predict the market. What’s the probability of a rebound – 30 per cent? 60 per cent? 90 per cent? Who knows? Even if I knew for certain that a rebound was on its way, what would that mean for oil prices? Would they go up? Down? Sideways? Who knows? And even if I knew for certain that oil prices were going up, what would that mean for my portfolio? Will I still be long oil at the time? Will I have cut my position in half? Doubled it? Gone the other way and sold oil short?

It’s the tyranny of efficient markets: anything you know, everybody else knows. So if you’re certain a bounce is on its way, then there’s a fair chance everybody else is equally certain, and current market prices reflect that information. Trying to predict the future is a mug’s game; building a complicated spreadsheet based on these predictions is beyond stupid.

So I fall back on the cardinal rule. I build a spreadsheet that will help me keep my position.

Building a good scenario analysis spreadsheet is an art. A common beginners’ mistake is to paint too rosy a picture: assigning too high a probability to the “good” scenarios, or assuming that the market will react to unexpected news in just the “right” way to help you make money. A moderately competent desk head will see right through that, so you have to be a bit subtler in your approach. One good technique is to shovel all the bad news into a scenario that you know your boss does not believe in. For example, if he’s a peak-oil fanatic, then make sure that your worst P&L occurs in the scenario where oil drops back to $20 per barrel. Another tactic is to choose your market outcomes such that your boss’s favorite trade appears mediocre: your overall spreadsheet will then seem suitably conservative, but you won’t be asked to change your position. And of course you can always tweak the probabilities, fudge the predictions and alter the positions (“expected strategic/opportunistic reallocations”) to ensure your portfolio works like a charm in all states of the world.

Armed with these tricks of the trade, I create my spreadsheet. It takes about an hour to build (15 minutes to generate the numbers, 45 minutes to format them), and it’s a masterpiece. It has every single scenario imaginable, including several that are ludicrous in the extreme. Amazingly, my portfolio seems to make money in almost all the listed scenarios, yet there are no clear instances of bias or manipulation. All the numbers are given to five significant figures, and there’s an abundance of extraneous detail. Important cells (profits) are carefully highlighted, while irrelevant ones (losses) are equally carefully downplayed.

The spreadsheet works like a charm. Confronted by rows and rows of densely packed figures, the Boss’s eyes glaze over. He focuses on a cell that implies that we’ll make a ton of money while taking minimal risk, and that seems to please him; he says to me, “Good work!” Then he does what any good manager does when out of his depth. He changes the subject.