 
|
Concepts Covered:
 | Naming cells
|
| Formula using subtract and multiply
|
|
IF Function
|
|
Creating and naming a table and using it with
a Lookup Function
|
| Printing Formula
|
|
Cell Formatting
|
|
Print Header
|
Instructions:
| Rename the first sheet Calculations, the 2nd sheet Assumptions and
delete the 3rd sheet (right click on the word Sheet1 in the tab on the
bottom left, select Rename and type the new name)
|
Your calculation sheet will look like:
| |
A |
B |
|
|
| 1 |
Income |
$xx,000
|
|
This is how much $ you make.
<-Your choice of value |
| 2 |
Deductions |
xx,000 |
|
This is stuff like charity,
home mortgage interest and other stuff the gov't doesn't feel you should
be taxed for
<-Your choice of value |
| 3 |
Net
Income |
xx,000 |
|
The difference ...calculate it |
| 4 |
Exemptions |
x,500 |
|
$500 per
Exemption...calculate it using the values on
the Assumptions sheet |
| 5 |
Taxable
Income |
$x,500
|
|
...calculate it |
| 6 |
Tax Rate |
|
x% |
...calculate it using the table on the Assumptions sheet |
| 7 |
Taxes |
$ x,xxx
|
|
Per table...calculate it |
| On the
calculation sheet type the labels in
column
A
(note Tax rate is right aligned)
|
| Use the
names in column A to
name column
B even though
there are no values yet in the cells of column B
|
| Format properly
|
| On the
Assumption sheet enter the labels in column
A and values in column B
| |
A |
B |
| 1 |
Marital Status |
Single |
| 2 |
Number of Exemptions |
1 |
| 3 |
Value per
Exemption |
$500 |
|
|
Enter the values above initially for
Marital status, Number of Exemptions and Value of exemption in column
B. Once the sheet is totally completed:
 |
change Single to Married and make
sure your Calculation sheet changes correctly
|
|
increase the Number of Exemptions
and make sure your Calculation sheet changes correctly.
|
|
|
Name the column
B cells using the names in column A
|
|
Still on the Assumptions sheet, in cells
A5 through C10: Create a 3 column table to do the tax rate
lookup as follows
Note: The table is not in the correct form for Excel since tables must have only one value per cell.
The
exact amount are on the
IRS web sheet
but use those below for this problem.
| |
Single |
Married |
| 0-$8,000 |
0 |
0 |
|
8,000-15,000 |
15% |
11% |
|
15,001-20,000 |
28% |
22% |
|
20,001-25,000 |
31% |
28% |
|
25,001 & up |
39% |
33% |
|
|
Name the table "taxrates"
(Remember only the numbers, not the words, are part of the table)
|
|
Since the table lookup must
use column 2 of the table when single and column 3 when married,
so:
|
create an
IF function
formula in cell B12 that compares the marital
status cell to the word "Single" on top of the tax table.
|
|
put cell B12's name, "MaritalStatusColumn", in the cell A12.
|
|
then name B12 using the name in
A12.
|
|
|
Back to the Calculations
sheet, make up and enter the values for Income (cell B1)
and Deductions (cell B2)
|
|
Calculate the remaining cells (cells
B3, B3, B4, B5, B6, B7)
 |
Net
Income is "Income" minus Deducations (note names appear in formula)
|
|
Exemptions is umber of
Execmtions time value of Exemption (both on Assumtions sheet)
|
|
Taxable
Income is Net Income mimus Exemptions |
|
Tax
Rate- looked up using "Taxable Income" and
"MaritalStatusColumn" on Assumtions sheet
|
|
Taxes -
taxible income times tax rate |
|
|
Make an appropriate Print Header and Footer with a
sheet title, your name, the date, file name, sheet number, etc. on the
calculation sheet
|
|
Email the Excel Workbook file (both sheets are saved
together) as an attachment or
Print the Calculation sheet and then Print the Formulae. Print the
Assumption sheet formulae only for your portfolio.
|
|
