The purchase of a car usually entails extensive bargaining between
the dealer and the consumer. The dealer has an asking price, but typically
settles for less. The commission paid to a salesperson depends on how
close the selling price is to the asking price. Exotic Motors has the
following compensation policy for its sales staff:
The dealer's asking price is based on the dealer's cost plus a 20% markup;
for example, the asking price on a car that cost the dealer $20,000 would
- A 3% commission on the actual selling price for cars sold at 95%
or more of the asking price.
- A 2% commission on the actual selling price for cars sold at 90%
or more (but less than 95%) of the asking price.
- A 1% commission on the actual selling price for cars sold at less
than 90% of the asking price. The dealer will not go below 85% of her
Develop a spreadsheet to be used by the dealer, which shows her profit (the
sales price less dealer's cost and commission) on every sale. The
spreadsheet should be completely flexible and allow the dealer to vary the
markup or commission percentages without having to edit or recopy any of
the formulas. Use the file dealer.xls on the R: drive in the
excelprj folder as your starting point.