New Perspectives Excel 2016 | Module 2: SAM Project 1a

The Good Breeze Hotel

FormatTING workbook text and data

GETTING STARTED

• Open the file NP_EX16_2a_FirstLastNamexlsx, available for download from the SAM website.
• Save the file as NP_EX16_2a_FirstLastNamexlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file 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_EX16_2a_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. Stephanie manages the employees at the Good Breeze Hotel. For the new year, Stephanie decided to update the workbook she uses to track her team’s compensation data in order to make the information easier to understand.

Change the theme of the workbook to Office.

2. Switch to the Hourly Compensation Apply the Title cell style to the merged range A1:G1, and then center the text in that merged range.
3. Merge and center the range A2:G2, and then change the font size of the merged range to 14 pt.
4. Italicize the contents of the range A3:B3, change the font to Times New Roman, and then change the font color to Orange, Accent 2 (6^th column, 1^st row of the Theme Colors palette).
5. Format cell B3 using the Short Date (e.g., 3/13/2018) number format.
6. Use the Format Painter to copy the format from cell A6 to the range B6:G6.
7. In cell E7, create a formula that calculates the salary for Sarah, one of the servers at The Good Breeze Hotel. Her salary is calculated by multiplying the number of regular hours worked (cell B7) by her hourly rate (cell D7), and then adding to that the number of overtime hours worked (cell C7) multiplied by her hourly rate (cell D7) times 1.5. (Hint: Sarah is paid 50% more than—or 1.5 times—her regular hourly rate when she works overtime.)

To calculate the salary for all of the employees, copy the formula you created and the formatting in cell E7 into the range E8:E10.

8. Stephanie wants to see what percentage of her employees’ total working hours are spent working overtime. In cell G7, enter a formula that divides Sarah’s overtime hours (cell C7) by her total hours worked (cell F7).

Copy the formula and the formatting in cell G7 into the range G8:G10.

9. Change the fill color of cells E8, E10, G8, and G10 to Blue, Accent 1, Lighter 80% (5^th column, 2^nd row of the Theme Colors palette).
10. To quickly see which employees worked overtime, use the Highlight Cells Rules conditional formatting to format cells in the range C7:C10 with a value greater than 0 using Light Red Fill with Dark Red Text.
11. In cell A11, increase the indent of the cell contents twice.
12. Stephanie would like to know the average number of regular hours worked by her employees. In cell B11, create a formula using the AVERAGE function to find the average number of regular hours her employees work in a week (B7:B10).

Copy the formula from cell B11 into the range C11:G11 to find the average of the overtime hours, hourly rates, salaries, work hour totals, and percentage of overtime hours for the team.

13. In the range E7:E11, apply the Currency number format with zero decimal places and $ as the symbol.
14. In the range G7:G11, apply the Percentage number format with one decimal place.
15. For the merged range A20:A30, rotate the cell contents to 0 degrees.
16. Find and replace all instances of the text “Weekly Hour” with Weekly Hours. (Hint: You should find and replace two instances.)
17. Stephanie plans to print the worksheet to review with her team.

Change the page orientation to Landscape, and then set the margins to Wide.

18. She wants the table and the chart to appear on separate pages when printed. Select cell A13, and then insert a page break.
19. Set rows 1-3 as print titles. (Hint: Rows 1-3 should repeat at the top of each printed page of the worksheet.)
20. 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.

Switch back to Normal View if necessary.

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: Hourly Compensation Worksheet