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.
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 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.
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, you modified Financial Dashboard should now look like this:
Conclusion
Excel 2016 has a lot of new and exciting feature that are really enhancing it as a business intelligence tool. As always contact KTL Solutions to help kick starting your organization into becoming data driven organization by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP and CRM needs.
