Shelly Cashman Excel 365 | Module 6: End of Module Project 1 ActiveBee
CREATE, SORT, AND QUERY TABLES
· Excel Projects Help
GETTING STARTED
1. Save the file SC_EX365_EOM6-1_FirstLastName_1.xlsx as SC_EX365_EOM6-1_FirstLastName_2.xlsx
a. Edit the file name by changing “1” to “2”.
b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.
2. With the file SC_EX365_EOM6-1_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
a. If cell B6 does not display your name, delete the file and download a new copy.
PROJECT STEPS
1. Dalia Fernandez is the sales manager for ActiveBee, a fitness product manufacturer with headquarters in Raleigh, North Carolina. Dalia is analyzing the performance of sales representatives to determine their bonuses. She asks for your help in using Excel tables to complete the analysis. Go to the Sales Bonuses worksheet, which lists details about ActiveBee sales representatives and their customers, sales, and customer ratings. Format the completed sales representatives data (range B11:J30) as a table with headers using Blue Gray, Table Style Medium 2 so that Dalia can summarize and filter the data. Use SalesReps as the name of the table.
2. Sort the SalesReps table first in ascending order by the name of the sales rep and then in descending order by total sales so Dalia can quickly identify the customer with the top sales for each rep.
3. Each sales rep is assigned to a level depending on their sales, which determines whether they receive a salary bonus. Dalia wants you to list the level for each sales rep according to the Bonus Levels data in the range L6:M10. In cell I12, enter a formula using the XLOOKUP function. Use a structured reference to the value in the Rating column as the lookup value. Use an absolute cell reference to the Bonus Levels Rating column (the range L7:L10) as the lookup_array, and an absolute reference to the Level column (the range M7:M10) as the return_array. Leave the if_not_found argument blank, and use -1 as the match mode to return the next smallest value. If Excel does not copy the formula automatically, fill the range I13:I30 with the formula in cell I12. (Hint: To avoid a #SPILL error, enable implicit intersection by placing the This Row specifier [@ character] in front of field names.)
4. ActiveBee awards a bonus of 7.5% (.075) to sales reps who earn Diamond-level ratings from their customers. In cell J12, enter a formula using the IF function and structured references that tests whether the value in the Level field is equal to "Diamond". If it is, multiply the value in the Total Sales field by 0.075. Otherwise, enter 0 (zero) in the cell. If Excel does not copy the formula automatically, fill the range J13:J30 with the formula in cell J12. (Hint: To avoid a #SPILL error, enable implicit intersection by placing the This Row specifier [@ character] in front of field names.)
5. Dalia asks you to identify the sales reps with high, average, and low customer ratings. In the Rating column (range H12:H30), create a new Icon Set conditional formatting rule using the 3 Stars icons. Edit the rule to display a shaded star in cells with a Number type value greater than or equal to 8. Display a half-shaded star in cells with a Number type greater than or equal to 6. Display an unshaded star in cells with a Number type value less than 6.
6. Add a total row to the SalesReps table, which automatically totals the bonuses. Using the total row, display the count of customer names, the sum of the total sales, and the average of the ratings.
7. In the range O7:P9, Dalia wants you to list statistics from the SalesReps table. In cell P7, enter a formula using the DAVERAGE function to average the sales of the Electronics product type. Use a range reference to the SalesReps table (range B11:J30) as the database, "Total Sales" as the field, and the range R6:R7 as the criteria.
8. In cell P8, enter a formula using the SUMIF function that totals the sales of the Electronics product type. Use a range reference to the Product Type values (range F12:F30) as the range, cell R7 as the criteria, and a range reference to the Total Sales values (range G12:G30) as the sum_range.
9. In cell P9, enter a formula using the COUNTIF function that counts the number of electronic products, using a structured reference to the Product Type column (SalesReps[Product Type]) as the range and cell R7 as the criteria.
10. Dalia wants you to identify customers in the southeast with total sales of $100,000 or more, and then list them in a separate part of the worksheet. In cell E7, enter a criterion to select customers in the Southeast region. In cell G7, enter a criterion to select Total Sales greater than or equal to 100000. Create an advanced filter using the data in the SalesReps table (range B11:J30) as the List range. Use the range B6:J7 as the Criteria range. Copy the results to another location, starting in the range B35:J35.
11. As a contrast, Dalia also wants you to list the customers in other regions. In the SalesReps table, display the filter arrows, and then filter the table to display all customers except those in the southeast. (Hint: Use the Filter command on the Sort & Filter menu to display the filter arrows.)
12. Go to the New Products worksheet, which includes a table named Products that lists details about new products ActiveBee recently developed. Clear the filter from the table to display all the data.
13. The Products table is currently sorted by release date, but Dalia prefers to list the product names in alphabetic order. Sort the Products table in ascending order by Product Name.
14. Dalia asks you to format the Products table to match the SalesReps table and display the full text of the table headers. Apply Blue Gray, Table Style Medium 2 to the Products table. In cell E5, wrap the text to display the complete data.
15. The Products table is missing one new product that ActiveBee introduced. Add a record to the end of the table containing the data shown in Table 1.
Table 1: New Record for the Products Table
Product ID | Product Name | Product Type | Release Date | Unit Cost | Unit Price | $ Profit | % Profit PS-5500 | Wrist Tracker | Personal | 1/17/2029 | $86 | $100 | [calculated] | [calculated]
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.
Final Figure 1: Sales Bonuses Worksheet
Final Figure 2: New Products Worksheet