Assignment:

Open Assignment Data File Download Open Assignment Data File. You will use data in this file to create an order form for eCity.

This file has three worksheets: Order, Shipping, and SKU.$$

SKU worksheet: A table lists all items in the store with their SKU $($Stock Keeping Unit$)$ code, description, price, and category of merchandise.

Shipping worksheet: there are three tables in this worksheet.$$

Base Rates: lists base rate shipping for all categories

Shipping Method: lists available shipping methods and their rate adjustment factor for shipping

State Rate Factor: lists all states and their shipping adjustment factors.

To calculate the shipping charge for each item, multiply the base rate of the category by shipping method and state rate factors.$$

Customers place orders using a catalog.$$ Your order form must recognize category, description, and price of each item by entering the Stock Keeping Unit $($SKU$).$ Customer total charge is calculated by the quantity and price of items ordered and shipping charge.$$ The shipping charge for each item depends on the base rate of its category, the shipping method, and the state rate factors. All of these items are listed in worksheet $$Shipping$.$Multiply these factors and the quantity ordered in a formula to calculate shipping charge for each order line.$$ Prepare your order form by using light blue background cells $($B$2,$ B$3,$ and A$6$: B$15)$ for data entry, and light green background cells for functions and formulas.$$ Reference data in SKU and Shipping worksheets in your formulas.$$

Using named ranges for data references can help referencing and reducing the chance of error.$$ Create the following named ranges and use them in your formulas:

$$Named Range

Worksheet

Range

SKU

SKU

A$2$:E$110$

Base$\_$rate

Shipping

A$3$:B$8$

Ship$\_$method

Shipping

E$3$:F$5$

State$\_$Rate$\_$Ship

Shipping

K$3$:L$52$

$$

Data entry cells:

Customer ID $($B$2)$ is a reference to the database $($no need to enter it$,$ we don't have the database here$)$

Shipping Method $($B$3)$ needs a drop$-$down listing all available methods.$$ Create this drop$-$down list.

State Code $($B$4)$ needs a drop$-$down listing all available State Codes. Create this drop$-$down list.

SKU $($A$6$:A$15)$ all these cells need a drop$-$down listing the SKUs.$$ Create these drop$-$down lists.

Quantity $($B$6$:B$15)$ for entering the quantity of each item when ordered.

Formula cells:

Description $($F$6$:F$15).$ Each cell will automatically retrieve the description of the item ordered based on its SKU in the same row

Category $($E$6$:E$15).$ Each cell will automatically retrieve category of the item ordered based on its SKU in the same row

Price $($C$6$:C$15).$ Each cell will automatically retrieve the unit price for the item ordered based on its SKU in the same row

QoH $($D$6$:D$15).$ Each cell automatically retrieves QoH for the item ordered based on its SKU in the same row.

Shipping $($G$6$:G$15).$ Each cell will automatically retrieve the shipping method rate factor, category base rate and the state rate factor.$$ Shipping charge is the product of these numbers and the quantity ordered.

Subtotal $($H$6$:H$15).$ Each cell calculates the price for the quantity ordered in the same row.

Grand total $($H$17).$ Adds up all subtotals and shipping costs.

$$

Enter all formulas for description, category, price, shipping, and subtotal in the first row $($row $6)$ and copy them down through row $15.$

Formulas referencing blank cells return error messages. Your formula cells should not show any error message when there is no order.

To test your formulas:

Shipping Method: Standard

State Code: AZ

select the following $4$ SKU values in column A: APS$539,$ FDW$194,$ QGK$217,$ YWA$277$

enter quantities for them in column B

APS$539-5$FDW$194-3$QGK$217-3$YWA$277-2$

all values including subtotal and grand total must automatically calculate.

Apply a comma style format to all numbers.