transactions that have reached Visa and appeared on the statement whose number
is contained in cell G2, labelled “Last Statement Received.”
4
The formula either
copies the statement balance from the cell immediately above (if this
transaction’s statement number is
larger
than the statement number in
cell G2)
or
adjusts 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 to
the 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 spreadsheet
Whenever 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, and
add 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
formula
in column I; the formula decides whether to estimate the Canadian
CS 200 Excel and Excel Macros
Page 6
4
Recall 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.