question archive 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_FirstLastName_1
Subject:MS ExcelPrice:19.87 Bought3
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_FirstLastName_1.xlsx, available for download from the SAM website.
Save the file as NP_EX16_2a_FirstLastName_2.xlsx 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_FirstLastName_2.xlsx 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.
Apply a theme to the workbook.
2. Switch to the Hourly Compensation worksheet. Apply the Title cell style to the merged range A1:G1, and then center the text in that merged range.
Apply a cell style to a merged range.
Align cell content.
3. Merge and center the range A2:G2, and then change the font size of the merged range to 14 pt.
Merge and center a range of cells.
Change the font size of range of cells.
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 (6th column, 1st row of the Theme Colors palette).
Change the font style of cell contents.
Change the font of cell contents.
Change the font color of cell contents.
5. Format cell B3 using the Short Date (e.g., 3/13/2018) number format.
Apply a number format to a cell.
6. Use the Format Painter to copy the format from cell A6 to the range B6:G6.
Copy and paste formatting to a range.
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.
Create a formula.
Copy a formula into a range.
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.
Create a formula.
Copy a formula into a range.
9. Change the fill color of cells E8, E10, G8, and G10 to Blue, Accent 1, Lighter 80% (5th column, 2nd row of the Theme Colors palette).
Change the fill color of a range of cells.
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.
Apply a conditional formatting rule to a range.
Format a conditional formatting rule.
11. In cell A11, increase the indent of the cell contents twice.
Indent cell contents.
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.
Create a formula using a function.
Copy a formula into a range.
13. In the range E7:E11, apply the Currency number format with zero decimal places and $ as the symbol.
Apply a number format to a range of cells.
14. In the range G7:G11, apply the Percentage number format with one decimal place.
Apply a number format to a range of cells.
15. For the merged range A20:A30, rotate the cell contents to 0 degrees.
Change the orientation of cell content.
16. Find and replace all instances of the text “WeeklyHour” with Weekly Hours. (Hint: You should find and replace two instances.)
Find and replace cell values.
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.
Change the orientation of a worksheet.
Set the margins of a worksheet.
18. She wants the table and the chart to appear on separate pages when printed. Select cell A13, and then insert a page break.
Insert a page break in the worksheet.
19. Set rows 1-3 as print titles. (Hint: Rows 1-3 should repeat at the top of each printed page of the worksheet.)
Add print titles to a worksheet.
20. Create a custom footer for the worksheet. In the leftfooter 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.
Add a footer to a workbook.
Purchased 3 times