formulas and Functions

Assignment Overview
Using the MS Excel workbook that you created for last week’s Apply assignment, you will incorporate basic formulas and functions to calculate payroll for your 10 employees.
Note: In order for the formulas and functions to calculate properly, the cells for each day may only contain the total number of hours worked (e.g. 8), rather than the scheduled shift (e.g. 8am-4pm). Also be sure to remove any labels in the cells, such as the word “hours”. The days off may be left blank or contain a 0.
To receive full credit, your workbook should adhere to the guidelines below.

Add a column to the right of your existing data, titled “Hours Worked”.

Using the AutoSum Sum function, total the hours that each employee worked the previous week.

Add another column to the right, titled “Hourly Wage”. Create an hourly wage for each employee, between $5-$50.

No more than four employees should have the same hourly wage.

Add another column to the right, titled “Gross Pay”. Using a formula, calculate the gross pay (hours worked X hourly wage) for each employee.

Change the format of the column to Accounting. To do this:

Click the letter at the top of the column to select the whole column.
In the Number section of the Home ribbon, either click the “$” or click the drop-down menu which has “General” selected and change it to “Accounting”.

Add another column to the right, titled “Taxes”. Create a formula (gross pay X tax rate), which calculates the amount of money taken out of the paycheck for taxes. For this example we are going to use a tax rate of 21%.

Change the format of the column to Accounting, so that the numbers display in dollars and cents.

Add 1 final column to the right, titled “Net Pay”. Create a formula (gross pay – taxes), which calculates the “take home” amount.

Change the format of the column to Accounting, so that the numbers display in dollars and cents.

Now that you’ve made changes to the structure and purpose of this document, merge, center, and update your title again, so that it is relevant and centered over your data. To do this:

Make sure that, on the top bar, the “Home” ribbon is selected (not Insert, Page Layout, Formulas, etc.).
Select cells A1-M1 (Click in cell A1 where your title should be already. Then, while holding the “Shift” key, click in cell M1.)
Click the “Merge & Center” button on the top toolbar (located in the “Alignment” category of the “Home” ribbon).

Using AutoSum, create functions to total the “Gross Pay”, “Taxes”, and “Net Pay” columns at the bottom of each.
Change the color of the worksheet tab. To do this:

Right-click on the tab in the bottom left-hand corner.
Click “Tab Color” and select one from the grid.

Rename the worksheet to something more appropriate.

Due Date: By end of week: Sunday, 11:59 p.m. ET