Create budget plan worksheet templates by using a wizard

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2

This topic explains how to create a Microsoft Excel template for budget plan worksheets by using a wizard in Microsoft Dynamics AX, and how to decide which fields to use for the worksheet header, which columns to include, and which matrix fields to create. Each template is specific to a legal entity and an account structure.

This feature is available only 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 a budget plan templates manually. For more information, see Create budget plan templates manually.

The following illustration shows how to create a template for budget plan worksheets by using a wizard. The numbers correspond to the procedures later in this topic.

Prerequisites

The following table shows the prerequisites that must be in place before you start.

Category	Prerequisite
Installation	Install the Microsoft Office Add-ins component. For more information, see Install Office Add-ins.
Application Integration Framework (AIF)	Initialize AIF. For more information, see Initialization checklists and Set up Application Integration Framework. Activate the BudgetServices inbound port. (Click System administration > Setup > Services and Application Integration Framework > Inbound ports. Select the BudgetServices service, and then click Activate.) For more information, see Managing integration ports.
Document management	Activate the BudgetPlanOfficeAddinService document data source. (Click Organization administration > Setup > Document management > Document data sources. Select the Budget module and the BudgetPlanOfficeAddinService data source name, and then click Activate.) For more information, see Set up integration with Microsoft Office Add-ins.
Budgeting setup	Configure budget planning. For more information, see Key tasks: Configure budget planning and set up budget planning processes.

1. Decide which fields to use for the worksheet header

For the worksheet header, include fields that identify the budget plan that the worksheet template will be used for. Use the following table to help you decide which fields to include.

Type of worksheet header field	Considerations	Examples
Budget plan identifiers	Use one or more of these fields to help identify the budget plan for the users of the worksheet template.	Budget plan Budget planning process Budget planning stage Document number Document status Responsibility center
Responsible party	Use this field to identify the person who is responsible for the budget plan.	Budget plan preparer
Budget plan characteristics	Use one or more of these fields to track information about the budget plan.	Budget plan priority Created by Created date and time Is historical Modified by Modified date and time Rank (Budget plan) Workflow status

2. Decide which columns to include on the worksheet

For the worksheet columns, include fields that represent budget plan lines. Use the following table to help you decide which fields to include.

Type of worksheet column	Considerations	Example
Budget plan identifiers	Use one or more of these fields to identify the budget plan that the budget plan lines are a part of.	Budget plan name Document number
Budget plan scenario information	Use one or more of these fields to specify scenario information for the budget plan line.	Budget plan scenario Budget plan scenario description Scenario unit of measure class Unit of measure
Budget plan line fields	Use one or more of these fields to specify information about the budget plan line.	Asset Budget class Budget plan estimate type Comment Currency Dimension description Dimension name Effective date Forecast position Is recurring New request Project Proposed asset Proposed asset description Proposed project Proposed project description

3. Decide which matrix fields to create

You can add one or more matrix fields to show totals for groups of related records on the budget plan lines. Examples of matrix fields include the following:

• Previous year actuals

• Previous year budgeted

• Department request Q1

• Approved budget January

4. Create a template for a budget plan worksheet

To create a budget plan worksheet, follow these steps:

1. Click Budgeting > Setup > Budget planning > Budget planning configuration.

2. Select the Templates page, and then click the Wizard button.

3. On the first page of the wizard, click Next >.

4. On the Define the template parameters page, enter the following information, and then click Next >.

   Field	Description
   Template name	This name is displayed in the header of the worksheet template and is used for the file name of the template.
   Attachment folder	The template file is stored in this location.
   Legal entity	Specify the legal entity that will use this template.
   Account structure	Specify the accounting structure to use with this template.

5. On the Define the template header fields page, select the budget plan document headers to use for the template, and then click Next >.

6. On the Define the columns for the template page, select the budget plan fields to use for columns in the template, and then click Next >.

7. On the Define the matrix fields for the template page, click the Matrix fields button.

8. On the Matrix fields for worksheet templates page, click New, and then enter the following information.

   Field	Description
   Name	Enter the name for the matrix field. This field is used as a column name for the calculated amounts column.
   Description	Enter the description for the matrix field.
   Measure	Select whether to use amount, quantity, or price for the calculation.
   Read-only	Select this check box to prevent the calculated amount from being edited in the Excel template. You might do this for a budget plan template based which budget stage it’s in. For example, if the budget plan is at the manager approval stage, department request amounts should not be changed. Or, you might use this check box for previous year actual and budgeted amounts, which are included in the template for reference.
   Filters	Select the filters to apply to budget plan lines when the matrix field values are calculated.

9. Repeat step 8 for each matrix field that has to be calculated in the worksheet template.

   You can use the Copy button to create a new matrix field that is based on an existing matrix field.

10. On the Define the matrix fields for the template page, move the matrix fields from the Available list to the Selected list, and then click Next >.

11. On the Preview the template page, review the wizard selections. Navigate back in the wizard to change the selections as needed.

12. Click Finish to create the Excel template.

13. Review the worksheet template opened as an Excel file and make adjustments as needed (such as formatting, adding charts, and so on). Save the Excel file after you make changes.

    Security Note
    Do not change the location where the file is stored.

5. Select the worksheet template to use for the budget planning process

To select the worksheet template to use for the budget planning process, follow these steps:

1. Click Budgeting > Setup > Budget planning > Budget planning process.

2. Select the budget planning process where the template will be used.

3. On the Budget planning stage rules and templates FastTab, select a budget planning workflow and budget planning stage, and then select the template that you created in the previous procedure.

6. Create a budget plan and review its information by using a worksheet template

After the worksheet template has been created and added to a budget planning process, you can create a budget plan and then export it to an Excel workbook that uses the worksheet template.

To create a budget plan and review its information by using a worksheet template, follow these steps:

1. Click Budgeting > Common > Budget plans > All budget plans.

2. On the Action Pane, click Budget plan to create a budget plan.

3. In the Budget planning process field, select a process.

4. In the Budget plan field, enter a name for the budget plan.

5. Click Create.

6. To add lines to the budget plan, click Add line. Select the accounting structure, and then enter financial dimension values and the amount for each budget plan line.

7. In the Budget plan form, on the Action Pane, click Worksheet. A Microsoft Excel file, which is based on the worksheet template that you created in the previous procedure, opens.

Next step

Work with others in your organization to add budget information to the worksheet in Excel. When you are satisfied with the information that’s in Excel, import the budget plan information back into Microsoft Dynamics AX. For more information, see “Use worksheets and justifications in budget plans” in Key tasks: Create and process budget plans.

Related tasks

Create budget plan templates manually

Technical information for system administrators

If you don't have access to the pages that are used to complete this task, contact your system administrator and provide the information that is shown in the following table.

Category	Prerequisite
Configuration keys	Budget control Budget planning configuration key
Security roles	To create a budget plan worksheet template by using a wizard, you must be a member of the Budget manager (BudgetBudgetManager) security role.