Export (0) Print
Expand All

Create budget plan worksheet templates by using a wizard [AX 2012]

Updated: November 27, 2013

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.

Budget plan template wizard process

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.

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

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

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

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 noteSecurity Note

    Do not change the location where the file is stored.

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.

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.

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.

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.


Announcements: To see known issues and recent fixes, use Issue search in Microsoft Dynamics Lifecycle Services (LCS).
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft