Import Jet Budgets Data into Dynamics GP

Overview

How-To Video

1. Here we see that we have an existing Jet Budget for GP with values already entered for a specific time period

   

   We see our main account segment, the other segments we've associated with this budget, and the budget values for each period.

    

2. Within Dynamics GP, navigate to the Budgets area by selecting Cards, Financial, and then Budgets:

   

3. From the Budget card, we see all of the existing budgets within GP

   

   
   

   If we wanted to bring our data into an entirely new budget, we would select using Budget Wizard for Excel from under the New button...

   

    

   and then work through the Budget Wizard...

     

4. For this example, let's proceed by bringing our Jet Budgets data into an existing GP budget.

   We'll start by exporting our existing GP budget structure to Excel by selecting Export to Excel from under the File button:

   

    

   On the next screen, we'll select New Workbook:

   

   
   

   We'll click OK and then specify the name and location under which we want to save the workbook:

   

   
   

   Once we click Open, Dynamics GP will place all the information from our existing GP budget into that workbook...

   

   ... and then automatically open the workbook in Excel:

   

5. This file will be used for importing our Jet Budgets data into GP. 

   

   First, however, we need to save and work with a temporary copy.  So, the the first thing we need to do within Excel is save this file under a temporary name:

   

   Also, because Excel was started in an automated fashion from within Dynamics GP, the Jet add-in may not be present.

   

   If this is the case, close Excel, re-start Excel, and then re-open the temporary file you just saved

6. We need to transform this temporary file into a Jet report.  The first step of that is to right-click on both row 1 and column A and then insert a new blank Row 1 and Column A:

   

7. Next, we enter Auto+Hide+Values in cell A1 (this signifies that this is a Jet report) and - to make it easier to build our Jet functions - we'll enter values in row 2 to represent our budget periods (in this case, 1 through 12):

   

8. And now we're ready to gather the Jet Budget data for each combination of date period, account number and other segments:

   

   Here we see a GL(JetBudgets) function: it contains the name of our Jet Budget Name, uses an Excel function to extract our main or "natural" G/L Account number from the full account listed in column B, and uses the period numbers we placed in row 2 to make the full period name for our Start Period and End Period.

   We can then copy that function to all the other cells.  Because we used the $ symbol in our function, we can ensure that either the row or column (depending on where we placed the $) stays fixed when the function is copied.

9. Now we can run our report (we'll then see all the Jet Budgets information associated for each of our selected accounts - broken down by period):

   

   We can COPY those values from this copy document, PASTE them in the ORIGINAL file we exported from Dynamics GP, and then save that file.

10. From within Dynamics GP, we can now select Import from Excel from under the File button:

    

    Select to place the data in an existing budget:

    

    ... and the click the search icon and select the GP budget name:

    

    
    

    Click Next and then click Browse... to navigate to and select the file in which you saved your Jet Budgets data:

    

    ... and select the worksheet holding the Jet Budgets data.

    Click Next and then click Finish

11. Dynamics GP will now import that data from the Excel file and place it in your selected GP budget: