Sign Up for Training |
Jet Global Company Site
Community
Downloads
Training
Submit a Request
Become a Jet Insider
Give Feedback

Enter Jet Budgets Values Based on Previous Actuals


Related Articles...

Overview

When adding values to a Jet Budget, oftentimes it is convenient to base those values on last year's actual figures.

Let's look at a simple way to accomplish this...

Step-by-Step

  1. Here we see our Budget structure.

    For this example, we'll add a New Work Item so as to add budget values for our Revenue section.

    actuals01.png

    For this example, we'll enter the data ourselves:

    actuals02.png
  2. We want to select the accounts and NAV dimensions, D365 Business Central dimensions, or GP segments for which we will be entering data.

    For this NAV-based example, we'll select our Revenue and Sales Discounts accounts

    actuals04.png
  3. Once we've finished defining our Work Item, we need to select how we are going to enter our data.

    Since we are going to be using Jet functions to retrieve actuals from our data source, we'll select Enter Data in Excel.

    actuals05.png

  4. We can then download the Work Item and open it in Excel

    actuals06.png
  5. To make the process simpler, we'll create a new sheet ("Sheet1") in our workbook and duplicate our account and data structure there.  

    First, we'll copy the account information...

    actuals07a.png

    ... and then paste that to our new sheet:

    actuals07b.png
  6. Next, we only want the account numbers (not the names) for use in the Jet GL() function. 

    We can use Excel functionality to extract that information:

    actuals08a.png
    In this example, all our account numbers are 5 digits.  As your account schedule will differ, you'll need to adjust the Excel function you use.

    We can then copy that function to the rest of our accounts:

    actuals08b.png
  7. Now we want to create the date structure.

    Since the Jet GL() function needs a date format (e.g, 1/1/18..1/31/18 and not "January 2018"), we'll enter the first date we need and then use Excel's EOMONTH function to calculate the last day of the month:

    actuals09a.png

    We can base the start of the next month on the last day of the previous month...

    actuals09b.png

    ... and then copy our functions across to include all the dates we need:

    actuals09c.png
  8. Now we can create a Jet GL() function to retrieve the GL information for our first account during the first time period:

    actuals10.png
    Note that we have used Excel's mixed cell referencing [with a $] to keep either the row or column from changing when we copy this function

    actuals11.png
    Note: when working with Dynamics GP, the GL() function has a different format. See GL Function for Dynamics GP for more information.
  9. Next, we'll copy that function to all the other cells in our grid:

    actuals12.png
    because we used Excel's mixed cell referencing in our function, the copies reference the correct cells.
  10. Going back to our Budget sheet, we can now add references to the data we just created:

    actuals13a.png

    ... making any adjustments we may want to the values:

    actuals13b.png
  11. Once we have placed references to our Jet functions located on the other sheet, we can click on Save to Budget (in the Jet ribbon) to place those values in Jet Budgets.

    actuals14.png
  12. Within Jet Budgets, we can then view the applicable Work Item and see the values that were entered.

    actuals15.png
  13. We can then submit those values or we can return to Excel to make adjustments.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments