In this exercise, we'll work with spreadsheet formulas, functions and addresses, using Microsoft Excel.

We want to create a spreadsheet that will calculate and show our net pay per pay period. The idea is that we want to be able to see very quickly how much our take home pay would change if our salary were increased, if we updated our retirement savings, or if management moved from a bi-weekly to a twice monthly pay system.

The general idea behind the computation is shown in the flow chart below:
gross to net pay
Tax-exempt deductions include health insurance premiums, flexible spending accounts and, in some cases, retirement savings. Non-exempt deductions are generally taxes (federal, state, city, Medicare and Social Security). Non-exempt deductions are based on the taxable income.

The structure of the spreadsheet we will create to do our computations can be seen below:
take Home Pay

To begin, download this starting workbook to your H: drive. Note that a workbook is a collection of spreadsheets and "xlsx" is the default extension for a file that should be opened using Excel. Go ahead and open the file using Excel and familiarize yourself with its contents.

HINT: I recommend that you right-click on the image of the spreadsheet above, and save and display the image on your desktop. That way as you go through the lab below, you do not have to keep scrolling back up to see what the values are or what the formatting looks like.

Formatting Text

At present, everything in the spreadsheet has exactly the same formatting, making it hard to see which cells are related to each other, and which are not. Using the picture above as a reference, do the following:

Entering Data

We now have a clearly labeled sheet, with no real data in it. The next step is to add some actual numbers. Ideally, the only numbers that we would type would appear in the assumptions area of the sheet. Our sheet is not quite that nicely laid out since we will also have to type in the numbers for the annual tax exempt deductions and the withholding rates.

Do this ...

Start by adding values for the numbers (as opposed to rates) in your sheet:

Entering Rates

Before entering the rates, we need to think carefully about how Excel will use those values. Since we intend to use the rates for computations, we must be sure to enter their values in such a way that our calculations will be correct. For example, if we intend a Retirement Savings Rate of 4%, we should not type the number 4 and a percent sign in cell B5, because that is not a number that Excel can use in a computation. Instead, we use the value 0.04, and then format the cell to display as a percentage. To be precise: the value in a cell is disjoint from how that cell is displayed. The formatting determines how the cell is displayed, but does not effect the value stored within the cell.

Do this ...
  1. Enter values for Retirement Savings (in cell B5), and each of the withholding rates (in cells I4 through I8). Use the withholding rates shown in the example above.
  2. Format each of the cells in Step 1 as percentages with 2 decimal places:
    1. Highlight the cell or range of cells to be formatted
    2. Click the percent sign from Number menu
    3. Add decimal places

Entering Formulas

Every remaining cell that we will use will not have a value typed into it. Instead, we will compute the value for each cell using a formula.

Let me repeat that:

Every remaining cell that we will use will not have a value typed into it. Instead, we will compute the value for each cell using a formula.

Formulas in Excel have a very specific format: they always begin with an equal sign (=). Note: There must not be any spaces before the equal sign in order for Excel to do the specified computation.

The first formula we will write will be to compute the gross pay per pay period.

Do this ...

The gross pay per pay period can be computed by dividing the gross yearly salary by the number of pay periods. You can enter this in Excel by typing:
into the cell B9.

(Notice: No numbers are being used: only cell references.)

The second formula we will write will be to compute the annual retirement savings.

Do this ...

The annual retirement savings can be computed by multiplying the Gross Yearly Salary by the Retirement Savings Rate. You can enter this in Excel by typing:
into the cell E6.

Before we can compute any of the Non-Exempt Deductions we first need to determine the taxable income. In cell E7, we want to compute the total annual tax-exempt deductions. We could write a formula that types in each of the values in the column above, but then if we ever needed to change one of those, we would also somehow have to remember to change the formula in E7. Instead, we can use the SUM function and a cell range to add together whichever values happen to be in the column above.

Do this ...
  1. In cell E7, write a formula that uses the SUM function to compute the total of the values in cells E4 through E6 (you can specify a range of cells by giving the starting cell, a colon, and the ending cell).
  2. In cell E9, write a formula that subtracts the result from Step 1 from the Gross Yearly Salary (be sure to use cell references, and not actual numbers in your formula).
  3. Write formulas in cells F4 through F7 to compute the tax exempt deductions per pay period.
  4. Write a formula in cell F9 to compute the total taxable income per pay period.

Now that we have taxable income, we can write formulas for the non-exempt deductions (i.e., the withholdings). In each case, the withholding can be computed by multiplying the taxable income per pay period by the withholding rate.

Do this ...

Write formulas for cells J4 through J9 to compute the withholdings per pay period and then total withholding. As above, be sure to only use cell references in your formulas as opposed to actual values.

Right now, the federal withholding rate is set at 14%. However, usually, this rate changes depending on the amount of taxable income. We can model these tax brackets in our spreadsheet by using an IF function.

In terms of formulas, all that remains is to compute values for the empty cells in the results section of our spreadsheet.

Do this ...

Enter appropriate formulas for the Net Yearly Pay and Net Pay Per Pay Period. I recommend you compute the latter first.  Then, the Net Yearly Pay is just the Net Pay per Pay Period times the number of periods (B4).  To compute the Net Pay per Pay Period, notice that you have already computed the Taxable Income per Pay Period, and the Total Non-exempt Withholding per Pay Period.  Use these two cells in your computation.

If each of the formulas has been entered correctly, you should at this point be able to update the values in the assumptions area of your spreadsheet, and subsequently see the computed values throughout the rest of your sheet update automatically. Try it!

Formatting Currency

Throughout this spreadsheet, we have entered and computed numeric values intended to represent money. The formatting of these cells should reflect this underlying assumption.

Do this ...

Format each cell that is being used to represent a monetary value as US currency. Each cell should have a dollar sign ($) and two decimal places of accuracy.

Submitting your Work

Be sure to save your modified workbook on your H: drive, and then upload your solution to Moodle.

Grading Criteria

We will grade this assignment according to the following criteria: