This topic explains how to create a Microsoft Excel template for the budget plan spreadsheets using a wizard in Microsoft Dynamics AX and how to decide which fields are to be used for the spreadsheet header, which columns to include And which array fields should be created. Each template is specific to a legal entity and an accounting structure.
This feature is only available if Cumulative Update 7 for Microsoft Dynamics AX 2012 R2 and Office Add-ins for Microsoft Dynamics AX are installed.
Starting with Microsoft Dynamics AX 2012 R2, you can create budget plan templates manually. For more information, see Creating Budget Plan Templates Manually.
The following example shows how to create a template for the worksheets in the budget plan using a wizard. The numbers correspond to the procedures described later in this topic.
Process of the Budget Plan Template Wizard
The following table lists the prerequisites that must be met before you begin.
Install the Microsoft Office Add-ins component. For more information, see Install Office Add-ins.
Implementation Integration Framework (IDA)
Initialize AIF. For more information, see Initialization checklists and Configure Application Integration Framework (AIF).
Enable the BudgetServices input port. (Click System Administration> Configure> Services and Application Integration Framework> Inbound Ports. Select the BudgetServices service, and then click Activate.) For more information, see Managing integration ports.
Activate the data source of the BudgetPlanOfficeAddinService document. (Click Organization Management> Configure> Document Management> Document Data Sources. Select the Budget module and the BudgetPlanOfficeAddinService data source name, and click Enable.) For more information, see Configuring Integration With the Microsoft Office add-ons.
Configure budget planning. For more information, see Key Tasks: Configure Budget Planning and Define Budget Planning Processes.
1. Decide which fields are to be used for the spreadsheet header
For the spreadsheet header, include the fields that identify the budget plan for which the spreadsheet template will be used. Use the following table to help you decide which fields are to be included.
Spreadsheet Header Field Type
Budget Plan Identifiers
Use one or more of these fields to help you identify the budget plan for users of the spreadsheet template.
Budget Planning Process
Budget planning stage
Center of responsibility
Use this field to identify the person responsible for the budget plan.
Preparer of budgetary plans
Characteristics of the budget plan
Use one or more of these fields to track information about the budget plan.
Priority of the budget plan
Date and time of creation
Date and time of modification
Category (Budget Plan)
2. Decide which columns should be included in the spreadsheet
For the columns in the worksheet, include the fields that represent lines in the budget plan. Use the following table to help you decide which fields are to be included.
Spreadsheet column type
Budget Plan Identifiers
Use one or more of these fields to identify the budget plan for which the budget plan lines are part.
Name of budget plan
Information on the status of the budget plan
Use one or more of these fields to specify the status information for the budget plan line.
Status of the budget plan
Description of the situation of the budget plan
Unit type of measure of the situation
Unit of measurement
Budget Plan Line Fields
Use one or more of these fields to enter information about the budget plan line.
Type of budget plan estimate
Description of assets proposal
Description of proposed project
3. Decide which array fields to create
You can add one or more matrix fields to display the totals for the groups of related records in the budget plan lines. Some examples of array fields include the following:
Reais from the previous year
Last budget year
First quarter of the department request
Approved Budget, January
4. Create a template for a budget plan worksheet
To create a budget plan worksheet, follow these steps:
Click Budget Management> Configure> Budget Planning> Budget Planning Settings.
Select the Templates page, and then click the Wizard button.
On the first page of the wizard, click Next>.
On the Define Template Settings page, specify the following information, and then click Next>:
This name appears in the header of the spreadsheet template and is used for the template file name.
The template file is stored in this location.
Specify the legal entity that will use this template.
Specify the accounting structure to use with this template.
On the Define Template Header Fields page, select the document headers for the budget plan that you want to use for the template, and then click Next>.
On the Define columns for template page, select the fields in the budget plan that you want to use for the template, and then click Next>.
On the Define Template Array Fields page, click the Array Fields button.
On the Array Fields page of the worksheet templates, click New, and then specify the following information.
Specify the name of the array field. This field is used as the column name for the calculated amounts column.
Specify the array field description.
Select whether to use the amount, amount or price for the calculation.
Check this box to prevent the calculated amount from being edited in the Excel template. You can do this for a budget plan template depending on the budget stage in which you are. For example, if the budget plan is in the director approval stage, the department request amounts should not be changed. Or you can use this box for the actual and budgeted amounts of the previous year, which are included in the template as a reference.
Select the filters to apply to the budget plan lines when calculating the array’s field values.
Repeat step 8 for each array field that has to be calculated in the spreadsheet template.
You can use the Copy button to create a new array field that is based on an existing array field.
On the Define Template Array Fields page, move the fields from the array in the Available list to the Selected list, and then click Next>.
On the Preview Template page, review the wizard selections. Go back in the wizard to change the selections as needed.
Click Finish to create the Excel template.
Review the open spreadsheet template as an Excel file and make any necessary adjustments (for example, formatting, adding graphics, etc.). Save the Excel file after making changes.
5. Select the worksheet of the spreadsheet for use in the budget planning process
To select the worksheet template for use in the budget planning process, follow these steps:
Click Budget Management> Configure> Budget Planning> Budget Planning Process.
Select the budget planning process in which the template will be used.
On the Budget Planning Stage Rules and Templates drop-down tab, select a workflow and a budget planning stage, and then select the template that you created in the previous procedure.
6. Create a budget plan and review the information with a spreadsheet template
After you create and add the worksheet template to a budget planning process, you can create a budget plan and export it to an Excel workbook that uses the worksheet template.
To create a budget plan and review the information with a worksheet template, follow these steps:
Click Budget Management> Common> Budget Plans> All Budget Plans.
In the action pane, click Budget Plan to create a budget plan.
In the Budget Planning Process field, select a process.
In the Budget Plan field, specify a name for the budget plan.
To add lines to the budget plan, click Add Line. Select the accounting structure and specify the financial dimension and amount values for each line in the budget plan.
In the Budget Plan form, in the action pane, click Spreadsheet. A Microsoft Excel file will open, which is based on the template of the worksheet that you created in the previous procedure.
Work with other people in the organization to add budget information to the spreadsheet in Excel. When you are satisfied with the information in Excel, re-import the budget plan information into Microsoft Dynamics AX. For more information, see “Using Spreadsheets and Justifications in Budget Plans” in Key Tasks: Creating and Processing Budget Plans.
Create Budget Plan Templates Manually
Technical Information for System Administrators
If you do not have access to the pages that are used to complete this task, contact your system administrator and provide the information listed in the following table.
Budget Planning Configuration Key
To create a budget plan worksheet template using a wizard, you must be a member of the Budget Director role (BudgetBudgetManager).