Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

Calculate Sales Tax Houston Texas

VIEWS: 57 PAGES: 53

Calculate Sales Tax Houston Texas document sample

More Info
  • pg 1
									DISCLAIMER - LIMIT OF LIABILITY: WHILE BEST EFFORTS HAVE BEEN USED IN THE
CREATION OF THIS WORKSHEET, THERE ARE NO REPRESENTATIONS OR WARRANTIES
WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS
WORKSHEET AND SPECIFICALLY DISCLAIM ANY IMPLIED WARRANTIES FOR A
PARTICULAR PURPOSE. NO WARRANTY ARE CREATED OR EXTENDED. THE CREATION
AND UTILIZATION OF FORMULAS, TEMPLATES, EQUATIONS AND FORMATTING MAY
NOT BE SUITABLE FOR YOUR SITUATION. THESE WORKSHEETS, EXPLANATIONS AND
EQUATIONS ARE SHOWN AS EXAMPLES ONLY. NEITHER THE UNIVERSITY OF NORTH
TEXAS, UNT/CENTER FOR PUBLIC MANAGEMENT NOR THE AUTHOR OF THIS
WORKSHEET SHALL BE LIABLE FOR ANY LOSS, INACCURACIES OR DAMAGES INCLUDING

Sections - Using the TABs at the bottom of the sheet:
- CD Calculator
- Treasury Bill Calculator
- Discount Notes
- Treasury and Agency Notes
- Investment Pool Reconcliation
- Cash Flow Example
- Collateral Calculations
- Using IF Statements
- TEXT and DATE Examples
- Using INDEX and MATCH Sample
- Analyzing Multiple Criteria
- Calculating Weighted Average Maturity
- Using Data Validation
- Using Data Filtering
- Using Comments and Links
- Conditional Formatting
- Using Pivot Tables
CD Interest Calculator

           Amount           75,000.00
       Term (months)               12
        Interest Rate         3.000%

       Compounding       FV Excel       Interest    APY
         Annually        77,250.00      2,250.00   3.000%
       Semi-Annually     77,266.88      2,266.87   3.022%
         Quarterly       77,275.44      2,275.44   3.034%
         Monthly         77,281.20      2,281.20   3.042%
           Daily         77,283.99      2,283.99   3.045%
T-Bill Calculator
Settlement / Issue Date (I):                      4/8/2009
Maturity Date (M):                               7/15/2009
Discount Rate:                                      3.000%

Days to Mat (M - I):                                   98




Price per $100                                 99.18333333         99.18333333        <--     TBILLPRICE(settlement,maturi
Price per $1000                                991.8333333

Investment Rate (APR)/ TBILLEQ:                    3.067%                3.067%       <--     TBILLEQ(settlement,maturity,d

APY Yield:                                         3.101%
TBILLYIELD:                                                             3.0247%       <--     TBILLYIELD(settlement,maturi



Price is the price per USD 100 of par value, and days to maturity is the number of actual calendar days between the settlemen

Dollar Disc. = (Disc. Rate X FV) x (DTM/360)

Purchase Cost = FV – Dollar Disc.

Purchase Price = (Purchase Cost / FV) x 100
 TBILLPRICE(settlement,maturity,rate)



 TBILLEQ(settlement,maturity,discount)



 TBILLYIELD(settlement,maturity,price)



dar days between the settlement date and maturity date.
Discount Notes             (Treasury Bills & Agency discount Notes)

Face / Par Value:                   1,000,000.00
Issue / Settlement Date:              3/28/2007
Maturity Date:                        9/26/2007
Discount Rate:                          5.0500%

Days to Maturity (DTM):                       182

Dollar Discount =          (Disc. Rate x Future Value) x (DTM / 360)                          25,530.56
                           (.05050 x 1,000,000) x (182 / 360)

Purchased Cost =           (Future Value - Dollar Discount)                                 974,469.44
                           (1,000,000 - 25,530.56)

Purchased Price =          (Purchased Cost / Future Value) x 100                          97.44694444
                           (974,469.44 / 1,000,000) x 100

Money Market Yield =       ((Dollar Disc. / DTM) x 360) / Purchased Cost:                      5.1823%
                           ((25,530.56 / 182) x 360) / 974,469.44

Annualized Yield =         ((Dollar Disc. / DTM) x 365) / Purchased Cost:                      5.2543%
                           ((25,306.56 / 182) x 365) / 974,469.44



USING EXCEL                     =PRICEDISC          PRICEDISC(settlement,maturity,discount,redemption,basis)
                               97.44694444          Settlement is the security's settlement date. The security settlement
                                                    Maturity is the security's maturity date. The maturity date is the date
                                                    Discount is the security's discount rate.
                                                    Redemption is the security's redemption value per $100 face value.
                                                    Basis is the type of day count basis to use.


                                                       Basis     Day count basis
                                                    0 or omitted US (NASD)
                                                                  30/360
                                                              1   Actual/actual

                                                               2       Actual/360
                                                               3       Actual/365
                                                               4       European
                                                                       30/360

                                 =TBILLEQ           TBILLEQ(settlement,maturity,discount)
                                 5.2543%            Settlement is the security's settlement date. The security settlement
                                                    Maturity is the security's maturity date. The maturity date is the date
                                                         Discount is the security's discount rate.


CALCULATE THE DISCOUNT
[(Par x Disc. Rate)/360] x Days to Mat.                     43,622.78
                            Par:          1,000,000.00
                     Disc. Rate:                 4.66%
                         Settle:                1/1/09
                      Maturity:               12/4/09
                 Days to Mat. :                    337

CONVERTING THE DISCOUNT RATE TO A PRICE               95.63772222
$100 x (1 - Disc. Rate x Days to Mat.)/360
                            Par:         1,000,000.00
                     Disc. Rate:                4.66%
                         Settle:               1/1/09
                      Maturity:              12/4/09
                 Days to Mat. :                   337

COMPUTING PRINCIPAL
(Par - Discount)                                          956,377.22
nt,redemption,basis)
date. The security settlement date is the date after the issue date when the security is traded to the buyer.
 The maturity date is the date when the security expires.

n value per $100 face value.




date. The security settlement date is the date after the issue date when the security is traded to the buyer.
 The maturity date is the date when the security expires.
Notes                      (Treasury Notes & Agency Notes)

TREASURY NOTES
Face / Par Value:                     1,000,000.00
Issue / Settlement Date:                3/28/2007
Maturity Date:                          9/30/2008
Interest Rate:                             4.6250%
Price:                                        99.29            <--           99 and 29/32
Price as a Decimal:                       99.90625             <--           =DOLLARDE(99.29, 32)

Days to Maturity (DTM):                        552             <--           (Maturity - Settlement)

Yield:                                      4.689%



USING EXCEL                        =YIELD             YIELD(settlement,maturity,rate, price,redemption,frequency,bas
                                  4.6898%             Settlement is the security's settlement date. The security settleme
                                                      Maturity is the security's maturity date. The maturity date is the d
                                                      Rate is the security's annual coupon rateDiscount is the security's
                                                      Price is the security's price value per $100 face value.
                                                      Redemption is the security's redemption value per $100 face valu
                                                      Frequency is the number of coupon payments per year. (1 = annu
                                                      Basis is the type of day count basis to use.

                                                                                Day count
                                                            Basis                  basis
                                                      0 or omitted             US (NASD)
                                                                               30/360
                                                                         1     Actual/actual
                                                                         2     Actual/360
                                                                         3     Actual/365
                                                                         4     European
                                                                               30/360


FNMA NOTES
Face / Par Value:                     1,000,000.00
Issue / Settlement Date:                3/28/2007
Maturity Date:                          9/15/2008
Interest Rate:                            5.0000%
Price:                                      100.09

Days to Maturity (DTM):                        537             <--           (Maturity - Settlement)

Yield:                                      4.937%
USING EXCEL                           =YIELD              YIELD(settlement,maturity,rate, price,redemption,frequency,bas
                                     4.9341%              Settlement is the security's settlement date. The security settleme
                                                          Maturity is the security's maturity date. The maturity date is the d
                                                          Rate is the security's annual coupon rateDiscount is the security's
                                                          Price is the security's price value per $100 face value.
                                                          Redemption is the security's redemption value per $100 face valu
                                                          Frequency is the number of coupon payments per year. (1 = annu
                                                          Basis is the type of day count basis to use.

                                                                                    Day count
                                                                Basis                  basis
                                                          0 or omitted             US (NASD)
                                                                                   30/360
                                                                             1     Actual/actual
                                                                             2     Actual/360
                                                                             3     Actual/365
                                                                             4     European
                                                                                   30/360


CALCULATE PRINCIPAL GIVEN THE PRICE
(Par x Price) / 100                                                  995,000.00
                     Par:           1,000,000.00
                    Price:                  99.5

CALCULATING SEMI-ANNUAL COUPON PMTS.
(Par x Coupon Rate) / 2                                               30,000.00
                        Par:     1,000,000.00
               Coupon Rate:              0.06

CALCULATING PURCHASED INTEREST - TNOTE
(Next Coupon Pmt. / Days in Period) x DB Settle.                      15,164.84
               Coupon Pmt:                    30,000.00
             Days in Period:                       182
    Days Before Settlement:                          92
           Purchased Date:                   1/30/2009
         Next Coupon Date:                   4/30/2009
               Last Coupon:                 10/30/2008
demption,frequency,basis)
 te. The security settlement date is the date after the issue date when the security is traded to the buyer.
 he maturity date is the date when the security expires.
Discount is the security's discount rate.
  face value.
 value per $100 face value.
 ents per year. (1 = annual, 2 = semi-annual, 4 = quarterly.
demption,frequency,basis)
 te. The security settlement date is the date after the issue date when the security is traded to the buyer.
 he maturity date is the date when the security expires.
Discount is the security's discount rate.
  face value.
 value per $100 face value.
 ents per year. (1 = annual, 2 = semi-annual, 4 = quarterly.
Accrued Interest                            (Treasury Notes & Agency Notes)

Semi-Annual Interest Payments = (Par x Coupon) / 2


Differences in Dates:                            Treasury Note

                  Date #1                                2/28/2007
                  Date #2                                8/31/2007
                  Day Count Basis:           (Actual / Actual) Days
                  Difference in Dates:                          184 (Date #2 - Date #1)


EXAMPLE:                                         Treasury Note
                  Last Payment Date:                     2/28/2007
                  Next Payment Date:                     8/31/2007
                  Days in Period:                               184
                  Settle/Accred Date:                    6/30/2007
                  Days in Accrual:                              122
                  Par Amount:                         1,000,000.00
                  Coupon Rate:                             5.0000%
                  Coupon Amount:                          25,000.00

                  Accrued Interest:                      16,576.09

Just a few days can make a difference in the accrued interest over time, but each day it's typically a small amount.
                  Agency Notes

                          2/28/2007
                          8/31/2007
                        30/360 Days
                                180 =DAYS360(Date #1 - Date #2)


                  Agency Notes
                         2/28/2007
                         8/31/2007
                               180
                         6/30/2007
                               120
                      1,000,000.00
                           5.0000%
                          25,000.00

                          16,666.67

ch day it's typically a small amount.
Pool Reconciliation
POOL:          NAME       Account:         DSR

    Date       DOW    Allocation Factor    Rate     7-Day Rate   BOD Balance

    9/1/2009   Tue      0.000063684       2.3245%                 10,000,000.00
    9/2/2009   Wed      0.000063488       2.3173%                 10,050,000.00
    9/3/2009   Thu      0.000063640       2.3229%                 10,010,000.00
    9/4/2009    Fri     0.000063228       2.3078%                 10,010,000.00
    9/5/2009   Sat      0.000063228       2.3078%                 10,085,000.00
    9/6/2009   Sun      0.000063228       2.3078%                 10,085,000.00
    9/7/2009   Mon      0.000063153       2.3051%     2.31%       10,085,000.00
    9/8/2009   Tue      0.000063702       2.3251%     2.31%       10,085,000.00
    9/9/2009   Wed      0.000063584       2.3208%     2.31%       10,235,000.00
   9/10/2009   Thu      0.000063359       2.3126%     2.31%       10,175,000.00
   9/11/2009    Fri     0.000063891       2.3320%     2.32%       10,175,000.00
   9/12/2009   Sat      0.000063891       2.3320%     2.32%       10,175,000.00
   9/13/2009   Sun      0.000063891       2.3320%     2.32%       10,175,000.00
   9/14/2009   Mon      0.000066903       2.4420%     2.34%       10,175,000.00
   9/15/2009   Tue
   9/16/2009   Wed
   9/17/2009   Thu
   9/18/2009    Fri
   9/19/2009   Sat
   9/20/2009   Sun
   9/21/2009   Mon
   9/22/2009   Tue
   9/23/2009   Wed
   9/24/2009   Thu
   9/25/2009    Fri
   9/26/2009   Sat
   9/27/2009   Sun
   9/28/2009   Mon
   9/29/2009   Tue
   9/30/2009   Wed

  Summary               0.000063776       2.3278%    2.3192%
Deposits     Withdrawals     EOD Balance      Daily Interest   Acc. Interest

 50,000.00                    10,050,000.00           640.02            640.02
               (40,000.00)    10,010,000.00           635.51          1,275.54
                              10,010,000.00           637.04          1,912.58
 75,000.00                    10,085,000.00           637.65          2,550.23
                              10,085,000.00           637.65          3,187.88
                              10,085,000.00           637.65          3,825.54
                              10,085,000.00           636.90          4,462.44
150,000.00                    10,235,000.00           651.99          5,114.43
               (60,000.00)    10,175,000.00           646.97          5,761.39
                              10,175,000.00           644.68          6,406.07
                              10,175,000.00           650.09          7,056.16
                              10,175,000.00           650.09          7,706.25
                              10,175,000.00           650.09          8,356.34
250,000.00                    10,425,000.00           697.46          9,053.81
                                       0.00             0.00          9,053.81
                                       0.00             0.00          9,053.81
                                       0.00             0.00          9,053.81
                                       0.00             0.00          9,053.81
                                       0.00             0.00          9,053.81
                                       0.00             0.00          9,053.81
                                       0.00             0.00          9,053.81
                                       0.00             0.00          9,053.81
                                       0.00             0.00          9,053.81
                                       0.00             0.00          9,053.81
                                       0.00             0.00          9,053.81
                                       0.00             0.00          9,053.81
                                       0.00             0.00          9,053.81
                                       0.00             0.00          9,053.81
                                       0.00             0.00          9,053.81
                                       0.00             0.00          9,053.81

525,000.00    (100,000.00)                          9,053.81          9,053.81
Cash Flow
            General Fund (000's)        Debt Service Fund (000's)   Cap. Improv. Fund (000's
Beginning Balance:     $ 6,000                     $   250

             Inflows Outflows Balance   Inflows Outflows Balance    Inflows
May               500    (850)  5,650        125     -       375         -
June              300    (850)  5,100          15    -       390         -
July              500    (850)  4,750           5   (175)    220         -
August            300    (850)  4,200           5    -       225         -
September         500    (850)  3,850           5    -       230         -
October           300    (850)  3,300           5    -       235         -
November          500    (850)  2,950           5    -       240         -
December        1,000    (850)  3,100           5    -       245         -
January         3,000    (850)  5,250        -       (40)    205         -
February        1,750    (850)  6,150        -       -       205         750
March             750    (850)  6,050        -       -       205         -
April             750    (850)  5,950          10    -       215         -
Cap. Improv. Fund (000's)
          $    750
                             Overall
          Outflows Balance   Balance
               (50)    700       6,725
              (200)    500       5,990
              (200)    300       5,270
              (200)    100       4,525
              (100)     -        4,080
               -        -        3,535
               -        -        3,190
               -        -        3,345
               -        -        5,455
               -       750       7,105
               -       750       7,005
               -       750       6,915
Collateralization Levels
           Time Deposits

              Maturity                   Face Value                Accrued Interest
             5/15/2010                      8,000,000.00                         80,000.00
            12/15/2010                      7,000,000.00                        120,000.00
Total                                      15,000,000.00                       200,000.00


        Collateral Securities

             CUSIP                       Face Value                  Description
          31389AKN1                         8,000,000.00        FNMA 3.00% 1/4/2013
          31361K2M2                         2,000,000.00         FHLB 2.75% 5/1/2010
          3133YC6X4                         5,500,000.00        FNMA 2.00% 5/1/2011
Total Market Value                         15,500,000.00

+ FDIC Insurance

Total MV + Insurance

Total Collateralization Level                              Collateralized




IF you utilize a Lettter of Credit (LOC) incorporate the LOC amount as a separate line item.
Required Level:               105.00%




                          Total
                           8,080,000.00
                           7,120,000.00
                         15,200,000.00




    Market Price      Market Value
           101.2726       8,101,808.00
           97.61888       1,952,377.60
           102.2525       5,623,887.50
                        15,678,073.10

                            250,000.00

                         15,928,073.10

                             104.790%
IF Statements
                                                                        Alert Criteria            2,000
General Fund (000's)
Beginning Balance:                                  $       6,000
Purchase Securities                                        (5,000)
                                       Inflows           Outflows          Maturities           Balance
                    May                    500                (850)            1,000              1,650
                   June                    300                (850)                               1,100
                    July                   500                (850)              1,000            1,750
                 August                    300                (850)                               1,200
              September                    500                (850)              1,000            1,850
                October                    300                (850)                               1,300
              November                     500                (850)              1,000            1,950
              December                   1,000                (850)                               2,100
                January                  3,000                (850)                               4,250
               February                  1,750                (850)                               5,150
                  March                    750                (850)                               5,050
                   April                   750                (850)              1,000            5,950
                                        10,150            (10,200)               5,000

Try to avoid putting criteria values in the equations. Instead, reference the criteria value.
Alert




 x
 x
 x
 x
 x
Text Date

      Date                 Display          Equation
        1/1/2009              1             =TEXT(A2,"d")
        1/2/2009             02             =TEXT(A3,"dd")
        1/3/2009             Sat            =TEXT(A4,"ddd")
        1/4/2009           Sunday           =TEXT(A5,"dddd")


         1/5/2009              1            =TEXT(A8,"m")
         1/6/2009             01            =TEXT(A9,"mm")
         1/7/2009            Jan            =TEXT(A10,"mmm")
         1/8/2009          January          =TEXT(A11,"mmmm")
         1/9/2009              J            =TEXT(A12,"mmmmm")


        1/10/2009            09             =TEXT(A15,"yy")
        1/11/2009           2009            =TEXT(A16,"yyyy")




More Text                 String #1               String #2
                     C:\EXCEL\TEXT.XLS          may expenses
Function:
LEFT(string,3)                C:\                  may
MID(string,4,5)             EXCEL                  expe
RIGHT(string,8)            TEXT.XLS              expenses
LOWER(string)           c:\excel\text.xls      may expenses
UPPER(string)        C:\EXCEL\TEXT.XLS        MAY EXPENSES
PROPER(string)         C:\Excel\Text.Xls       May Expenses
FIND("E",string)               4                 #VALUE!
FIND("e",string)           #VALUE!                  5
SEARCH("E",string)             4                    5
LEN(string)                   17                    12
Explanation
Day as a number w/o leading zero
Day as a number with leading zero
Day as an abbreviation
Day as full name


Month as a number w/o leading zero
Month as a number with leading zero
Month as an abbreviation
Month as a full name
Month as a single letter


Year as a 2-digit year
Year as a 4-digit year




                         String #3
                   jOHNNY fEVER

                        jOH
                       NNY f
                     NY fEVER
                    johnny fever
                  JOHNNY FEVER
                   Johnny Fever
                          9
                      #VALUE!
                          9
                         12
Using INDEX & MATCH

Enter Acct. #:                       29-1345                                   Acct. #
                                                                               20-0002
Lookup Account Name is:              Vendor C                                  01-0045
                                                                               29-1345
                                                                               12-1212
                                                                               56-8654
           =INDEX(D4:E14,MATCH(B3,D4:D14,0),2)                                 77-3110
                                                                               14-1882
                                                                               14-5710
                                                                               07-0025
                                                                               07-4441
                                                                               16-6658


= INDEX(array,row num,column num)


= MATCH(lookup value, lookup array, [match type])
   - 0 (MATCH TYPE) finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument
              Acct. Name
               Vendor A
               Vendor B
               Vendor C
               Vendor D
               Vendor E
               Vendor F
               Vendor G
               Vendor H
               Vendor I
               Vendor J
               Vendor K




e values in the lookup_array argument can be in any order.
Multiple Criteria

Month   Region      Amount                        Desires Result
Jan     North          100   Sum of Amt. where Month = "Jan"
Jan     South          200   Count of Amt. where Month="Feb"
Jan     West           300   Sum of Amt. where Month<>"Jan"
Feb     North          150   Sum of Sales where Month="Jan" or "Feb"
Feb     South          250   Sum of Sales where Month="Jan" AND Region="North"
Feb     West           350   Sum of Sales where Month="Jan" AND Region<>"North"
Mar     North          200   Count of Sales where Month="Jan" AND Region="North"
Mar     South          300   Sum of Sales where Month="Jan" AND Sales>= 200
Mar     West           400   Sum of Sales between 300 AND 400
                             Count of Sales between 300 AND 400

                             * Array requires the use of CTRL+SHIFT+ENTER
Answer                             Equation                       Array
  600    =SUMIF(A2:A10,"Jan",C2:C10)
   3     =COUNTIF(A2:A10,"Jan")
 1650    =SUMIF(A2:A10,"<>Jan",C2:C10)
 1350    =SUMIF(A2:A10,"Jan",C2:C10)+SUMIF(A2:A10,"Feb",C2:C10)
  100    =SUM((A2:A10="Jan")*(B2:B10="North")*C2:C10)             *
  500     =SUM((A2:A10="Jan")*(B2:B10<>"North")*C2:C10)           *
   1     =SUM((A2:A10="Jan")*(B2:B10="North"))                    *
  500    =SUM((A2:A10="Jan")*(C2:C10>=200)*(C2:C10))              *
 1350    =SUM((C2:C10>=300)*(C2:C10<=400)*(C2:C10))               *
   4     =SUM((C2:C10>=300)*(C2:C10<=400))                        *
Weighted Average Maturity

Using the Current Date and Maturity Date: Weighted Average Maturity (WAM) =
The overall sum of each security’s par amount multiplied by its number of days to maturity, divided by the total of all investm

Security Description          Investment Amount                   Mat. in Days (DTM)                    WAM
Investment Pool                       3,000,000.00                  1                                    0.46
Discount Note                         2,500,000.00                 96                                   36.42
US Treasury Note                      1,000,000.00                 192                                  29.14
Bank CD                                   90,000.00                180                                   2.46
Total                                 6,590,000.00                                                      68.47




WAM Calculations that are based on Floating Rate and Variable Rate securities use the reset date in the calculations.
d by the total of all investments.




n the calculations.
Data Validation
                                                                                Data
                              General Fund (000's)                              - Data Validation
Beginning Balance:                              $          6,000

                                  Inflows            Outflows             Balance
May                                         500             (850)              5,650
June                                      1,000             (850)              5,800
July                                        500             (850)              5,450
August                                      300             (850)              4,900
September                                   500             (850)              4,550
October                                     300             (850)              4,000
November                                    500             (850)              3,650
December                                  1,000             (850)              3,800
January                                   3,000             (850)              5,950
February                                  1,750             (850)              6,850
March                                       750             (850)              6,750
April                                       750             (850)              6,650

                              Can not enter an Inflow amount of 10,000.



Another Example
                              Vendor ID           Vendor Name
Do not allow duplicate        A-989               Company A
vendor names to be entered.   A-784               Company B
                              B-884               Company C
Data                          B-982               Company D
- Validation                  C-091               Company E
                              D-331               Company F
                              F-873               Company G




Data Validation Criteria      =COUNTIF($B$25:$B$36,B25)=1




           STUDENT                 GRADE             ETHNICITY
Bill Jones                                   12
Sue Henderson                                 9
James Quinn                                  10                                            List
Brad Benson       11   Africian American
Kaytlin Fritzke   12   American Indian
Brandon Moore      9   Asian
                       Hispanic
                       White
Filtering
              Record Count
                  50


Month       Vendor           ID        State        Sales Type
January     Company A        255-133   Texas        Software
January     Company A        255-133   Texas        Hardware
January     Company B        255-253   Florida      Hardware
January     Company B        355-253   Florida      Software
January     Company A        255-133   Arkansas     Software
January     Company A        255-133   Arkansas     Hardware
January     Company C        275-264   Texas        Hardware
January     Company C        275-264   Texas        Software
January     Company D        525-356   Utah         Construction
January     Company D        525-356   Utah         Construction
January     Company E        311-904   California   Food Svc
February    Company A        255-133   Texas        Hardware
February    Company E        311-904   California   Food Svc
February    Company B        355-253   Florida      Hardware
February    Company B        355-253   Georgia      Software
February    Company A        255-133   Louisiana    Hardware
February    Company B        355-253   Florida      Software
March       Company A        255-133   Arkansas     Software
March       Company A        255-133   Arkansas     Hardware
March       Company C        275-264   Texas        Software
March       Company C        275-264   Texas        Hardware
March       Company D        525-356   Nevada       Construction
February    Company C        275-264   Texas        Software
January     Company D        525-356   Nevada       Construction
May         Company D        525-356   Utah         Construction
April       Company B        355-253   Florida      Hardware
April       Company A        255-133   Arkansas     Software
April       Company A        255-133   Arkansas     Hardware
April       Company C        275-264   Texas        Hardware
April       Company B        355-253   Florida      Software
May         Company C        275-264   Texas        Software
May         Company D        525-356   Nevada       Construction
May         Company D        525-356   Arizona      Construction
August      Company B        355-253   Florida      Hardware
August      Company D        525-356   Utah         Construction
August      Company A        255-133   Texas        Hardware
September   Company C        275-264   Texas        Hardware
October     Company C        275-264   Texas        Hardware
December    Company C        275-264   Texas        Hardware
December    Company A        255-133   Arkansas     Software
December    Company E        311-904   California   Food Svc
March       Company D        525-356   Arizona      Construction
June        Company B        355-253   Florida      Software
June       Company D   525-356   Utah         Construction
November   Company A   255-133   Texas        Hardware
November   Company C   275-264   Texas        Software
July       Company C   275-264   Texas        Software
March      Company C   275-264   Texas        Software
July       Company A   255-133   Arkansas     Hardware
July       Company E   311-904   California   Food Svc
   Subtotal                Subtotal Function
   18,434,395.00 SUBTOTAL(function_num, ref1, ref2, ...)Function_num is the number 1 to 11 (includes hidden values)


Amount
     $311,569.00
     $568,944.00                                                         1 AVERAGE
     $789,504.00                                                           2 COUNT
     $255,975.00                                                          3 COUNTA
      $89,005.00                                                             4 MAX
     $103,900.00                                                             5 MIN
     $654,300.00                                                         6 PRODUCT
     $145,900.00                                                           7 STDEV
      $54,900.00                                                          8 STDEVP
      $99,875.00                                                            9 SUM
     $856,004.00                                                            10 VAR
      $80,900.00                                                           11 VARP
     $560,900.00
     $998,900.00
     $315,900.00
     $209,450.00
     $314,569.00
     $151,001.00
     $569,900.00
     $450,900.00
     $890,544.00
     $900,500.00
     $133,131.00
     $115,000.00
      $98,750.00
     $314,569.00
     $151,001.00
     $850,450.00
     $790,545.00
     $650,950.00
     $133,131.00
     $199,500.00
     $122,575.00
     $262,450.00
     $135,450.00
     $283,930.00
     $364,080.00
     $524,440.00
     $335,900.00
     $433,689.00
     $349,500.00
     $122,575.00
     $262,450.00
$135,450.00
$283,930.00
$364,080.00
$524,440.00
$335,900.00
$433,689.00
$349,500.00
11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list
function to use in calculating subtotals within a list.
Comments & Links
                   General Fund (000's)
Beginning Balance:                  $           6,000

                       Inflows            Outflows       Balance
May                            500               (850)        5,650
June                           300               (850)        5,100
July                           500               (850)        4,750
August                         300               (850)        4,200
September                      500               (850)        3,850
October                        300               (850)        3,300
November                       500               (850)        2,950
December                     1,000               (850)        3,100
January                      3,000               (850)        5,250
February                     1,750               (850)        6,150
March                          750               (850)        6,050
April                          750               (850)        5,950
Totals



                Link & Comment
April Data
             General Property Tax:
                    Current                         600
                    Deliquent - Prior Year           25
                    Deliquent - Current Year        100
                    Interest and Collections Fees    25
                    Total General Property Tax      750
Conditional Formatting                       Max Criteria:         4,000
                                             Min Criteria:         3,000
                     General Fund (000's)
Beginning Balance:                     $           6,000

                         Inflows            Outflows         Balance
May                              500               (850)          5,650
June                             300               (850)          5,100
July                             500               (850)          4,750
August                           300               (850)          4,200 `
September                        500               (850)          3,850
October                          300               (850)          3,300
November                         500               (850)          2,950
December                       1,000               (850)          3,100
January                        3,000               (850)          5,250
February                       1,750               (850)          6,150
March                            750               (850)          6,050
April                            750               (850)          5,950
Totals                        10,150            (10,200)

                                                                     Excel 2003 has 3 levels of condit
                                                                     2007 has many more options fo
     levels of conditional formatting. Excel
any more options for conditional formatting.
Pivot Tables & Pivot Charts
Month       Vendor      ID        State        Sales Type     Amount
January     Company A   255-133   Texas        Software            $311,569.00
January     Company A   255-133   Texas        Hardware            $568,944.00
January     Company B   255-253   Florida      Hardware            $789,504.00
January     Company B   355-253   Florida      Software            $255,975.00
January     Company A   255-133   Arkansas     Software             $89,005.00
January     Company A   255-133   Arkansas     Hardware            $103,900.00
January     Company C   275-264   Texas        Hardware            $654,300.00
January     Company C   275-264   Texas        Software            $145,900.00
January     Company D   525-356   Utah         Construction         $54,900.00
January     Company D   525-356   Utah         Construction         $99,875.00
January     Company E   311-904   California   Food Svc            $856,004.00
February    Company A   255-133   Texas        Hardware             $80,900.00
February    Company E   311-904   California   Food Svc            $560,900.00
February    Company B   355-253   Florida      Hardware            $998,900.00
February    Company B   355-253   Georgia      Software            $315,900.00
February    Company A   255-133   Louisiana    Hardware            $209,450.00
February    Company B   355-253   Florida      Software            $314,569.00
March       Company A   255-133   Arkansas     Software            $151,001.00
March       Company A   255-133   Arkansas     Hardware            $569,900.00
March       Company C   275-264   Texas        Software            $450,900.00
March       Company C   275-264   Texas        Hardware            $890,544.00
March       Company D   525-356   Nevada       Construction        $900,500.00
February    Company C   275-264   Texas        Software            $133,131.00
January     Company D   525-356   Nevada       Construction        $115,000.00
May         Company D   525-356   Utah         Construction         $98,750.00
April       Company B   355-253   Florida      Hardware            $314,569.00
April       Company A   255-133   Arkansas     Software            $151,001.00
April       Company A   255-133   Arkansas     Hardware            $850,450.00
April       Company C   275-264   Texas        Hardware            $790,545.00
April       Company B   355-253   Florida      Software            $650,950.00
May         Company C   275-264   Texas        Software            $133,131.00
May         Company D   525-356   Nevada       Construction        $199,500.00
May         Company D   525-356   Arizona      Construction        $122,575.00
August      Company B   355-253   Florida      Hardware            $262,450.00
August      Company D   525-356   Utah         Construction        $135,450.00
August      Company A   255-133   Texas        Hardware            $283,930.00
September   Company C   275-264   Texas        Hardware            $364,080.00
October     Company C   275-264   Texas        Hardware            $524,440.00
December    Company C   275-264   Texas        Hardware            $335,900.00
December    Company A   255-133   Arkansas     Software            $433,689.00
December    Company E   311-904   California   Food Svc            $349,500.00
March      Company D   525-356   Arizona      Construction   $122,575.00
June       Company B   355-253   Florida      Software       $262,450.00
June       Company D   525-356   Utah         Construction   $135,450.00
November   Company A   255-133   Texas        Hardware       $283,930.00
November   Company C   275-264   Texas        Software       $364,080.00
July       Company C   275-264   Texas        Software       $524,440.00
March      Company C   275-264   Texas        Software       $335,900.00
July       Company A   255-133   Arkansas     Hardware       $433,689.00
July       Company E   311-904   California   Food Svc       $349,500.00
Put the Cell Pointer on the Month Heading
- Select Data
- Select Insert, Pivot Table and Pivot Chart Reporting
Use the Wizard
- Select either Table or Chart
- Build the Layout
VENDORS                                                     EMPLOYEE
         Address              City       State     Zip                  Address
PO Box 4333            Houston           TX    77210-4333   Box 187
4200 S. Freeway #718   Fort Worth        TX   76115-1403    PO Box 477
PO Box 448             Belton            TX   76513-0448    244 S. Main St.
801 E. Brown St.       Alpine            TX   79830-3209    PO Box 9
PO Box 657             Big Sandy         TX   75755-0657    PO Box 259
1901 N. Highway 87     Big Spring        TX   79720-0283    PO Box 123
1135 Mission Rd.       San Antonio       TX   78210-4598    PO Box 705
PO Box 101             Bluff Dale        TX   76433-0101    Box 100
PO Box 125             Booker            TX   79005-0125    PO Box 31
PO Box 623             Brenham           TX   77832-0623    PO Box 227
PO Box 1030            Childress         TX   79201-1030    206 E. Main St.
PO Box 44              Abbott            TX   76621-0044    PO Box 746
RR10 Box 5186          Nacogdoches       TX   75965-9463    RR1 Box 87C
PO Box 2000            Aransas Pass      TX   78335-2000    PO Box 1409
1796 Reeves Rd.        Whitewright       TX   75491-5197    PO Box 519
PO Box 310             Big Lake          TX   76932-0310    703 Broadmoor Dr.
PO Box 430             Brookeland        TX   75931-0430    PO Box 460
PO Box 248             China             TX   77613-0248    PO Box 390
91 N. Side Square      Cooper            TX   75432-1936    PO Box 68
RR5 Box 391            Wichita Falls     TX   76305-9591    PO Box 157
150 PR 8279            Ben Wheeler       TX   75754-5272    PO Box 390
507 Cadena             El Cenizo         TX   78046-7947    PO Box 145
7286 Dietz Elkhorn     Fair Oaks Ranch   TX   78015-4707    PO Box 70
PO Box 197             Fruitvale         TX   75127-0197    PO Box 63
703 Broadmoor Dr.      Dallas            TX   75010-2587
PO Box 37              Groveton          TX   75845-0037
PO Box 1285            Buda              TX   78610-1285
PO Box 3116            Abilene           TX   79604-3116
PO Box 840             Italy             TX   76651-0840
112 Bauman St.         Kirby             TX   78219-1063
PO Box 112             La Coste          TX   78039-0112
PO Box 60              La Villa          TX   78562-0060
207 Milam Rd.          Longview          TX   75603-5608
100 S. Highridge Dr.   Little Elm        TX   75068-4300
PO Box 1108            Latexo            TX   75849-1108
110 Parker Pkwy.       Lincoln Park      TX   76227-9297
14387 Alamo Rd.        Log Cabin         TX   75148-3511
PO Box 293             Mabank            TX   75147-0293
PO Box 787             Marfa             TX   79843-0787
PO Box 85              Marquez           TX   77865-0085
100 Kent St.           Merkel            TX   79536-3612
PO Box 52              Milano            TX   76556-0052
201 E. Wilson          Morton            TX   79346-2650
PO Box 96              Mullin            TX   76864-0096
PO Box 325             Corsicana         TX   75151-0325
PO Box 123                  Kennedale          TX   75661-0259
222 S. Harvard Ave.         Corsicana          TX   75109-3629
PO Box D                    Nome               TX   77629-0218
PO Box 37                   Nomangee           TX   77871-0037
613 N. Lacy Dr.             Waco               TX   76705-1009
PO Box 458                  Kaufman            TX   75145-0458
1313 Stuart Place Rd.       Harlingen          TX   78552-6369
5700 E. Parker Rd.          Allen              TX   75002-6754
PO Box 37                   Pecan Gap          TX   75469-0037
1094 S. Lowrance Rd.        Red Oak            TX   75154-7626
PO Box 300                  Sandia             TX   78383-0300
PO Box 191                  Poynor             TX   75782-0191
PO Box 629                  Quanah             TX   79252-0629
31850 Rangerville Rd.       San Benito         TX   78586-7058
PO Box 27                   Richland Springs   TX   76871-0027
PO Box 249                  Riesel             TX   76682-0249
PO Box 40                   Ross               TX   76684-0040
PO Box 129                  San Felipe         TX   77473-0129
4615 Main St.               Mathis             TX   78368-4430
PO Box 121                  San Perlita        TX   78590-0121
5500 Buchanan               Fort Worth         TX   76114-1251
PO Box 204                  Sunset             TX   76270-0204
PO Box 300                  Tahoka             TX   79373-0300
5100 N. New Braunfels       San Antonio        TX   78209-5822
PO Box 398                  Three Rivers       TX   78071-0398
2105 Chestnut Dr.           Brookston          TX   75421-9730
PO Box 124                  Westbrook          TX   79565-0124
PO Box 248                  Weston             TX   75097-0248
41 Champion Cir.            Wimberley          TX   78676-3327
PO Box 369                  Roby               TX   79543-0369
PO Box 305                  Bronte             TX   76933-0305
404-A McKinney Pkwy.        McKinney           TX   75071-1877
1014 15th St.               Wellington         TX   79095-3704
PO Box 539                  Paducah            TX   79248-0539
2949 N. Stemmons            Dallas             TX   75247-6195
801 E. 3rd St.              Hereford           TX   79045-5727
PO Box 12668                Austin             TX   78711-2668
PO Box 1007                 Clarendon          TX   79226-1007
PO Box 1458                 Ozona              TX   76943-1458
Box 180                     Crowell            TX   79227-0180
4701 E. Farm Rd. 678        Gainesville        TX   76240-9398
PO Box 13047                Austin             TX   78711-3047
6001 Gulf Freeway Bldg B3   Houston            TX   77023-5423
RR5 Box 498                 Waco               TX   76705-9605
PO Box 156                  Goree              TX   76363-0156
RR1 Box 1                   Graford            TX   76449-9701
PO Box 665                  Clarendon          TX   79226-0665
PO Box 109           Gunter          TX   75058-0109
PO Box 368           Hallettsville   TX   77964-0368
701A S. Washington   Marshall        TX   75670-5335
PO Box 400           Harrold         TX   76364-0400
PO Box 440           Hawley          TX   79525-0440
PO Box 155           Higgins         TX   79046-0155
PO Box 246           High Island     TX   77623-0246
215 Northwest 2nd    Tulia           TX   79088-2203
   City           State          Zip
Howe         TX           75459-0187
Daisetta     TX           77533-0477
Jacksboro    TX           76458-2359
Tuscola      TX           79562-0009
Karnack      TX           75661-0259
Kennedale TX              75661-0259
Kendleton    TX           77451-0705
Sarita       TX           78385-0100
Junction     TX           76849-0031
Junction     TX           76849-0227
Kirbyville   TX           75956-2128
Knox City    TX           79529-0746
Falfurrias   TX           78355-9712
La Joya      TX           78560-1409
La Pryor     TX           78872-0519
Dallas       TX           75010-2587
Memphis      TX           79245-0460
Lefors       TX           79054-0390
Leggett      TX           77350-0068
Jewett       TX           75846-0157   Creating Range Names
Linden       TX           75563-0390   - Formula - Name Manager
Lindsay      TX           76250-0145
Vancourt     TX           76955-0070
Lipscomb     TX           79056-0063

								
To top