Using the Excel Templates with the Planning Applications

Follow

 

You may wish to use Excel’s features to manipulate data entry for Data Projections. The Import/Export feature in the Planning applications enables you to export data to an Excel spreadsheet, edit and add rows for Data Projections, and then import the file to the application. The template does NOT delete Data Projections from Planning Maestro.  You can only Delete Data Projections directly in the Planning application or using the Clear Data tool.

This document shows you, a Planning Maestro user, how to import and export Excel templates in the Planning applications.

You have three options related to Excel for the working grid in the Planning applications.

Export Grid to Excel

Export Data Projections to Excel

Import Data Projections from Excel

 

Export Grid to Excel

Use this option to export the working grid to Excel. The Excel file contains a worksheet that contains only rows for the Data Projections that are currently visible in the "working grid."  The current selections for the Filters are the header rows and each Dimension located in Rows are the columns in this worksheet.  This is presented in the same table format as the Planning application.

Info.png Please Note: This option ONLY  allows you to review data. You cannot re-import a grid back into the application.

Export Data Projections to Excel

Use this option to export the Data Projections (line items) to Excel into a template structure file.  The file includes a row for each Data Projection currently in the "working grid."  There is a column for each dimension including: Name, VersionAccount Number, the Dimension Member selected along with the properties of each line item. This format is useful if you want to edit Data Projections. This file includes worksheets aligned to functions of the properties for the Data Projections.

Info.png Please Note: This option allows you to review or edit data. You can re-import Data Projections back into the application if needed.

 

Step 1: To export data to Excel, hover over the Export/Import icon ( Export-import_icon.png ) and select the desired option.

N2d.jpg

Step 2: An Excel spreadsheet with the Grid or Data Projections downloads to your computer. Open this file to view and edit the data as indicated.

E1f.png

Data Projections Template

Each application produces an Excel workbook with a similar template format consisting of worksheets that apply to the properties that can be assigned to the data projections.

The Main tab is titled for the application from where it is generated: Operating ExpensesRevenuePersonnelDrivers, or Adjustments. Additional worksheets include Manual Period ValuesFactors, Factors Period Values, Data Link Definitions, and Increase_Decrease Methods.

The first few columns of each worksheet align to the Dimensions deployed to your plan and the Data Projection type. Each row represents a Data Projection in Planning Maestro.

Main Tab

The Main tab enables you to to edit properties of Existing Data Projections as well as create new Data Projections. Deleting rows on the template file does NOT remove them from Planning Maestro when the file is imported.

For existing Data Projections, you can edit the Property columns, with begin with Description 1 and flow to the right.  Any changes made to dimension columns will create a new data projection upon import.

Manual Period Values Tab

The Manual Period Values tab enables you to enter specific amounts for each period, instead of using the Amount and Spread Method indicated on the Main tab. The values entered on this worksheet will override any data entered for Amount and Spread Method on the Main tab. When this information is imported, the user will see these values displayed as purple. The appropriate row(s) to edit on this worksheet are indicated for the related application. For each Data Projection that you would like to enter manual period values, enter values to the period column.

Note that only certain Transaction Types are editable through Excel files. Any Transaction Type that includes “default” or “(default)” is uneditable:

Application

Editable Transaction Types

(any type other than Default types)

Operating Expenses

Operating Expenses and Expense Input

Revenue

Revenue and Revenue (Input)

Personnel

Headcount (End), Headcount Decrease, Headcount Increase, Planned Hours, Planned Hours (Input), Regular Hourly Wages, Regular Hourly Wages (Input), Headcount (End),

Drivers

Monetary, Monetary (Input), Related Cost, Related Cost (Input), Hourly Wage, Hourly Wage  (Input), Hours, Hours (Input), Period Related Expense, Period Related Expense (Input)

Adjustments

Adjustment, Adjustment (Input)

Best practices for using this tab are:

  • Do not add new Data Projections to this tab.  Add them to the main tab and then copy the appropriate dimension columns to this tab.
  • Enter Manual Period Values to the appropriate Input row for the application. When you use this tab, we recommend that you remove all "other" Transaction Type rows. To do this, filter the Transaction Type column to view all but the Expense Input rows and then delete those rows.
  • Do not use formulas to populate values to the periods

Factors, Factors Period Values, and Data Link Definitions Tabs

These tabs contain information about the Data Links you can attach to lines of your budget. For example, a Data Link can link an employee’s vacation hours to their Personnel record.

Info.png Please Note: For guidance in using these tabs for your specific plan, please contact Centage Support or your Centage Consultant.

Increase_Decrease Methods Tab

The Increase_Decrease Methods tab enables you to attach or detach Increase/Decrease Methods, such as a 10% Increase Q3, for multiple Data Projections.

Unlike the Main tab, if you delete a row from this tab, it will remove the method from being attached to the Data Projection.

Best practices for using this tab are:

  • Copy the dimension columns from the Main tab
  • Make sure to use the correct name (exact character match) from Planning Maestro for every Increase/Decrease Method.
  • Before you Export the template add the Increase/Decrease Method to the property in Planning Maestro so you can copy that information to all the relevant rows.
  • Do not delete rows from this tab unless you would like to remove the Increase/Decrease Method from the selected Data Projection.

Importing from Excel

You can import Data Projections you created or edited in Excel into any of the Planning applications.

Info.png Please Note: Before you import from Excel, make sure that none of the Data Projections in the spreadsheet are linked to an Undesignated account.

 Step 1: To import Data Projections you created or edited in Excel, click on the Export/Import icon ( Export-import_icon.png ) and select Import Data Projections from Excel.

 N2e.jpg

Step 2: Select the correct Excel file from your desktop and click Open.

Step 3: A popup message displays how many records you updated or created. Click OK.

E1h.png

  • Records you edited in Excel are updated in this application.
  • Records you created appear in the table.

Info.png Please Note: If you delete rows in the Excel template you are importing, Planning Maestro will not delete those Data Projections in the application itself. To delete a record, use the Delete icon ( Delete_line_item.png )  in this application. To delete multiple records, use the Clear Data tool.

E1i.png

Info.png Please Note: If your Excel file contains errors, your import will not complete. An error message appears and displays the reason(s) you cannot import your file. Common reasons include:

  • Empty data cells
  • Using formulas in cells
  • Data type mismatch in cells
  • Incomplete or nonexistent members for any of the Dimension columns
  • Duplicated rows on a single worksheet - each row must be unique combination of dimension selections
  • Rows on supporting tabs where the Data Projection does not exist in Planning Maestro or on the Main tab

Use the error message(s) to edit the Excel spreadsheet until you remove all errors. Then, try importing it again.

Info.png Please Note: Each Import process must complete before any other user can initiate another import.  When this is the case, a message is displayed to notify the user that another Import is currently being processed, please try again later.

1 out of 1 found this helpful

Comments

0 comments

Please sign in to leave a comment.