New Perspectives Excel 365 | Module 2: SAM Critical Thinking Project C Sharma Cybersecurity Consulting

GETTING STARTED

1. Save the file NP_EX365_2_CT1C_FirstLastName_1.xlsx as NP_EX365_2_CT1C_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_2_CT1C_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. Yong Choe is the business manager at Sharma Cybersecurity Consulting. She asks you to format the workbook she uses to analyze monthly sales to make the data easier to read and use. In the Sales Analysis worksheet, change the theme of the workbook to the Office 2013-2022 Theme to use the standard theme for the company.

2. The headings on the Sales Analysis worksheet do not stand out from the data and could use some formatting. Merge and center the top row through column G.

3. Apply the Heading 1 cell style in the newly merged range to format it as the worksheet title.

4. Merge and center the second row in the same way as the first row.

5. Change the font color of the subtitle in the second row to match the font color of the worksheet title.

6. Change the font of the worksheet title and subtitle to Californian FB to match the font used in other company reports.

7. Next, Yong needs you to format the report date and average sales information. Change the background color of the labels and date to match the other shaded cells in the row.

8. Change the font style of the "Report date" and "Average sales this month" labels to bold to clarify they are labels and not data. Also bold the "Sales to date" label above the second table to emphasize the text.

9. Change the format of the report date information to show a two-digit month, a two-digit day, and a two-digit year to use the standard date format for company reports.

10. Yong also wants you to format the headings in the two tables. Change the font style of the table headings to italic. Also, center them each horizontally except for the Category headings in the first column of each table.

11. Change the background color of the column headings in both tables to match the background color of the Category heading.

12. Change the background color of the row headings in the first table to match the background color of the row headings in the second table.

13. Yong wants the total rows in each table to stand out. She asks you to apply the appropriate cell style to the cells containing the Total label and total values in each table.

14. Merge and center the Analysis row heading in the second table with the two cells below it, and then center the heading vertically.

15. Copy the formatting from the Analysis row heading to the Test heading and the two cells below it, and to the Reporting heading and the two cells below it.

16. Rotate the row headings in the second table so that the text reads from bottom to top.

17. Yong also wants you to format the sales numbers so they are easier to decipher. Change the number format for the Current month, Previous month, and Net change data in the first table to a format that shows the values as dollar amounts with the dollar signs aligned on the left and zero decimal places. Apply the same format to the monthly and total values in the second table. [Hint: After completing this step, reperform step 13 on the cells containing the Total label and total values in each table.]

18. Change the number format for the % change data to show percentages with one decimal place.

19. Yong asks you to identify the services that generated the least amount of revenue in the last four months. Use conditional formatting to highlight the monthly service totals below $40,000 with dark red text on a light red background.

20. She also needs to calculate the average sales for the month. In the last shaded cell after the appropriate label, create a formula using a function to average the Current month values.

21. Indent the "Average sales this month" label slightly to separate the label from the date.

22. Find and replace all instances of the word "Test" with Testing to correct the name of the category.

23. Finally, Yong needs to format the workbook for printing. Insert a page break to start a new page at row 12.

24. Set the margins to Narrow.

25. Set rows 1 and 2 as print titles.

26. Create a custom footer for the worksheet. In the left footer section, display the current page number using a Header and Footer element. In the center footer section, display the Sheet Name using a Header and Footer element.

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 Analysis Worksheet