This information applies to Dynamics NAV 2013
To help improve the NAV 2013 Budgeting process, Jet Reports has developed some budgeting templates. These templates use a combination of NAV and Jet functionality and the Jet Scheduler to facilitate the budgeting process and allow you to:
- Take a prior budget or actuals as a base to create a budget with rounding, uplift factors, etc.
- Enter a manual amount and allocate evenly over the periods
- Enter amounts with an allocation basis
Watch the Video
In addition to the instructions in this article, you can also watch the video walking you through the process:
The three templates are designed to allow you to use three different methods for settings your budgets - static, dynamic, and scheduled. You can:
- Set budgets based on account/dimensions which have been exported from NAV (static)
- Set budgets by using the Jet Excel add-in to insert the accounts (dynamic)
- Use the Jet Excel add-in Scheduler to replicate and distribute budgets.
Overview - How it Works
NAV has the ability to import and export budgets. When a budget is exported from NAV it creates range names in the excel workbook. The range names include:
|GLacc||List of all the GL Accounts|
|NAV_DIM1||First dimension selected to export|
|NAV_DIM2||Second dimension selected to export|
|Period1||First period column|
|Period2||Second period column|
All of the Range names are in separate columns and start and end on the same rows. The cell above each of the range names is the identifier (For example, the Date, or the Dimension)
When you import the worksheets back into NAV, it will read those range names as arrays and import the data back in.
The templates already have those range names set up along with certain Jet/excel functions which allow you to update the budget figures
Static Method - using Budgeting NAV 2013 Template.xlsx
The lines at the top are different Jet or Excel functions which provide several methods of budget input/allocation
The rows to copy down are:
- Previous actual or budget uplift (change and round) - GL accounts only
- Previous actual or budget uplift (change and round) - GL accounts and one dimension
- Previous actual or budget uplift (change and round) - GL accounts and two dimensions
- Straight line allocation of fixed amount
- Customized allocation
- (the allocation basis is in the next row down - DO NOT COPY THIS ROW !!! )
There are two techniques you can use for this template
1 - Manually insert accounts and dimensions into the template
- Copy or enter account codes and dimensions into the template - one line for each unique combination
- Now you can copy in the functions from the top of the template (lines 3 through 6) into the cells
2 - Copying from an exported budget template
Here you export a template from NAV and the copy into the template. This has the advantage of having subtotals in the template.
- From NAV Export your budget to excel (Even if there are no numbers, this will export the lines and columns
- Save this report, and exit excel. Start a new instance of excel and open the template report. Then open the Budget we exported earlier. (We need to do it this way so both workbooks are in the same instance of Excel)
- We need to open from within excel rather than double clicking on the file from explorer - if we do that then it will open in a separate instance of excel and formulas wont copy across
- The template is setup for 2 dimensions. When you export from nav it will insert columns for those dimensions. If you have selected 1 or no dimensions you will need to insert columns before copying them into the template
- Now you can copy in the functions from the top of the template (Lines 3-6) into the cells.
Dynamic Method - using Budget template NAV 2013 Batch reports.xlsx
This template provides the ability to automatically insert rows from NAV using Jet functionality.
- You can choose which of the function types to use by copying in the formulas fro the rows at the top into the report (on row 13)
- If you select uplift, select weather you want to copy actuals, or budgets, the time lag, rounding etc. from the options
- Select the account range you want to create budget lines for.
- Select a budget Name.
- Then select which Dimension you want. You can either select a global dimension or the dimension of the budget that you selected. If you don’t budget by dimensions, leave this blank
- Select the Filtering you want on the Dimension. There are a number of options here:
- You can select multiple dimensions (Eg: ADMIN..SALES) This will insert multiple sheets, 1 sheet for each Dimension value will be inserted
- Leave blank - this will report for amounts which have no Dimensions
- If * will select all values in a single sheet (With no dimension allocated)
- Run report
- You can edit the numbers manually if you wish. This will be highlighted with conditional formatting so you know which cells have been changed.
- In NAV, import the excel workbook. (Note that if you have run the report inserting sheets, and then re-run the report for a single sheet, the tab name of the report page will show as the dimension name of the first sheet when the report was run)
Scheduler Method - using Budget template Batch Master.xlsx and Budget template NAV 2013 Batch report.xlsx
You can use the Jet Excel add-in Scheduler to run and distribute departmental budgets. This involves setting up a scheduled job in the Jet Excel add-in to run the batch master . The batch master report then initiates the running of the Batch report once for each line in the master.
In the Budget template Batch Master ...
- Insert one line for each report you want sent out, with the appropriate parameters (You can put jet functions in here to have the report create the lines from NAV as well)
- Setup the description for the email header and body. This template inserts the department code into the header.
- Check that the report name and location is correct in the adjacent blue columns
- Once that is done you need to create a scheduled task in the Jet Excel add-in Scheduler. You can automate this or just run manually. To test, you can insert your own email address in the master.
- Ensure that you select “Create and execute batch file from report” and select the range name of "Batch"