Sensitivity analysis in PowerPoint

Today, I am adding a simple table to the store to show the sensitivity of an analysis. Each cell in the table shows an outcome of an analysis with a slight variation on 2 critical input variables. I used this type of a slide a lot in discounted cash flow valuation models, where assumptions about discount rates and assumed perpetual growth could make a significant impact on the outcome of your analysis.

The base case scenario is put very prominently in the centre of the table, it is the anchor for the viewer from which to start studying the other scenarios. I prefer making these type of charts manually, and not via a blanket copy-paste out of an Excel sheet. In that way, you have to think about whether each cell in the table is meaningful, and you can make sure that the data is formatted and rounded in the best way.

You can download this sensitivity analysis from the store, subscribers can do so free of charge.

---

Cover image by Markus Spiske on Unsplash

Balance sheet in PowerPoint

Today, I added 2 slides that show a balance sheet to the template store.

The first is a traditional table format. But this PowerPoint version is a bit different from the a typical balance sheet in an annual report to improve the readability. Usually, the PowerPoint presentation with the financials is not the legally binding one, so we can omit some details that clutter up the page. This table has fewer lines (consolidating multiple entries into one), and the amounts are rounded up.

The second chart is a balance sheet represented as a column chart. Even more details has been taken out and the result is a nice and unusual way to visualise the key balance sheet ratios. Instantly you can see the proportion between current and non-current assets (and liabilities), and you can see the leverage of the company (ratio between debt and equity). This chart is probably easier to follow than the Profit and Loss statement as a column chart which I discussed earlier.

Click on the image to download the slides from the template store. Subscribers can do so at no extra cost.

Cover image by NeONBRAND on Unsplash

A Pareto chart

Recently, someone searched for a Pareto chart on my site. I must admit, I had to consult WikiPedia about this type of slide. A Pareto chart shows 2 data series, each with their own vertical axis; the first show the absolute frequency count of something, the second one the cumulative occurrence of the data series, adding up to hundred for the last entry. I have added the Pareto chart to the store. 

How to format tables in PowerPoint

Tables can carry more data than a data chart and as a result can be less effective in a presentation. For some situations though, there is no point trying to avoid using a table in PowerPoint. For example, when investors want to see the quarterly numbers, they expect to see a table.

The way you format tables can make a huge difference in how your chart looks. When done well, a table can actually be an effective presentation slide. Have a look at the simple P&L table below.

A PowerPoint table to present a P&L

This might look like a super simple slide design (it is), but a lot of thought and little tweaks have gone into its design. Let's take them one by one:

• Colours have been adjusted to your own colour template, not the standard PowerPoint colours
• Fonts have been matched to your current template (table can be stubborn sometimes and stick to Arial)
• Instead of dark lines around boxes, I used lines that match the background colour, making cells a light colour of grey to stand out (or dark, black if you use that background)
• Totals are bold, and a bit darker
• The row labels are right aligned
• The row labels are a bit darker than the cells
• The data cells are right aligned
• Numbers are rounded to the same amount of digits, so the dots line up
• There are not too many digits in the table, enough to convey the data, but not too much to make it cluttered. If the numbers get too big, switch to thousands or millions.
• There is a bit of inset in each cell, the text does not touch the edges
• All the rows have the same height
• All the data columns have the same width
• The column headings are centered
• The unit of measure is put at the top of the chart, not repeated inside the data values
• The table covers the entire frame of the presentation template
• Double check by hand/calculator: the numbers add up...

Excel can be an excellent starting point for a table. Pull the data values you want to show with the correct rounding into a new worksheet (tables for presenting are different from tables for analysing). Think hard about what rows you want show, consolidating/combining values that do not add to the overall message of your slide. Then copy-paste the whole thing into PowerPoint where it will show up as an ugly table. Go through the steps above to clean things up. Alternatively, you can apply a lot of similar formatting already in Excel, making your spreadsheet tables good enough to put straight on the projector. This is handy when your numbers update frequently.

Feel free to copy the design, or download this table from the template store. You search for more slides with tables as well.

Quarterly performance summary: lots of different KPIs on a page

I often use the slide below in quarterly investor presentations for large corporates. How to give a quick overview of the key financials in one chart?

A chart with an overview of the main financial indicators of the last quarter

This chart is an example of why often a "manual" chart is much more powerful than a simple copy and paste from Excel:

• The chart contains values that can differ vastly in range: sales can be 100s of billions of dollars, EPS can be less than a dollar. Margins are percentages, not dollars.
• Despite this, I forced the Q1 column of each of these values to be the same. In the underlying spreadsheet, they will all say "100". The other values are calculated as a relative value compared to this 100. To accentuate this in the chart, I connected the left columns with a dotted line.
• As a result, all labels in the chart need to be filled out by hand, the same for the growth bubbles which I placed over the columns (again a bit unusual)

You can download this KPI chart from the template store.

Photo by Sabri Tuzcu on Unsplash

P&L as a column chart

When you want to show a P&L with very few lines, consider using a column chart instead of the more traditional data table. In the chart below you can see how to go about it. Use a bold color for the profit series, a light color for the COGS, and manually add the gross profit as bubbles.

Things get a bit trickier for years where there is a loss. You create a separate data series in the same color as the last cost category (opex in this example), and calculate how much of the opex goes under the line and how much above. Tweak data labels manually. For these types of charts it is best to sketch them completely on a piece of paper, and then fiddle with PowerPoint/Excel to get it right.

The column chart works well, it shows a number of trends in 1 slide: sales growth, profit growth, and how fixed/variable certain cost types are.

Below is the data I used to create this chart.

You can recreate your own chart with the description above, or download the example from the SlideMagic template bank.

Slide make-over: polarising brands

I am across this chart recently:

You will find more statistics at Statista

This chart can be improved in a number of ways:

• Actually, take down the brand images, or make the smaller. The different colours and sizes make the chart cluttered
• It is hard to read what the percentages actually mean
• The biggest problem is the confusing line up of the data bars
• The data could be sorted, to create additional structure for the viewer

I replaced the chart with the one below in PowerPoint, using a line graph with big markers instead of the bars.

Ideally, I would have like to flip the chart 90 degrees, but this would require quite a lot of PowerPoint surgery (you can probably do it with a scatter chart somehow). The other option is to construct a highly complicated "waterfall" chart.

Mixed cluster and stack charts

On a few occasions, I had to use a combination of a cluster and a stack chart. This chart is not available as a standard option in PowerPoint. Here is how to make it:

• Create a regular stacked column chart
• Set the gap width to 0
• Blank out the data where you want the gap between the years to be
• Manually add labels for each of the years

You can create one yourself using the above ingredients, or you can download the one I made in the SlideMagic template store:

Consistency in financials

Financial projections of new business ideas are totally made up / not accurate, so being of by a few million here and there would not matter? We can make quick changes in our financials in the presentation slides, and then "forget" about updating our financials spreadsheet with the new information. 

While the absolute numbers of your financial model might be totally pulled out of the hat, it is the thought process of how you got to them that is still valuable for investors. How does your business model work? What would I have to believe in order for your year-5-dream-scenario to come true?

And that model should be consistent across all your documents: presentations, spreadsheets, budgets, everything:

• Discrepancies make you look sloppy (a little preview of things to come when you need to work together with an investor on a Board)
• A consistent model of totally made up numbers makes sure that everything is, well, consistent. If you just slashed sales & marketing cost by 50% but maintain the same amount of sales people, something goes wrong.
• Inconsistencies make it harder to understand your story for an outsider. If sales are $50m on one page and $49m on another people get confused. You established "$50m" as a mental shortcut for year-5-sales-in-the-most-optimistic-scenario, and all of a sudden you create a new shortcut.

So, even if nobody can predict the future accurately. there is still value to create a consistent financial model the same way as you would for a business in which you know every single detail (next year's budget of an established company for example).

What can you do to incorporate the fact that numbers are highly uncertain?

• Round things up to whole numbers (no $49.569m sales in year 5)
• Minimise the number of assumptions you put in the model and make cells that are guesses highly visible (I usually mark them bright yellow).  Rather than "guessing" the number of customers for each year, and the number sales people for each year (10 assumptions over 5 years), you could assume a % growth of customers, and a fixed ratio of sales people to customers (2 assumptions).

Slightly more complex models might actually be simpler to understand.

The unit measure

There are endless ways to show financial ratios and benchmarks. Each industry has their own specific ones.

Beer brewers think of the manufacturing cost of beer in terms of cost per hecto liter (not liter, not kilo), but when they think about distribution cost, they think per 6 pack case (not liter, not kilo, not bottle). The cost of sending a letter is usually in weight, the cost of sending a package is usually expressed in terms of volume. Retailers think in terms of sales per square meter. Technology startups think often in terms of per user per month.

Find out what the norm is in your industry and adjust your financials.

Tedious number updates

I am not a believer in copy-pasting Excel numbers into PowerPoint. Excel is for analysis, PowerPoint for presenting, and presenting numbers is different from analyzing them. The charts I use to present are often so simple (only one data series, rounded digits) that I tap them in by hand.

There is an exception though. If you are the junior analyst on the team, and you are the last step in the "supply chain", i.e., constantly updating charts with new versions of numbers that roll out of the spreadsheet, it might be time to change working practice. Personally, I remember many McKinsey projects I did as an analyst where I designed a model in week 1 of a project, and ended up updating numbers on slides for weeks after that.

In these cases, add a worksheet to your Excel file, and pull the numbers from the analysis. Apply all rounding, i.e., if the chart or table requires millions, divide by 1e6. For tables, put all numbers in the right order, invert rows, columns if required.

You can go further and actually create objects in Excel that can almost literally be copied into PowerPoint. Create data charts, fix fonts, colors, etc. exactly as in the PowerPoint file. Format Excel cells exactly as the table in your PowerPoint document.

The cost of this effort is time: it will take some effort to get it right, but it will pay off in subsequent number update rounds, and it can prevent you from making mistakes in last minute model changes.

Be careful not to introduce new mistakes though. This type of work can best be done when things are relatively quiet. Take an existing, stable version of the deck, and start recreating the charts in Excel, constantly double checking whether you can replicate the numbers you put in by hand in the charts. Once you are confident that everything works for an old/trusted version of the document, you can use it for future versions. Still, some spot sanity checks are always a good idea.

---

Image via WikiPedia

Chart chooser

There are endless types of data charts out there: lines, bars, scatters, pies. Which one to pick depends on the type of data you have, and what message you want to convey. Here is a new handy tool that can make life easier for you: chart chooser.

A box of handy cards that guide you step-by-step through a selection process. Excel templates are an optional add-on that could be useful, some of these diagrams are tricky to recreate.

Investment bank memos

Investment bankers usually prepare the data rooms for due diligence in M&A transactions. Shelves full of information that is usually summarized in an information memorandum. I never found these summaries very useful. It is impossible to create a financial picture in hour head from just reading text with data. "Last year's sales were x, growth for the 3rd quarter was y, the company is doing business in 3 product segments." The only way to understand this is to extract the information and put it in tables or graphs to see what is going on.

And that is most of the times exactly what the buying side will do, hire a consultant/analyst to go through the material and start creating these charts, then someone more senior will go through the charts and select which ones are important, and which ones not.

You can accelerate the sales process by anticipating all of this, and do some of the homework of the buy side. In the process, you can spoon feed them the right insights you want them to have.

Similar but unrelated, this is also why wordy descriptions of company results in newspaper articles do not work for me. The journalist took the data tables and graphs and translated them into words, the exact opposite direction of where I want her to go.

---

Image by Andreas Poike on Flickr

Visualizing contrasts

This article on Vox tries to find statistics on voting fraud in the US. I will stay out of politics on this blog, but the 2 graphs it uses show the power of a good visualization.

A column chart shows the relative difference, but fails to communicate the large overall absolute value of the right column

The image does a better job to depict the statistics. It requires some math and Photoshop skills though to produce

The column chart is the correct representation of the 2 values, but it fails to communicate the huge amount of ballots we are talking about in the right column. The image does a better job, but it will be hard to construct for a layman designer.

---

Image from WikiPedia

One science is harder to explain than another

Most of my client work involves a presentation with a hard/difficult business or scientific issue to explain. All science is complex, and it requires someone to study for years to understand it. But in some scientific disciplines I can get away with things.

Take medicine for example. Here is what you can do to make things understandable to the layman:

• Micro focus on one very specific condition/disease, and omit all other 35,000 medical issues a student usually has to go through
• Simplify, eliminate complex / long / Latin names
• Abandon commonly used diagrams, and instead make your own, completely non-standard drawings that are solely aimed at explaining a phenomenon.
• Shrink all the statistical proof into a footnote. It is important for peer-reviewed research, it is not needed to understand the basic mechanism of a drug

This can work in other disciplines as well: economics for example. But it does not work everywhere. Mathematics for examples requires a broad understanding of concepts that are all stacked on top of each other, depend on each other. And there is no easy to simplify formulas. 

---

Image from WikiPedia

Words and number consistency

It is impossible to make a correct 5 year business forecast in an investor pitch. But your financial projection is not really a forecast, a prediction of the future, it is a picture made out of numbers. "If our company will be successful, this is what it could look like".

Mistake number one is to make the projection ultra precise with 5 digits after the comma. It is just a guess, so a year 5 revenue number of $99,234,318 is not more credible than ~$100m.

But oversimplifying is not right either. The fact that you are for sure going to be wrong does not mean that you simply take 1% of a big market number to get to the year 5 scenario.

The trick is to make the words/visuals in your presentation consistent with your financial model. You are going to sell to millions of individual customers, drive the model that way. You rely on 5 big telco operators, put it in. SAAS company with recurring revenues? Model it. One-off perpetual licenses, use it as the basis of your model.

Teaching investors how the business works is more important than getting the point estimate right.

Grasping data charts

Here is what a first time viewer needs to go through when looking at a data chart:

• What's the unit on the y-axis
• What's on the x-axis
• What do the legend colours stand for
• What is "good" or "bad", are things supposed to go up or down?
• What is actually happening
• Is the y-axis broken or does it start on zero?
• Why is this unexpected, interesting, counter-intuitive, amazing, terrifying?

If you did the analysis then you live and breathe the data in the chart, the only thing you watch out for is how the line/column/bar looks different from the previous version of the chart you saw. For a completely new audience it is a different story.

There are 2 types of data charts:

1. Charts to ponder and refine research and analysis
2. Charts to communicate the result of the pondering and refining

If you make a presentation of result, you deal with scenario 2. Make a diagram completely from scratch. Start with "what is actually happening" and only put that piece of data on the chart.

For example if a complex stacked line diagram shows that the % of returning visitors gets smaller over the past 5 years, you can replace all that monthly visitor data with 3 data points: % of return visitors in the last 3 years.

---

Image by Phil Roeder on Flickr

Storyfying financials

Most presentations have some sort of written summary on the first page. You often almost skip it when presenting live, but it can be important for an important that reads your slide deck at the computer rather than attending a live presentation.

The one thing that I find really hard to summarise are financials. Prose that goes like "sales were x, up y% compared to last quarter but z% versus the same quarter last year at a slightly higher operating margin of v%" makes me totally lose the picture.

Financial journalists fill pages of the Wall Street Journal with this type of texts. And they are upset that it is now possible for an automated bot to do this job: insert a financial table and out comes a perfectly written paragraph. (Robo journalism)

What do I do as a reader? I reconstruct the table back in my head. 

So what to do in a presentation? I recommend a super simple written summary on page 1 (sales were up, but profits down), and then a very crisp financial summary table on the next page. Round up numbers, and use colour coding to show what went up or down.

---

Image from WikiPedia