Skip to content

Practice Exam 1 - Project 4

Music Store

You are the manager of a Music Store. The store sells instruments, parts, and music lessons. You pay a 3% bonus commission to the employees based on their total sales at the end of each year. You are updating the Excel 2016 workbook used to summarize the sales.

Task 1

On the Sales Commissions worksheet, configure theTarget Sales column so the data still exists, but is not visible.

Hint

  1. On the Sales Commissions worksheet, click column B to select the entire column.

  2. Right-click and select Hide.

Task 2

Modify the scaling of the Sales Commissions worksheet so it fits on a single page when printed.

Hint

  1. Click the File tab.

  2. In the category column, click Print.

  3. At the botton of the Settings section, click the down-arrow to change No Scaling to Fit Sheet on One Page.

  4. Click the circled arrow located in the upper-left of the File tab to return to the Sales Commissions worksheet.

Task 3

Remove the cell containing the data Projected Sales in the Sales Commissions worksheet.

Hint

  1. Select cell A13.

  2. Right-click and select Delete...

  3. On the Delete pop-up window, accept the default selection and click OK.

Task 4

In the Sales Commissions worksheet, use an automatic formatting method to apply Orange Gradient Fill Data Bars to the Total Sales column that changes the cell formatting whenever the cell values change.

Hint

  1. Select cell range F4:F10 on the Sales Commissions worksheet.

  2. On the Home tab, in the Styles group, click Conditional Formatting to open the menu.

  3. Select Data Bars and beneath the Gradient Fill section, click Orange Data Bar.

Task 5

In the Sales Commissions worksheet, insert a formula in cell G4 that references the data in the Total Sales column and the Commission Rate in cell J3 to calculate the values in the Commission column. Copy the formula into all the cells in the Commission column.

Hint

  1. Select cell G4.

  2. In the Formula Bar, type the following: =F4*J$3 (Hint: The result should be $ 4,599)

  3. In the lower-right corner of the G4 cell, click the small square (a.k.a, the Fill Handle) and drag down the Commission column to copy the formula into cells G5 through G10.

(Hint: If the copied formula fails to display a value, check your absolute referencing to cell J3. You must include the $ symbol in front of the row reference to prevent relative referencing which changes the row value.)