Exp22_Excel_Ch04_HOE - Toy Store 1.1

Step

Instructions

Points Possible

1

Start Excel. Download and open the file named Exp22_Excel_Ch04_HOE_ToyStore.xlsx. Grader has automatically added your last name to the beginning of the filename.

0

2

You would like to set page breaks on the June Totals worksheet to allow the document to print without splitting transactions dates. Modify the existing page breaks to ensure the 6/13 and 6/24 transaction dates print on the same page.

3

3

Create a copy of the June Totals worksheet and name it June 1st Totals.

5

4

Return to the June Totals worksheet. When printed you would like the titles to be preserved. Set the titles in row 5 and columns A:B to repeat when printed. Additionally set the print order to print over then down.

3

5

You would like to convert the dataset to a table in order to add aggregation, structured references, and table style. Convert the range A5:K110 to a table. Be sure to include column labels. Name the table Totals and apply the table style Light Yellow, Table Style Light 19.

8

6

You don't need the Sales_First column. To reduce the size of the dataset you will remove the column. Delete the Sales_First column from the table.

3

7

You need to document the rebate information and total purchase price. To do so you will add two new columns to the data. Type Rebate in cell K5 and Owed in cell L5. Set the width of columns I:J to autofit.

2

8

You notice you are missing records 2024-068 and 2024-105. You will manually add the records back to the table. Insert a row in the table at row 73 and row 112. Enter the following records. Row 72 - 2024-068, 6/22/2024, Shah, Collectibles, 1014, Mattel, Store Credit, Standard, 4, $16.99 Row 112 - 2024-105, 6/30/2024, McGowan, Action Figures, 1015, Mattel, Store Credit, Promotion, 1, $13.49

2

9

You notice there are duplicate values in the table that need to be removed. Remove all duplicate values from the Totals table.

3

10

To calculate rebate amount you will create an IF function with structured references in column K. Enter an IF statement in cell K6 to determine the customers rebate. If the value in cell H6 = Promotion the customer receives a 10% rebate on the total purchase (Purchase Price * Quantity*Rebate) if they are not eligible the function should return 0. Be sure to use the appropriate structured references and then use the fill handle to copy the function down completing the column.

10

11

To calculate the total owed you will create a formula in column L using structured references. Enter a formula using structured references in cell L6 to calculate the total owed. The total owed is the purchase price * quantity - rebate.

10

12

You would like to focus a portion of the report on June sales. Add a total row to the existing table and set the Rebate and Quantity subtotals to Sum.

4

13

Sort the June Totals worksheet by Pay_Type A to Z, then by Trans_Type A to Z, and Owed largest to smallest. For the last level of the sort add a custom list for the department column. The list should use the following order Electronics, Collectibles, Infants, Action Figures, and Board Games.

6

14

Apply a filter to the table to only display only electronics department sales from sales rep Radomanski.

10

15

Apply a filter to the table to only display transaction amounts of $300 or more.

5

16

Apply a filter to the table to only display transaction dates between 6/16/2024 and 6/30/2024.

4

17

You would like to use conditional formatting to highlight several key performance indicators in the June Individual worksheet. Make the June Individual worksheet active. Create a new conditional formatting rule to highlight the name Rodriguez in column C with Green Fill with Dark Green Text.

4

18

Create a new conditional formatting rule to highlight the top 3 sales amounts in column L with light red fill and dark red text.

4

19

Add Blue Data Bars conditional formatting to column K.

4

20

Create a conditional formatting rule that highlights any transaction in column A that is completed by the sales rep Rodriguez with a value over $500. Ensure the formatting applies Bold font with Orange Accent 2 background color.

6

21

Filter column A based on color.

4

22

Save and close Exp22_Excel_Ch04_HOE_ToyStore.xlsx. Exit Excel.

0

Total Points

100