Visualizing your Financial Statement with a Waterfall Chart

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.

clip_image002

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:

clip_image004

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:

clip_image006

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.

clip_image008

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.

clip_image010

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.

clip_image012

clip_image014

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.

clip_image016

Once you have set all of the check points, your modified Financial Dashboard should now look like this:

clip_image018

Conclusion

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s