Excel has been a key part of Microsoft Office and continues to be enhanced in Office 216, part of Microsoft Office 365 (Office 365 Consulting in Canada).  As a complement to Microsoft Dynamics, Office 365 and Excel 2016 not only provides additional functions and usability, but there are now dedicated mobile apps,  browser-based online versions as well as the traditional Office desktop workhorse apps for Mac and PC.

The following are a quick Top 3 Excel Functions for Microsoft Dynamics
1. Excel templates in Dynamics CRM and GP.

There are a number of great ‘canned’ reports within Dynamics GP and CRM.  Some of the more popular GP ‘canned’ reports include: Export Chart of Accounts, Sales Analysis, Purchasing Analysis and Inventory Analysis. New within Microsoft Dynamics CRM Online 2016, you can now create your own Excel templates and reports. This is an exciting new function within Microsoft Dynamics CRM Online 2016.  Excel templates can now be formatted and embedded within CRM and made available as a personal or system-wide template.  This means the full capabilities of Excel formatting, including Pivot Tables, Pivot Charts, conditional formatting or offline calculations that can’t be easily handled inside CRM, can now be created as templates and made available to all users.

2. Excel PowerPivot for advanced slicing and dicing.

This this free add-in within Excel, simply type into the search “Power Pivot”, Excel will point you to the data analysis add-in called Power Pivot.  Vlookup and Hlookup are good, but being able to use a graphical interface to create joins between multiple tables via Power Pivot makes the consolidation and analysis of information a breeze.  Within Excel 2016, Power Pivot has been expanded to include that ability to preview and filter (ETL) before extracting data from a view, instead of downloading the entire Dynamics View or SmartList. This is a great time saver when you are dealing with large data sets with, hundreds of thousands or millions, of rows of transactional data. We recommend that you get some help from an expert (Endeavour Microsoft Dynamics Consultants) to build and create the custom connections to views or database tables in Dynamics CRM or Dynamics GP before performing your slicing and dicing.  Be sure to periodically reconcile a simple PowerPivot report against your current reporting system to confirm that you are capturing each of the right tables and transactions.

3. Creating budgets within Excel and re-importing them back into Dynamics GP.

Under the Financial Card in Dynamics GP, you can use the ‘Budget Wizard for Excel” to create GP friendly budgets that can be re-imported into GP for actual vs. budget comparisons.  To help kick-start your budgeting process, you can export an Excel budget template with starting data from Microsoft Dynamics GP based on:

• A percentage change +or- from the open year, or
• A percentage change +or- from a previous year, or
• A percentage change +or- from a previous budget, or
• A blank Excel budget template that you can populate from scratch before re-importing back into Dynamics GP.

For more sophisticated budgeting and forecasts, you may also want to consider Endeavour Solutions’ approved Dynamics ISV partner, Prophix for automated budgeting workflows in support of a collaborative approach to setting budgets and forecasts.  With their new cloud-based options, Prophix is now more affordable for Microsoft Dynamics users in the mid-market. See Endeavour’s dedicated Prophix page at www.endeavoursolutions.ca/Prophix