In July, Microsoft introduced six new chart types that will be available as part of Office 2016. Today, I’m will show you how to take advantage of the waterfall chart, one of the most popular visualization tools used in small and large businesses by modifying Dynamics GP’s Excel Financial Dashboard with Excel 2016 to add a Waterfall chart. A waterfall chart is a form of data visualization that helps in understanding the cumulative effect of sequentially introduced positive or negative values and is very useful in analyzing an income statement.
Dynamics GP’s Standard Financial Dashboard
The first thing you need to do if you haven’t already is deploy Dynamics GP’s Excel Report to either a network drive or SharePoint site. Once deployed, your financial dashboard should look like this.
We are going to modify it to add additional detail to the Revenue and operating Expenses. To do that we insert one row below the Net Sales and three rows below the Operating Expenses. We they add the “Discounts & Returns” header to the newly inserted row below Net Sales and rename the “Net Sales” to “Gross Sales”. We then rename the “Operating Expenses” to “Wages & Benefits” and add “Expenses”, “Operating Income” and “Depreciation” to the remaining inserted rows. The final product should look like this:
The next step is to replicate the “Revenue” tab in the spreadsheet and change the title to “Discounts” and then modify the filters in each tab to only look at gross revenue and discounts & returns. Moving on to the Operating Expenses, I replicate the “OpExp” tab three times and rename those tabs – Expenses, Depreciation and Wages. I change each tab’s filter to only look at the appropriate expenses for each category. I then link each of the tabs results back to the Dashboard tab which produces the final results:
Waterfall Charts – making financial statement analysis easy
Waterfall charts work off of positives and negatives so I first have to convert my positive expenses into negative number. To do that I copy the Indicator section of the Dashboard and paste it into Column M. From there I change the Expenses into negative and change the calculations for the sub-totals of Gross Profit, Operating Income and Net Income.
Its now time to insert my waterfall chart. To do that I highlight the Gross Sales through Net Income rows and columns as shown on the below screen shot.
I then navigate to the Excel 2016 Insert tab and click on the Waterfall chart icon. This will add the waterfall chart to my spreadsheet.
Making the Waterfall chart understandable
To make our Waterfall chart understandable, we want to set visual checkpoints to the subtotals. In any Waterfall chart, you might set a subtotal to show the absolute value rather than as a floating value relative to the previous column. In this example, the line item accounts—Net Sales, Gross Profit, Operating Income and Net Income—are all totals that behave like checkpoints in understanding our financial statement. Currently the chart characterizes these accounts as increasing cash inflow, resulting in a positive skew.
To set a subtotal, select the data point and then right click the data point and select Set as Total from the list of menu options.
Once you have set all of the check points, your modified Financial Dashboard should now look like this:
Excel 2016 has a lot of new and exciting feature that are really enhancing it as a business intelligence tool. I look forward to more great features in the coming months.