Create budget plan worksheet templates by using a wizard

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.

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.

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.

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.