Scenario
You are in charge of calculating the payroll for the employees at LakeHawk Technical Services. The
employees receive a base salary plus commission. You need to calculate new salary figures for all
the employees because all employees will receive a 6.75 percent raise. You create a pie chart to
display the results in an easy-to-read format.
Steps
1. Begin with Lastname_ExcelProject.xlsx open.
2. Change the width of column A to 22.00. Change the width of column B to 13.00. Change
the width of columns C through G to 16.00.
3. Type the title Commission in cell E2.
4. In cell E3, calculate the commission using a formula that multiplies projected sales by the
commission rate. Hint: Use an absolute reference for cell B18. Copy the formula in cell E3
to cells E4:E12.
5. Type the title Salary in cell F2.
6. In cell F3, calculate the salary by adding the base salary and the commission. Copy the
formula in cell F3 to cells F4:F12.
7. Type the title New Salary in cell G2.
8. Employees will all receive a 6.75 percent raise on their base salary. In cell G3, calculate the
new salary using the formula Base Salary + (Base Salary * Raise) + Commission. Hint: Use
an absolute cell reference in your formula. Copy the formula in cell G3 to cells G4:G12.
9. Apply the Currency format with no decimal places to the values in columns C through G.
10. Set the height of rows 13 through 16 to 22.50.
11. Right align the Total, Average, Lowest, and Highest labels and apply bold formatting. Move
these labels from cells A13:A16 to cells B13:B16.
12. Use the appropriate functions to calculate the Total, Average, Lowest, and Highest values
for columns C through G.
13. Merge and center cells A1:G1 and set the row height of row 1 to 48.00.
14. Apply the Title cell style to cell A1.
15. Set the row height of row 2 to 20.25.
16. Apply the Integral theme.
17. Center the column headers in row 2, apply the Accent5 cell style, and apply bold
formatting.
18. Add a thick outside border around the column headers in row 2.
19. Apply the Accent2 cell style to cells C13 though G13, Accent3 cell style to cells C14 through
G14, Accent4 cell style to cells C15 through G15, and Accent5 cell style to cells C16 through
G16.
20. Type your name in cell A20.
21. Change the page orientation to landscape.
22. Create a 3-D pie chart of the new salaries for all employees. Hint: Use the Ctrl key to
highlight A2:A12 and G2:G12 before creating the chart.
23. Move the location of the chart to a new worksheet and rename the sheet, typing Salary
Chart as the new name.
24. Use the Chart Elements button to add data labels that display the category name at the
Inside End position along with the already displayed salary value.
25. Change the chart style to Style 8.
26. Modify the chart title, typing EMPLOYEE 2022 SALARIES as the new chart title.
27. Rename Sheet1, typing Data as the new worksheet name.
28. Save the workbook file by replacing the word Lastname with your actual last name and
