What Happens When You Import Lines to Negotiations by Spreadsheet

By Importing negotiation lines by spreadsheet you can effectively reduce negotiation creation time by completing a spreadsheet file offline and then using that file to import your line information.

This feature is especially useful for negotiations with a large number of lines or complex lines with multiple attributes. This topic contains instructions on how to complete the spreadsheet file and import it to your new negotiation.

The .zip file you export contains a template for you to fill out. You have other reference files containing any cost factors, UOM values, and attribute groups in the application in case you need to use them.

Once you have exported the appropriate spreadsheet files, fill in the template file with your negotiation line information. The following table describes each spreadsheet field in detail and indicates which fields are required and which are optional. Required fields are marked with an asterisk (*). You must enter a value for a required. You don't have to enter a value for an optional field.

The table also explains each field's length restrictions. If a field's maximum data length is 4000 characters, the length for a multi-byte language such as Japanese the limit is smaller. If the field length is less than 4000 characters, the maximum length is the same for both single-byte and multi-byte languages.

The table shows the fields as they appear in the exported spreadsheet. Some columns always appear while other columns appear depending on which negotiation outcome is selected, standard purchase order or blanket or contract purchase agreement. Generally, you can rearrange the column sequence but don’t change the column titles. The attribute, cost factor, price breaks, and price tier columns are an exception to this rule. They should not be rearranged. Be sure that the End of Line Delimiter column is the last column of your spreadsheet. You may also delete optional columns; see the Note following the table for more details.

Notes: All date, time and price format settings should be the same as your user preference settings in the application; for example, 1212,40 vs. 1,212.40. When entering numeric values into the specified fields, set the cell format as Text; other formats might result in error upon importing. If the format is Text and the length is longer than 255 characters, set the cell format as General.

Note that the application treats the values you enter as case sensitive. For example, Lot line is correct, Lot Line isn’t and causes an error.

How You Add Negotiation Lines Spreadsheet Column Explanations

The table shows the different fields available in the import lines spreadsheet. It explains which fields are used with which negotiation type and outcome.

Column Name	All Outcomes	Purchase Order Outcome Only	Purchase Agreement Outcome Only
Line with Price and Quantity (applicable to RFIs only)	Enter Yes to create a line requiring price and quantity values.	NA	NA
* Type	Select the type of line you’re defining. Valid entries include Line, Lot, Lot line, or Group, Group line. To create a lot, first define a row for the lot, and then define a row for each of the lot lines. Lots and lot lines are defined the same way as regular lines. That’s, they can have line attributes, cost factors and other line-related characteristics. Make sure you define your lot lines immediately after the lot row. To create a group, first define a row for the group, and then define a row for each of the group lines. Group level rows are defined differently from regular lines. They can’t have line attributes, cost factors or other line-related characteristics. When defining a group level entry, enter values only for the Type, Description and End of Line Delimiter columns. Make sure you define your group lines immediately after your group row. Group lines rows are defined the same way as regular lines and can have attributes, cost factors, and other line-related characteristics.	NA	NA
Description	Enter a description of each line you want to purchase. This column has a maximum length of 2500 characters.	NA	NA
Requisitioning BU	The business unit requesting the item or service. This column doesn’t appear if you’re using standalone Sourcing.	NA	NA
Line Type	Distinguishes between quantity-based and services-based lines. Select one of the predefined values set up in the application.	NA	NA
Item	Enter the item number based on the value in the Item Master in the application.	NA	NA
Revision	Enter the item revision number.	NA	NA
Category Name	The category name describes the broad family or category to which the line belongs. The application has predefined categories for all items.	NA	NA
Quantity	NA	Enter the number of units (in terms of the unit of measure you defined) you want to purchase.	NA
Estimated Quantity	NA	NA	The estimated number of units of the item required, in the buyer's specified unit.
UOM	Enter the unit of measure in which you want to negotiate for the line if it’s different from the UOM associated to that line type. See the AddLinesUOM.txt file in the .zip for available UOM values.	NA	NA
Location	NA	Enter the name of the address where you want the item or service to be delivered. Not entering a location results in the default location being used. If this is a new location, you must first enter the location online before you can use it in this template.	NA
Requested Delivery Date	NA	To request delivery to your location address on a specific date, enter that delivery date in this column. Make sure you use the same format in the spreadsheet as the date format in your user preferences for the application.	NA
Estimated Total Amount	NA	NA	Estimated amount of business in dollars that you expect to pay for this line.
Minimum Release Amount	NA	NA	Enter the minimum amount which can be released against an agreement.
Current Price	Enter the price at which you currently buy one unit of the item or service. If you plan to add cost factors to a line, this value represents the Total Current Price for one unit of the item or service. The price should be entered in numeric format (for example, 5.75).Five seventy-five is invalid. Omit currency signs. Current price is used by the application when calculating savings.	NA	NA
Start Price	Enter the starting response price for one unit of the item or service you want to purchase. If you plan to add cost factors to a line, this value represents the Start Price Total for one unit of the item or service. Use the unit of measure and currency you have specified for this negotiation. The price should be entered in numeric format (for example, 5.75). Five seventy-five is invalid. Omit currency signs.	NA	NA
Target Price	Enter the price at which you want to purchase one unit of the item or service. If you plan to add cost factors to a line, this value represents the Target Total Price for one unit of the item or service. State the price in terms of the unit of measure and currency that you have specified for this negotiation. The price should be entered in numeric format (for example, 5.75). Five seventy-five is invalid. Omit currency signs.	NA	NA
Display Target Price	Enter Yes or No to indicate whether you want to display the target price to the suppliers. The application defaults to No if you don't enter a value.	NA	NA
Allow Alternate Lines	Designate whether the supplier can enter an alternate line in response to this negotiation.	NA	NA
Note to Suppliers	Enter a text note to the suppliers who submit responses on the item or service you want to purchase. This column has a maximum data length of 4000 characters.	NA	NA
Line Target Price	Enter the total target price of your line excluding any cost factors. This column only appears if cost factors are enabled for this negotiation.	NA	NA
Display Line Target Price	Enter Yes or No to indicate whether you want the line target price to be displayed to suppliers. This column only appears if cost factors are enabled for this negotiation.	NA	NA
Cost Factor	Enter the name of any cost factor to which you want the suppliers to respond. This column only appears if cost factors are enabled for the negotiation. A text file AddLinesCostFactors.txt is included in the exported .zip. The Cost Factor column and the three following columns comprise the group of columns used to define a single cost factor for this line. Insert and complete a new group of the four columns for each additional cost factor associated with this line.	NA	NA
Pricing Basis	Enter per unit, fixed amount or percentage of line price, to specify how the particular cost factor value is calculated. This column only appears if cost factors are enabled for the negotiation	NA	NA
Cost Factor Target Value	Enter the target value of the cost factor. This column only appears if cost factors are enabled for the negotiation	NA	NA
Display Cost Factor Target	Enter Yes or No to indicate whether you want the cost factor's target value to be displayed to suppliers. This column only appears if cost factors are enabled for the negotiation	NA	NA
Suppliers Modify Price Breaks	Enter Yes or No to provide the supplier with the ability to modify the price breaks.	NA	NA
Price Break Type	Select Cumulative to apply the price break to the cumulative quantity on all released shipments for the item as part of the blanket purchase agreement. Select Noncumulative to apply the price break to quantity purchased on a single release against the blanket purchase agreement.	NA	NA
Price Break Ship-to Organization	Ship-to organization where the price break applies.	NA	NA
Price Break Location	Ship-to location where the price break will be applied.	NA	NA
Price Break Quantity	Specify the quantity for which this price break is requested.	NA	NA
Price Break Target Price	Specify a price that the buying organization wants to pay for one unit of the item.	NA	NA
Price Break Start Date	Specify the start date for delivering the items for which this price break is requested.	NA	NA
Price Break End Date	Specify the end date for delivering the items for which this price break is requested.	NA	NA
Price Tier Minimum Quantity	Specify the minimum quantity the buying organization requires for this quantity-based price tier.	NA	NA
Price Tier Maximum Quantity	Specify the maximum quantity the buying organization requires for this quantity-based price tier.	NA	NA
Price Tier Target Price	Specify a price that the buying organization wants to pay for one unit of the item for this quantity-based price tier.	NA	NA
Attribute	Enter the name of the line attribute you want to define (Example: PPM rate, grade). The Attribute column and the five following columns comprise the group of columns used to define a single attribute for this line. Insert and complete a new group of the six columns for each additional attribute associated with this line.	NA	Maximum character length 4000 characters
Attribute Group	Used to categorize attributes. If a value for Attribute Group isn’t defined, the default Attribute Group value is used. See the AddLineAttributeGroups.txt spreadsheet included in the .zip for the group values available to this negotiation.	NA	Maximum length 240 characters
Attribute Response	Enter Required, Optional or Display only to indicate how you want the suppliers to respond, and whether or not entering a response value is required or optional. If this is a multiattribute scoring negotiation and you want to score the attribute, then it must be Required.	NA	NA
Attribute Value Type	The format of the attribute value you want to specify and need suppliers to enter. Enter Text for text, Number for number, Date for date or URL for URLs. Text can display all types.	NA	NA
Attribute Target	The target value for the attribute. Your entry must match the Attribute Value Type you specify.	NA	Maximum length 4000 characters
Display Attribute Target	Enter Yes or No to indicate whether you want the attribute target to be displayed to suppliers.	NA	NA
* End of Line Delimiter	Enter EOL.	NA	NA

The spreadsheet template contains the six columns (Line Target Price , Display Line Target Price, Cost Factor, Pricing Basis, Cost Factor Target Value, and Display Cost Factor Target) that are required to define one cost factor. If you want to enter more cost factors, for each additional cost factor, copy the last four columns (Cost Factor, Pricing Basis, Cost Factor Target Value, Display Cost Factor Target) and insert them after the existing six columns. You can enter as many cost factors as you want. Cost factor columns can be blank. For example, you may want to have the first cost factor for every line to be Tooling. However, if Tooling doesn’t apply to a particular line, simply leave the columns referring to Tooling blank for that line.

The spreadsheet template contains eight columns of which one column Suppliers Modify Price Breaks is common for all price breaks and the columns Price Break Type, Price Break Ship-to Organization, Price Break Location, Price Break Quantity, Price Break Target Price, Price Break Start Date, and Price Break End Date are required to define one price break. Also, the eight columns must be treated as a group: either all columns are deleted or none are deleted.

The spreadsheet template contains three columns Price Tier Minimum Quantity, Price Tier Maximum Quantity, and Price Tier Target Price that are required to define one price tier. Also, the three columns must be treated as a group: either all columns are deleted or none are deleted. During an import, either price break or price tier can be added.

The spreadsheet template contains the six columns (Attribute, Attribute Group, Attribute Response, Attribute Value Type, Attribute Target, Display Attribute Target) that are required to define one attribute. If you want to enter more attributes, simply copy the attribute columns and insert them at the end of the spreadsheet immediately preceding the End of Line Delimiter column. You can enter as many attributes as you want. Attribute columns can be blank. For example, you may want to have the first attribute for every line to be Grade. If Grade doesn't apply to a particular line, simply leave the columns referring to Grade blank for that line.

You can modify the spreadsheet files by deleting any of the optional columns although you can’t delete these columns: Action (when creating amendments or new rounds of responding), Type, Internal Line ID (when creating amendments or new rounds of responding), End of Line Delimiter. Also, the six attribute columns and the six cost factor columns must be treated as a group: either all columns are deleted or none are deleted.

If you’re creating a multiattribute scoring negotiation, you must enter the scores and weights details online after the lines are imported.

Lookup Files

Three lookup files are included in the .zip file you export from the application. These files contain the values defined in the application for

• Attribute groups

• Cost Factors

• UOM values

For cost factors and UOM values, you can use values contained in these files to complete the template as appropriate. If you try to use a value that doesn't exist, your import process returns an error. If you need additional or different cost factors, or UOM values, you must define them in the application first.

For attribute groups, you can use the predefined ones, or you can create ones in the spreadsheet.

The table shows the fields in the attribute lookup file.

Name	Description
Attribute Group	The name of the attribute group
Description	Description of the attribute group.

The table shows the fields in the cost factor lookup file.

Name	Description
Cost Factor	Name of the cost factor.
Description	Description of the cost factor.
Pricing Basis	Method used to calculate the cost factor.

The table shows the fields in the UOM lookup file.

Name	Description
UOM	Name of the UOM.
Description	Description of the UOM value.

Import Your Line Information

1. Once you have completed your spreadsheet, save it to the location of your choice. Your spreadsheet must be saved as a tab delimited file.

2. Return to the application. Follow the directions to import your completed spreadsheet.

3. When the application receives your spreadsheet for import, the spreadsheet validation process occurs and your imported lines appear on the screen.

4. If the application discovers errors on your spreadsheet, the spreadsheet lines on which those errors occurred are identified, and the application notes the specific error that occurred. Errors are diagnosed in 3 phases:

   • Phase one checks file errors.

   • Phase two checks column errors.

   • Phase three checks field errors.

   Errors are listed in a table, which identifies the Spreadsheet Row Number, Column Title, Value, and Error that occurred. Common errors include:

   • File errors occur when your spreadsheet file type or file format doesn't match the import format type supported by the application. For example, if you import a word-processing file which the application doesn't recognize, a file error occurs.

   • Format errors occur when information that you entered in your spreadsheet can’t be understood by the application. For example, if you enter alphabetic characters into fields where only numeric characters are expected, a format error appears. Also check that the column names and column sequence in the file you’re importing matches the names and sequence in the file you exported.

   • Validation errors occur when information that you entered on your spreadsheet doesn't match corresponding information already held within the application. For example, if you enter values for UOM or, Category Name that the application doesn't recognize, a validation error occurs.

5. If any of your spreadsheet lines contain errors, the application indicates the necessary corrections. Open the spreadsheet file you tried to import, make changes where necessary, save the file, browse to locate the updated file, and run the import process again. Note: If any error occurs during the import, none of the lines are loaded. Note also that since the errors are checked in phases, you may need to try importing more than once to fix all the errors and obtain a clean import.

6. If the application detects no error on your spreadsheet, all of your negotiation lines import successfully. You can then edit any of your lines.

7. Once your negotiation lines import successfully, continue the negotiation creation process.

Example of Importing Lines into a Negotiation

In this example the category manager uses spreadsheet export and import to add lines to an agreement negotiation for janitorial supplies that she is creating. Specifically, she adds

• 10 lots of cleaning supplies. Each lot has

  • One line for 100 dozen cleaning brushes,

  • One line for 90 dozen cleaning rags,

  • One line for 50 dozen cleaning brooms,

• A group of cleaning solutions consisting of

  • 20 gallons of cleaning solvent.

  • 15 gallons for cleaning disinfectant.

Using these instructions, she exports the spreadsheet .zip, opens it, and completes the template according to the details in the tables described earlier. Notice that possible cost factors and line attributes aren't shown in this example, but could also be included if appropriate. Once the template is finished, it looks like the following:

The table shows the sample initial spreadsheet.

Type	Description	Line Type	Item	Revision	Category Name	Estimated Quantity	UOM	Other Columns...	End of Line Delimiter
Lot	Cleaning Supplies	Goods	NA	NA	Miscellaneous maintenance supplies	10	EA	NA	EOL
Lot line	Cleaning brushes	Goods	NA	NA	Miscellaneous maintenance supplies	100	Dozen	NA	EOL
Lot line	Cleaning rags	Goods	NA	NA	Miscellaneous maintenance supplies	90	Dozen	NA	EOL
Lot line	Cleaning brooms	Goods	NA	NA	Miscellaneous maintenance supplies	50	Dozen	NA	EOL
Group	Cleaning Solutions	NA	NA	NA	NA	NA	NA	NA	EOL
Group line	Solvent	Goods	NA	NA	Miscellaneous maintenance supplies	20	Gallon	NA	EOL
Group line	Disinfectants	Goods	NA	NA	Miscellaneous maintenance supplies	15	Gallon	NA	EOL

She returns to the application and imports the completed spreadsheet. If there are any errors, they’re displayed. She corrects the errors and imports again. Note that since errors are checked in phases, it may require more than one import to correct all errors.

Additional Instructions for Multiple Rounds of Responding or Amendments

You can use the line import spreadsheet to amend the list of lines in a subsequent round of responding. When you start a new round of responding you can add, delete, and update lines from a spreadsheet. This gives you the flexibility to quickly launch the new round. To adjust the list of lines using a spreadsheet you typically follow these steps:

1. Export the spreadsheet template. It includes the lines already available from the previous round.

2. Make the appropriate changes to the line.

3. Import the modified spreadsheet.

4. Confirm the modifications.

The spreadsheet template you import between rounds of responding is slightly different than the one used to create the original negotiation lines. The main differences are:

• The exported spreadsheet is populated with the lines available from the previous round.

• The template includes five additional columns as shown.

The table shows the fields in a spreadsheet used in a multi-round negotiation.

Column Name	Description
From Requisition	The Yes or No value indicates whether the line was created from a requisition in the previous round. If the value is Yes, then don’t modify these values for the next round: Requisitioning BU, Line Type, Item, Revision, Description (if Item is specified), Category Name, UOM, and Location.
From Agreement	The Yes or No value indicates whether the line was created from an agreement in the previous round. If the value is Yes, then don’t modify these values for the next round: Line Type, Item, Description (if Item is specified), and Category Name.
Line	The current line number as displayed in the negotiation. This column is for reference only and helps you identify the lines that you want to update or delete. don't modify the values in this column. Leave this column empty if you’re adding a new line. Note that existing line numbers don't change.
Action	Specify to add, update, or delete the line. You can enter one of these values: + to add a new line. - to delete an existing line. # to update an existing line. If you leave the Action column blank, the application ignores the row, leaving the line unchanged. Note: If you’re using Excel, use the Tab key to exit the Action column.
Internal Line ID	Application generated ID that’s populated when you export the spreadsheet template. don't change the values in this column. If you’re adding a new line, leave the Internal Line ID column blank. Internal Line ID must be the last column before the End of Line Delimiter column.

For example, assume your initial spreadsheet looks like this:

The table shows the sample initial lines spreadsheet.

Line Number	Action	Type	Description	Estimated Quantity	UOM	Other Columns...	Internal Line ID	End of Line Delimiter
1	NA	Lot	Cleaning Supplies	10	EA	NA	123.221	EOL
1.1	NA	Lot line	Cleaning Brushes	100	Dozen	NA	123.321	EOL
1.2	NA	Lot line	Cleaning Rags	90	Dozen	NA	234.432	EOL
1.3	NA	Lot line	Brooms	50	Dozen	NA	345.654	EOL
2	NA	Group	Cleaning Solutions	NA	NA	NA	345.745	EOL
2.1	NA	Group line	Solvent	20	Gal	NA	346.234	EOL
2.2	NA	Group line	Disinfectants	15	Gal	NA	3467.121	EOL

Amend the line list and import the spreadsheet.

• To modify a line, enter a '#' in the Action column. You can change any attribute between rounds of negotiating. Scores and weights are automatically copied over to the next round if matching attributes are found.

• To delete a line, enter a '-' in the Action column.

• To add a line, enter a '+' in the Action column and follow the steps indicated in the previous section to complete the remaining columns. Lines are defined the same way that you entered them when you created the previous round of negotiation. don't enter a value for the internal line ID for the line that you’re adding.

Continuing the example, you can modify the spreadsheet to delete line 1.2, change the Quantity of line 1.3, and add a new line:

The table shows the example spreadsheet with modifications.

Line Number	Action	Type	Description	Estimated Quantity	UOM	Other Columns	Internal Line ID	End of Line Delimiter
1	NA	Lot	Cleaning Supplies	10	EA	NA	123.221	EOL
1.1	NA	Lot line	Cleaning Brushes	100	Dozen	NA	123.321	EOL
1.2	-	Lot line	Cleaning Rags	90	Dozen	NA	234.432	EOL
1.3	#	Lot line	Brooms	75	Dozen	NA	345.645	EOL
NA	+	Lot line	Mops	5	Dozen	NA	NA	EOL
2	NA	Group	Cleaning Solutions	NA	NA	NA	345.745	EOL
2.1	NA	Group line	Solvent	20	Gal	NA	346.234	EOL
2.2	NA	Group line	Disinfectants	15	Gal	NA	3467.121	EOL

The table shows list of updated lines.

Line Number	Action	Type	Description	Estimated Quantity	UOM	Other Columns	Internal Line ID	End of Line Delimiter
1	NA	Lot	Cleaning Supplies	10	EA	NA	123.221	EOL
1.1	NA	Lot line	Cleaning Brushes	100	Dozen	NA	123.321	EOL
1.3	NA	Lot line	Brooms	75	Dozen	NA	345.645	EOL
1.4	NA	Lot line	Mops	5	Dozen	NA	568.245	EOL
2	NA	Group	Cleaning Solutions	NA	NA	NA	345.745	EOL
2.1	NA	Group line	Solvent	20	Gal	NA	346.234	EOL
2.2	NA	Group line	Disinfectants	15	Gal	NA	3467.121	EOL