Problem 1

The table in worksheet "Problem 1" is a list of orders from a company that sells sports products. Each row is an order. Only one type of product is sold in each order, but the quantity and price of each unit vary across orders. Information about each order includes location, customer, segment, product sold, order date, the list price per unit, the price each unit was actually sold in the order (unit price), quantity of units sold, revenue (sales), and percent discount on each other.

• The company charges customers to ship the orders and uses a shipping rate for each order that depends on the region and business segment. Shipping is 6% of revenue for orders shipped to the North America Region in the Business Segment of Bikes. The shipping rate is 2% for all other orders. Show the appropriate rate for each order in column ShipRate.
• The company pays sales tax on revenue of each order. Tax is 9% of revenue if revenue is less than $1,000, 8% if revenue is greater than or equal to $1,000 but less than $2,000, and 7% if revenue is greater than or equal to $2,000. Add a column TaxRate with the tax rate.
• On column GrossSale calculate the revenue of each order plus shipping (in dollars) minus tax (in dollars).

Market

Customer

Business Segment

Category

Model

Color

SalesDate

ListPrice

UnitPrice

OrderQty

Discount

Revenue

ShipRate

TaxRate

GrossSale

Central

Great Bicycle Supply

Bikes

Mountain Bikes

Mountain-100

Black

05/16/09

$3,375

$3,289

6

3%

$19,736

Northwest

Metro Cycle Shop

Components

Brakes

Front Brakes

Silver

09/01/09

$107

$64

4

40%

$256

Southwest

Sturdy Toys

Bikes

Mountain Bikes

Mountain-200

Black

08/12/09

$2,295

$2,292

4

0%

$9,168

Northwest

Metro Cycle Shop

Bikes

Mountain Bikes

Mountain-200

Silver

07/10/10

$2,320

$1,392

4

40%

$5,568

Canada

Quick Parts and Service

Bikes

Touring Bikes

Touring-1000

Blue

03/29/10

$2,384

$1,430

1

40%

$1,430