Docstoc

Calculate Credit Card Payment in Excel

Document Sample
Calculate Credit Card Payment in Excel Powered By Docstoc
					                               Ch 04 (Banks and Checkbooks) Objectives
 1   Identify the parts of a check, a deposit slip and a check stub
 2   Types of checking accounts
 3   Calculate monthly service change
 4   Complete a check register
 5   Identify bank services available to customers
 6   Understanding interest-paying checking plans
 7   Determine deposits with credit-card transactions
 8   Calculate the discount fee on credit-card deposits
 9   What is a Bank Statement
10   What is a checkbook
11   Why is it important to Reconcile
12   Reconcile a bank statement with a checkbook
Suix's Accounting
3985 14th Ave. S.
Seattle, WA 98106

                 PAY TO THE ORDER OF:

                                 Amount

Boeing Employees Credit Union
12770 Gateway Dr.
Tukwila, WA 98168

                                     By
 Memo Purchase Desk
           l▪▪ 1210 ▪▪▪ 0420 l▪▪ 6005655960 ll■ 4025


front
canceled
different than video ==>
This is a question you will see in a quiz.




                                        Banks will accept payments for the business
                                        Banks may set up a level of credit for the business to make short-term loans
                                        Large businesses may make overnight or short-term deposits to earn interest

                                      ATM Machines:
                                        Be sure to keep the printed out receipts for your records

                                      On-line banks:
                                        Reduced costs of on-line services can result in higher interest being paid and low
                                        Transactions and banking activity can be performed more conveniently from a c
                                        Check balances and reconcile on-line at home or at the business
                                        Pay bills from the on-line bank site

                                      Debit Cards
                                        When you use a debit card the cash is withdrawn from your account


                                      Credit Cards
                                        Credit Cards are not associated directly with your checking account
                                        When you use a credit card you "go into debt"
          Home Depot

          One Hundred Fifty Eight Dollars And Seventy Cents----------------------------



          Suix Radcoolinator
▪ 6005655960 ll■ 4025

          Checking Accounts:
           Deposit money with bank
           Withdraw money by check, ATM machine, wire transfer, on-line transfer, or bill pay

          Check:
           Payor's or Payee's or Drawer’s printed address
           Check number
           Date
           ABA transit number
            Numerator: City, State, Bank
            Denominator: Federal Reserve District and Bank
           Pay to the order of:
            Payee’s name
           Amount line:
            Write close to dollar sign
            Amount in words
            Use “and” between dollars and fraction of dollars
            Use entire line ----------
            Words supersede numerals for amount
           Bank’s address
           Signature line
            Should match signature on signature card

          Who is the drawer, or payor, or payer?
           Person who writes check
          Who is the payee?
           Person to whom the payment is made
          What is a check?
           Order by drawer for bank to make payment to payee
Check that has not been filled out?
 Paper
Signed check made out to you?
 Asset: Cash
An electronic photo of a canceled check that you obtain?
 Record of business transaction
 Proof that you paid someone

                         Types of checking accounts:
Checking accounts are offered by:
 Banks
 Saving and Loans
 Credit Unions
 On-line banks (reduced costs of on-line services can result in higher interest being paid and lower service costs)

Personal checking accounts:
 Below a set balance may result in a service fee
 Account may earn interest

Business checking accounts:
 Banks offer more services because of the increased levels of money deposited and potential for making loans
 Banks will accept payments for the business
 Banks may set up a level of credit for the business to make short-term loans
 Large businesses may make overnight or short-term deposits to earn interest

ATM Machines:
 Be sure to keep the printed out receipts for your records

On-line banks:
 Reduced costs of on-line services can result in higher interest being paid and lower service costs
 Transactions and banking activity can be performed more conveniently from a computer
 Check balances and reconcile on-line at home or at the business
 Pay bills from the on-line bank site

Debit Cards
 When you use a debit card the cash is withdrawn from your account


Credit Cards
 Credit Cards are not associated directly with your checking account
 When you use a credit card you "go into debt"
                  No.                4025
                            66-420
                             1210

                Date    7/16/10
              Amount     $158.70

------------------------------------------------------------------------------------------------------------------
                       Dollars
      4025                             Suix's Accounting
Date:        January 23, 2007          3985 14th Ave. S.
Amount      $158.70                    Seattle, WA 98106
To         Home Depot
For        Purchase Desk                              PAY TO THE ORDER OF:
Bal. Bro't For'd          5000   .25                                   Amount
Am't Deposited
Total                     5000   .25   Boeing Employees Credit Union
Am't this Check            158   .70   12770 Gateway Dr.
Balance For'd             4841   .55   Tukwila, WA 98168

                                                                           By
                                        Memo Purchase Desk
                                               l▪▪ 1210 ▪▪▪ 0420 l▪▪ 6005655960 ll■ 4025

                 Why are checks stubs important?
Because you use the check stub as the source information to enter the
  business transaction into the official business accounting system
          Home Depot
          One Hundred Fifty Eight Dollars And Seventy Cents----------------------------



          Suix Radcoolinator
▪ 6005655960 ll■ 4025
                  No.                4025
                            66-420
                             1210

                Date         7/16/10
              Amount    $158.70
------------------------------------------------------------------------------------------------------------------
                       Dollars
Types of Endorsements
 Restrictive endorsement
   Restricts or limits further transfer of title
     Example: “for deposit only”
 Blank endorsement
   Signature of payee only – does not restrict further transfer
 Special endorsement
   “Pay to the order of…”
 Qualified endorsement
   “Without recourse
     Frees endorser (original payee) from future liability if drawer does not honor the check
    Monthly service charges vary depending on the type of account that you have and the contract that is
                                                  signed.
                      Read the details of your contract before you sign the dotted line

 Shop around, some Banks like Boeing Employees Credit Union or E-Trade have really nice accounts w
   no service charges, a small amount of interest, free checking/bill pay, and low balance requirements

Book example:
                                                                     Maintenance
                                                                     Charge per Per-Check
                                      Average Balance                Month       Charge
                                      Less than $500.00                   $12.00       $0.20
                                      $500.00 - $1,999.99                   7.50        0.20
                                      $2,000.00 - $4,999.99                 5.00        0.10
                                      $5,000.00 or more                     0.00        0.00

 Using the table above, if your monthly average balance was $833.00 and you wrote 38 checks for the month, what
                                       would your monthly service charge be?
Average Balance                                               $833.00
Maintenance Charge per Month                                      7.50
Checks written                                                      38
Charge per check                                                  0.20
Per-Check Charge = Checks written *
Charge per check =                                                7.60
Monthly Service Charge =
Maintenance Charge per Month + Per-
Check Charge =                                                   15.10

Using the table above, if your monthly average balance was $3,149.00 and you wrote 64 checks for the month, what
                                       would your monthly service charge be?
Average Balance                                             $3,149.00
Maintenance Charge per Month                                      5.00
Checks written                                                      64
Charge per check                                                  0.10
Per-Check Charge = Checks written *
Charge per check =                                                6.40
Monthly Service Charge =
Maintenance Charge per Month + Per-
Check Charge =                                                   11.40

  Using the table above, if your monthly average balance was $9,522.00 and you wrote 500 checks for the month,
                                    what would your monthly service charge be?
Average Balance                                            $9,522.00
Maintenance Charge per Month
Checks written                                                    500
Charge per check
Per-Check Charge = Checks written *
Charge per check =
Monthly Service Charge =
Maintenance Charge per Month + Per-
Check Charge =                                                 0.00

 Using the table above, if your monthly average balance was $50.25 and you wrote 17 checks for the month, what
                                      would your monthly service charge be?
Average Balance                                               $50.25
Maintenance Charge per Month                                    12.00
Checks written                                                     17
Charge per check                                                 0.20
Per-Check Charge = Checks written *
Charge per check =                                               3.40
Monthly Service Charge =
Maintenance Charge per Month + Per-
Check Charge =                                                  15.40




                                           VLOOKUP
                                                                      Maintenance
                                                                      Charge per  Per-Check
Average Balance                       VLOOKUP #s                      Month       Charge
Less than $500.00          $0.00   $12.00   $0.20
$500.00 - $1,999.99       500.00     7.50    0.20
$2,000.00 - $4,999.99   2,000.00     5.00    0.10
$5,000.00 or more       5,000.00     0.00    0.00
ou have and the contract that is

e dotted line

de have really nice accounts with
and low balance requirements




hecks for the month, what




checks for the month, what




00 checks for the month,
hecks for the month, what
Check                                     Amount of       Date of   Amount of
No.      Date        Check Issued To      Check          Deposit   Deposit       Balance
                                                              Balance Forward ==>       3587.77
  2097    06/05/10   Starbuck's                456.00                                 3,131.77
  2098    06/06/10   Office Depot              415.00                                 2,716.77
  2099    06/07/10   Whole Foods Market        157.00                                 2,559.77
  2100    06/07/10   Suix Trucking              59.00                                 2,500.77
          06/08/10   Deposit                               06/08/10        588.00     3,088.77
  2101    06/08/10   Macbeth Hardwoods         499.00                                 2,589.77
  2102    06/08/10   Chin Ho                   392.00                                 2,197.77
          06/10/10   Deposit                               06/10/10        612.00     2,809.77
  2103    06/11/10   Home Depot                411.00                                 2,398.77
  2104    06/11/10   Macbeth Hardwoods         490.00                                 1,908.77
  2105    06/11/10   Google                    251.00                                 1,657.77
  2106    06/11/10   Starbuck's                 36.00                                 1,621.77
  2107    06/12/10   Sheliadawn Smith          411.00                                 1,210.77
          06/12/10   Deposit                               06/12/10      2,116.00     3,326.77
  2108    06/13/10   Suix Trucking              50.00                                 3,276.77
          06/13/10   Suix Trucking             150.00                                 3,126.77
          06/14/09   Google                     20.00                                 3,106.77




Check                                     Amount of       Date of   Amount of
No.      Date        Check Issued To      Check          Deposit   Deposit       Balance
                                                              Balance Forward ==>       3316.07
  2153    08/28/10   Dunn Lumber               342.00                                 2,974.07
  2154    08/29/10   Dunn Lumber               223.00                                 2,751.07
  2155    08/30/10   Home Depot                344.00                                 2,407.07
  2156    08/30/10   Macbeth Hardwoods         350.00                                 2,057.07
          08/31/10   Deposit                               08/31/10        516.00     2,573.07
  2157    08/31/10   Office Depot               67.00                                 2,506.07
  2158    08/31/10   Home Depot                423.00                                 2,083.07
          09/02/10   Deposit                               09/02/10      1,974.00     4,057.07
  2159    09/03/10   Dunn Lumber               334.00                                 3,723.07
  2160    09/03/10   Dunn Lumber               113.00                                 3,610.07
  2161    09/03/10   Starbuck's                 53.00                                 3,557.07
  2162    09/03/10   Whole Foods Market        155.00                                 3,402.07
  2163    09/04/10   Chin Ho                   154.00                                 3,248.07
          09/04/10   Deposit                               09/04/10      1,483.00     4,731.07
                                                                                      4,731.07
                                                                                      4,731.07
                                                                                      4,731.07
                                                                                      4,731.07
                                                                                      4,731.07
                                                                                      4,731.07
                                                                                      4,731.07
                                                                                      4,731.07
                                                                                      4,731.07
                                                                                      4,731.07


Check                                     Amount of       Date of   Amount of
No.      Date        Check Issued To      Check          Deposit   Deposit       Balance
                                                              Balance Forward ==>       6962.95
  2209    11/20/10   Whole Foods Market        279.00
  2210    11/21/10   Google                    157.00
  2211    11/22/10   Starbuck's                451.00
  2212    11/22/10   Macbeth Hardwoods         361.00
          11/23/10   Deposit                                11/23/10     2,157.00
  2213    11/23/10   Office Depot               47.00
  2214    11/23/10   Home Depot                491.00
          11/25/10   Deposit                                11/25/10      731.00
  2215    11/26/10   Dunn Lumber               479.00
  2216    11/26/10   Google                     91.00
  2217    11/26/10   Suix Trucking             132.00
  2218    11/26/10   Suix Trucking              50.00
  2219    11/27/10   Whole Foods Market        234.00
          11/27/10   Deposit                                11/27/10      918.00




Check                                     Amount of       Date of   Amount of
No.      Date        Check Issued To      Check          Deposit   Deposit       Balance
                                                              Balance Forward ==>       4943.85
  2265    02/12/11   Macbeth Hardwoods         376.00
  2266    02/13/11   Suix Trucking             183.00
  2267    02/14/11   Chin Ho                    94.00
  2268    02/14/11   Starbuck's                223.00
          02/15/11   Deposit                                02/15/11     1,772.00
  2269    02/15/11   Sheliadawn Smith          466.00
  2270    02/15/11   Dunn Lumber               492.00
          02/17/11   Deposit                                02/17/11     2,474.00
  2271    02/18/11   Whole Foods Market        152.00
  2272    02/18/11   Sheliadawn Smith          168.00
  2273    02/18/11   Dunn Lumber               226.00
  2274    02/18/11   Starbuck's                423.00
  2275    02/19/11   Home Depot                 61.00
          02/19/11   Deposit                                02/19/11      895.00
Check                                     Amount of       Date of   Amount of
No.      Date        Check Issued To      Check          Deposit   Deposit       Balance
                                                              Balance Forward ==>       5410.57
  2321    05/07/11   Dunn Lumber               358.00
  2322    05/08/11   Starbuck's                 43.00
  2323    05/09/11   Sheliadawn Smith          472.00
  2324    05/09/11   Office Depot              151.00
          05/10/11   Deposit                                05/10/11     1,307.00
  2325    05/10/11   Sheliadawn Smith           33.00
  2326    05/10/11   Sheliadawn Smith          431.00
          05/12/11   Deposit                                05/12/11      574.00
  2327    05/13/11   Suix Trucking             495.00
  2328    05/13/11   Home Depot                 72.00
  2329    05/13/11   Starbuck's                414.00
  2330    05/13/11   Whole Foods Market        298.00
  2331    05/14/11   Office Depot              395.00
          05/14/11   Deposit                                05/14/11     2,152.00
Assum




Assum
Assum




Assum
Assum
       Assumptions
     Start Check #        2097
     Start Date           3809
 1   Home Depot
 2   Office Depot
 3   Starbuck's
 4   Suix Trucking
 5   Chin Ho
 6   Sheliadawn Smith
 7   Google
 8   Whole Foods Market
 9   Dunn Lumber
10   Macbeth Hardwoods




       Assumptions
     Start Check #        2153
     Start Date           3893
11   Home Depot
12   Office Depot
13   Starbuck's
14   Suix Trucking
15   Chin Ho
16   Sheliadawn Smith
17   Google
18   Whole Foods Market
19   Dunn Lumber
20   Macbeth Hardwoods
       Assumptions
     Start Check #        2209
     Start Date           3977
21   Home Depot
22   Office Depot
23   Starbuck's
24   Suix Trucking
25   Chin Ho
26   Sheliadawn Smith
27   Google
28   Whole Foods Market
29   Dunn Lumber
30   Macbeth Hardwoods




       Assumptions
     Start Check #        2265
     Start Date           4061
31   Home Depot
32   Office Depot
33   Starbuck's
34   Suix Trucking
35   Chin Ho
36   Sheliadawn Smith
37   Google
38   Whole Foods Market
39   Dunn Lumber
40   Macbeth Hardwoods
       Assumptions
     Start Check #        2321
     Start Date           4145
41   Home Depot
42   Office Depot
43   Starbuck's
44   Suix Trucking
45   Chin Ho
46   Sheliadawn Smith
47   Google
48   Whole Foods Market
49   Dunn Lumber
50   Macbeth Hardwoods
                                  Suix's Accounting Check and Deposit Register
             Check
Date          No.     Rec?                    Payee Or Deposit Notes
             /Ref#
 2005
 June 12       1992          Vendor 1
 June 12       1993         Vendor 2
       12      1994         Vendor 3
       13      1995         Vendor 4
       14      1996         Vendor 5
       15      1997         Vendor 6
       16   DS52            Customer deposits 1
       17      1998         Vendor 8
       18      1999         Vendor 9
       19      2000         Vendor 10
       20      2001         Vendor 11
       21      2002         Vendor 12
       22      2003         Vendor 13
       23      2004         Vendor 14
       24   DS53            Customer deposits 2
       25      2005         Vendor 16
       26      2006         Vendor 17
       27      2007         Vendor 18
       28      2008         Vendor 19
       29      2009          Vendor 20
       30      2010          Vendor 21
  July 2       2011          Vendor 22
        4   DS54             Customer deposits 3
        6      2012          Vendor 24
        8      2013          Vendor 25
       10      2014          Vendor 26
       12      2015          Vendor 27
       14      2016          Vendor 28
       16      2017          Vendor 29
       18      2018          Vendor 30
       20   DS55             Customer deposits 4
       22      2019          Vendor 32
       25      2020          Vendor 33
       28      2021          Vendor = Davis Inc. (reduce Accounts Payable)
       31      2022          Vendor 35
       31      2023          Vendor 36
       31   DS56             Customer deposits 5
       31      2024          Vendor 37
d Deposit Register                                         
             Amt. Of
                          Amt. Of Deposit
             Check                          Balance of
                            (Deposits
          (Withdrawals                        Cash
                            (Credits))
            (Debits))
                                               $6,875.63
                $461.00                        $6,414.63
                 100.00                        $6,314.63
               2,500.00                        $3,814.63
                 690.00                        $3,124.63
                  25.00                        $3,099.63
                  25.89                        $3,073.74
                                 3,584.01      $6,657.75
                  12.87                        $6,644.88
                  22.87                        $6,622.01
                  74.00                        $6,548.01
                  52.00                        $6,496.01
               1,190.36                        $5,305.65
                  47.01                        $5,258.64
                  52.36                        $5,206.28
                                 2,963.41      $8,169.69
                  12.01                        $8,157.68
                  68.88                        $8,088.80
                  61.00                        $8,027.80
                  25.00                        $8,002.80
               2,189.00                        $5,813.80
                  47.00                        $5,766.80
                  52.36                        $5,714.44
                                 1,987.52      $7,701.96
                  17.95                        $7,684.01
                  87.25                        $7,596.76
                   3.65                        $7,593.11
                 922.00                        $6,671.11
                 401.00                        $6,270.11
                  41.05                        $6,229.06
                  52.36                        $6,176.70
                                 2,510.30      $8,687.00
                  89.14                        $8,597.86
                  25.36                        $8,572.50
                $532.00                        $8,040.50
                $119.00                        $7,921.50
                $827.00                        $7,094.50
                               $1,303.00       $8,397.50
                 $67.00                        $8,330.50
Bank services available to customers and the account charges associated with those services

NSF charge (Nonsufficient funds charge)
 “Bounced Check”
 $10 -$20 charge.
 Insufficient funds to cover a check
 The check was improperly filled out

Overdraft protection
 Bank will cover overdrafts when account balances are insufficient to cover a check. Charges vary.
A Returned Deposited Item
 When a deposited check is returned because the customer’s bank account does not have enough funds
 or because the check was improperly filled out. $5 - $10 charge.
  Stop Payment Order
AA request to the bank from the depositor that the bank not honor a check that the depositor had
 written. $15 - $20 charge.
Cashier’s Check
 A Check written and backed by the bank. $5 - $15 charge
Money Order
 Purchase instrument similar to cash. $5 - $10 charge
Notary Service
 Official certification of a signature on a document. Charges vary.
Telephone transfers
 Transfer between accounts. Charges vary.
Transfers
 Customers will transfer money from their account to your account. Charges vary.
On-line transfers
On-line bill pay

Some checking and savings accounts pay interest

It pays to shop around and find the best bank and account at that bank
                           Define terms:
                    Customers use them at your business. The bank
                    that Issues the credit card agrees to pay the
                    money to the business on the behalf of the
                    customer. The credit card company then collects
Credit Cards        from the customer later.
Merchant =          Business owner
Revenue =           Sales for the business for the day or month
Credit card sales   Total sales or Revenue for the business for the
slips =             day

                    When you make a refund to a customer who paid
Credit slip =       with a credit card (a reduction from Revenue)
                    Banks don't collect the money from your
Credit card fee =   customer for free!!!!
                    The amount the bank deposits in the merchant
Credit to your      business account after the bank takes its credit
account =           card fee

                               Determine deposits with Credit Card Transactions
                     If the total credit card sales slips for the day were 5,526.36 and you
                     had "credit slips" that totaled 57.88, what is the gross amount of the
                                          credit card deposits for the day?
                    Credit card sales slips                                 $5,526.36
                    Credit slips                                               $57.88
                    Gross amount for deposit                                $5,468.48

                    But is this the amount that is deposited in your bank
                    account?

                    Credit card fee rate =                                    3.50%
                    Credit card fee =                                        $191.40
                    Total amount that will be deposited in the merchants
                    account = 'Credit to your account' =                    $5,277.08


                      Calculate the "discount" or credit card fee on credit-card deposits

                      If you ran a boomerang selling internet site, and you sold $100.00
                     worth of boomerangs to a customer and collected tax (because the
                     sale is in the state), what would the credit card fee ( 3.50% ) be and
                      how much would be credited to your business merchant checking
                                                    account?

                    Boomerangs sold =                                               5
                    Boomerang price each =                                  $ 20.00
                    Invoice Total for 5 Boomerangs =                        $ 100.00    Rates
Sales Tax = 100 x 8.25% =                                          8.25     0.0825
Total                                                            108.25
Credit card fee = 108.25 * 3.50% =                                 3.79      0.035

Credit card company deposits the 'Credit to your account' in
Business Checking Account = 108.25 - 3.79 =                      104.46



            Calculate the discount fee on credit-card deposits

  If you ran a boomerang selling internet site, and you sold $258.50
 worth of boomerangs to a customer and collected tax (because the
 sale is in the state), what would the credit card fee ( 3.25% )be and
  how much would be credited to your business merchant checking
                               account?

Boomerangs sold =                                                     11
Boomerang price each =                                         $ 23.50
Invoice Total for 11 Boomerangs =                              $ 258.50    Rates
Sales Tax = 258.5 x 9.10% =                                       23.52     0.0910
Total                                                            282.02
Credit card fee = 282.02 * 3.25% =                                 9.17     0.0325

Credit card company deposits the 'Credit to your account' in
Business Checking Account = 282.02 - 9.17 =                      272.85



            Calculate the discount fee on credit-card deposits

  If you ran a boomerang selling internet site, and you sold $259.87
 worth of boomerangs to a customer and collected tax (because the
 sale is in the state), what would the credit card fee ( 2.75% )be and
  how much would be credited to your business merchant checking
                               account?

Boomerangs sold =                                                     13
Boomerang price each =                                         $ 19.99
Invoice Total for 13 Boomerangs =                              $ 259.87    Rates
Sales Tax = 259.87 x 7.55% =                                                0.0755
Total
Credit card fee = 279.49 * 2.75% =                                          0.0275

Credit card company deposits the 'Credit to your account' in
Business Checking Account = 279.49 - 7.69 =
108.25
  3.79


104.46
                                  Suix's Accounting Check and Deposit Register
             Check
Date          No.     Rec?                    Payee Or Deposit Notes
             /Ref#
 2005
 June 12       1992          Vendor 1
 June 12       1993         Vendor 2
       12      1994         Vendor 3
       13      1995         Vendor 4
       14      1996         Vendor 5
       15      1997         Vendor 6
       16   DS52            Customer deposits 1
       17      1998         Vendor 8
       18      1999         Vendor 9
       19      2000         Vendor 10
       20      2001         Vendor 11
       21      2002         Vendor 12
       22      2003         Vendor 13
       23      2004         Vendor 14
       24   DS53            Customer deposits 2
       25      2005         Vendor 16
       26      2006         Vendor 17
       27      2007         Vendor 18
       28      2008         Vendor 19
       29      2009          Vendor 20
       30      2010          Vendor 21
  July 2       2011          Vendor 22
        4   DS54             Customer deposits 3
        6      2012          Vendor 24
        8      2013          Vendor 25
       10      2014          Vendor 26
       12      2015          Vendor 27
       14      2016          Vendor 28
       16      2017          Vendor 29
       18      2018          Vendor 30
       20   DS55             Customer deposits 4
       22      2019          Vendor 32
       25      2020          Vendor 33
       28      2021          Vendor = Davis Inc. (reduce Accounts Payable)
       31      2022          Vendor 35
       31      2023          Vendor 36
       31   DS56             Customer deposits 5
       31      2024          Vendor 37
d Deposit Register                                         
             Amt. Of
                          Amt. Of Deposit
             Check                          Balance of
                            (Deposits
          (Withdrawals                        Cash
                            (Credits))
            (Debits))
                                               $6,875.63
                $461.00                        $6,414.63
                 100.00                        $6,314.63
               2,500.00                        $3,814.63
                 690.00                        $3,124.63
                  25.00                        $3,099.63
                  25.89                        $3,073.74
                                 3,584.01      $6,657.75
                  12.87                        $6,644.88
                  22.87                        $6,622.01
                  74.00                        $6,548.01
                  52.00                        $6,496.01
               1,190.36                        $5,305.65
                  47.01                        $5,258.64
                  52.36                        $5,206.28
                                 2,963.41      $8,169.69
                  12.01                        $8,157.68
                  68.88                        $8,088.80
                  61.00                        $8,027.80
                  25.00                        $8,002.80
               2,189.00                        $5,813.80
                  47.00                        $5,766.80
                  52.36                        $5,714.44
                                 1,987.52      $7,701.96
                  17.95                        $7,684.01
                  87.25                        $7,596.76
                   3.65                        $7,593.11
                 922.00                        $6,671.11
                 401.00                        $6,270.11
                  41.05                        $6,229.06
                  52.36                        $6,176.70
                                 2,510.30      $8,687.00
                  89.14                        $8,597.86
                  25.36                        $8,572.50
                $532.00                        $8,040.50
                $119.00                        $7,921.50
                $827.00                        $7,094.50
                               $1,303.00       $8,397.50
                 $67.00                        $8,330.50
                                                                        Boeing Employees Credit Union
                                                                                Bank Statement
                                                                   For The Period Jul 01, 2005 to Jul 31, 2005
      Customer: Bell Video                  Statement Date                          7/31/2005
                3985 14th Ave. S.           Beginning Balance($)                   $ 8,463.80
                Seattle, WA 98106           Withdrawals (Debits)                   $ 4,601.62
      Account # 6005655960                  Deposits (Credits)                     $ 5,103.82
                                            Ending Balance($)                      $ 8,966.00
                                                                                     Withdrawals (Debits)
      Date          Check Numbers/Ref #             Amount
         7/7/2005                    2009                     2,189.00   Vendor 20
         7/9/2005                    2010                        47.00   Vendor 21
       7/11/2005                     2011                        52.36   Vendor 22
       7/13/2005                     2012                        17.95   Vendor 24
       7/15/2005                     2013                        87.25   Vendor 25
       7/17/2005                     2014                         3.65   Vendor 26
       7/19/2005                     2015                       922.00   Vendor 27
       7/21/2005                     2016                       401.00   Vendor 28
       7/23/2005                     2017                        41.05   Vendor 29
       7/25/2005                     2018                        52.36   Vendor 30
       7/27/2005                     2019                        89.14   Vendor 32
       7/29/2005                     2020                        25.36   Vendor 33
       7/31/2005                     2021                      $523.00   Davis Inc.
       7/31/2005                     NSF                       $125.00   NSF Check from D.M. Scott
       7/31/2005           ATM Withdraw                         $20.00   ATM withdrawal
       7/31/2005           service charge                        $5.50   Bank Service/Collection Charge
Total Withdrawals (Debits)                                   $4,601.62

                                                                                      Deposits (Credits)
      Date                 Deposit                  Amount
        7/4/2005 Deposit                                   $ 1,987.52    Customer deposits 3
       7/20/2005 Deposit                                   $ 2,510.30    Customer deposits 4
       7/31/2005 Deposit                                     $ 606.00    Note Collected by Bank from Lee & Brock (Principle
Total Deposits (Credits)                                   $5,103.82




                                            * indicates a check out of order
Employees Credit Union
 Bank Statement
od Jul 01, 2005 to Jul 31, 2005




  Withdrawals (Debits)
                                          Description




k from D.M. Scott

ice/Collection Charge



   Deposits (Credits)
                                          Description
deposits 3
deposits 4
cted by Bank from Lee & Brock (Principle = $600.00 and Interest = $6.00)

                                           Why Bank Reconciliations?

Because there are things in our check book that the bank does not know about (like outstanding checks)
Because there are things on the bank statement that we have not put into our check book yet (like service charge

Your checkbook balance almost never will equal the bank statement balance!
Banks make mistakes
Verify checking account records with those of the bank
 Check accuracy of:
    Checks written
    Deposits made
    Service charges incurred
    Interest earned

                                                 Define Terms:
Outstanding checks
 Checks that the business has written that have not arrived at the bank yet
NSF checks "Not Sufficient Funds"
 Checks that we originally deposited in the bank, but did not clear (bank did not collect on them) because the
 person/business that gave the check to us did not have enough money in the account!
Deposits In Transit
 Deposits that we have made that the bank has not received yet
                      Business Checkbook                                             Bank Statement
                           Amount of            Amount of
Check         Check Issued Check or    Date of Deposit or                           Check and
No.     Date To            Debit      Deposit Credit     Balance       Check No.  Debits
                                      Balance Forward ==> $ 3,538.32
 1256    5/01 Name 1         108.50                         3,429.82         1257      75.00
 1257    5/02 Name 2           75.00                         3,354.82
 1258    5/08 Name 3         218.62                         3,136.20         1259     836.15
              Deposit                      5/10 1,201.12     4,337.32         1260     400.00
 1259    5/15 Name 4         836.15                          3,501.17   RC               49.07
 1260    5/16 Name 5         400.00                          3,101.17         1262      60.01
 1261    5/18 Name 6           25.33                         3,075.84   ATM             40.00
 1262    5/23 Name 7           60.01                         3,015.83   IC
 1263    5/24 Name 8           97.37                         2,918.46   SC               7.60
              Deposit                      5/26   865.50     3,783.96   CP              12.00
ATM      5/26                  40.00                         3,743.96
Bank Statement

      Deposits   Date Balance
                  5/01 $ 3,211.20
                  5/03 3,136.20
       1,201.12 5/05 4,337.32
                  5/09 3,501.17
                  5/11 3,101.17
                  5/11 3,052.10
                  5/16 2,992.09
                  5/16 2,952.09
            3.22 5/24 2,955.31
                  5/24 2,947.71
                  5/24 2,935.71




                                                             Compare the list of checks paid by the bank with your records. List and total
                                       Checks Outstanding                                             yet paid.
                                    Number     Amount
                                                              (1) Enter new balance from bank statement:

                                                              (2) List any deposits made by you and
                                                                  not yet recorded by the bank:


                                                              (3) Add all numbers from lines above.
                                                                                                        Total:
                                                              (4) Total of checks outstanding:
                                                              (5) Subtract (4) from (3). This is the adjusted balance

                                    Total

                                                               To reconcile your records:
                                            (6) List your checkbook balance

                                              Write the total of any fees or charges deducted by the bank
                                         (7)     and not yet subtracted by you from your checkbook:                  -
                                         (8) Subtract line (7) from line (6).
                                         (9) Add interest credit:                                                   +
                                        (10) Add line (9) to line (8). This is the checkbook balance.
k with your records. List and total the checks not
et paid.




                                +
                                +
                                +
                                +


                                -
adjusted balance




                                     Adjusted bank
                                     balance and
                                     adjusted
                                     checkbook
                                     balance MUST
                                     be equal
                      Business Checkbook                                               Bank Statement
                           Amount of             Amount of
Check         Check Issued Check or     Date of Deposit or                          Check and
No.     Date To            Debit      Deposit Credit      Balance      Check No.  Debits
                                       Balance Forward ==> $ 3,538.32
 1256    5/01 Name 1         108.50                         3,429.82         1257      75.00
 1257    5/02 Name 2           75.00                        3,354.82              
 1258    5/08 Name 3         218.62                         3,136.20         1259     836.15
              Deposit                      5/10 1,201.12    4,337.32         1260     400.00
 1259    5/15 Name 4         836.15                         3,501.17   RC                49.07
 1260    5/16 Name 5         400.00                         3,101.17         1262      60.01
 1261    5/18 Name 6           25.33                         3,075.84   ATM             40.00
 1262    5/23 Name 7           60.01                        3,015.83   IC
 1263    5/24 Name 8           97.37                         2,918.46   SC                7.60
              Deposit                       5/26   865.50    3,783.96   CP               12.00
ATM      5/26                  40.00                        3,743.96
Bank Statement

      Deposits   Date Balance
                  5/01 $ 3,211.20
                  5/03   3,136.20
       1,201.12 5/05     4,337.32
                  5/09   3,501.17
                  5/11   3,101.17
                  5/11   3,052.10
                  5/16   2,992.09
                  5/16   2,952.09
            3.22 5/24    2,955.31
                  5/24   2,947.71
                  5/24   2,935.71




                                                                Compare the list of checks paid by the bank with your records. List and tota
                                       Checks Outstanding                                                yet paid.
                                    Number     Amount
                                       1261             25.33   (1) Enter new balance from bank statement:
                                       1263             97.37
                                                                (2) List any deposits made by you and
                                                                    not yet recorded by the bank:


                                                                (3) Add all numbers from lines above.
                                                                                                          Total:
                                                                (4) Total of checks outstanding:
                                                                (5) Subtract (4) from (3). This is the adjusted balance

                                    Total       $    122.70

                                                                 To reconcile your records:
                                            (6) List your checkbook balance

                                              Write the total of any fees or charges deducted by the bank
                                         (7)     and not yet subtracted by you from your checkbook:                    -
                                         (8) Subtract line (7) from line (6).
                                         (9) Add interest credit:                                                      +
                                        (10) Add line (9) to line (8). This is the checkbook balance.
k with your records. List and total the checks not
et paid.


                                    $ 2,935.71

                                +       865.50
                                +
                                +
                                +

                                    $ 3,801.21
                                -   $ 122.70
adjusted balance                    $ 3,678.51



                                     Adjusted bank
                                     balance and
                $ 3,743.96           adjusted
                                     checkbook
                                     balance MUST
                                     be equal
                     68.67
                  3,675.29
                      3.22
                $ 3,678.51
TRUE
                                                                             Average
                                                                             Balance
                                                                             Less than
                                                                             $500.00
                                                                             $500.00 -
                                                                             $1,999.00
                                                                             $2,000.00 -
                                                                             $4,999.00
                                                                             $5,000.00 or
                                                                             more



Average Balance
Maintenance Charge per Month
Checks written
Charge per check
Per-Check Charge = Checks written * Charge per check =
Monthly Service Charge = Maintenance Charge per Month + Per-Check Charge =


Average Balance
Maintenance Charge per Month
Checks written
Charge per check
Per-Check Charge = Checks written * Charge per check =
Monthly Service Charge = Maintenance Charge per Month + Per-Check Charge =


Average Balance
Maintenance Charge per Month
Checks written
Charge per check
Per-Check Charge = Checks written * Charge per check =
Monthly Service Charge = Maintenance Charge per Month + Per-Check Charge =


Average Balance
Maintenance Charge per Month
Checks written
Charge per check
Per-Check Charge = Checks written * Charge per check =
Monthly Service Charge = Maintenance Charge per Month + Per-Check Charge =
                        VLOOKUP


Average Balance                   VLOOKUP #s
Less than $500.00                       $0.00
$500.00 - $1,999.00                    500.00
$2,000.00 - $4,999.00                2,000.00
$5,000.00 or more                    5,000.00
Maintenance
Charge per Per-Check
Month       Charge

    $12.00       $0.20

      7.50        0.20

      5.00        0.10

      0.00        0.00
Maintenance
Charge per   Per-Check
Month        Charge
      $12.00        $0.20
        7.50         0.20
        5.00         0.10
        0.00         0.00
Check                                     Amount of       Date of
No.      Date        Check Issued To      Check          Deposit
                                                              Balance Forward ==
  2097    06/05/10   Starbuck's                456.00
  2098    06/06/10   Office Depot              415.00
  2099    06/07/10   Whole Foods Market        157.00
  2100    06/07/10   Suix Trucking              59.00
          06/08/10   Deposit                                06/08/10
  2101    06/08/10   Macbeth Hardwoods         499.00
  2102    06/08/10   Chin Ho                   392.00
          06/10/10   Deposit                                06/10/10
  2103    06/11/10   Home Depot                411.00
  2104    06/11/10   Macbeth Hardwoods         490.00
  2105    06/11/10   Google                    251.00
  2106    06/11/10   Starbuck's                 36.00
  2107    06/12/10   Sheliadawn Smith          411.00
          06/12/10   Deposit                                06/12/10




Check                                     Amount of       Date of
No.      Date        Check Issued To      Check          Deposit
                                                              Balance Forward ==
  2153    08/28/10   Dunn Lumber               342.00
  2154    08/29/10   Dunn Lumber               223.00
  2155    08/30/10   Home Depot                344.00
  2156    08/30/10   Macbeth Hardwoods         350.00
          08/31/10   Deposit                                08/31/10
  2157    08/31/10   Office Depot               67.00
  2158    08/31/10   Home Depot                423.00
          09/02/10   Deposit                                09/02/10
  2159    09/03/10   Dunn Lumber               334.00
  2160    09/03/10   Dunn Lumber               113.00
  2161    09/03/10   Starbuck's                 53.00
  2162    09/03/10   Whole Foods Market        155.00
  2163    09/04/10   Chin Ho                   154.00
          09/04/10   Deposit                                09/04/10
Check                                        Amount of       Date of
No.      Date     Check Issued To            Check          Deposit
                                                                 Balance Forward ==
  2209    11/20/10                  #NAME?        170.00
  2210    11/21/10                  #NAME?         97.00
  2211    11/22/10                  #NAME?        372.00
  2212    11/22/10                  #NAME?        437.00
          11/23/10 Deposit                                     11/23/10
  2213    11/23/10                  #NAME?        111.00
  2214    11/23/10                  #NAME?         35.00
          11/25/10 Deposit                                     11/25/10
  2215    11/26/10                  #NAME?         86.00
  2216    11/26/10                  #NAME?        291.00
  2217    11/26/10                  #NAME?        351.00
  2218    11/26/10                  #NAME?        321.00
  2219    11/27/10                  #NAME?        117.00
          11/27/10 Deposit                                     11/27/10




Check                                        Amount of       Date of
No.      Date     Check Issued To            Check          Deposit
                                                                 Balance Forward ==
  2265    02/12/11                  #NAME?        398.00
  2266    02/13/11                  #NAME?        115.00
  2267    02/14/11                  #NAME?         58.00
  2268    02/14/11                  #NAME?         45.00
          02/15/11 Deposit                                     02/15/11
  2269    02/15/11                  #NAME?        484.00
  2270    02/15/11                  #NAME?        139.00
          02/17/11 Deposit                                     02/17/11
  2271    02/18/11                  #NAME?        129.00
  2272    02/18/11                  #NAME?        324.00
  2273    02/18/11                  #NAME?        191.00
  2274    02/18/11                  #NAME?        261.00
  2275    02/19/11                  #NAME?        446.00
          02/19/11 Deposit                                     02/19/11
Check                                        Amount of       Date of
No.      Date     Check Issued To            Check          Deposit
                                                                 Balance Forward ==
  2321    05/07/11                  #NAME?        277.00
  2322    05/08/11                  #NAME?        454.00
  2323    05/09/11                  #NAME?         34.00
  2324    05/09/11                  #NAME?        138.00
          05/10/11 Deposit                                     05/10/11
  2325    05/10/11                  #NAME?        224.00
  2326    05/10/11                  #NAME?        205.00
          05/12/11 Deposit                                     05/12/11
  2327    05/13/11                  #NAME?        162.00
  2328    05/13/11                  #NAME?        118.00
  2329    05/13/11                  #NAME?        462.00
  2330    05/13/11                  #NAME?        269.00
  2331    05/14/11                  #NAME?        165.00
          05/14/11 Deposit                                     05/14/11
      Amount of
      Deposit       Balance
Balance Forward ==>       3587.77




            588.00


            612.00




          2,116.00




      Amount of
      Deposit       Balance
Balance Forward ==>       3316.07




            516.00


          1,974.00




          1,483.00
      Amount of
      Deposit       Balance
Balance Forward ==>       5141.22




            844.00


          1,097.00




          2,242.00




      Amount of
      Deposit       Balance
Balance Forward ==>       6132.46




          1,615.00


          2,430.00




          1,203.00
      Amount of
      Deposit       Balance
Balance Forward ==>         6678




          1,933.00


            744.00




            881.00
       Assumptions
     Start Check #        2097
     Start Date           3809
 1   Home Depot
 2   Office Depot
 3   Starbuck's
 4   Suix Trucking
 5   Chin Ho
 6   Sheliadawn Smith
 7   Google
 8   Whole Foods Market
 9   Dunn Lumber
10   Macbeth Hardwoods




       Assumptions
     Start Check #        2153
     Start Date           3893
11   Home Depot
12   Office Depot
13   Starbuck's
14   Suix Trucking
15   Chin Ho
16   Sheliadawn Smith
17   Google
18   Whole Foods Market
19   Dunn Lumber
20   Macbeth Hardwoods
       Assumptions
     Start Check #        2209
     Start Date           3977
21   Home Depot
22   Office Depot
23   Starbuck's
24   Suix Trucking
25   Chin Ho
26   Sheliadawn Smith
27   Google
28   Whole Foods Market
29   Dunn Lumber
30   Macbeth Hardwoods




       Assumptions
     Start Check #        2265
     Start Date           4061
31   Home Depot
32   Office Depot
33   Starbuck's
34   Suix Trucking
35   Chin Ho
36   Sheliadawn Smith
37   Google
38   Whole Foods Market
39   Dunn Lumber
40   Macbeth Hardwoods
       Assumptions
     Start Check #        2321
     Start Date           4145
41   Home Depot
42   Office Depot
43   Starbuck's
44   Suix Trucking
45   Chin Ho
46   Sheliadawn Smith
47   Google
48   Whole Foods Market
49   Dunn Lumber
50   Macbeth Hardwoods
                                      Determine deposits with Credit Card Transactions

Credit card sales slips
Credit slips
Gross amount for deposit

But is this the amount that is deposited in your bank account?

Credit card fee rate =
Credit card fee =
Total amount that will be deposited in the merchants account = 'Credit to your account' =


                                       Calculate the discount fee on credit-card deposits



Boomerangs sold =
Boomerang price each =
Invoice Total =
Sales Tax =
Total
Credit card fee =
Credit card company deposits the 'Credit to your account' in
Business Checking Account =
Rates
                   Business Checkbook                                           Bank Statement
                        Amount of           Amount of
Check      Check Issued Check or    Date of Deposit or                         Check and
No.   Date To           Debit      Deposit Credit     Balance     Check No.  Debits
                                   Balance Forward ==>   2782.95
Bank Statement

       Deposits Date Balance




                                                        Compare the list of checks paid by the bank with your records. List and total th
                                  Checks Outstanding                                             yet paid.
                               Number     Amount
                                                         (1) Enter new balance from bank statement:

                                                         (2) List any deposits made by you and
                                                             not yet recorded by the bank:


                                                         (3) Add all numbers from lines above.
                                                                                                   Total:
                                                         (4) Total of checks outstanding:
                                                         (5) Subtract (4) from (3). This is the adjusted balance

                               Total

                                                          To reconcile your records:
                                       (6) List your checkbook balance

                                         Write the total of any fees or charges deducted by the bank
                                    (7)     and not yet subtracted by you from your checkbook:                  -
                                    (8) Subtract line (7) from line (6).
                                    (9) Add interest credit:                                                   +
                                   (10) Add line (9) to line (8). This is the checkbook balance.
k with your records. List and total the checks not
et paid.




                                +
                                +
                                +
                                +


                                -
adjusted balance




                                     Adjusted bank
                                     balance and
                                     adjusted
                                     checkbook
                                     balance MUST
                                     be equal
Step 1: List Details and Variables
  Credit card sales slips                                          $   8,752.40
  Credit slips                                                           573.94
  Credit card fee rate =                                                  2 1/2% <== custom number format: 0 ?/?%
  Bank Statement Balance                                               4,228.34
                                 Checks Outstanding
  C#?                                                                    758.14
  C#??                                                                    38.37
  C#???                                                                 1671.88
  C#????                                                                 120.13
  C#?????                                                               2264.75
  C#??????                                                                78.11
  C#???????                                                             3662.73
  C#????????                                                             816.25
  C#?????????                                                               400
                                    Bank Deposits
  BD#?                                                                   458.23
  BD#??                                                                  771.18
  BD#???                                                                 235.71
  BD#????                                                               1278.55
  BD#?????                                                               663.52
  BD#??????                                                             1475.39

Step 2: Set up and solve. AND Step 3: Write answer in words.
  Credit card sales slips                                          $   8,752.40
  Credit slips                                                     $     573.94
a Gross amount for deposit =                                       $   8,178.46
  Credit card fee rate =                                                  2 1/2%
  Credit card fee =                                                       204.46
  Total amount that will be deposited in the merchants account =
b 'Credit to your account' =                                       $   7,974.00

  C#?                                                                    758.14
  C#??                                                                    38.37
  C#???                                                                 1671.88
  C#????                                                                 120.13
  C#?????                                                               2264.75
  C#??????                                                                78.11
  C#???????                                                             3662.73
  C#????????                                                             816.25
  C#?????????                                                               400
c Outstanding Checks Total =                                            9810.36

  BD#?                                                                   458.23
  BD#??                                                                  771.18
  BD#???                                                                 235.71
  BD#????                                                               1278.55
  BD#?????                                                               663.52
  BD#??????                                                             1475.39
d Total for deposits that were not recorded =                      $   4,882.58

   Bank Statement Balance                                              4,228.34
   Outstanding Checks Total =                                          9,810.36
   Total for deposits that were not recorded =                         4,882.58
  Total amount that will be deposited in the merchants account =
  'Credit to your account' =                                           7,974.00
e Balance in Ms. Fulgham's checking Account =                          7,274.56
m number format: 0 ?/?%

				
DOCUMENT INFO
Description: Calculate Credit Card Payment in Excel document sample