Updating Product Prices

This section discuss how to update product prices and work with related tasks.

The Calculator button acts directly on the data in the spreadsheet. The other buttons open dialog boxes wherein you make additional selections.

You are limited to one list price maintenance spreadsheet on your hard drive. If you have an existing spreadsheet from a previous maintenance session, then the system will prompt you to override it with the current download or not.

You can access the price maintenance spreadsheet you are working on by opening it directly in Microsoft Excel. Downloaded information appears in Microsoft Excel in three sections on the spreadsheet delineated by different column colors:

  • Key fields that identify the product appear in gray columns.

  • Fields that can be edited, such as New Cost, New Price, New MSRP, and New Eff. Date (new effective date), appear in light green columns. All other cells on the spreadsheet are locked.

  • Current price and cost information appears in beige columns.

Image: Product Price Maintenance spreadsheet

This example illustrates the fields and controls on the Product Price Maintenance spreadsheet. You can find definitions for the fields and controls later on this page.

Product Price Maintenance spreadsheet

Field or Control

Definition

Download Prices Button

Click the Download Prices button to view product information in the spreadsheet.

Calculator Button

Click the Calculator button to toggle between the two views using the Price Maintenance toolbar. Differences are not uploaded, but the view affects the mass maintenance changes that are available when changes are based on the differences in values.

Key Field Columns

Field or Control

Definition

Prod ID (product ID) and Prd Grp (product group)

A product may be included in more than one product group. If this is the case, and you have downloaded data that encompasses more than one group, the product will appear multiple times on the spreadsheet. The only data changes that are uploaded are those made in rows where the key fields appear in bold. The system emphasizes the group you designate as the primary pricing product group on the Product Group page. If you do not designate a primary group among multiple groups, the system uses the first one it encounters.

Inv BU (inventory business unit)

If the product is not stocked in a warehouse (a service or product kit, for example), the value in the column will be Non Inv (non-inventory) and the Item ID field will be blank.

Editable Columns

These editable columns are affected by pricing, costing, and effective date changes. The data in these columns is uploaded to update PeopleSoft tables. You can edit each cell individually or apply Mass Maintenance.

Field or Control

Definition

New Cost

The spreadsheet displays only information for inventory products that are actual, periodic, perpetual, standard-costed, retroactive perpetual average, and non-inventory products. This column does not contain a value for other products. The system retrieves product costs for standard-costed stocked products from PeopleSoft Inventory, and the cost cannot be changed here. Unit costs for non-stocked products are established on the Product Price page in PeopleSoft Order Management, where you can update the cost. The data that appears preceding any changes reflects the costs as of the Product Cost Effective Date selected on the Product Price Maintenance page.

Note: Stocked products that are not costed in PeopleSoft Inventory are not downloaded.

New Price

Matches the Active Price values until you make changes. These prices are active for the Product Price As Of Date selected in Product Price Maintenance changes.

New Eff. Date (new effective date)

Matches the Product Cost Effective Date selected on the Product Price Maintenance page until you make changes. To prevent inconsistent pricing for the current date, the minimum new effective date value allowed for uploaded rows is the current date plus one day.

Note: If you selected the current date as the Product Cost Effective date on the Product Price Maintenance page, then change the value of New Eff. Date here for any rows you want uploaded.

New Diff. (new difference) and Diff. abs. (difference percent)

View the difference between the New Cost and the New Price as an absolute amount (New Diff. = New Price-New Cost) or as a percentage (New Diff. = (price – cost) / cost * 100).

Current Price and Cost Information Columns

The Active Cost, Active Price, Diff % (difference percent), MSRP (Manufacturer's Suggested Retail Price) and Eff. Date (effective date) reflect the status of the data at download. They are available for comparison, but you cannot change the values in these columns. The toggle between the two differences affects both the New Diff. column and the Diff % column.

The spreadsheet provides auto filtering for all columns, which enables you to select criteria in one or more columns that limits the product rows that will appear to a subset of those downloaded. After selecting a subset of the data, you can apply changes just to that subset. This capability enables you to apply different adjustments to separate subsets of the downloaded data.

Click the arrow in the heading row for a column and select a value from the drop-down list to filter the parameters. When filtering criteria are in effect, the color of the arrow changes from black to blue.

In addition to the values in the column, you can apply five additional options to each column:

  • (All): After applying filters, this value resets the column to display all the data from the download.

  • Top 10: This standard Microsoft Excel filtering option is not used in price maintenance.

  • Custom: Set a particular range for column values.

  • Blanks: Displays only those rows without a value.

  • Non-Blanks: Displays only those rows with a value.

Use the Price Maintenance dialog box to apply changes to multiple price lists prices or product price list prices in the editable columns:

Image: Price Maintenance dialog box

This example illustrates the fields and controls on the Price Maintenance dialog box. You can find definitions for the fields and controls later on this page.

Price Maintenance dialog box

Field or Control

Definition

Mass Maintenance Button

Click the Mass Maintenance button to access the Mass Maintenance dialog box.

Type

The selection options and the label of the first field change depending on the type of change you are making. Options are unavailable when the selection criteria do not apply. You can make a Percent change, an Amount change, or a change to the Eff. Date.

Based On

You can base a change on the: List Price (New Price column), Differences between the cost and list price (New Diff. column), Non-Inv Cost (New Cost column for non-inventory products only), and MSRP (New MSRP column).

Note: The display mode for differences on the spreadsheet affects the selection criteria for changes. If differences appear as percentages, the Type must be Percent. If differences appear as absolute amounts, the Type must be Amount.

Preview

Click the Preview button to view an example of the current change before you apply it to the data. Keep in mind that prices are not limited to positive values.

Number of decimals for rounding

When a pricing change is applied, you can alter the number of decimals that the system uses to round to the new price or cost by changing the value in this field. Values are 0 to 4. You can round to a different number of decimals without changing any other data by keeping the default of 0 in the first field in the dialog box and altering the Number of decimals for rounding field.

Note: The default value that appears in the Number of decimals for rounding field is determined by the settings on the computer. To change the default, select Control Page > Regional Settings > Currency.

Use the Reset Data dialog box to reset the data to the original data from the download:

Image: Reset Data dialog box

This example illustrates the fields and controls on the Reset Data dialog box. You can find definitions for the fields and controls later on this page.

Reset Data dialog box

Field or Control

Definition

Reset Data Button

Click the Reset Data button on the Price Maintenance toolbar to reset the columns to the original settings from the download. You can apply resets selectively using the filtering function.

New Prices to Active Prices

Resets values in the New Price column to those that were downloaded. These are the same values as those in the Active Price column.

New Prices to New Cost

Resets values in the New price column to match the values in the New Cost column.

Effective Dates

Resets values in New Eff. Date column to those that were downloaded.

New Cost to Orig. Value (reset new cost to original value)

Resets the values in the New Cost column for non-inventory products to those that were downloaded. These values might not match those in the Active cost column if the Product Price As Of Date is different from the Product Cost Effective Date.

New MSRP to Active MSRP

Resets values in the New MSRP column to those that were downloaded. These values are the same as those in the Active MSRP column.

Use the Customize Sheet dialog box:

Image: Customize Sheet dialog box

This example illustrates the fields and controls on the Customize Sheet dialog box. You can find definitions for the fields and controls later on this page.

Customize Sheet dialog box

Field or Control

Definition

Customize Sheet Button

Click the Customize Sheet button.

Current Settings

Select each column from the drop-down list box, and enter a New Heading and or a New Width if you prefer different headings or column widths.

Adjust Column Width to Best Fit

Select this option to set column widths to display the values entered and override any manually entered column widths.

Display primary pricing rows only

Select this option to view only the bold rows on the spreadsheet. To view all rows, deselect this option.

Display marked for upload rows only

Select this option to view only changed rows. To view all rows, deselect this option.

Reset all Data Filters

Select this option to display all the downloaded data if you previously restricted the display of spreadsheet rows by using the filtering option on one or more columns.

Hide Cost Columns

Select this option to hide Cost Columns.

Hide Alternate Cost Columns

Select this option to hide Alternate Cost Columns.

Redisplay this page when finished

Select this option if you change the heading or width of columns.

Apply

Click the Apply button to apply changes to each column one at a time.

Use this page to print the spreadsheet. The system includes auto-filter settings and prints only changed rows.

Field or Control

Definition

Print Price List Button

Click the Print button to print the spreadsheet data.

Click the View Charts button to create graphical charts of the list prices. You can compare Cost Differences, Prices and both Prices and Cost Differences.

To change the display properties of the chart, use the standard Microsoft Excel menu Format options.

Field or Control

Definition

View Charts Button

Click the View Charts button to create charts based on prices, differences, or both.

The system uploads only rows with changes (where the key fields appear in bold) in the New Price, New Cost (non-inventory products only), New MSRP, and New Eff. Date columns.

If you changed the new effective date, the system inserts a new row in the product price table. This action is comparable to entering a new row in the product price table for the price by inventory business unit with a new effective date. When the effective date of the spreadsheet row is unchanged, but you have altered the price, the current row in the product price table is updated.

Note: Keep in mind that at download the New Eff. Date column is populated with the Product Cost Effective Date you selected on the Product Price Maintenance page. Because the minimum new effective date allowed for upload is the current date plus one day, selecting the current date as the Product Cost Effective Date requires that you change the new effective date for all rows you want to upload.

Uploading Modified Product Price Data

Click the Upload Prices button on the price maintenance toolbar.

Field or Control

Definition

Upload Prices Button

The prices are uploaded into the PeopleSoft system. If the row is applied successfully, the status in the row Status column changes to Uploaded. If the row is not applied successfully, the status in the Status column changes to Failed. Correct the errors for the row and click the Upload Prices button.