New Perspectives Excel 365 | Module 6: End of Module Project 2 JKS Communications

GETTING STARTED

1. Save the file NP_EX365_EOM6-2_FirstLastName_1.xlsx as NP_EX365_EOM6-2_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 NP_EX365_EOM6-2_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. Wanda Owen runs JKS Communications, a small, business education company that employs four instructors to conduct business seminars for government and corporate clients in Chicago, Illinois. She keeps track of the workshops taught by her four instructors in an Excel workbook, and asks for your help in updating and analyzing the data. Switch to the Kendall worksheet, which contains a table named Kendall that lists the seminars that Gary Kendall teaches for JKS Communications. Unfreeze the top row of the worksheet because the worksheet is not long enough to scroll.

2. Remove the filters from the Kendall table to display all of the data. Sort the data in ascending order first by the Seminar Type field and then in ascending order by the Seminar Date field (Oldest to Newest) to make it easy to track Kendall's seminars.

3. Wanda wants to compare the average seminar revenue and average Instructor Fee for the seminars that Kendall taught. Insert a Total Row in the Kendall table, change Total in cell A11 to Average and then use the Total row to calculate the average of the values in the Seminar Revenue and Instructor Fee columns. Format the values in columns E and F with the Currency format with 0 Decimal places.

4. Switch to the Janzen worksheet, which contains listing information for Sally Janzen. Format the range A2:F10 as an Excel table with headers using the Aqua, Table Style Medium 5 table style. Enter Janzen as the name of the table.

5. Wanda needs to add a new seminar taught by Sally Janzen. Add the record shown in Table 1 to the end of the Janzen table.

Table 1: New Record for the Janzen Table

| A | B | C | D | E | F 11 | JKS-141 | Marketing | Social Media Basics | 7/30/28 | 32,000 | 4,000

6. Wanda also wants to focus on Sally Janzen's seminar revenue of $32,000 or more. Use a custom Number filter to display only seminars with revenue greater than or equal to 32,000.

7. Seminar instructor Peter Wong teaches more seminars than any other instructor. Wanda wants to summarize the Wong listings data using subtotals to show the revenue of each seminar type, especially Management seminars. Switch to the Wong worksheet and then sort the table in ascending order by the Seminar Type field. Convert the table to a normal range. Insert subtotals into the range A2:F15, with the subtotals appearing at each change in the Seminar Type column value. The subtotals should use the SUM function and include subtotals for the Seminar Revenue and Instructor Fee fields.

8. Switch to the Javez worksheet, which contains a table named Javez that lists data for instructor Antonia Javez. Apply the Aqua, Table Style Medium 5 table style to the Javez table, and then display the filter buttons so Wanda can easily filter records according to specific criteria.

9. Wanda noticed that the Javez table includes a duplicate record. Use a table tool to remove the duplicate record based on the values in the Seminar ID and Seminar Date columns.

10. The data bars in the last two columns of the table make some numbers hard to read and could coordinate better with the formatting of the Javez table. Edit the Data Bars conditional formatting rules for the range E3:F11 to use the Standard Colors palette Light Blue Gradient Fill. [Mac Hint: Only change the positive value.]

11. Switch to the All Instructors worksheet, which contains a table named Instructor listing data for all of the JKS Communications instructors. Freeze the first two rows of the worksheet to display the worksheet title and column headings when the worksheet is scrolled.

12. Wanda wants to calculate the totals for the instructor data and the difference between the Seminar Revenue and Instructor Fees. In cell J3, use the COUNTA function with a structured reference to count the values in the [Seminar ID] column of the Instructor table. In cell J4, use the SUM function with a structured reference to total the values in the [Seminar Revenue] column of the Instructor table. In cell J5, use the SUM function with a structured reference to total the values in the [Instructor Fee] column of the Instructor table.

13. Wanda added a table column to the end of the Instructor table to calculate the difference between the Seminar Revenue and Instructor Fee in order to determine the net revenue. Use Net Revenue as the column heading. In cell G3, enter a formula using structured references but no function to subtract the value in the [Instructor Fee] column (cell F3) from the value in the [Seminar Revenue] column (cell E3). Fill the range G4:G41 with the formula in cell G3 if Excel does not automatically do so.

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: All Instructors Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 2: Kendall Worksheet

Final Figure 3: Janzen Worksheet

Final Figure 4: Wong Worksheet

Final Figure 5: Javez Worksheet