Exp22_excel_ch04_hoe – toy store 1.1

 Exp22_Excel_Ch04_HOE – Toy Store 1.1 

 Exp22 Excel Ch04 HOE – Toy Store 1.1

 Excel Chapter 4 Hands-On Exercise – Toy Store 


Project Description:

You work for the owner of Trinkets Toys & Games LLC. All merchandise is categorized into one of five departments for inventory records and sales. Along with the owner, there are three sales representatives. The sales system tracks which sales representative processed each transaction. The business has grown rapidly and you want to analyze the sales data to increase future profits. You downloaded June 2024 data from the sales system into an Excel workbook. Because the dataset is large, you will convert the data into a table, sort, filter, and utilize conditional formatting to complete your analysis.


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.


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.


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


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.


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.


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.


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   in cell L5.   Set the width of columns I:J to autofit.


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



You notice there are duplicate   values in the table that need to be removed.

  Remove all duplicate values from the Totals table.


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.


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. 


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.


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.


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


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


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


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.


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


Add Blue Data Bars conditional   formatting to column K.


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. 


Filter column A based on color.


Save and close Exp22_Excel_Ch04_HOE_ToyStore.xlsx.   Exit Excel. 

"Get 15% discount on your first 3 orders with us"
Use the following coupon

Order Now