How You Analyze and Award an Auction or RFQ by Spreadsheet

Once you have exported the spreadsheet, you can open it in Microsoft Excel (version 2003 or later). Excel automatically formats the display based on your style format. The spreadsheet consists of multiple worksheets.

These sections describe the information contained in each worksheet, and the tables in the sections describe each spreadsheet field for that worksheet. As you use the spreadsheets, note that some fields are automatically calculated and updated as you enter values into the spreadsheet. These fields are enclosed by a thick cell border.

Excel also automatically formats date fields according to your user preferences into the spreadsheet.

Your spreadsheet can have multiple tabs, depending on the negotiation content. Once you have completed the spreadsheet, import it back to the application.

The following table shows the spreadsheet table and the content they contain.

Worksheet Tabs	Contents
Line Summary Tab	Use this tab to view a summary of all responses to each line in the negotiation including any award decisions made. The Line Summary worksheet displays all groups, group lines, lots, and regular lines. It doesn't display lot lines
Overview Tab	Use this tab to view the responses' header and requirement information side by side for easy comparison. You can optionally enter or change the scores given to the requirement responses and see how it impacts the Total Weighted Score for the supplier.
Lines Tab	Use this tab to view all the responses' line detail information (for example, cost factors, price breaks, and attributes.) side by side for easy comparison. You can award the lines to the responses and can specify other details like the award or agreement quantity and award reason in this worksheet. All the lines defined in the negotiation are displayed except for lot lines.
Requirements Scoring Tab	Use this section to view any Requirements scoring information.
Attributes Scoring Tab	Use this section to view any line attribute scoring information.

Using Spreadsheet Import

This file contains the instructions for analyzing and awarding responses in negotiations using an XML spreadsheet. Spreadsheet import is very useful when dealing with large negotiations and effectively speeds up the response process. The spreadsheet displays only the necessary information for analyzing and awarding responses. You can easily experiment with different award decisions and see how it impacts the overall savings or other award criteria. The spreadsheet doesn't include all the negotiation details that can be found either online or in the .pdf file.

In each worksheet, fields display negotiation information such as negotiation type, open date, negotiation currency.

Using the Lines Worksheet and the Line Summary Worksheet

The application imports the information entered into the Line Summary Worksheet into the application when processing your award decisions. For straightforward negotiations, for example a simple negotiation, you may want to enter your award values directly into the Line Summary Worksheet.

For more complex negotiations, you may want to use the Lines Worksheet. You can use the Lines Worksheet to perform analysis using the additional supplier response values. For example, you can perform what-if analysis, side-by-side comparison, and additional award and savings calculations that are only available on the Lines Worksheet. You can enter and adjust award quantities to see the effect on the award total.

As you enter award quantities into the Lines Worksheet, the values are automatically copied up into the Line Summary Worksheet. Once you determine your award quantity amounts using the Lines Worksheet, you do not have to reenter the values in the Line Summary Worksheet for uploading into the application.

Note that if you enter values directly into a field in the Line Summary Worksheet, the formula is erased.

Line Summary Worksheet

The Line Summary worksheet displays the information defined for the negotiation lines as well as information for any responses on those lines. The worksheet name specifies the range of lines it contains. The Line Summary has entries for each regular line, lot, group, and group line. It doesn't contain entries for lot lines. In Line Summary table, multiple rows are displayed, one for each response received for the negotiation.

Any award information you enter into the Lines worksheet (see the following) is displayed in summary form on the Line Summary worksheet. Alternatively, you can enter award decisions into the Line Summary worksheet.

The following table shows the attributes for the Lines Summary worksheet.

Field Name	Meaning
Requisitioning BU	The business unit that originated this line. If you're using standalone Sourcing, this column doesn't appear.
Line	Line number and description as entered by the category manager.
Alternate line	If this is an alternate line offered by the supplier, this field displays the alternate line name.
Item	The number of the item that the category manager wants to purchase.
Item Revision	The item revision of the item that the category manager wants to purchase.
Supplier	The name of the supplier who responded to this line.
Business Relationship	The level of participation for this supplier. Suppliers with a business relationship of Prospective can participate, but you can't award business to them until their status is upgraded to Spend Approved.
UOM	The unit of measure in which the category manager plans to buy the item.
Quantity	The number of units the category manager wants to buy.
Response Quantity	The number of units offered by the supplier.
Estimated Quantity	The number of units the category manager wants to buy over the life of the agreement.
Estimated Total Amount	Estimated amount of business you expect to pay for this line over the life of this agreement. Values only appear if this line is a service line type.
Award	Valid selections are Yes or No. For amount-based and fixed price services line types for a purchase order, use Yes or No to indicate an award to this supplier. For purchase agreements, you can enter a value regardless of line type.
Award Quantity	For goods-based line types, enter number of units awarded to this supplier.
Agreement Quantity	Enter the number of units awarded to this supplier (for goods and rate-based temp labor lines only.
Award Reason	Optional text note to the supplier.
Rank	The rank of this supplier's response among the other responses.
Response Price	The price offered by the supplier for one unit of the item.
Score	If the ranking method for this negotiation is Multiattribute Weighted Scoring, displays the overall line attribute score received by the response.
Promised Delivery Date	The date by which the supplier promises to deliver the item or service.
Response Minimum Release Amount	The minimum release amount offered by this supplier.
Supplier Site	The supplier site which submitted the response.
Response	The number the application assigned this response.
Response Type	Primary or secondary If you submit alternate responses, you can designate one of the responses as your primary.
Requested Delivery Date	The date by which the item or service the category manager wants to purchase is needed at the location.
Target Minimum Release Amount	The minimum release amount asked for by the category manager.
Category Name	The category name describing the broad family or category to which this line belongs.
Location	The address where the item or service should be delivered.
Line Type	The type of line being negotiated, for example, goods or amount-based.

Overview Worksheet

This worksheet displays the responses' header and requirement information side by side for easy comparison. You can optionally enter or change the scores given to the requirement responses and see how it impacts the Total Weighted Score for the supplier.

The following table shows the fields in the Overview worksheet.

Field Title	Meaning
Supplier company name and Response number	The name of the supplier who submitted a response on this negotiation and the number assigned to the response by the application. Each response has a separate column in the Overview table. This title identifies the supplier who submitted the response.
Business Relationship	The level of participation for this supplier. Suppliers with a business relationship of Prospective can participate, but you can't award business to them until their status is upgraded to Spend Approved.
Supplier Site	The supplier site from which this response was submitted.
Supplier Contact	Contact who submitted the response.
Response Status	The status of the response.
Shortlist Status	Whether the response is included on the shortlist.
Response Currency	The currency in which the supplier submitted the response (in multiple currency negotiations).
Conversion Rate	The exchange rate defined between the negotiation currency and the response currency (in multiple currency negotiations).
Response Total (Negotiation Currency)	The amount of the supplier's response (response price * quantity) in the negotiation currency (in a multiple currency negotiation).
Response Amount (Response Currency)	The amount of the supplier's response (response price * quantity) in response currency.
Total Award Amount	The total amount awarded to this supplier in the response currency. This field automatically updates as you enter award decision information into the Lines worksheet.
Time of response	The time the response was received by the application.
Response Valid Until	The date after which the response is no longer valid.
Reference Number	A number entered by the supplier for internal tracking.
Note to Buyer	A text note entered by the supplier.
Attachments	Indicator shows the presence of an attachment that can be downloaded online.

If requirements were defined for this negotiation, the supplier response information is displayed in the Requirements section. For each Requirement, supplier responses are displayed for easy comparison. If scoring criteria is defined, you can use the View Scoring Criteria link see the scoring criteria. If the Requirement is internal, you see no supplier response.

If your negotiation contains many lines, you can control the display by using the down arrow. You have several options including sorting the lines by line number to display specific lines. The information in the following table is displayed for each line of the negotiation.

You can use this section of the Overview worksheet to manipulate scores for manually scored requirements. You can enter different values in the Score and Weighted Score fields for supplier responses, and the Weighted Score field recalculates automatically.

The following table shows the fields in the Requirement section of the Overview worksheet.

Field Title	Meaning
View Scoring Criteria	Link to the Requirements Scoring Worksheet. This link only appears if the category manager defined scoring information for at least one requirement.
Requirement	The name of the requirement section is displayed. there's a row for each requirement. If there's a branched requirement, it's listed in its parent requirement. The hierarchy of the branched requirement is indicated by the numbering value: Number: the number of the parent requirement Letter: the indicator of which response to the parent requirement triggered the branch question If this is a two stage RFQ, there's one or more section labeled Technical and one or more sections labeled Commercial. In two stage RFQs, you must unlock and score the supplier's technical requirements before you can unlock and score any commercial requirements.
Weight	The weight assigned to this requirement (a section's weight is the sum of its requirements' weights). If manual or automatic scoring is defined for this requirement, you can change its weight by entering new numbers into the spreadsheet. You can use weighting only if the category manager enabled weights for this negotiation.
Target Value	The target value defined by the category manager.
Score and Weighted Score (Requirement)	The score and weighted score for this requirement, based on the supplier response. If the requirement is manually scored, you can enter scores and the weighted score is calculated automatically. Weight values only appear if weights were enabled by the category manager.
Score or Weighted Score (Section)	The score or weighted score for this requirement section, based on the supplier response. Weight values only appear if weights were enabled by the category manager
Total Score or Total Weighted Score	The total score or total weighted score for this supplier's response. Weight values only appear if weights were enabled by the category manager
Supplier's Response Value (per Requirement)	The response value entered by the supplier. there's a separate column for each supplier response.
Attachments	Whether the supplier provided any attachments with the response
Comments	Any comments entered by the supplier

Lines Worksheet

The following table shows the summary fields in the Lines worksheet. These fields are automatically updated as you process the spreadsheet.

Field Name	Meaning
Number of Awarded Lines	The current number of lines you have awarded.
Number of Awarded Suppliers	The current number of suppliers who have been awarded business.
Total Current Value	The sum of all current amounts. The value of this negotiation based on the price currently being paid for this item by the category manager (total current amount = number of units being awarded * current price).
Total Award Amount	The value currently awarded for this negotiation. This is the sum of all current award totals. This field is automatically updated as you enter award decision information into the spreadsheet.
Total Savings Amount	The current amount you have saved.
Total Savings Percent	The current Total Savings Amount converted into a percentage.

If your negotiation contains many lines, you can control the display by clicking the down arrow. You have several options including sorting the lines by line number to display specific lines. The information in the following table is displayed for each line of the negotiation.

The following table shows the fields in the line section of the Lines worksheet.

Field Name	Meaning
Line	Line number and description as entered by the category manager
Line Type	The type of line (for example, goods or amount-based
Item	The Item Number of the item that the category manager wants to purchase.
Item Revision	Item revision number of the item that the category manager wants to purchase.
Category Name	The category name describing the broad family or category to which this line belongs.
UOM	The unit of measure for this line.
Start Price	The starting response price for one unit of the item or service being negotiated. Your response price can't be higher than the response start price.
Location	The address where the item or service should be delivered.
Current Price	The current price the category manager is paying for this item or service. The Current Price value is used by the application to calculate savings amounts.
Alternate Lines Provided	An indicator showing the presence of supplier-provided alternate response lines.
Award Quantity	For purchase order outcomes, the number of units currently awarded to all suppliers for this line. This field is automatically updated as you enter award decision information into the spreadsheet.
Agreement Quantity	For purchase agreement outcomes, the number of units currently awarded to all suppliers for this line. This field is automatically updated as you enter award decision information into the spreadsheet.
Award Amount	The total amount awarded for this line. This amount is calculated automatically as you enter award decisions into the spreadsheet.
Unit Price Savings	The savings amount per unit gained based on the award quantity.
Unit Price Savings Percent	The savings amount per unit converted into a percentage. This percent is calculated automatically as you enter award decisions into the spreadsheet.

The response section of the Lines Worksheet displays information on the responses received for this line. The Target Value column displays any target values the category manager has defined. Following the Target column, responses for individual suppliers are displayed in side-by-side columns to allow easy comparison.

The following table shows response information to the negotiation lines.

Field Name	Meaning
Business Relationship	The level of participation for this supplier. Suppliers with a business relationship of Prospective can participate, but you can't award business to them until their status is upgraded to Spend Approved.
Supplier Site	The supplier site that submitted the response.
Response Status	Status of the response.
Shortlist Status	Whether the response is included on the shortlist.
Rank	The rank of this response among other suppliers' responses.
UOM	The unit of measure for this line.
Response Currency	The currency in which the supplier submitted the response (if multiple currency responses are allowed).
Response Price (Response Currency)	The price offered by the supplier, including any cost factors (assuming the entire response quantity is awarded). In a multi-currency negotiation, this value is in the supplier's chosen currency.
Response Price (Negotiation Currency)	The price offered by the supplier, including any cost factors (assuming the entire response quantity is awarded). In a multi-currency negotiation, this value is in the negotiation currency.
Response Quantity	The quantity offered by the supplier's response.
Unit Price Savings	The difference between the current price being paid for the line as defined by the category manager and the price being offered in the supplier's response.
Unit Price Savings Percent	The Unit Price Savings amount converted to a percentage.
Minimum Release Amount	The minimum monetary amount for a release against this agreement (purchase agreements only) offered by the supplier's response.
Award	For a purchase agreement outcome, select Yes or No to enter an award to this supplier. You can optionally also enter a Agreement Quantity value. This field is enabled when the outcome is a purchase agreement or when the outcome is a purchase order and line type is fixed price services. Select Yes or No to enter an award for this supplier
Award Quantity	If this is a purchase order negotiation for a Goods-Based line type, enter the quantity of units you're awarding this supplier.
Agreement Quantity	Enter the number of units you're awarding this supplier (for purchase agreements only).
Award Price	The response price adjusted based on the number of units awarded to this supplier. This is only displayed if the line has any quantity-based price tiers or fixed-amount cost factors where award price will be dependent on the quantity awarded to the suppliers.
UOM	The unit of measure for this line.
Award Amount	The amount of this supplier's award and is calculated as Award Price * Award Quantity or Agreement Quantity.
Note to Buyer	A text note entered by the supplier.
Attachments	An indicator that shows whether the supplier also submitted an attachment for this line with the response.

The cost factors section displays any cost factors the category manager applied to the line. There will always be at least two rows. The first, Line Price (Per Unit), represents the response price offered by the supplier for one unit of the item or service. This row is followed by one or more rows representing the additional cost factors for this line. The values in the Target Value column display the category manager's target value. The values in the supplier columns display the cost factor amounts applied to that supplier.

The following table shows the fields in the cost factor section of the lines spreadsheet.

Field Name	Meaning
Cost Factor Name (Pricing Basis)	The name and pricing basis of cost factor. The first row will always be called Line Price (Per Unit) and represents the price for one unit of the item or service.
Target Value	The target value defined by the category manager. `
Supplier Response(s)	The value offered by the supplier for this cost factor.

If the category manager defined price breaks for this negotiation, the Price Breaks section displays the information defined by the category manager and any supplier responses. The Target Value column displays the price break information specified by the category manager. Following the Target column, there are columns displaying the suppliers' responses side by side for easy comparison. If the category manager allows the price breaks to be modified and suppliers defined any of their own price breaks, there will be rows showing the breaks offered by that supplier.

The following table shows the fields in the price break section of the lines worksheet.

Field Name	Meaning
Ship-to Organization	The organization to which the location is defined.
Location	The location to which this price break applies.
Quantity	The unit quantity to which the price break applies.
Start Date	The date when the price break begins.
End Date	The date the price break expires.
Price	The price offered.
Price Discount Percentage	The discount offered.

The category manager can specify price variations based on quantity. These function similarly to price breaks, but are based on quantity only. In the Quantity-Based Price Tiers section, there's one row for each price tier.

The following table shows the fields in the price tiers section of the lines worksheet.

Field Name	Meaning
Minimum Quantity	The minimum quantity for this price tier.
Maximum Quantity	The maximum quantity for this price tier.
Response Price	The supplier's price offered for this price tier.

Requirements Scoring Tab

The Requirements Scoring Worksheet only appears if scoring criteria was defined by the category manager. All requirements that are scored are displayed, including ones that do not have scoring criteria.

The following table shows the fields in the Requirements Scoring tab of the Lines spreadsheet.

Field Name	Meaning
Requirements	The name of the requirement section is displayed. there's a row for each requirement. If there's a branched requirement, it's listed in its parent requirement. The hierarchy of the branched requirement is indicated by the numbering value: Number :the number of the parent requirement Letter :the indicator of which response to the parent requirement triggered the branch question If this is a two stage RFQ, there's one or more section labeled Technical and one or more sections labeled Commercial. In two stage RFQs, you must unlock and score the supplier's technical requirements before you can unlock and score any commercial requirements.
Acceptable Values	The acceptable response values for the requirement as defined by the category manager. For a text requirement, this will be a list of values. For numeric, date, and date/time type requirements, it can be a set of numbers, dates, or one or more ranges of numbers (ranges can't overlap).
Score	For automatically scored requirements, the numeric score assigned to that value by the category manager.
Weight or Maximum Score	If weights are enabled, for each requirement, the numeric value assigned to it by the category manager to reflect that requirement's importance, relative to any other requirements; otherwise, the Maximum Score for the requirements is displayed as defined by the category manager.

Attributes Scoring Tab

The following table shows the fields of the Attributes Scoring tab. It shows the attribute scoring criteria for all attributes in all lines.

Field Name	Meaning
Attribute	Name of the attribute.
Acceptable Response Values	The acceptable response values for the attribute as defined by the category manager. For a text attribute, this will be a list of values. For numeric and date type attributes, it can be a set of numbers or one or more ranges of numbers (ranges can't overlap).
Score	For each acceptable attribute value, the numeric score assigned to that value by the category manager.
Weight	If weights are enabled, for each attribute the value assigned to it by the category manager to reflect that attribute's importance, relative to any other attributes for the line.