How to generate Dynamics GP Financial Reports in Excel

​Dynamics GP is a mature Enterprise Resource Planning (ERP) system that has a number of different partner tools that can create financial statements. Some tools can export the financial reports to Excel, but there are benefits to being able to perform financial reporting directly in Excel, which is exactly what a tool called Jet Reports enables users to do.  Jet Reports allows users to create financial statements directly in Microsoft Excel by pulling live financial data from Dynamics GP. This allows accountants to harness the power of Excel to make the financial reporting process easier. This post will walk you through how to create financial statements in Excel using Jet Reports, pulling in live Dynamics GP data.

​Jet Reports is an add-on to Excel that allows users to build any type of financial report directly in Excel. Since Jet is a tool built directly in Excel, there is a limitless number of reports that can be created.  Here is an example of some financial reports that can be created in Jet Reports:

• Income Statement by Quarter, Month, Year
• Income Statement by Segment
• Income Statement using Account Categories
• Income Statement by Subsidiary, with each subsidiary on a different worksheet
• Incomes Statement by Subsidiary, with each subsidiary as a different column
• Income Statement Budget vs. Actual with Variance
• Projected Project and Loss Statement
• Consolidated Balance Sheet
• Balance Sheet by Subsidiary
• Balance Sheet with Previous Year Comparison
• Balance Sheet using Account Categories
• Common Size Balance Sheet
• Cash Flow Statement
• Cash Flow Statement by Subsidiary
• Projected Cash Flow Statement

Since Jet Reports is an add-on To Excel, once it has has been enabled in Excel, a new Jet tab will display which allows users to connect directly to Dynamics GP and pull in live GP data. Below you can see the Jet tab with a number of functions available for creating reports, connected to the Fabrikam demo Dynamics GP company.

Jet Reports allows you to write functions in Excel to pull Dynamics GP data directly into Excel. The function that is most commonly used for financial reports is called the GL function. This function has the following parameters (options):

• Segment 2
• Segment 3
• Segment 4
• Segment 5
• Include Unposted
• Company
• Data Source

To demonstrate the ease and flexibility of creating a financial report in Jet Reports, we will walkthrough how to create a simple income statement report. In this example, the rows of the income statement will be based on Account Category and the columns will be based on Segment 1 (Department).

Jet Reports has the flexibility to have the rows of the income statement defined however you would like, but in this example, the rows of the report are based on Account Categories. The first thing we need to do is create a GL Function to define the rows.

1. On the Jet ribbon in Excel, click Design

Jet will automatically place a value of Auto+Hide+ Values in Cell A1. The first row and column are used to specify if there are any columns or rows that you want hidden when the report is run. This is useful if you want to hide some calculations.

2. Click in Cell B3 and click the GL Jet Function in the toolbar
   1. Select Rows for Where, since we are defining what the rows will be
   2. Select Categories for What, since we want the report to pull Account Category information from GP
   3. Enter a range of the Categories you want to include, in this demo company, Account Categories 31 to 47 represent the profit and loss accounts
   4. Click OK

Jet will now pull in live data from Dynamics GP. When in Design mode, Jet will pull in the first record. When the report is run it will pull in all the rows within the range. In this case the number of the first category within the range, which is 31, will display.

We want to display the name of the Account Category in the report so we will add a formula to cell C3 to display the Account Category name.

After entering the formula for the Category Name, we now see the word “Sales” displayed which is the name of the first Account Category.

Similar to rows, in Jet Reports, you can define the columns of the financial report to be whatever you would like. In this walkthrough we will be setting the columns of the report to pull from Segment 1 in Dynamics GP, which in this demo company, is Department. 

We will add a formula to cell D3 to dynamically pull in the departments for GP. We could have also simply typed into Excel the departments but the advantage of using the Jet function is that if a new department is added in Dynamics GP, the Jet report will automatically update with the new department.

Now the first department code will display, which in this example is “000.” When the report is run, a column will dynamically be created for each department.

Next, we will define what the amount will be based on, which is the account category we defined for the row, and the department we defined for the columns. Since this is an income statement report, we will also be defining a start and end date, which we will allow the user to specify on an Excel tab called “Options.” We will enter another Jet GL function, this time in cell D3.

Excel now displays live from Dynamics GP in Excel the amount of sales for department 000 in the period specified, which in this case is 0. When we run the report, we will see the values for the combination of all account categories and departments.

Now that we have all of the Jet formulas created, we will add standard Excel formatting to make our income statement look more professional. One of the advantages of Jet being an Excel add-on is that any standard Excel functionality can be used, so you can make the financial statement look exactly how you need it to look. For this example, we will add the following using standard Excel:

• Report heading
• Company name
• Column headers with bold and underlines
• Number formatting
• Total

Our report is now ready to run, and we simply hit the Run button in the Jet toolbar. We now have our income statement by department with live data from Dynamics GP, right in Microsoft Excel.

If someone reviewing the report has a question about one of the numbers displayed, they can simply click on a number on the report and drill down to display details directly from Dynamics GP. In this example, we are drilling down on the Salary Expense for department 000 and the following details are displayed:

Jet Reports allows users to take control of their financial and operational reporting in Microsoft Excel with data updated in real-time from Microsoft Dynamics GP. If you would like to learn more about how you can use Jet Reports to improve your financial reporting contact a Rand Group reporting export today. Rand Group is a certified partner for both Microsoft and Jet Reports with nearly 20 years of experience implementing ERP and reporting solutions.