Lewellen Group

GENERATE REPORTS FROM MULTIPLE WORKBOOKS

 GETTING STARTED

• Open the file NP_EX19_EOM5-1_FirstLastNamexlsx, available for download from the SAM website.
• Save the file as NP_EX19_EOM5-1_FirstLastNamexlsxby changing the “1” to a “2”.
  • If you do not see the .xlsxfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • Support_EX19_EOM5-1_2021.xlsx
• With the file NP_EX19_EOM5-1_FirstLastNamexlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
• PROJECT STEPS

1. Lonnie Holtzman is a financial analyst for the Lewellen Group, a management consulting firm headquartered in Providence, Rhode Island. The firm also has offices in Charlotte, North Carolina; Milwaukee, Wisconsin; and Denver, Colorado. Lonnie is using an Excel workbook with multiple worksheets to create a profit and loss statement for the firm. He asks for your help in completing the statement.
   Go to the Dashboard Lonnie first wants to complete the list of links in the range H7:H10. In cell H7, create a hyperlink to the www.lewellen.example.netwebsite.
2. In cell H8, create a hyperlink to the info@lewellen.example.netemail address. Use Contact a Lewellen consultant as the ScreenTip text.
3. In cell H9, create a hyperlink to cell A1 in the Categories
4. In cell H10, create a hyperlink to the workbook Support_EX19_EOM5-1_2021.xlsx, which contains Lewellen profit and loss data from 2021.
5. Lonnie wants to compare 2021 profit and loss totals to those for 2022 and needs to add the 2021 data to the Dashboard Open the file Support_EX19_EOM5-1_2021.xlsx. Copy the values in the range C6:C19. In cell C6 of the Dashboardworksheet in the original workbook, use the Paste Link command to create external references to the values in the Support_EX19_EOM5-1_2021.xlsx workbook. Delete the unnecessary values in cells C8, C12, and C16, and close the Support_EX19_EOM5-1_2021.xlsx workbook.
6. Lonnie needs a quick way to refer to the budgeted payroll and operating expenses. Use the name PayrollExpto define the range D9:D11. Use the name OpExp to define the range D13:D15.
7. In cell D18, enter a formula using the SUM function to total the expense values using the defined range names PayrollExp and OpExp.
8. Lonnie prefers that accountants and others contact Lewellen through the link in cell H8 rather than one that connects to his email address. In cell C21, remove the hyperlink, leaving the unlinked text “Lonnie Holtzman” in the cell.
9. Lonnie wants to combine the profit and loss data for 2022 from each of the three Lewellen offices. In cell E6, enter a formula using the SUM function, 3-D references, and grouped worksheets that totals the values from cell C6 in the Charlotte:Milwaukee worksheets. Copy the formula from cell E6 to cell E7, the range E9:E11, the range E13:E15, and the range E17:E19. In the range E17:E19, copy the formula and number formatting only.
10. Go to the Charlotte Lonnie needs to calculate the percent of sales for each expense item. Assign the defined name Revenueto cell C6.
11. In cell D7, enter a formula without using a function that divides the Cost of sales value in cell C7 by the revenue amount, using the defined name Revenue for the Charlotte Copy the formula from cell D7 to the range D9:D11, the range D13:D15, and the range D17:D19. In the range D17:D19, copy the formula and number formatting only.
12. Lonnie wants to apply consistent formatting to the worksheets he collected from the three Lewellen offices. Group the Charlotte, Denver, and Milwaukeeworksheets, and then apply the Heading 1 cell style to cell B2 to use the same formatting as in the Dashboard Ungroup the worksheets, and then verify that the formatting is applied to all three worksheets.

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 SAM website to submit your completed project.

• Final Figure 1: Dashboard Worksheet

 

 

 

• Final Figure 2: Charlotte Worksheet

 

 

• Final Figure 3: Denver Worksheet

 

 

• Final Figure 4: Milwaukee Worksheet

 

• Final Figure 5: Categories Worksheet

 

 

 

New Perspectives Excel 2019 | Module 6: End of Module Project 1

 

Veritate Online

MANAGE YOUR DATA WITH DATA TOOLS

 

 GETTING STARTED

• Open the file NP_EX19_EOM6-1_FirstLastNamexlsx, available for download from the SAM website.
• Save the file as NP_EX19_EOM6-1_FirstLastNamexlsxby changing the “1” to a “2”.
  • If you do not see the .xlsxfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file NP_EX19_EOM6-1_FirstLastNamexlsxstill open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
• PROJECT STEPS

13. Padma Laghari is a content manager for Veritate Online, a website that sells electronics, home furnishings, and other goods throughout the United States. Padma oversees the contractors the company hires to research, write, and edit content for the website, including product descriptions and answers to customer questions. She is organizing information about the contractors in an Excel workbook, and asks for your help in updating and analyzing the data.
    Switch to the Contractors Unfreeze the top row of the worksheet.
14. Padma wants to sort and filter the contractor data. Format the range A4:G52 as a table with headers using Blue-Gray, Table Style Medium 9. Use Contractorsas the name of the table.
15. Apply the First Column table style option to separate the ID values from the rest of the data. Resize column A to its best fit.
16. A new contractor started last week, and Padma wants to include her data with the other contractors. Insert the record shown in Table 1 to the end of the Contractors table at row 53.

• Table 1: New Record for the Contractors Table

 

ID	First	Last	Specialty	Start Year	Pay Category	Eval Rating
c5496	Mai	Vang	Garden and home	2022	1	3

 

17. Padma wants to quickly identify the contractors who have been working with Veritate the longest. Sort the data in the Contractors table first in ascending order by the Start Year field and then in ascending order by the Last field.
18. Padma knows the Contractors table contains a duplicate record. In the range A5:A53, create a conditional formatting Highlight Cells Rule that identifies duplicate cell values by formatting them with Light Red Fill with Dark Red Text. Delete the second instance of the duplicate record, the one with a Start Year of 2022.
19. The conditional formatting rule in column G highlights cells that contain the value 5, the highest evaluation rating. Padma wants to change the highlighting to use colors that are associated with positive values. Edit the conditional formatting Highlight Cells Rule for the range G5:G52 to highlight the cells containing values equal to 5 with a font color of Teal, Accent 2, Darker 50% (6th column, 6th row in the Theme Colors palette) and a fill color of Teal, Accent 2, Lighter 60% (6th column, 3rd row in the Theme Colors palette).
20. Padma wants to list the number of years each contractor has been working for Veritate. Insert a column to the right of the Eval Rating column. Use Yearsas the column heading. In cell H5, insert a formula without a function that uses a structured reference to subtract the Start Year from 2022, the current year. If Excel does not automatically copy the formula to the other cells in column H, fill the range H6:H52 with the formula in cell H5. Clear the conditional formatting rule from the range H5:H52 if Excel applies it.
21. Padma asks you to make it easy to filter the table based on the contractor’s specialty and starting year. Insert two slicers, one based on the Specialty field and the other based on the Start Year field.
22. Resize both slicers to a height of 2.2″ and a width of 1.5″. Move the Specialty slicer so its upper-left corner is in cell I4 and its lower-right corner is in cell I14. Move the Start Year slicer so its upper-left corner is in cell J4 and its lower-right corner is in cell J14.
23. Use the slicers to filter the Contractors table to show contractors who started in 2021 or 2022 with specialties of Electronics and Movies and music.
24. Since Padma is using slicers to filter the data, hide the filter buttons in the Contractors table.

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 SAM website to submit your completed project.

• Final Figure 1: Contractors Worksheet

New Perspectives Excel 2019 | Module 6: End of Module Project 1

 

Veritate Online

MANAGE YOUR DATA WITH DATA TOOLS

 

 GETTING STARTED

• Open the file NP_EX19_EOM6-1_FirstLastNamexlsx, available for download from the SAM website.
• Save the file as NP_EX19_EOM6-1_FirstLastNamexlsxby changing the “1” to a “2”.
  • If you do not see the .xlsxfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file NP_EX19_EOM6-1_FirstLastNamexlsxstill open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
• PROJECT STEPS

25. Padma Laghari is a content manager for Veritate Online, a website that sells electronics, home furnishings, and other goods throughout the United States. Padma oversees the contractors the company hires to research, write, and edit content for the website, including product descriptions and answers to customer questions. She is organizing information about the contractors in an Excel workbook, and asks for your help in updating and analyzing the data.
    Switch to the Contractors Unfreeze the top row of the worksheet.
26. Padma wants to sort and filter the contractor data. Format the range A4:G52 as a table with headers using Blue-Gray, Table Style Medium 9. Use Contractorsas the name of the table.
27. Apply the First Column table style option to separate the ID values from the rest of the data. Resize column A to its best fit.
28. A new contractor started last week, and Padma wants to include her data with the other contractors. Insert the record shown in Table 1 to the end of the Contractors table at row 53.

• Table 1: New Record for the Contractors Table

 

ID	First	Last	Specialty	Start Year	Pay Category	Eval Rating
c5496	Mai	Vang	Garden and home	2022	1	3

 

29. Padma wants to quickly identify the contractors who have been working with Veritate the longest. Sort the data in the Contractors table first in ascending order by the Start Year field and then in ascending order by the Last field.
30. Padma knows the Contractors table contains a duplicate record. In the range A5:A53, create a conditional formatting Highlight Cells Rule that identifies duplicate cell values by formatting them with Light Red Fill with Dark Red Text. Delete the second instance of the duplicate record, the one with a Start Year of 2022.
31. The conditional formatting rule in column G highlights cells that contain the value 5, the highest evaluation rating. Padma wants to change the highlighting to use colors that are associated with positive values. Edit the conditional formatting Highlight Cells Rule for the range G5:G52 to highlight the cells containing values equal to 5 with a font color of Teal, Accent 2, Darker 50% (6th column, 6th row in the Theme Colors palette) and a fill color of Teal, Accent 2, Lighter 60% (6th column, 3rd row in the Theme Colors palette).
32. Padma wants to list the number of years each contractor has been working for Veritate. Insert a column to the right of the Eval Rating column. Use Yearsas the column heading. In cell H5, insert a formula without a function that uses a structured reference to subtract the Start Year from 2022, the current year. If Excel does not automatically copy the formula to the other cells in column H, fill the range H6:H52 with the formula in cell H5. Clear the conditional formatting rule from the range H5:H52 if Excel applies it.
33. Padma asks you to make it easy to filter the table based on the contractor’s specialty and starting year. Insert two slicers, one based on the Specialty field and the other based on the Start Year field.
34. Resize both slicers to a height of 2.2″ and a width of 1.5″. Move the Specialty slicer so its upper-left corner is in cell I4 and its lower-right corner is in cell I14. Move the Start Year slicer so its upper-left corner is in cell J4 and its lower-right corner is in cell J14.
35. Use the slicers to filter the Contractors table to show contractors who started in 2021 or 2022 with specialties of Electronics and Movies and music.
36. Since Padma is using slicers to filter the data, hide the filter buttons in the Contractors table.

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 SAM website to submit your completed project.

• Final Figure 1: Contractors Worksheet

 

 

Pierce Software

SUMMARIZE DATA WITH PIVOTTABLES

 

 GETTING STARTED

• Open the file NP_EX19_EOM7-1_FirstLastNamexlsx, available for download from the SAM website.
• Save the file as NP_EX19_EOM7-1_FirstLastNamexlsxby changing the “1” to a “2”.
  • If you do not see the .xlsxfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file NP_EX19_EOM7-1_FirstLastNamexlsxstill open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
• PROJECT STEPS

37. Tiana Morales is the director of the customer call center for Pierce Software. For the past three months, she has been keeping track of the support calls the center receives, and asks for your help in analyzing the data.
    Go to the Customer Call Logworksheet, which contains the Calls table where Tiana has been entering support call data. In the Call Type column, she needs to list the type of call corresponding to the code in the Call Code column. In cell E5, enter a formula using the HLOOKUP function to look up the call type according to the call code. Use a structured reference to look up the value in the Call Code column of the Calls table. Retrieve the value in row 2 of the CallTypes table (range A3:F4 in the Survey Questions worksheet) using a structured reference to the entire CallTypes table. Because each call type covers a range of values, find an approximate match. Fill the formula into the range E6:E143, if necessary.
38. Customers can rate their service representative by completing a four-question survey at the end of each call. Tiana entered the scores in columns Question 1, Question 2, Question 3, and Question 4 of the Calls table. She wants to calculate the average rating for each call in the Average column. In cell J5, enter a formula using the AVERAGE function and structured references to average the values in the Question 1 to Question 4 columns of the Calls table. Fill the formula into the range J6:J143, if necessary.
39. In the range L4:N13, Tiana set up an area to summarize some data about the service representatives handling the customer support calls. First, she wants to count the number of calls each service representative handled. In cell M5, enter a formula using the COUNTIF function and a structured reference to the Service Rep column in the Calls table to count the number of calls Barry (cell L5) handled. Fill the formula into the range M6:M13 without formatting.
40. Tiana also wants to average the combined scores for each employee. In cell N5, enter a formula using the AVERAGEIF function and structured references to the Service Rep and Average columns in the Calls table to average the scores Barry (cell L5) received for all the calls he handled. Fill the formula into the range N6:N13 without formatting.
41. Go to the Call Type by Rep Tiana wants to compare the call data by type for each service rep. In cell A3, insert a PivotTable based on the Calls table. Use RepCallsas the name of the PivotTable. Display the service rep names as column headings, and the call types as both row headings and values.
42. Apply Light Yellow, Pivot Style Medium 12 to the PivotTable to coordinate with the table on the Customer Call Log Center the data in the range B4:K10 to make it easier to read.
43. Tiana notices that Service Rep Bruno has no calls in the How to category, which she knows is incorrect. Return to the Customer Call Log worksheet, and then change the Call Code in cell D10 to 12, which assigns a call to Bruno in the How to category. Go to the Call Type by Repworksheet and refresh the data in the PivotTable.
44. Go to the Scores by Date In cell A3, insert another PivotTable based on the Calls table. Use Scoresas the name of the PivotTable. Display the Date values as column headings, which automatically groups the data by month. Display the Service Rep names as row headings and the Average score data as values.
45. Change the summary function to Average for the data in the Values area and apply the Number number format with 2decimal places to those values.
46. In cell A3, use the text Average Scoresto identify the values. In cell B3, use the text Months to identify the column headings. In cell A5, use the text Service Reps to identify the row headings. Apply Light Yellow, Pivot Style Medium 12 to the PivotTable to match the other PivotTable.
47. Tiana wants to focus on scores for calls involving technical problems in February. Create a slicer for the Scores PivotTable based on the Call Type field. Resize the slicer to a height of 5″. Move the slicer so its upper-left corner is in cell F3 and its lower-right corner is in cell G14. Use the slicer to filter the Scores PivotTable by the Tech problem call type.
48. Create a timeline slicer based on the Date field in the Scores PivotTable. Resize the timeline slicer to a width of 7″. Move the slicer so that its upper-left corner appears within cell A16 and its lower-right corner appears in cell G23. Use the timeline slicer to show data for February only.
49. Resize columns B:C to 12.00 and center the data in the range B3:C15.

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 SAM website to submit your completed project.

• Final Figure 1: Customer Call Log Worksheet

 

 

 

• Final Figure 2: Call Type by Rep Worksheet

 

 

 

• Final Figure 3: Scores by Date Worksheet

 

 

 

• Final Figure 4: Survey Questions Worksheet