Concept: Quote Spreadsheet Setup

Spreadsheet Setup

General Setup

The first row in each of the tables that you copy must be the table token for table identification, and the second row must be a header row with the column names.
A token is a label that you add at the top most left cell row of the respective data table. Based on the table type, you can specify these labels:
<DISCOUNTS>
<EXPENSES>
<ITEMS>
<3RD PARTY COSTS>
<PHASES AND TASKS>
<ROLES>
<SERVICES>
<TASKS & PHASES ALLOCATIONS>
For nested tables, start with the service table first. You can nest the rest of the tables under the respective service line.
The last row must have a <LINES END> token to signify the end of the data.
Column names must be the same as the Workday predefined column names. Workday ignores column order.
This match is case-sensitive. When the names don’t match, Workday ignores that column data in the paste process. See the example sheet.
Make sure there are no blank rows in a table in the spreadsheet. When there is a blank row in the middle of a table, all data after the blank row is ignored.
For large data sets, when you want to delete all existing data in your quote grid and paste new data from your spreadsheet, use the <REPLACE ALL> token in the top most left cell row of the given data table.
See the example spreadsheet template for all required fields, optional fields, table tokens, and column names.

Services Setup

Specify these in your spreadsheet for the service:

Service Name
: The display name of the service. It is used for mapping Roles to their Services.
Effort Estimation
- How the service will be estimated:
High Level
,
By Phase
, or
With Phases and Tasks
.
When the effort estimation for the service is
High Level
, you must specify an end date for the service.
When the effort estimation for the service is
By Phase
, you can paste in Phases and specify allocations for Roles by Phase.
When the effort estimation for the service is
With Phases and Tasks
, you can paste in Phases and Tasks, and specify allocations for Roles by Task.
Billing Model
,
Project Hierarchy
,
Project Billing Rate Sheet
,
Sales Item Price List
,
Rate Type
, and
Worktags
for the service.
When you have multiple worktags, add a separate column for each worktag. You can retrieve the Reference ID for the worktag type by accessing the
Maintain Reference IDs
report.
When using the
Rate Type
of
Days
, you can also add a
Number of Hours per Day
column to specify a value for the days. When you don't provide this value, Workday defaults to 8 hours.

Roles Setup

Specify these in your spreadsheet for the roles:

Role Reference ID
is required. Note that you can modify this value in the tenant to be the same as the role name (using the "maintain reference ids" task).
The
Role Display Name Override
column is a required field and cannot be blank. When you don't specify a value, we prevent you from pasting the data. To use the same name as your project role, ensure that the override column has the same names.
You must define:
If the role will have a discount or not by adding a
Prohibit Discount
criteria column at the end of the respective tables.
If the role is billable or not by adding a
Billable
criteria criteria column at the end of the respective tables.
Rate Categories
field is optional and you can add it at the end of the respective table. Add a separate column for each rate category.

Items Setup

Specify these in your spreadsheet for items:

You must define if the item will have a discount or not by adding a
Prohibit Discount
criteria column at the end of the respective tables.
You must define if the item is billable or not by adding a
Billable
criteria criteria column at the end of the respective tables.
You can define if an item is recurring or not by adding a
Recurring Item
,
Reference Reference ID
, and
Recurrence Length
columns at the end of the items table.

Expense and Third Party Cost

You must add a
Billable
criteria column at the end of the expenses and third party costs tables to define if they are billable or not.

Phase and Task Allocations

To paste allocations for phases and tasks, you first need to create the phases and tasks under the service, and then you can paste in the allocations from the
Phase and Task Allocations
table.

Paste Behavior

Pasting Process for the Grid

You can paste only when the existing quote is in edit mode and not in the
View Quote
mode.
The new data you add in the quote, appears below the existing data and doesn’t override the data.
If the data in the table is missing required fields, Workday doesn't create the rows in the quote grid when you paste the data.
When there are duplicate columns, Workday uses the data from the last column to populate the role lines.
Workday allocates hours evenly across the duration or phases.
The pasting process doesn’t override the existing role lines.
You can access the pasted data in weeks, months, contract years, phases and summary view.
You can access the pasted data in the
Quantity
view only when the service role has phases.
When you paste data for a service with phases and tasks, first paste the table with the Work Breakdown Structure data including services, phases, and tasks in the
Quote
cell in the quote grid. Next, paste the allocation data including roles and hours as a second step in the
Service
cell.

Spreadsheet Limitations

You can paste a maximum of 500 role rows and 500 service rows each.
Workday can’t spell check the data imported from Excel. When you misspell a column name, we don’t include it.
When there is a blank row in the spreadsheet, Workday ignores all data after the blank row.
When you encounter any errors, the entire paste process doesn’t execute. You must first fix the errors, and then try pasting the entire data set again.
The paste process ignores:
Formulas in the spreadsheet and only includes the calculated value in the quote.
All currency symbols in the numerical fields of the spreadsheet.
All formatting including font and color.
References between different spreadsheets, but supports values referenced on the different tabs of the same spreadsheet.
You can paste
Unit Cost Override
data only when you first enable the
Allow Role Unit Cost Overrides
option on the
Maintain Services CPQ Settings
task.
Role allocation cells that are empty in the spreadsheet, will default to a value of 0 when you paste the data into a quote if the role exists in the service. When that role is not present in a service, Workday ignores the information and leaves the value blank in the quote.
This functionality only works in the Timeline and Summary view. When you are in the Work Breakdown view, you won’t be able to copy data and paste into the quote.

Example