May 2024 (v24.5)

Template Setup

10 Minutes to read

Print
Dark
Light
PDF

Template Setup

10 Minutes to read

Print
Dark
Light
PDF

Article Summary

Share feedback

Thanks for sharing your feedback!

The Planning Control Panel User Guide provides comprehensive information on all fields, functions and capabilities in the Planning Control Panel.

For detailed information on setting up templates for all template types, see the Planning Templates Guide.

What is Template Setup

Template Setup is the 2nd part to creating a template. Adding the template is the first part discussed here. Use the Template Setup page to set up the template structure such as lines, account mapping, formulas, and formatting. Different fields are available based on the type of template selected.

All fields and functionality is discussed in the Description of Fields section, although not all fields and functionality is available. It is based on template type.

How to Access Template Setup

Navigate to Maintenance > Templates > Template Setup. Access the Template Setup page from the Template List page by selecting Setup >Template Setup.

Description of Fields on the Template Setup Application Page

Mappings

Account Mapping

Maps template lines to segment members. Select one of the following options:

Destination Account (DA) - select a line in the template and open the Destination Account page to map template rows to segment combinations. Destination accounts are used for saving the budget or forecast data entered or calculated in a selected budget template against a General Ledger account.

On the Destination Account Mapping page:

To perform a Mass Destination Account Mapping, select All from the Lines list box. This selection displays all template lines and enables Mass Mapping.

Select segment members for each line and click Save.

Reference Account (RA) - select a line and open the Reference Account page to enable the look up of data from another template. For example, payroll accounts may be looked up from the HR template.

On the Reference Account Mapping page:

To perform a Mass Reference Account Mapping, select All from the Lines list box. This selection displays all template lines and enables Mass Mapping.

Select segment members for each line and click Save.

Reference Cube (RC) - select a line and open the Rule page to look up data from the cube. For example, you can look up total revenue for all sales departments within a legal entity.
Mass Load- this option mass loads multiple account members at once and inserts rows for each. . On the Mass Load page, select the checkbox to select or deselect all accounts to load.

Template Mappings

Entity Mapping - open the Entity Mapping page to map entities to the template.
The indication of a Locked entity and an Entity with Data is displayed by a tooltip and a status column.

Locked Entity: If an entity is currently in use by another user, the status column and the tooltip will indicate the same. You will not be able to unmap it.
Entity with Data: If an entity contains data, the status column and the tooltip will display the same message, and you will be informed about it in case you try to unmap the entity.

The following is an illustration of the same.

Approval Role - open the Approval Role page where you can set approval actions.

Global Field Mapping - open the Map Global Fields page to map global fields to the template.

Methods

Monthly Increase$ - Select this option to apply a dollar amount increase over the history for each monthly period.
Monthly Increase % - Select this option to apply an increase percent based on the history amounts for each month.
Full Year Growth - Select this option to apply a total increase percent based on history, and use a spread method to distribute the amounts to the monthly periods.
Annual Value - Select this option to apply a total dollar amount based on history, and use a spread method to distribute the amounts to the monthly periods.
Spreads - Spreads allow users to spread annual value to period columns based on a selected spread method. Select an option (i.e. evenly, 4-4-5, etc.) to apply the spread to the template row that can be modified by the user. Spreads are optional. You may opt to spread based on history data (History Scenario).

Note:

The attribute cell must be unlocked to allow users to make modifications.

Global Fields - Apply a global field to a template row, which displays the lookup formula in the template setup. Global Fields that populate this list box are defined on the Global Fields page. Global Fields are optional.
Round - Round numbers off to the nearest whole number. For example; 67.7 will appear as 68.

Account Reports

Destination (Account) Report - open the Report page to view each line that is identified as a destination account and print or download the report in Excel format.
Reference (Account) Report - open the Report page where each line that is identified as a reference account is displayed. Print or download the report in Excel format.
Show Rule - open the Rule page to view associated multi-dimensional expressions.

Header

Notes - View all notes for the related scenario.
Snapshot - Take a snapshot of the template. Snapshots may be stored in the File Cabinet.
Template History - View information on when the template was modified and by whom.

Grid Actions

Select from the following actions:

Show/Hide Formula Bar - Display or hide the Excel formula bar.

Hide/Show Format Bar - Display or hide the format area, which is where you insert lines, borders, format numbers, and more.

Show/Hide Row Header- Hide or display the template row numbers.

Show/Hide Column Header- Hide or display the template column letters.

Show/Hide Tabs - Hide the tabs for Global Fields and Spreads that appear at the bottom of the Template Input and View screens.

Show/Hide Scenario Header - Display or hide column header information. Column scenario headers display scenario name. Admin users can choose to set the display globally (to hide or show) for column headers. If an Admin user hides the display of column headers globally, other users will not be able to see this information.

Show/Hide Column- Hide or show selected columns (within any template except Initiative templates) to improve visibility.

Reset Column Width- Reset the column width based on the template setup your Admin user performed.

Note:

Column widths are set by administrators. Application users can adjust column widths as well. To adjust the width of a column, open the template, drag the column border to desired width and save the template.

Show/Hide Data Filter- Filter Spreadsheet Data similar to Excel as shown below.

Data filter functionality is available for the following template types: Allocation, Block, Sales, Global Template - Single Copy, Global Template - Entity Copy, Line Item, and HR.

Note:

Data filters will be lost during Scenario Seeding when using a Reference Scenario and Change by Periods.

Reset Column Width Globally - Set columns widths to a default width. This option is available for Global Template Single Copy, Global Template Entity Copy, and Allocation template types. Selecting this option will reset column width preferences of all template users for a specific scenario irrespective of the budget entities. This option is available for templates that are not associated with the Default scenario only

Freeze/UnFreeze Grid- Select Freeze to keep an area of the worksheet visible while you scroll to another area. UnFreeze disables this ability.Freeze panes are user-specific. For example, Jane Doe can set her freeze panes for the BudgetQ12019 template (from the Planning Control Panel - Template Input page) and John Doe can set his freeze panes as desired for the same template. Even if Jane and John have different freeze pane settings, the template will display accordingly based on their selections.

Hide/Show Attributes - Display or show the Attributes column.

Seeding Report

The application logs the cases when formulas could not be transposed for a Plan Scenario and provides this information in the form of an Exception report. This report contains details such as Scenario, Entity, Template, Column and Period references, Source Value, Destination Formula, and Destination Value.

This report specifies details about the Values in Source Scenario and the replaced Value and Formula in the Target Scenario.

Format Bar

Insert and remove template lines. Select one of the following row types for each line within the template:

LINE (L) - Allow data entry or calculations to be performed for the row. Reference and destination accounts may be applied to a line.
HEADER (H) - Format sections or provide additional spacing within a template or add instructions for end users.
CALC (C)- Allow calculations to be defined for a row within the template design, preventing budget users from modifying the formula. Destination accounts may be applied to a calc line.
Apply bold, italic, text color, background color, and borders.
Format:
- Text - Select to allow the entry of text.
- Number - Select to allow numerical entries.
- Currency - Select to allow the system to enter a currency symbol ($) for all numerical data entered.
- Percent - Select to allow the system to place a percent symbol (%) behind all numerical data entered.
Hidden- Select to show or hide the column from users.

Format Bar

Insert and remove template lines. Select one of the following row types for each line within the template:
- LINE (L) - Allow data entry or calculations to be performed for the row. Reference and destination accounts may be applied to a line.
- HEADER (H) - Format sections or provide additional spacing within a template or add instructions for end users.
- CALC (C) - Allow calculations to be defined for a row within the template design, preventing budget users from modifying the formula. Destination accounts may be applied to a calc line.
Apply bold, italic, text color, background color, and borders.
Format:
- Text - Select to allow the entry of text.
- Number - Select to allow numerical entries.
- Currency - Select to allow the system to enter a currency symbol ($) for all numerical data entered.
- Percent - Select to allow the system to place a percent symbol (%) behind all numerical data entered.
Hidden- Select to show or hide the column from users.

You can also reorder the rows in a template setup screen. You can use this option to restructure the defined templates. Move Row Up and Move Row Down arrows options are introduced in the top menu of the template screen.

When you select a row and click the Move Row Up/Down arrows, the contents in the selected row move accordingly, and the remaining rows are adjusted automatically. The number of rows up/down depends on the number of times you click the option.

It is the Admin’s responsibility to verify the functionality of the formulas in the moved and adjusted rows. Using the Move Row Up/Down feature in a new or an empty template is recommended to avoid any discrepancies.

Note:

This feature is applicable only for the default scenario. For now, the Move Up/Down feature is available in GTSC, GTEC, Allocation, New Line Item, Capital, and Allocation templates.

Wildcard Support for Destination and Reference Account Lines

Planful supports wildcard (or special) characters when mapping data from Planful to your local chart of accounts and when you map Destination and Reference account lines during template setup. This section discusses wildcard use in Destination and Reference account lines specifically.

For information on wildcard use in Translations, see the Planning Admin Guide.

When setting up a template, you can map lines to segment/dimension members. You can choose to map a Destination account to populate a budget or forecast amount entered in a template against a general ledger (destination) account. Or, you might opt for a Reference account to look up of data from another template to populate the current template. For example, payroll accounts may be looked up from the Workforce template.

Template Supported Wildcards

Wildcards supported for template Destination and Reference account mappings are shown in the table below.

Wildcard Character	Usage Pattern	Action	Description
%	123%	Allow	All source segments beginning with 123.
	%123	Allow	All source segments ending with 123.
	%123%	Allow	All source segments containing 123.
	123%%	Deny	More than one ‘%’ not allowed in sequence.
?	123‌?	Allow	All 4 digit source segments beginning with 123.
	?123	Allow	All 4 digit source segments ending with 123.
	?123?	Allow	A 5 digit source segments containing 123 in the middle.
	123??	Allow	Multiple ‘‌?’ are allowed in sequence. All 5 digit source segments beginning with 123.
~	100~200	Allow	All source values from 100 to 200 (both numbers inclusive).
	~300	Deny	‘~’ is not allowed as the first value in a source definition.
	500~	Deny	‘~’ is not allowed as the last value in a source definition.
	200~100	Deny	To value must be > than From value.
	100~200~300	Deny	Multiple ‘~’ not allowed in a single source definition.
	100~~200	Deny	Multiple ‘~’ not allowed in a sequence.
	100~??	Deny	‘~’ cannot be used with any other wild card character.
	%~100	Deny	‘~’ cannot be used with any other wild card character.
%	100%? or 100?%	Deny	'%’ & ‘‌?’ cannot be used In a sequence.
	%100?	Allow	All source segments containing 123 in the middle and one character after.
	?10%1	Allow	All source segments containing 10 in the middle and one character before and ending in 1.

Note:

All “LIKE” clauses are supported. SQL documentation with information on all cases supported by Planful are provided in following Microsoft document: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-2017

In Practice

The % wildcard is supported for Destination and Reference account mapping. It can be placed in front of or behind a number as shown in the image below. Based on the selections for the Destination Account mapping below, all history lines will be pulled for any account codes starting with 11.