INTRODUCTION

There are 2 basic ways for dealers to set up accessory pricing: Start with cost and add a mark up for each department or start with a market based price such as MSRP, and then subtract costs to determine dealer profit.

This tool allows the dealer to set up accessory pricing using either of these methods. In addition, this tool allows the user to compare the revenue and price point of these approaches with a few key strokes. Once the pricing is established, a different version is available to the sales team as a selling tool. A menu is also available for customers who want to know their options to personalize their vehicle.

This user guide provides insight on how to set up pricing with this tool, as well as some tips for using this as a menu at the dealership.

OVERVIEW:

The file is composed of 3 worksheets:

  • Pricing Worksheet where the parts pricing, labor times and sales person commissions are set up. This sheet has global settings which apply to all products, and then allows the user to adjust specific parts prices based on local market conditions.
  • Sales Manager Worksheet where the sales manager has all the pricing information for all models. It lists the internal price (parts and labor), retail prices and commissions. This worksheet is read only except for a comment field.
  • Customer Menu which can be customized by the dealer.

How to use this tool:

  1. Familiarize yourself with how this file is set up.
  2. Set global settings that will be used as a starting pointfor all prices.
  3. Review individual prices, labor times and commissions and adjust if necessary using the yellow fields.
  4. Check Sales Manager price worksheet for accuracy and add applicable comments in yellow fields. Note: be aware of exceptions such as required parts which may need to be sold together for a complete installation.
  5. Create customer friendly version to all departments as a tool to sell accessories.

Getting Familiar with the Tool

This file is supported by Excel 2003 and later format. When you first open the file, tabs for the worksheets are found on the bottom of the page. Navigation between these worksheets is accomplished by clicking on the tab. Let’s start with the Pricing Set Up tab.

This is the primary worksheet for the parts and service managers to set up the pricing. Changes made here will also reflect on the other pages as needed. To make it easier, the header is locked, so the user can see what column is being worked on.

Certain items are used consistently within this worksheet to make it easier to work with. For example, colors are used to indicate what type of information is found in the column:

Color Codes

Information in white is provided by Lexus

Gray is for calculated fields such as Internal Price or labor cost

Yellow fields can be modified by dealer

Green fields are Retail pricing and profits

Navigation

There are a several ways to move through the different series vehicles in this worksheet. By scrolling down, the user will be able to see all the rows. To make this easier, each series has a name, which can be reached using the quick links.

  1. Use the name drop down box. This allows you to navigate from anywhere in the file back to this worksheet.
  1. Use the quick links at the top of the page to navigate to the model you want to work on. Please note: The link will take you to the top of that section, but you may need to scroll down to see the accessory product information.

Changing Cells

There are 5 numeric fields and one comment field that are available to change. All other fields are locked and are either calculated or provided from Lexus. If one of these cells is changed, the user has a couple of ways to go back to what the default value was: Undo using the excel command or copy a cell in the same column to the one that was changed.

Other Worksheets

The other sheets are read only, and can be formatted to meet the needs of the dealership.

Printing Tips

If user wants to print this to review with other departments, Pricing Set Up can be printed on legal or A3 size paper. Sales Manager and Customer Menu can be printed on 8 ½ x 11” paper.

Global Settings

The global settings at the top of the Pricing Set Up worksheet are to establish pricing parameters for the specific dealer.

One added advantage of these settings is that by clicking the MSRP related prices, the dealer can evaluate different pricing scenarios on the impact they have on price and profitability.

  1. MSRP Pricing Selection: These check boxes allow the user to set all prices at MSRP and to set up how labor and commissions are handled:
  2. Sell at MSRP: Sets all prices to installed (PPO) MSRP. If there is not an MSRP available, the field will be blank and the dealer can set market based price.
  3. Used when: Dealer wants to use installed MSRP with 3 way profit split
  4. Impacts: Parts and installed price, departmental profit, internal pricing
  5. Include Labor Rate Profit: Takes the hourly profit margin (difference between posted rate and labor cost) and includes it into the total profit for that product.
  6. If used with MSRP checked: Will include labor profit in overall total that is split between departments and resulting internal pricing.
  7. If used without MSRP checked: Will show service profit for each product in the departmental profit which is part of dealer profit.
  8. Sales Commissions as Cost of Sale allows the dealer to include the commission paid to a sales person as a cost of sale. This allows the departmental profit to be split based on profit net of commissions.
  9. If used with MSRP checked: Deducts cost of commission from overall profit that is split between departments and resulting internal prices.
  10. If used without MSRP checked: Deducts sales person commission from Sales Department profit.
  11. Global Settings for Cost Plus pricing method:
  12. Parts Markup: Multiplied by the cost to determine how much profit the parts department receives for the sale
  13. Service Labor Rate: Rate Service department charges for accessory installation
  14. Labor Cost: Actual weighted technician cost that is used to determine service profit when “Include Labor Rate Profit” is selected
  15. Sales Markup: Multiplied by internal price to determine how much profit the Sales Department receives for the sale
  16. Sales Commission: Multiplied by sales price to determine sales commission. Dealer can adjust the dollar amount for each product in the worksheet
  17. Global settings for profit split calculations: Total needs to equal 100%
  18. Parts Split: Percentage of overall profit that is attributed to Parts department to determine internal parts price
  19. Service Split: Percentage of overall profit that is attributed to Service Department. Could be zero if service is already getting profit from each hour sold
  20. Sales Split: Percentage of overall profit that is attributed to sales department if MSRP is used or dealer adds market based price

File Structure

The worksheets are set up to make it easier to find related products. Each worksheet follows a variation of the following:

  1. Series: The vehicle being

displayed. These names are also

used for navigation.

  1. Category: Group of products that are

all related based on similar function.

  1. Product: Accessory namewhich is

typically the same as the part name.

  1. Part Number: TMS generated Unique

identifier

  1. Comments: Vehicle body color or

description of interior color.

  1. Required Parts: Parts that are

required in order to complete the installation of a product. When pricing total

installed price, be sure to include any parts that are required.

Setting up Dealer Pricing

This section of the Pricing Set Up worksheet is divided into Parts, Service, Sales and Departmental Profit. Fields in White are provided by Lexus, fields in Gray or green are calculated, and fields in Yellow can be set by dealer.

  1. Parts Dealer Cost: These are the dealer parts costs that will be invoiced if the parts are ordered through Lexus. These are the same as what is provided to update the Dealer Management System.
  2. TMS Parts Only MSRP:These are the suggested parts only prices provided by Lexus.
  3. TMS PPO Install Time: These are the times that are used by Lexus to install the accessories when ordered as a Post Production Option. Dealertime may vary, and can be input into the adjacentcolumn
  4. PPO Installed MSRP: Not available for all products (DIO for example). These are the prices that are used when an accessory is shown on the Monroney as a PPO accessory.

Parts and Service Pricing Decisions

  1. Parts only internal: Calculated in one of 2 ways. Please Note: Actual internal pricing may vary based on dealer specific accounting processes.
  2. Dealer cost marked up by percentage determined in Global Settings
  3. In cases where dealer creates their own retail priceor selects MSRP in global settings, this field will take the dealer cost of the part and add the parts department profit from the departmental split. Overall Profit impacted by addition of labor profit or inclusion of sales commissions in total
  4. Retail Parts only price: Dealer can set the final customer price for parts, which is combined with labor to get final installed price. Initial value will either be Parts MSRP (if MSRP selected) or Internal Parts Price plus the Sales Mark Up
  5. Dealer Install Time: Service department can set times that are appropriate for their operation. This information is used to determine labor costs, which are the basis of the retail pricing. TMS PPO time is provided for reference. If no install time is available (DIO products for example) this will be blank and no installed prices will show.
  6. Labor Cost: Calculated field based on dealer labor rate (from global settings) times the Dealer Install Time.
  7. Sublet: Input by dealer in cases where installation is completed by non-dealer people. Sublet price can be marked up by Sales department.
  8. Internal Labor price and sublet: Calculated field which adds internal price to the sublet installation price. Calculation varies based on global settings:
  9. If MSRP not selected or if Retail labor Rate is changed by the dealership: Labor Price plus any sublet repairs
  10. If MSRP selected: Total profit for installed MSRP price (PPO MSRP less Parts and labor cost) plus any departmental profit split from global settings
  1. Retail Labor Price: Input by dealer based on price dealer wants to charge customers (including sales profit). Includes labor, sublet and profit for sales department. This total is added to the Retail Parts Only price to determine Installed Price. Initial price will either show MSRP (if selected) or Internal Labor and sublet plus sales mark up.

Sales Department and Departmental Profit

  1. Internal Installed Price: Calculated field for internal parts and labor price. This installed price is the cost to the Sales Department, and is the basis for calculating retail price using the traditional method. The values here will change as follows:
  2. Using the traditional method, this total is marked up by the Sales Mark up in the global settings
  3. If MSRP is selected, this will reflect parts plus labor cost plus the profit for Parts and Service
  4. Sales Commission: Input by dealer. Default value is percentage of selling price defined by dealer in global settings. Dealer can make adjustments for each product
  5. Retail Price-Parts Only: This is set by Parts department, and will either be: calculated by markup method, MSRP or manually input in yellow field
  6. Retail Price - Installed price: Retail Parts only price plus Retail Labor only. Will either be: calculated by markup method, MSRP or manually input in yellow field
  7. Departmental Profit-Parts: Difference between dealer cost and the internal price. Equals cost multiplied by parts markup for traditional mark up dealers. If MSRP model is used, this will be the departmental split of profit selected in the global settings.
  8. Departmental Profit-Service: If MSRP is not selected, this will show $0 unless the “Show Labor Profit” button is selected. If MSRP is selected, this will show the service portion of overall dealer profit selected in the global settings.
  9. Departmental Profit-Sales: Traditional model, difference between selling price and internal price. Calculated by multiplying internal price by Sales Mark up in global settings. Can be reduced to reflect actual profit if “include Sales Commission is selected. If MSRP is selected, overall dealer profit multiplied by profit split selected in global settings.
  10. Dealer Profit: Sum of departmental profits
  11. Profit %: Total dealer profit percentage (overall dealer profit divided by selling price)

Sales Manager Worksheet

This is a view only file compressed to show

product level sorting.

Comment field: Is available to provide

additional information. Areas where

comments might be required are:

Wheels (more than 1 required), products

that require multiple parts etc.

Customer Menu

This is a printable menu that can be shared with clients. Comment fields populate based on what is put into Sales Manager Worksheet, but can be changed if desired.

Tips:

  • Add photos from ASG or taken by dealer to dress up customer menu
  • Use Comments field to add sales oriented message
  • Use tool to point out combinations of products that make sense for customer

Glossary of Terms:

  • Category: Group of products that are all related based on similar function
  • Departmental Profit: Amount of profit each department receives from sale of a product. Can be determined based on traditional mark up by department, or by taking the total profit and dividing it between the departments.
  • DIO: Dealer Installed Option
  • Global Settings: Used for pricing worksheet to set overall pricing strategy for dealer. This is first step before adjusting individual product prices
  • Install Time: Sometimes referred to as Flat Rate Hours, Time needed to install products on vehicle. Will either be suggested time (matching PPO install time), or can be set by dealer based on local needs
  • Installed: Includes parts and labor associated with product
  • Installed MSRP: PPO pricing that reflects on Monroney
  • Labor cost: Determined by service manager as the true cost of labor. Could include sales weighted average labor cost, shop supplies etc.
  • Labor Profit: Hourly difference in rate between service labor rate and weighted cost
  • Market Based Price: Price that dealer sets to reflect local market conditions
  • Markup: Used for Sales and Parts to reflect percentage of cost that is marked up to get to final (internal or customer) price. This is always higher than the resulting gross profit margin
  • Parts Only: Pricing does not include labor costs
  • Product: Accessory name, usually the same as the part name if there are not any required parts listed
  • Profit Split: How total profit for the installed accessory is divided between the departments
  • PPO: Post Production Options that can be ordered when vehicle is allocated
  • Required Parts: Parts required in order to complete the installation of a product. When pricing total installed price, be sure to include any parts that are required.
  • Sales Commissions: Dollar amount paid to sales person for sale of accessory. For this model, dealer can select a percentage of the retail price. Dealer may have other methods that can be reflected by manual input
  • Traditional Mark Up: “Cost Up” model where the cost of parts and labor for a product is marked up to determine retail pricing. Parts marks up the cost of the product, and service multiplies the number of flat rate hours times a rate to determine the Internal price. This price is then marked up by the sales department to determine retail