A. Sales Records 1. Format the sales records dataset as a table. Make sure the table columns are wide enough to fit the column contents.

2. Sort the sales records by Region first in a custom order of Midwest, East, West, South. In the same Region group, sort the records then by ProdID in alphabetical order.

3. Add a new column at the right of ProdID, and name it Price. Use vLookUp function in the Price column. For each sales record, use the ProdID as lookup value to find and return the product price. The product prices are provided in the B. Summary worksheet. You can use the data range of B8:E10 from the B. Summary worksheet as the table array in making the vLookUp function.

4. Add a new column at the right of UnitsSold, and name it SubTotal. The subtotal of each sales record should be the product of price and units sold (Price * UnitsSold).

5. Add a new column at the right of SubTotal, and name it Discount. This company provides 8%of the subtotal amount as discount to all sales made in the West region. In the other 2 regions, the company offers 10% of the subtotal amount as discount to Holiday sales in December. The other sales records that are not in West and not in December receive $0 discount. The two discounts cannot be stacked - none of the order can take both West region and the holiday discounts at the same time. Use a nested IF function for the Discount column to show the correct discount for each sales record. (Hint: you can make separate IF function for the two conditions, and then apply one into the other as value if false.)

6. Add a new column at the right of Discount, and name it Discount Amount. This is equal to Discount*SubTotal.

7. Add a new column at the right of Discount Amount, named OrderTotal. The order total of each sales record will be the difference between subtotal and the discount amount (SubTotal -Discount Amount).

ProdID	Salesperson	SaleMonth	Region	UnitsSold
A	Charlene	March	East	6
A	Hong	March	East	40
A	Jae	June	East	14
A	Jae	October	East	4
A	Jason	November	East	20
A	Jason	August	East	8
A	Jason	June	East	6
A	John	September	East	16
A	John	October	East	2
A	Peter	February	East	10
A	Vivian	July	East	6
A	Charlene	March	Midwest	28
A	Jae	April	Midwest	18
A	Jae	April	Midwest	18
A	Jason	June	Midwest	6
A	John	December	Midwest	6
A	Julio	January	Midwest	14
A	Julio	September	Midwest	18
A	Peter	April	Midwest	30
A	Peter	May	Midwest	12
A	Peter	January	Midwest	16
A	Peter	October	Midwest	6
A	Samuel	February	Midwest	12
A	Vivian	June	Midwest	14
A	Vivian	April	Midwest	6
A	Vivian	August	Midwest	6
A	Charlene	May	South	8
A	Charlene	December	South	6
A	Damon	March	South	18
A	Jae	October	South	4
A	Jae	December	South	18
A	Jason	November	South	4
A	John	June	South	6
A	Julio	June	South	34
A	Julio	November	South	16
A	Samuel	January	South	14
A	Vivian	August	South	14
A	Vivian	April	South	8
A	Charlene	January	West	20
A	Charlene	March	West	14
A	Charlene	June	West	18
A	Charlene	August	West	12
A	Jae	November	West	8
A	Jason	March	West	18
A	Jason	October	West	14
A	Jason	February	West	2
A	Jason	May	West	18
A	Julio	November	West	14
A	Julio	October	West	2
A	Julio	January	West	8
A	Peter	April	West	12
A	Samuel	February	West	30
A	Vivian	June	West	14
A	Vivian	February	West	14
A	Hong	October	East	14
A	Hong	January	East	8
A	Jason	April	East	8
A	Jason	March	East	14
A	Julio	March	East	10
A	Julio	May	East	8
A	Peter	December	East	6
A	Damon	January	Midwest	14
A	Hong	January	Midwest	6
A	Jae	July	Midwest	4
A	Jason	November	Midwest	20
A	Julio	April	Midwest	14
A	Julio	December	Midwest	8
A	Julio	March	Midwest	8
A	Peter	August	Midwest	12
A	Samuel	November	Midwest	6
A	Vivian	October	Midwest	6
A	Vivian	February	Midwest	16
A	Charlene	August	South	6
A	Charlene	January	South	10
A	Damon	August	South	12
A	Hong	March	South	18
A	Hong	June	South	6
A	Hong	April	South	6
A	Jae	April	South	6
A	Jae	August	South	10
A	Jason	April	South	16
A	John	April	South	16
A	Julio	September	South	18
A	Peter	August	South	12
A	Vivian	April	South	16
A	Charlene	January	West	14
A	Charlene	April	West	6
A	Charlene	February	West	6
A	Damon	September	West	4
A	Damon	May	West	16
A	Damon	September	West	20
A	Hong	March	West	14
A	Jae	September	West	4
A	Jae	September	West	6
A	Jason	June	West	18
A	Julio	November	West	8
A	Julio	September	West	14
A	Peter	February	West	12
A	Samuel	May	West	18
A	Vivian	February	West	18
A	Charlene	October	East	8
A	Damon	December	East	16
A	Hong	June	East	6
A	Jae	November	East	16
A	Jae	January	East	10
A	Jae	July	East	10
A	Jason	February	East	10
A	Jason	August	East	4
A	John	January	East	12
A	John	May	East	14
A	John	December	East	16
A	Peter	May	East	12
A	Peter	May	East	12
A	Samuel	August	East	12
A	Samuel	April	East	6
A	Vivian	February	East	4
A	Charlene	March	Midwest	4
A	Damon	December	Midwest	16
A	Damon	January	Midwest	4
A	Damon	September	Midwest	6
A	Hong	January	Midwest	10
A	Hong	July	Midwest	14
A	Hong	November	Midwest	18
A	Jason	August	Midwest	6
A	John	May	Midwest	14
A	John	August	Midwest	18
A	John	March	Midwest	18
A	Peter	April	Midwest	16
A	Peter	December	Midwest	12
A	Peter	February	Midwest	14
A	Samuel	May	Midwest	2
A	Vivian	May	Midwest	20
A	Vivian	March	Midwest	18
A	Charlene	January	South	6
A	Damon	December	South	8
A	Hong	November	South	18
A	Hong	September	South	18
A	Jae	February	South	8
A	John	July	South	2
A	Julio	November	South	2
A	Julio	December	South	4
A	Julio	March	South	10
A	Samuel	May	South	14
A	Charlene	August	West	16
A	Damon	October	West	18
A	Hong	July	West	8
A	Hong	December	West	2
A	Jae	January	West	4
A	John	July	West	2
A	John	December	West	6
A	Julio	September	West	8
A	Julio	August	West	18
A	Peter	January	West	8
A	Peter	April	West	6
A	Peter	March	West	18
A	Peter	November	West	8
A	Samuel	January	West	10
A	Samuel	January	West	8
A	Charlene	November	East	10
A	Charlene	October	East	12
A	Hong	September	East	12
A	Hong	January	East	20
A	Jae	January	East	16
A	Jason	October	East	8
A	John	May	East	14
A	Hong	December	West	14
A	Jae	December	West	16
A	Jason	May	West	18
A	John	April	West	6
A	Julio	January	West	6
A	Julio	November	West	2
A	Peter	April	West	14
A	Hong	May	East	6
A	Hong	May	East	8
A	Hong	January	East	8
A	John	February	East	18
A	John	July	East	12
A	Vivian	January	East	6
A	Vivian	April	East	2
A	Hong	June	Midwest	14
A	Hong	September	Midwest	2
A	Vivian	February	West	6
A	Charlene	February	East	8
A	Hong	November	East	16
A	Jason	October	East	14
A	John	July	East	10
A	Samuel	August	East	12
A	Samuel	September	East	8
A	Charlene	April	Midwest	18
A	Charlene	September	Midwest	14
A	Damon	March	Midwest	8
A	Hong	July	Midwest	18
A	Hong	September	Midwest	8
A	Jae	November	Midwest	18
A	Jae	February	Midwest	8
A	John	July	Midwest	14
A	John	April	Midwest	6
A	Julio	November	Midwest	16
A	Samuel	November	Midwest	2
A	Samuel	October	Midwest	18
A	Vivian	September	Midwest	10
A	Vivian	May	Midwest	16
A	Damon	July	South	18
A	Damon	February	South	4
A	Damon	February	South	10
A	Damon	August	South	18
A	Hong	October	South	6
A	Jae	February	South	10
A	Jae	June	South	12
A	Jason	November	South	8
A	Jason	October	South	6
A	Jason	December	South	16
A	Julio	March	South	18
A	Samuel	June	South	18
A	Vivian	November	South	4
A	Hong	March	West	20
A	Hong	May	West	4
A	Hong	April	West	14
A	Jae	December	West	8
A	Jae	December	West	4
A	Jason	May	West	14
A	Jason	July	West	16
A	Julio	September	West	8
A	Peter	February	West	20
A	Vivian	April	West	6
A	Vivian	October	West	18
A	Charlene	June	East	18
A	Charlene	August	East	6
A	Damon	November	East	16
A	Damon	January	East	20
A	Jason	May	East	10
A	John	April	East	4
A	John	February	East	6
A	Julio	April	East	12
A	Julio	January	East	4
A	Peter	December	East	6
A	Vivian	February	East	12
A	Vivian	December	East	6
A	Vivian	August	East	18
A	Charlene	May	Midwest	6
A	Damon	May	Midwest	20
A	Hong	December	Midwest	16
A	Jae	September	Midwest	18
A	Jae	November	Midwest	12
A	Jason	January	Midwest	16
A	Jason	March	Midwest	8
A	John	March	Midwest	20
A	John	November	Midwest	2
A	Julio	June	Midwest	8
A	Julio	February	Midwest	12
A	Julio	July	Midwest	8
A	Peter	October	Midwest	6
A	Samuel	June	Midwest	8
A	Vivian	January	Midwest	16
A	Charlene	May	South	8
A	Charlene	February	South	8
A	Damon	December	South	14
A	Hong	September	South	16
A	Hong	June	South	20
A	Jae	February	South	27
B	Hong	February	East	40
B	Jae	January	East	36
B	Jae	November	East	16
B	Jason	May	East	20
B	Jason	January	East	36
B	Jason	September	East	32
B	John	June	East	40
B	Julio	August	East	28
B	Peter	September	East	8
B	Samuel	March	East	16
B	Vivian	August	East	40
B	Charlene	August	Midwest	20
B	Charlene	January	Midwest	28
B	Charlene	June	Midwest	12
B	Damon	December	Midwest	36
B	Hong	January	Midwest	20
B	Jason	December	Midwest	24
B	John	October	Midwest	12
B	John	February	Midwest	32
B	Peter	February	Midwest	28
B	Charlene	April	South	20
B	Charlene	January	South	12
B	Damon	July	South	24
B	Damon	August	South	8
B	Damon	October	South	8
B	Hong	November	South	28
B	Jae	July	South	8
B	Julio	October	South	24
B	Julio	August	South	28
B	Samuel	August	South	4
B	Charlene	November	West	12
B	Damon	February	West	12
B	Damon	April	West	20
B	Damon	July	West	40
B	Damon	August	West	4
B	Hong	October	West	20
B	Jae	May	West	12
B	Jae	December	West	32
B	Jason	September	West	36
B	John	July	West	40
B	John	December	West	4
B	Julio	November	West	24
B	Julio	February	West	12
B	Peter	October	West	4
B	Samuel	November	West	12
B	Vivian	July	West	28
B	Charlene	December	East	16
B	Hong	July	East	16
B	Jae	January	East	8
B	Jae	August	East	16
B	Jae	October	East	24
B	Jason	May	East	36
B	Jason	January	East	24
B	John	February	East	12
B	Julio	April	East	16
B	Peter	February	East	28
B	Samuel	June	East	8
B	Vivian	August	East	36
B	Hong	October	Midwest	32
B	Jason	September	Midwest	12
B	Jason	June	Midwest	12
B	John	March	Midwest	16
B	Julio	July	Midwest	28
B	Julio	February	Midwest	16
B	Julio	February	Midwest	40
B	Peter	April	Midwest	32
B	Peter	May	Midwest	8
B	Peter	March	Midwest	8
B	Peter	September	Midwest	12
B	Samuel	March	Midwest	40
B	Samuel	March	Midwest	4
B	Samuel	September	Midwest	24
B	Samuel	June	Midwest	16
B	Vivian	September	Midwest	40
B	Charlene	June	South	40
B	Damon	June	South	32
B	Damon	April	South	12
B	Hong	September	South	28
B	Jae	September	South	4
B	Jae	August	South	20
B	Jason	December	South	40
B	Julio	June	South	32
B	Peter	December	South	28
B	Peter	March	South	20
B	Vivian	June	South	4
B	Vivian	March	South	24
B	Charlene	July	West	16
B	Charlene	November	West	8
B	Damon	June	West	32
B	Jae	February	West	28
B	Jae	April	West	32
B	Jae	March	West	16
B	Jae	June	West	40
B	Jason	October	West	24
B	Jason	September	West	40
B	Jason	December	West	16
B	John	June	West	12
B	Julio	May	West	8
B	Julio	February	West	8
B	Peter	September	West	8
B	Samuel	January	West	32
B	Vivian	March	West	12
B	Charlene	February	East	28
B	Damon	February	East	8
B	Hong	July	East	24
B	Hong	August	East	12
B	Hong	November	East	36
B	Jae	November	East	36
B	Jason	May	East	36
B	John	July	East	28
B	Julio	April	East	28
B	Peter	October	East	32
B	Peter	July	East	24
B	Peter	December	East	28
B	Peter	March	East	40
B	Samuel	May	East	12
B	Samuel	December	East	12
B	Vivian	April	East	12
B	Charlene	July	Midwest	24
B	Charlene	September	Midwest	20
B	Damon	December	Midwest	28
B	Damon	January	Midwest	14
B	Hong	May	Midwest	26
B	Jason	April	Midwest	28
B	Jason	July	Midwest	40
B	Jason	July	Midwest	36
B	Jason	June	Midwest	40
B	John	January	Midwest	36
B	John	August	Midwest	28
B	Julio	December	Midwest	28
B	Samuel	August	Midwest	36
B	Samuel	December	Midwest	24
B	Charlene	April	South	9
B	Charlene	January	South	9
B	Charlene	August	South	9
B	Damon	March	South	18
B	Damon	September	South	30
B	Hong	October	South	15
B	Hong	January	South	6
B	Jae	October	South	18
B	Jae	March	South	21
B	John	August	South	9
B	John	March	South	9
B	Julio	June	South	24
B	Peter	May	South	18
B	Samuel	January	South	6
B	Charlene	August	West	15
B	Damon	March	West	3
B	Hong	October	West	9
B	Jae	April	West	9
B	Jason	October	West	18
B	Jason	May	West	12
B	Julio	September	West	21
B	Peter	April	West	3
B	Peter	December	West	24
B	Samuel	August	West	15
B	Samuel	January	West	15
B	Vivian	July	West	18
B	Vivian	September	West	30
B	Jae	March	South	18
B	Jae	May	South	18
B	John	March	South	6
B	John	March	South	3
B	Julio	July	South	21
B	Julio	July	South	12
B	Samuel	May	South	30
B	Charlene	January	West	21
B	Charlene	November	West	15
B	Charlene	May	West	18
B	Hong	January	West	12
B	Jason	August	West	24
B	Jason	June	West	18
B	John	October	West	21
B	Julio	October	West	24
B	Julio	October	West	6
B	Samuel	April	West	18
B	Vivian	July	West	15
B	Charlene	December	East	24
B	Damon	September	East	24
B	Damon	May	East	9
B	Damon	August	East	24
B	Hong	December	East	15
B	Hong	September	East	18
B	Jae	January	East	21
B	John	August	East	27
B	John	January	East	3
B	John	February	East	30
B	John	February	East	27
B	John	December	East	3
B	Julio	August	East	24
B	Julio	June	East	9
B	Vivian	January	East	15
B	Vivian	March	East	9
B	Damon	April	Midwest	21
B	Hong	July	Midwest	12
B	Jae	August	Midwest	18
B	Jae	June	Midwest	18
B	Jason	May	Midwest	21
B	Jason	May	Midwest	3
B	John	June	Midwest	24
B	John	April	Midwest	30
B	John	December	Midwest	30
B	Julio	August	Midwest	21
B	Julio	June	Midwest	12
B	Peter	June	Midwest	3
B	Samuel	November	Midwest	21
B	Vivian	February	Midwest	9
B	Charlene	August	South	21
B	Damon	June	South	12
B	Jae	December	South	15
B	John	June	South	18
B	Julio	September	South	3
B	Vivian	October	South	21
B	Charlene	August	West	30
B	Charlene	June	West	24
B	Damon	October	West	3
B	Jae	November	West	18
B	Jae	October	West	18
B	Jae	January	West	12
B	Jason	November	West	9
B	John	August	West	3
B	Julio	October	West	3
B	Peter	June	West	27
B	Samuel	May	West	18
B	Vivian	March	West	27
C	Charlene	November	East	30
C	Charlene	January	East	9
C	Charlene	December	East	27
C	Charlene	August	East	12
C	Damon	August	East	6
C	Damon	March	East	27
C	Hong	August	East	15
C	Hong	June	East	27
C	Jason	August	East	3
C	John	July	East	21
C	John	January	East	30
C	Samuel	December	East	30
C	Damon	December	Midwest	12
C	Hong	September	Midwest	24
C	Jason	September	Midwest	3
C	John	March	Midwest	24
C	John	July	Midwest	3
C	Peter	August	Midwest	9
C	Vivian	November	Midwest	27
C	Vivian	November	Midwest	21
C	Vivian	March	Midwest	9
C	Charlene	June	South	24
C	Charlene	April	South	12
C	Charlene	December	South	3
C	Damon	February	South	30
C	Hong	January	South	21
C	Hong	March	South	12
C	Jae	July	South	27
C	Jae	June	South	27
C	Jae	February	South	21
C	John	January	South	18
C	John	October	South	15
C	Peter	March	South	21
C	Peter	November	South	15
C	Peter	November	South	6
C	Samuel	September	South	21
C	Samuel	January	South	18
C	Charlene	March	West	24
C	Damon	February	West	18
C	Damon	September	West	12
C	Hong	March	West	12
C	Jason	April	West	21
C	Jason	July	West	24
C	John	September	West	27
C	John	February	West	27
C	Julio	November	West	12
C	Jason	November	Midwest	24
C	Jason	September	Midwest	27
C	John	March	Midwest	12
C	John	January	Midwest	27
C	John	March	Midwest	3
C	John	March	Midwest	6
C	John	August	Midwest	3
C	Peter	October	Midwest	15
C	Vivian	September	Midwest	12
C	Vivian	April	Midwest	6
C	Vivian	April	Midwest	6
C	Vivian	October	Midwest	15
C	Vivian	October	Midwest	42
C	Vivian	December	Midwest	21
C	Charlene	April	South	22
C	Charlene	March	South	3
C	Charlene	May	South	21
C	Damon	November	South	22
C	Hong	July	South	24
C	Hong	February	South	21
C	Jason	May	South	12
C	Jason	April	South	18
C	Jason	September	South	40
C	John	August	South	28
C	Peter	April	South	8
C	Charlene	May	West	36
C	Charlene	October	West	24
C	Jae	June	West	20
C	Jae	May	West	16
C	Jason	May	West	36
C	John	June	West	8
C	Julio	November	West	20
C	Julio	April	West	24
C	Peter	July	West	20
C	Peter	February	West	24
C	Peter	January	West	40
C	Samuel	November	West	20
C	Samuel	August	West	40
C	Samuel	July	West	4
C	Charlene	August	East	24
C	Damon	August	East	32
C	Jae	August	East	16
C	Jae	December	East	36
C	Jason	December	East	40
C	John	August	East	16
C	Julio	July	East	28
C	Peter	March	East	16
C	Samuel	August	East	12
C	Hong	February	Midwest	8
C	Jae	August	Midwest	27
C	Peter	May	Midwest	30
C	Samuel	February	Midwest	18
C	Samuel	June	Midwest	21
C	Charlene	December	South	12
C	Charlene	January	South	24
C	Charlene	December	South	24
C	Charlene	July	South	30
C	Hong	October	South	21
C	Jae	June	South	30
C	Jae	January	South	21
C	John	July	South	18
C	John	December	South	27
C	Peter	April	South	9
C	Peter	November	South	27
C	Peter	May	South	27
C	Vivian	July	South	24
C	Vivian	August	South	15
C	Damon	August	West	3
C	Hong	February	West	21
C	Jae	June	West	9
C	Jason	September	West	27