If you create budgets, you’ll need to compare them to actual numbers to see how close you are coming to your estimates/forecasts/budgets.

If you are holding Department Heads/Managers accountable to budgets, they need to review how they are doing along the way.

In the attached 23-minute video, I walk you through the process of using eOne Software’s Excel Builder in Microsoft Dynamics GP and Excel to create a dashboard to assist in budget to actual comparisons.

We start by using a free template for the eOne Excel Builder, and add some additional tables and columns that I feel are important, specifically the Name of the Fiscal Period and the Account Category Description.  You can download my eOne Excel Builder template here!

After we publish the eOne Excel File, we open it up and use PowerPivot and the Camera Tool to make a Dashboard that makes it harder for users to… ahem…  alter the visuals or charts.

Another Great Use for Excel Files created from eOne Excel Builder

If you are running Microsoft Dynamics GP On-Premises, meaning not in the cloud, you can store your eOne published Excel file on Microsoft OneDrive for Business.  If you then use that Excel file to create Microsoft Power BI content, Power BI will automatically update with the data in the OneDrive based Excel File.  So, you only need to open the Excel file and refresh, to update your Power BI Data.

This is a “low tech” way to keep your Power BI data current, but it works seamlessly.  The reason it works is that OneDrive for Business (from Office 365) and Power BI uses the same Active Directory, which is stored in Azure.  In other words, the exact same log in information is shared between One Drive for Business and Power BI, so Power BI knows how and where to access the data.

5       Reasons to love eOne Software’s Excel Report Builder

1. No need to access SQL directly.
2. Negative items (like Returns, Credits and Payments) have a checkbox to appear as negative.
3. Fields are easier to understand. By this I mean, for example, a Boolean field will appear as the values of the Boolean.  So rather than a 0 or 1, I see Yes or No, or True or False, etc.
4. Anything that’ll make data refresh and appear in Excel automatically is AWESOME!
5. With one set of tools and skills, I can create Excel Reports, SmartLists and Navigation Lists.

Learn more about eOne products Here.

Download my edited version of the XML file from eOne’s Excel Report Builder to replicate this report on your Microsoft Dynamics GP Here.  eOne Excel Report Builder XML File – GL Actual vs Budget

Author: Belinda Allen

Belinda Allen (Microsoft MVP) is both a Microsoft Certified Professional for Dynamics GP (MCP) and a Microsoft Certified Trainer (MCT). She is also the co-founder of Smith & Allen Consulting, Inc. (www.saci.com), a business management software consulting firm for over 24 years. Belinda began implementing ERP systems so long ago that Windows was not an operating system, but an application. Larger businesses used old Main Frames with monitors that were Green and Black and smaller business did accounting by hand. Having seen the evolution that has taken place over the years from pieces of paper to analytics, Belinda still gets excited every time she helps a business improve. Belinda is also well known for her Blog www.BelindaTheGPcsi.com. Her blog is about sharing really useful information about the product quickly and succinctly. She has earned the nickname GP CSI because she excels at reviewing GP problems and figuring what went wrong and why. With followers all over the Globe she is able to share knowledge and achieve her mission: “To Improve the Lives and Business Success of my Followers.” In her time off, Belinda enjoys sailing, woodturning, crochet/knitting, sewing/quilting, reading and playing the ukulele (Belinda often participates in Ukulele Jams in NYC.) View all posts by Belinda Allen