transactions that have reached Visa and appeared on the statement whose number is contained in cell G2, labelled “Last Statement Received.”4The formula eithercopies the statement balance from the cell immediately above (if this transaction’s statement number is largerthan the statement number in cell G2)oradjusts the statement balance from the cell above by the amount of the withdrawal or deposit in this transaction (if this transaction’s statement number is less than or equal tothe statement number in cell G2). Ordinarily, as the label “Last Statement Received” implies, G2 contains exactly that: the sequence number you assigned to the last statement you received. However, sometimes it’s convenient to “back up” one or more statements, perhaps because you suspect yourself of having made an error when entering transaction data that appeared on a previous statement, or because you failed to notice an earlier discrepency. By changing the value in cell G2 you can easily cause the Statement Balance column L to show the effect of transactions that appeared on any statement up to but not beyond any statement you choose, making it easy to redo the process of reconciling that statement.M This provides a bit of space between the statement balance and comments columns, purely for the sake of readability.N (Comments) Additional comments of any kind about the transaction.The next unentered sequence number and charge receipt number are shown in the bottom left corner of the worksheet, in cells A157 and B155, respectively.How to use the spreadsheetWhenever you make a purchase:• add a row to the bottom of the spreadsheet (i.e. after the last transaction), entering appropriate formulas in K and L, and:– if you have a receipt ...enter the next file reference number in column B,write that file reference number on the receipt, andadd the receipt to your accumulated receipts(keep these in order so you can find a particular receipt easily);– enter the date in column C;– enter 100,000 in column D;– enter the payee in column F.• If the entry is in Canadian dollars, you enter the amount of the purchase in column I. If the entry is for a US-currency charge, you instead enter the US dollar amount in column G and a formulain column I; the formula decides whether to estimate the Canadian CS 200 Excel and Excel MacrosPage 64Recall that if a transaction has not yet appeared on any statement then the statement number for that transaction has the value 100000.
equivalent of the US value of the charge in column G, or to copy a Canadian equivalent value that you later enter in column H.When a statement arrives (see pages 11-12 for an example):• Assign it the next statement number in sequence.• Process each transaction listed on the statement, in order, assigning each a “posting order” sequence number. In particular, locate each such item on the statement, enter the statement number in cell D of the corresponding row on the spreadsheet, and enter the posting order sequence number in cell E.