Waterfall charts in Excel - Microsoft Excel Tutorial

- [Instructor] Variance analysis is critical in business, but for many firm managers the last thing they need is more numbers and figures to pour over. Instead firms often prefer to create visuals that encapsulate where the company is and how it's performing. A bridge chart or waterfall chart does just that. We'll learn how to create one of these charts in this video. I'm in the 02_02 Begin Variances file. So what we've got here is our same data for Acme Corporation and this shows on the left hand side the historical figures and on the right hand side our forecast. And we've discussed the plan variance and analyzed that the firm is trying to go from relatively poor performance in 2017 back to very positive performance by 2020. The firm is trying to go from relatively poor performance in 2017 with 120 billion in sales to relatively strong performance with 165 billion in sales by 2020. This necessitates roughly $44 billion in sales improvement over that time. Now of course the firm's goal is not just to improve sales but in fact to improve profits. The firm wants to move from 28 billion in gross income to almost 78 billion in gross income by 2020. Again a positive variance of $49 billion during that timeframe. To do that the firm's gonna break down the drivers for that profitability improvement into four categories. These four categories are common across many different types of waterfall charts. The four categories are Sales Volume, Sales Price, Cost Inflation, and Productivity. Now the firm hopes that all of these will be benefits and that they will help to improve profitability. In reality they may or may not actually be benefits. The firm is hoping that the number of units sold will increase. If in fact sales volumes fall, well then obviously that's a head wind for the firm. The firm has made various projections based on it CAGR assumptions, compounded annual growth rate assumptions, around Sales Volume, Sales Price, Cost Inflation and Productivity. And so the firm has broken down the drivers of it's productivity growth into the categories we see here. Our beginning profitability is 28 billion. In order to get to the 78 billion in ending profitability the firm believes that 23 billion of that incremental profit will come from additional volume, a further 19 billion will come from price increases that the firm can pass through to customers, 2.5 billion will come from reducing the cost of it's goods sold, productivity enhancement, that is improving the amount of production with the same level of cost input, will account for 2.6 billion, and then 2.4 billion will be from other third party factors. When we get all done the firm believes it'll have 78 billion in total profitability, from 28 billion initially. How would the firm represent this in a waterfall chart? Well the chart would look like what we see here. Now in order to insert this chart in Excel if we come over to the insert column you'll probably have a waterfall option right here under recommended charts. Click Waterfall with our data highlighted and we see that we have a variety of different options for charts. There are pluses and minuses to each of these different choices. From a practical standpoint you wanna pick a chart that is clean and simple and easy to read. That's one of the key things that executives are looking for Make sure that that chart represents positive numbers in green and negative numbers in red. Those are the key factors you wanna think about as you're building that chart out. This chart illustrates the fact that the firm hopes to go from a beginning profit of 28 billion to an ending profit of 78 billion when we get finished. And the profitability along the way is accounted for by the following figures, volume, price, COGs, productivity and other. This chart helps us to visualize the fact that while of course we care about productivity and COGs inflation, that is reducing the cost of our raw materials, the real drivers of success here are going to be volume and price. Those significantly out weigh COGs and productivity as part of the drivers of improved profitability for this firm. Our focus therefore, as a company, needs to be on increasing sales volumes and getting customers to accept a higher price for our product. At this point you should have an idea how to construct a basic bridge chart, and you should be starting to think about the types of information that the bridge chart or waterfall chart conveys to us. This is the type of information that the corporate executive would expect to see.