A Checkbook Example

• This covers material from chapter 11 of the book.
• A checkbook tutorial is available here
• Side note:
  • We will do a number of financial type computations in the upcoming sections.
  • I am not a financial expert, but I have lived.
  • Conditions with banks and financial institutions change, you need to make sure you understand their rules.
• A checking account is a standard financial tool you use.
  • You deposit money into this account.
    • You do this with a deposit slip
    • 
    • You can also have regular payments to you directly deposited in this account electronically.
  • You can then write "checks" to transfer this money to others.
  • 
  • Frequently there is a debit card associated with this account too.
  • You use the debit card like a credit card except
    • You can/should not spend more than you have in the account.
    • You may have to supply a pin.
  • You can also withdraw money from this account.
  • You can also set up to have regular payments from you deducted from this account electronically.
• Depending on the account you
  • May earn interest, but not likely.
  • May have to maintain a minimum balance or be charge a fee each month.
  • May have to other fees.
  • Will definitely be charged a penalty if you spend more than you have in the account.
    • This is called an overdraft.
    • Or a bounced check.
    • And both the bank and the person you wrote the check to can charge you for this.
  • In order to avoid bounced checks, you (should) regularly balance your account.
  • This is done in a check register.
    • 
    • When you buy checks, yes you have to buy them, you receive a register and deposit slips.
• Look at the register
  • One transaction per line
  • There is a place for:
    • The check number.
    • For the date of the Transaction.
    • A description of the transaction.
    • The amount withdrawn.
    • To mark that the bank has processed the withdraw.
    • The amount deposited.
    • The running balance.
• There is also a place for the starting balance for the page.
• Every time you have a transaction, you should record it in your check register
  • Both deposits and withdraws.
  • And compute the running balance
  • Add in deposits, subtract withdraws.
    
  • Keeping your balance up to date helps you know how much money is in the account.
  • Once a month, your bank will provide a statement of transactions.
    • This should list all of the transactions which have cleared.
      • Some checks take longer to clear.
      • Sometimes the person you wrote the check to will fail to cash it.
    • You should go through your register and make sure that
      • You have accounted for all transactions.
      • All transactions are correct
      • This prevents errors and fraud.
  • Building a check register is a natural activity in excel.
    • Excel is an example of spreadsheet software.
    • It is primarily used for mathematical computations.
    • But it can also be used for basic database processing.
  • Originally designed for this type of work (ledgers)
  • Consists of rows and columns.
    • Rows have a number
    • Columns have a letter
  • 
  • The intersection of a row and column is a cell
    • Cells are named by the column letter and row number (A1, B3, ...)
  • Cells can contain
    • Labels
    • Data
    • Computations.
  • When building a spreadsheet it is very important to label everything.
    • You might know what you are doing in a cell right now.
    • But others won't
    • And if you go to use it again in a month, you will not remember either.
  • Let's duplicate the headings in the register
    • In cell A1 enter "Number" (without the quotes)
    • B1: "Date"
    • C1: "Transaction"
    • D1: "Withdrawal"
    • E1: Put a checkmark, insert symbol, wingdings
    • F1: Deposit
    • Do some basic formatting.
      • Make the columns the right widths
        • Drag or click.
      • Make all bold and centered.
      • Add a thick border under all.
    • Add a line border F1 as well.
    • 
  • We should add a title space in row 1.
    • Highlight A1:G1
    • We specify ranges of cells with a : between the letters.
    • Grab the green border and move everything down one row.
    • Highlight the newly blank A1:G1 and click on the Merge and Center button in the Alignment group of the home row.
    • Enter "February 2018" in the new single cell.
      • Centered 14 point bold.
      • Changing the date format.
        • Quick on the home tab, number group, drop down box.
        • Far more right click in cell, select Format Cell
          • Select Number, Date, And a Format
          • Or even a custom date format.
      • 
  • Data
    • Data can be numbers, text or even other things.
    • We will enter the starting balance in cell G2.
      • Make this $500
      • Note the formatting options here as well.
    • Enter the following
      • Check number 1001
      • Date 2/1/14
      • Description Rent
      • Withdraw $425
  • Simple Computation
    • In cell G3 we want to compute the new balance.
      • This is found by subtracting the Amount withdrawn from the current balance.
      • Or cell G2 - D3
      • In Cell G3 enter "=G2-D3"
      • Note, you can also do this by clicking in the cells after you hit =
      • The result should be $75
      • But you should mentally calculate this as well just to make sure.
    • You should
      • Never do a computation by hand and enter the result, you should always allow excel to do this.
      • Always make sure the computation is correct by checking by hand
      • Always format the cell properly.
      • Make sure everything is clearly labeled.
    • Add a deposit
      • In row 4
      • No number
      • Date 2/1/2018
      • Paycheck Deposit
      • Amount $700 in the Deposit cell (F4)
    • Compute the new balance (=G3+F4)
    • Add another three transactions
      • Check 1002, Discover Card Services, 2/2/18, $200
      • Check 1003, Giant Eagle, 2/3/18, $57.34
      • Dan's Share of Rent, 2/3/18, $225.00
    • Enter the following formula in G4 (=G4-D5+F5)
      • Click in the box to highlight
      • Select the fill handle in the lower right hand corner.
      • Drag this down to cell G7.
      • Go back and enter this formula in G3 and G4
    • Note:
      • Excel will change the row number each time you copy down.
        • This is desired behavior in this case.
        • Later we will need to override this.
      • Excel treats an empty cell as the value 0.
      • If you put a blank in a cell, "clear cell" will help.
      • If a cell is not wide enough, #### will be displayed, just fix it.
      • Copying formulas is preferable to retyping them.
    • You can look at the formulas by typing ctrl-~
      • You can also do this with the Formulas tab, Formula Auditing Workgroup, Show Formulas button.
  • Let's assume that we are done for the month
    • We would like to do a double check.
    • We will use a function to do this.
    • Draw a thick border under row 7
    • In D8 enter =sum(D3:D7)
    • In F8 enter =sum(F3:F7)
    • In G8 enter =G2-D8+F8
    • In cell C8 enter "Double Check"
  • If everything is good, G7 and G8 should be the same.
  •