Help Center

Articles in this section

Excel Template: Data Sheets

Avatar
Meisterplan
  • Updated

This article describes the individual data sheets of the Excel template for the Excel Import and Export in Meisterplan.

For details about the Excel import and export, see the Excel Import and Export article.

For general information about the Excel template, see the Excel Template Overview article.

Roles Sheet

Roles such as Architect, Software Developer, etc. are described in the Roles sheet. Each role must have a unique id stored in the RoleId field. A role's capacity is the sum of  the resources' capacities that have this role defined as their primary role. Not only resources, but also roles can be allocated to projects (see Allocations sheet). Roles can be linked to different OBS units of different OBS types.

Roles are also available in the Roles view of the resource pool in Meisterplan and can be edited there (requires the Edit Resource Pool and Absences right).

Resources Sheet

If you wish to limit resource availability to after or before a given point of time, you can do this in Meisterplan by setting the resource capacity to 0%. To do this, right-click a resource and select Edit Capacity. In the capacity bar, click the desired hire or termination date. Click the capacity segment created this way and enter 0%. This will set the resource's capacity to zero before or after the selected date, to the same effect as setting the hire or termination date via the Excel import.

The Resources sheet contains:

  • ResourceId: unique identifier
  • Firstname: first name
  • Lastname: last name
  • Email: e-mail address
  • PrimaryRoleId: unique identifier of the resource's primary role (RoleId field from the Roles sheet)
  • CostPerHour: cost per hour
  • HireDate: resource start date - the resource will not be available before this date
  • TerminationDate: resource termination date - the resource will no longer be available after this date
  • External: specifies whether the resource is external (1) or internal (0)
  • Calendar: unique identifier of the resource's calendar (CalendarId field from the Calendars sheet)
  • Country: country for geo-reporting
  • City: city for geo-reporting
  • PostalCode: zip code for geo-reporting
  • OBS1 to OBS4: OBS units; additional OBS columns may be added
  • Skill1 to Skill3: skills; additional skill columns may be added

The resource’s capacity is calculated from its calendar.

If no value is entered in the CostPerHour field of a resource, the cost per hour of its primary role is used for allocation cost calculation.

Resources can be linked to different OBS units of different OBS types (defined in the OBS sheet) as well as to different skills (defined in the Lookups sheet). You may add additional OBS and skill columns as required, but referenced OBS units and skills must be specified in the OBS or Lookups sheets respectively.

The Country, City and Postal Code fields are currently not visible in Meisterplan and are provided for geo-reporting. 

Projects Sheet

In the Projects sheet, project data such as unique id, name, priority, manager, scheduled start date, scheduled end date, business goal id, notes and the links to different OBS units are specified.

Any custom project fields defined in Meisterplan must be present here with their internal name starting with “cust_” for the import to work. When exporting data from Meisterplan, any custom project fields will be exported along with the projects they belong to.

The Allocations, ProjectFinancials, ProjectSegments and Milestones sheets can contain references to the projects specified here.

The values in the Priority column must be an integer.

Allocations Sheet

The Allocations sheet associates projects with their allocated roles and resources. Each row is an allocation segment defining the time range and for how many hours a resource is allocated.

ProjectFinancials Sheet

The ProjectFinancials sheet contains benefits, CapEx costs and OpEx costs which are assigned directly to the projects.

  • FinanceType: Select OPEX, CAPEX or BENEFIT
  • Timing: Select the date on which the cost is paid or the benefit is received:
    • Project Start
    • Project Finish
    • Milestone (selected in the Milestone column)
    • Date (entered in the Date column)

OBS Sheet

This sheet contains data of the Organizational Breakdown Structure (OBS). More than one OBS can be defined by specifying different OBS types in the OBSType field. On this sheet, each OBS unit has its own row. Due to the hierarchical structure of an OBS, the full path from the root node (the OBS type) to the OBS unit must be entered in the FullPath field.

The OBS type, followed by a backslash ("/") and the OBS unit's full path is its unique id with which it can be referenced in the Roles, Resources and Projects sheets (e.g., the unique id of the first OBS unit in the screenshot below is "Department/Consulting").

  • The OBS structure is also available in the OBS view of the resource pool in Meisterplan and can be edited there (requires the Edit Resource Pool and Absences right).
  • Please note that OBS units can only be deleted in the resource pool. The Excel import will only create OBS units that don't exist in Meisterplan yet. It cannot be set to delete OBS units.

ResourceAvailabilityExceptions Sheet

This sheet stores all capacity changes for resources, specified as a percentage of each resource's original capacity defined by its Calendar and CalendarExceptions.

When resource availability exceptions are imported, neighboring segments with the same change factor will be merged (e.g., 50% from 01/01 until 03/31 and from 04/01 until 06/30).

Please note that you can also view and edit resource absences and capacity changes in Meisterplan:

  • In the Absences tab of a resource's detail view in the resource pool
  • Via the context menu of a resource's histogram in the resource section of the Portfolio Designer view

RoleCapacities Sheet

The RoleCapacities sheet holds role capacity changes (e.g., +2 FTE from 06/01/2017).

When role capacity changes are imported, neighboring segments with the same capacity change will be merged (e.g., +1 FTE from 01/01 until 03/31 and from 04/01 until 06/30).

Please note that you can also view and edit role capacity changes in Meisterplan.

Programs Sheet

Projects can be grouped into programs defined in this sheet. The program's priority is inferred from the highest contained project priority.

To add a project to a program, enter the program's unique identifier specified in the ProgramId field on this sheet in the ProgramId field in the Projects sheet.

ProjectSegments Sheet

If your project has several phases with breaks in between, you can detail the individual phases here. Make sure that the allocations and milestone are within the start and end date of a project segment. If you use project segments, the start and end dates of the project are ignored.

Milestones Sheet

Each milestone in the Milestones sheet refers to a project in the Projects sheet identified via the project's unique identifier ProjectId. The milestone id specified in the MilestoneId field has to be unique within a project's context. The scheduled date of the milestone has to be in the range of the scheduled start and end date of the referenced project (or for segmented projects within the range of the scheduled start and end date of a project segment).

A milestone may be assigned any of the project phases listed under Milestone Types in the Lookups sheet. Specify the desired project phase in the MilestoneType field. The assigned project phase will end with the respective milestone and start with the previous milestone or project start.

MilestoneDependencies Sheet

In Meisterplan, dependencies between projects are logically mapped to dependencies between milestones of different projects. Dependencies between milestones within a project cannot be defined in Meisterplan. Each dependency must refer to two different milestones from different projects via their entries in the MilestoneId field defined in the Milestones sheet.

Calendars Sheet

The Calendars sheet describes the work time for each weekday. Different calendars can be defined for each location and for each resource that has a diverged working time.

Each calendar must have a unique id in the CalendarId field so it can be referenced from the CalendarExceptions and Resources sheets.

The CalendarName will be displayed in the Calendars view of the resource pool in Meisterplan.

Derived Calendars and Inheritance

Calendars can be based on another calendar by entering the parent calendar's unique identifier in the ParentCalendarId field. The calendar inherits the week-day work time and calendar exceptions (holidays) of the parent calendar, so that only the differences to the parent calendar have to be specified. Inherited working hours and calendar exceptions can be overwritten in the derived calendar.

All parent calendars have to be listed before their child calendars to avoid circular references.

Default Calendar

Any one calendar can be set as the default calendar via the IsDefault field. This has two effects:

  • New resources created in Meisterplan as well as imported resources not assigned to a calendar will automatically be assigned to the default calendar.
  • The FTE (full time equivalent) unit used to measure the workload of roles and resources in Meisterplan refers to the amount of working hours in the considered period as defined by the default calendar and its calendar exceptions.

Calendars in the Resource Pool

Calendars and calendar exceptions are also available in the Calendars view of the resource pool in Meisterplan and can be edited there (requires the Edit Resource Pool and Absences right).

CalendarExceptions Sheet

Use calendar exceptions to define holidays.

Each exception has a Date, the number of working hours on that day (WorkTimeHours field) and a Description (for internal reference).

Calendar exceptions must refer to an existing calendar in the Calendars sheet via its unique identifier CalendarId.

Calendar exceptions from a parent calendar also appear in all sub-calendars. You only have to define exceptions for the parent calendar.

If desired, the WorkTimeHours and Description of a parent calendar's exception can be overwritten in a derived calendar. To do this, add a new exception for the derived calendar at the same date and enter the desired WorkTimeHours and Description.

Calendars and calendar exceptions are also available in the Calendars view of the resource pool in Meisterplan and can be edited there (requires the Edit Resource Pool and Absences right).

BusinessGoals Sheet

Business goals have a unique internal ID (BusinessGoalId field), name, color and description.

The name, color and description will be displayed in Meisterplan. The BusinessGoalId, Name and Color fields are mandatory.

Assign projects to one of the business goals defined here by entering the associated BusinessGoalId value listed here in the BusinessGoalId field in the Projects sheet.

Settings Sheet

The Excel template supports values for cut-off line and must-have line in the Settings sheet.

The Priority column in the Projects sheet is highlighted in

  • green for must-have projects
  • red for cut-off projects

Lookups Sheet

The Lookups sheet defines columns with predefined values. Currently, the possible project status, resource skills, milestone types for defining project phases and finance categories can be defined here.

  • Project status' are also available under Manage > Project Fields in Meisterplan and can be edited there (requires the Manage Project Fields, General Settings and Project Settings right).
  • Resource skills are also available in the Skills view of the resource pool in Meisterplan and can be edited there (requires the Edit Resource Pool and Absences right).
  • Project phases and finance categories are also available under Manage > General Settings in Meisterplan and can be edited there (requires the Manage Project Fields, General Settings and Project Settings right).

Note: The data import only adds newly entered lookups. You can delete existing values inside Meisterplan.

Share this article:

Was this article helpful?
0 out of 1 found this helpful
Need more help? Contact our support.
Return to top

Related articles