Let's Balance a Checkbook

• A checking account is a bank account where the owner has frequent and immediate access to the balance in the account.
• The owner may issue checks, or a document that requests funds from their account be transferred to another person or entity.
• Most checking accounts are also associated with a debit card.
• 
• In either case, money is withdrawn from the account when a check or debit card is used to pay for something.
• The owner needs to make sure that there are sufficient funds in the account to cover these withdraws.
  • Banks will sometimes provide "overdraft protection"
  • But this will normally cause you to pay a fee.
  • If not, the check will "bounce" and you will at least certainly be charged a heavy fee.
• Checking accounts sometimes pay simple interest.
• Banks sometimes charge fees for checking accounts.
• In order to ensure you don't have an overdraft, it is important to balance your checking account.
• This is done in a number of stages
  • 
  • Each time you have a transaction, write a check, are charged a fee, are credited with interest or make a deposit, you should record the transaction in the register.
  • Regularly you should "do the math" or balance your checkbook by adding in the deposits and subtracting the withdraws.
  • When you receive a statement, you should check reconcile your account or check to see if your account balance matches the bank.
  • If it does not, you need to find the error
    • Double check everything.
    • Perhaps talk to your bank.
• We can set up a spreadsheet to automate this task in excel.
• Looking at the above register, it matches a spreadsheet very closely.
• Set up columns for
  • Check number
  • Date
  • Transaction
  • Payment
  • Cleared Bank
  • Deposit
  • Balance
• Format them as follows
  • Mark "Wrap Text" in the Alignment group of the home tab.
  • Mark them all centered
  • Mark them all bold
  • Adjust the field widths as appropriate.
  • 
• In the balance column at the top, record a starting balance.
  • This is either the carry over balance from the previous (page, worksheet, ...)
  • Or the starting balance for the account.
  • For us let's start with a balance of $300.
  • Place this in cell G2
• Then record transactions.
  • Today I purchased a gift certificate and breakfast from McDonald's for $22.96
  • I used Check 100
  • Enter this information in cells A3-D3
  • Format the money cell appropriately
  • In cell G3 enter the formula =g2-d3+f3
    • This computes a "running balance"
    • The previous balance is in cell g2
    • From that any withdraw is subtracted.
    • And any deposit is added.
  • Add a transaction with debit card, (DBT) for $8.00 for lunch at the faculty dining hall.
  • Add a deposit on 3/24 for $100
  • Copy the formula down from G3 to g4:g5
    • Look at the formula, notice how it changes.
    • This is what we would like.
  • Let's add two more checks
    • 101, 3/25, Books from Amazon 27.63
    • 102, 3/30, Clothes from Walmart, $50.25
    • 
  • Assume that this is the end of the month.
  • Let's add a sanity check
    • Draw a line under the last check entry.
    • Add a "Totals" row below that
      • In Cell C8 add Totals
      • In Cell D8 add =sum(D3:D7)
      • In Cell F8 add =sum(F3:F7)
    • Add "End Balance" in D10
    • In E10 add =G2-D8+F8
    • In D11 add "Error"
    • In E11 add =E10-G7
    • 
  • I want to repeat this for each month, so
    • Rename the worksheet to March 17
    • Add a new worksheet April 17
    • Copy and paste the first few lines from March to April.
      • Erase the data.
      • Make3 G2 on April a link to G7 on March.