Jet Express for Excel and GP

Because Management Reporter doesn't work with the GP Web Client, Microsoft is recommending use of Jet Express for GP. It is a scaled down version of the full Jet Reports Professional, but works exceedingly well for most financial reports, e.g.: Income Statement, Balance Sheet, Trial Balance, Cash Flow Statement, Budgeting, etc.

One excellent advantage is that it is based on Excel. So finance managers can leverage their existing Excel expertise to create meaningful reports.

This short blog will introduce you to Jet Express for Excel and GP. When you go to the site, you will have the opportunity to watch the intro demo and various other training videos. There is also paid training for which you may register. Of course you will be able to download the version which matches your version of GP. Jet Express is free.

Here is a simple Trial Balance. While you can't drill through to the source transaction with the Express version, it is an easy way to have an immediate, real-time Excel report of your trial balances, for whatever period ranges you specify. You can always look up the source in GP, if you have it side-by-side to the Jet Trial Balance Report.

Let's look at the Trial Balance Report in Design Mode. Notice that it is Excel. See the VLOOKUP formula referencing a column on another tab.

Jet Uses the GL Function Wizard to list all the GL Account. Just highlight a cell, click on the Jet Function Wizard (will show you below where that Wizard is), and answer the "Where" and "What" questions.

Once you have the account numbers listed, you will want the account description (Account Name in Jet) along side of each account number. Notice the "What" is now "AccountName" rather than "Account" as above. It references the cell to its left, which is "D9". There is a dollar sign prior to the column designation so that Excel knows that the Column "D" will remain stable, while the row number will be changing.

Now we put in the dollars that go along with the Accounts to the left. I didn't show the Wizard window, but i do show the formula which it creates for you. It says, "In this cell, enter the GL Balance, for the account specified in Cell $D9 for the period specified in F$7, which means the balance for Account 10000 for the 7th fiscal period in 2017. Jet expresses period dates in "yyyy/m" fashion.

Once you create the balance formulas for the first month, you can simply copy and paste the other columns for prior periods and change the fiscal year/period numbers and titles. Then you will have a complete trial balance report as above. Once done, you just need to refresh it and pull in live data. Eventually, the next month (May in this case) arrives and you just insert an Excel column, cut and paste April, change the year/period, and refresh the report to pick up May's data.

Here is an Income Statement (P&L), with a similar design process. The numbers are YTD for each column. Each fiscal year (October 1) zeros out the balances from the prior year.

Below is that same P&L above in Design Mode:

Notice the "Jet" tab in the Excel menu. That has 3 tools on the ribbon that you need. The far left tool is "Design". That is where you create or design the report. The second tool, next to "Design", is "Report". That will run the Design to create the report output. The 3rd tool contains the "Jet Function Windows". The only one used for GP is called "GL". It opens the Jet Wizard you have seen in examples above. The Jet Function writes a formula to Excel after you fill in the blanks.

In Design Mode, the Excel window looks sparse, compared to the full report above. Clicking the "Report" or Refresh tool in the Ribbon will run the report and fill it in with information based on the formulas you designed.

In this case I didn't ask Jet to give me a list of Account Categories. Instead, entered them manually. The GL Accounts and Descriptions are automatically linked to the Categories. Therefore this report required more of a knowledge of what Categories are in what parts of the Income Statement.

The Balance Sheet design was similar to that of the Income Statement, in that i had to manually place Category numbers in the corresponding sections. The only trick with the Balance Sheet is the Net Profit line. That is solved by referencing the Net Profit cells in the Income Statement (provided you have the Income Statement and Balance sheet on separate tabs in your Excel file). Jet provides samples of many financial reports.

Of course you need to CHECK YOUR WORK, by comparing the report balances to GP and against one another.

Doing a Cash Flow Statement was too simple. I just downloaded the sample Cash Flow Statement from Jet and it worked - out of the box. Not even a tweak was necessary.