Docstoc

Where Can I Find the 2005 Tax Tables - Excel

Document Sample
Where Can I Find the 2005 Tax Tables - Excel Powered By Docstoc
					                                Three basic LOOKUP functions and INDEX and CHOOSE and MATCH:
VLOOKUP
HLOOKUP




LOOKUP




INDEX
CHOOSE
MATCH

  VLOOKUP: Looks at a cell, then takes that value and goes over to a look up table where it looks in the first column, and wh
  finds the corresponding value in the first columns, it moves to the left in that row and retrieves a value from one of the ta
                                                columns and brings it back to the cell.
Arguments

lookup_value


table_array

col_index_num



range_lookup

      steps
                1

                2

                3

                4
                            Three basic LOOKUP functions and INDEX and CHOOSE and MATCH:
             Most common because most lookup tables like tax tables and price tables are orientated vertically
             Most tables are vertical, not horizontal, so using HLOOKUP is rare.
             Although LOOKUP was one of the first functions in a spreadsheet, the VLOOKUP can do almost everything that
             the LOOKUP does and more. The one thing that the LOOKUP can do that the VLOOKUP (or HLOOKUP) can't do is
             retrieve a value to the left or above a lookup_value column or row; it can also have a column and a row area.
             LOOKUP function must have the lookup_vector sorted in ascending order. LOOKUP looks up the greatest value
             that does not exceed a specified value anywhere in a table or range .
             Good when you have more than one table to look values up in. (non-contiguous ranges must be in parenthesis
             and stored all on the same worksheet). You can also have a table with intersection lookup values where INDEX
             and MATCH can be used together to look up labels and return intersection of two labels. Also can be used as a
             replacement for VLOOKUP when the data you want to retrieve is to the left of the lookup column.
             CHOOSE can return a value or a range from a list of 1 to 254 arguments.
             This function tells you the ordinal position of an item in a list

 OKUP: Looks at a cell, then takes that value and goes over to a look up table where it looks in the first column, and when it
ds the corresponding value in the first columns, it moves to the left in that row and retrieves a value from one of the tables
                                           columns and brings it back to the cell.
             Descriptions
             The date you want to look up. This value can be a number, text, a logical value, or a name or cell reference that
             refers to a value.
             The lookup table. The first column must have the values to look up such as part number, part name, tax bracket
             wage figures, grade number intervals. The other columns can have data that you want to retrieve such as part
             price, tax rates, tax paid from earlier brackets.
             The number of the column containing the data you want to retrieve. The number 1 indicates that first column; 2
             indicates the second column.
             The type of lookup you want to perform: TRUE or FALSE. With TRUE (the default), the VLOOKUP function
             finds the greatest value that does not exceed the lookup_value value . When the lookup type is TRUE, the
             first column must be sorted in ascending order, otherwise the VLOOKUP might not retrieve the correct value. With
             FALSE, the VLOOKUP looks for an exact match of the lookup_value.

                                                                VLOOKUP Algorithm
             Looks for an exact match. If duplicate values it ignores the second value. If no match it goes to step 2
             If the lookup_value is smaller than the first value in the first column, #N/A error is displayed. If the value is
             greater than the first value in the first column, it goes to step 3
             The VLOOKUP function continues checking until it sees a value bigger than the lookup_value, then it uses the
             previous row and retrieves a value from that row.
             It looks to see if the next value is the last value in the table, if it is it retrieves a value from this row, otherwise it
             repeats step 3
1   Suix
2   Fred
3   Chin
4   Sheliadawn
  Example 1: Deliver value to cell. Find approximate
1      value from column 2 of lookup table.
                                       0F
                                   0.65 D
                                   0.75 C
                                   0.85 B
                                   0.95 A

  Score                                    Grade
                                    0.75

    Example 2: Deliver value to cell. Find exact value
2           from column 2 of lookup table.
  Product 1                                $      20.00
  Product 2                                $      25.00
  Product 3                                $      15.00
  Product 4                                $      15.00
  Product 5                                $      16.00

  Product                                  Price
  Product 2                                          25

     Example 3: Deliver value to cell. Find value from column 2 & 3. Use
3          COLUMN function (tells you what column you are in).
  Product                                Price          Description
  Boom01                                        $15.00 Flying Range is 10
  Boom02                                        $30.00 Flying Range is 20
  Boom03                                        $40.00 Flying Range is 50
  Boom04                                        $45.00 Flying Range is 60
  Boom05                                        $65.00 Flying Range is 70
  Boom06                                        $69.00 Flying Range is 80
  Boom07                                       $100.00 Flying Range is 85
  Boom08                                       $110.00 Flying Range is 110
  Boom09                                       $165.00 Flying Range is 160
                                                                             Alternative 1
  Product                                  Price       Description           Price
  Boom07                                           100 Flying Range is 85                100
      Example 4: Use VLOOKUP to deliver a value to a
4                       formula.                                                               Alternative 3
    Days Late                           % Late Fee                                             Price
                                     0             1%                                                      100
                                    30             2%
                                    60             3%                                          Alternative 2
                                    90             5%                                          Price
                                                                                                           100
    Days Late                            Balance      Late Charge
                                      89      $500.00             $15.00

      Example 5: Use multiple VLOOKUPs in 1 formula to deliver mutiple values to a formula. Also, Use IF function or IFERRO
5                                                                   error problem
                                                          Manufacturers Plus
                                                          Income Statement
                                        Projected Net Income For The Years Ended December 31

                                                  2003                  2004            2005             2006
    Net Revenues                          $450,000.00           $495,000.00     $544,500.00     $598,950.00
    Expenses                               500,000.00            510,000.00      520,200.00      530,604.00
    Net Income Before Income Taxes         (50,000.00)           (15,000.00)      24,300.00       68,346.00
    Tax Expense                                  0.00                  0.00        3,645.00       12,086.50
    Net Income                            ($50,000.00)          ($15,000.00)     $20,655.00      $56,259.50




                                                      0                     0           3645          12086.5
     Example 6: Create lookup_table in formula itself
   "hard code table into formula": 1) enclose the table
       in curly brackets { }; 2) column elements are
       separated by commas ,; 3) row elements are
    separated by semicolons ;. Use RANDBETWEEN to
  generate random integers (in 2003 you have to go to
6 Tools menu, Add-ins, then check Analysis ToolPak".)
                                         1 Suix
                                         2 Fred
                                         3 Chin
                                         4 Sheliadawn

  Find Name Randomly                    Fred

  Find Name Randomly                    Fred

  No. For Employee                      Employee
                                      1 Suix
Description
Flying Range is 85
            Description
            Flying Range is 85


            Description
            Flying Range is 85




Use IF function or IFERROR function to solve #N/A




ecember 31

                                 2007          2008
                      $658,845.00       $724,729.50
                       541,216.08        552,040.40
                       117,628.92        172,689.10
                        29,125.28         50,598.75
                       $88,503.64       $122,090.35

                        29,125.28                        Tax Tables 2004
                                                                             If Taxable Income Is:
                                                    VLOOKUP
                       29125.2788       50598.74838 Reference                   Over -         But Not Over

                                                                           0     $         0   $         50,000
                                                                     50,001           50,000             75,000
                                                                     75,001           75,000            100,000
                                                                    100,001          100,000            335,000
                                                                    335,001          335,000         10,000,000
                                                                 10,000,001       10,000,000         15,000,000
                                                                 15,000,001       15,000,000         18,333,333
                                                                 18,333,334       18,333,333


                                                        Assumptions
                                                      Increase In Sales                  10%
                                                      Increase in Expenses                2%
Tax Amount From     Tax Rate For
Previous Bracket    Remaining Amount

                                  15%
            7,500                 25%
           13,750                 34%
           22,250                 39%
          113,900                 34%
        3,400,000                 35%
        5,150,000                 38%
        6,416,667                 35%
Product 1 Product 2 Product 3 Product 4 Product 5           Product Product 3
$   20.00   $   25.00   $   15.00   $   15.00   $   16.00   Price           15

Product 1   $   20.00               Product   Price
Product 2   $   25.00               Product 3          15
Product 3   $   15.00
Product 4   $   15.00
Product 5   $   16.00
Product              Price                 Description
Boom01                            $15.00   Flying Range is 10                                1
Boom08                           $110.00   Flying Range is 110                              20
Boom09                           $165.00   Flying Range is 160                              30
Boom02                            $30.00   Flying Range is 20
Boom03                            $40.00   Flying Range is 50                              100
Boom04                            $45.00   Flying Range is 60
Boom05                            $65.00   Flying Range is 70                         Product
Boom06                            $69.00   Flying Range is 80                         Boom02
Boom07                           $100.00   Flying Range is 85

                                           Description                                Product
                                           Flying Range is 20                         Boom02

Flying Range is 10   Flying Range is 110   Flying Range is 160   Flying Range is 20
Rad
Cool
Super

Super
                                                      Allowances
                               0                 1            2        3        4        5
                   0      $6.00             $5.00        $4.00    $3.00    $2.00    $1.00
                 100      $7.00             $6.00        $5.00    $4.00    $3.00    $2.00
                 200      $8.00             $7.00        $6.00    $5.00    $4.00    $3.00
Income




                 300      $9.00             $8.00        $7.00    $6.00    $5.00    $4.00
                 400     $10.00             $9.00        $8.00    $7.00    $6.00    $5.00
                 500     $11.00            $10.00        $9.00    $8.00    $7.00    $6.00
                 600     $12.00            $11.00      $10.00     $9.00    $8.00    $7.00
                 700     $13.00            $12.00      $11.00    $10.00    $9.00    $8.00

                                 Relative Position
                                 in list
         Income        300.00000                  4
         Allowances            2                  3
    1                                                      Allowances
                                 0              1              2         3            4           5
                 0          $6.00          $5.00          $4.00     $3.00        $2.00       $1.00
               100          $7.00          $6.00          $5.00     $4.00        $3.00       $2.00
               200          $8.00          $7.00          $6.00     $5.00        $4.00       $3.00
Income




               300          $9.00          $8.00          $7.00     $6.00        $5.00       $4.00
               400         $10.00          $9.00          $8.00     $7.00        $6.00       $5.00
               500         $11.00         $10.00          $9.00     $8.00        $7.00       $6.00
               600         $12.00         $11.00         $10.00     $9.00        $8.00       $7.00
               700         $13.00         $12.00         $11.00    $10.00        $9.00       $8.00

                                   Relative
                                   Position in
                                   list                           Tax
         Income                500           6                          $9.00
         Allowances              2           3

    2                           Customer Type 10                                           Region       Shipping Method
                               Shipping Method - Standard                                 NW          Rail
          Region      Rail           Truck          Plane         Ship                    West        Truck
         NW            $     0.125    $    0.135     $    1.525    $     0.225            SW          Plane
         West          $     0.205    $    0.145     $    2.025    $     0.245            MidWest     Ship
         SW            $     0.265    $    0.165     $    2.125    $     0.225            East
         MidWest       $     0.305    $    0.185     $    2.275    $     0.225
         East          $     0.475    $    0.425     $    3.525    $     0.375                                Region
                                                                                                      West
                                Customer Type 20                                                        Shipping Method
                               Shipping Method - Preferred                                            Truck
         Region       Rail           Truck          Plane         Ship                                   Customer Type
         NW            $     0.115    $    0.125     $    1.375    $     0.205                        Customer Type 20
         West          $     0.185    $    0.135     $    1.825    $     0.215                          Shipping per Unit
         SW            $     0.245    $    0.155     $    1.915    $     0.205                                         0.135
         MidWest       $     0.275    $    0.165     $    2.055    $     0.205
         East          $     0.435    $    0.385     $    3.175    $     0.345

                                Customer Type 30
                             Shipping Method - Most Preferred
         Region       Rail           Truck          Plane         Ship
         NW            $     0.105    $    0.115     $    1.245    $     0.175
         West          $     0.175    $    0.125     $    1.645    $     0.165
         SW            $     0.215    $    0.135     $    1.725    $     0.185
         MidWest       $     0.255    $    0.155     $    1.845    $     0.185
East   $ 0.385   $ 0.345   $ 2.865   $ 0.305
Customer Type
Customer Type 10
Customer Type 20
Customer Type 30
             Example 1: INDEX and MATCH can be used together to look up labels and return
    1                                intersection of two labels
                                                  Allowances
                           0                    1           2         3          4           5
                 0    $6.00                $5.00       $4.00     $3.00      $2.00       $1.00
               100    $7.00                $6.00       $5.00     $4.00      $3.00       $2.00
               200    $8.00                $7.00       $6.00     $5.00      $4.00       $3.00
Income




               300    $9.00                $8.00       $7.00     $6.00      $5.00       $4.00
               400   $10.00                $9.00       $8.00     $7.00      $6.00       $5.00
               500   $11.00              $10.00        $9.00     $8.00      $7.00       $6.00
               600   $12.00              $11.00       $10.00     $9.00      $8.00       $7.00
               700   $13.00              $12.00       $11.00    $10.00      $9.00       $8.00

                                                     Tax
         Income          555                               $9.00
         Allowances        2

       Example 2: Use INDEX as a substitute for VLOOKUP when the value you want
    2                  to return is to the left of the lookup column.
      Product Price          Description
      Boom01       $15.00 Flying Range is 10
      Boom02       $30.00 Flying Range is 20
      Boom03       $40.00 Flying Range is 50
      Boom04       $45.00 Flying Range is 60
      Boom05       $65.00 Flying Range is 70
      Boom06       $69.00 Flying Range is 80
      Boom07      $100.00 Flying Range is 85
      Boom08      $110.00 Flying Range is 110
      Boom09      $165.00 Flying Range is 160

                               Description                         Product
                               Flying Range is 110                 Boom08           Boom01
  Customer Type
Customer Type 10
Customer Type 20
Customer Type 30

				
DOCUMENT INFO
Description: Where Can I Find the 2005 Tax Tables document sample