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:

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:

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:

• Format the section headings with a background color and possibly new font as well (you don't have to match the example exactly, but don't let the background be white).
• Center the headings over the columns to which they refer. To accomplish this, you will need to merge cells:
  1. Highlight all of the cells above the relevant columns
  2. From the Alignment menu choose "Merge and Center"
• Format the individual column headings to be bold and italicized
• Format the "Total Tax Exemption Deductions" and "Total Non-exempt Deductions" cells to be italicized and have a top border (adding a border can be accomplished using the Font menu item next to Underscore).

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.

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.

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.

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

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.

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.

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.

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.

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

We will grade this assignment according to the following criteria:

• 35% - Formatting as specified (headings and totals, currency, rates)
• 65% - Formulas (accuracy; only use cell references, not values)