CS200 Assignment 7 – Excel Macros Due Monday June 25th 2018, 11:59 pm
Readings and Resources
On Learn: Excel Review
Texts: Learning Excel Macros, Steve Roman, O’Reilly
Other Resources: https://www.student.cs.uwaterloo.ca/~cs200/Commented_Excel.shtml
Files
AccountingData.xls: Accounting Demo:
Excel’s Online Help
You are provided a spreadsheet with data that you will be manipulating and enhancing with formatting, formulas and macros.
Download this file. Open the file Accounting.xls. When you open the file, you should see a dialog that says “The workbook you opened contains automatic links to information in another workbook”. Click “Update”. If it opens a Finder window, make sure AccountingMacros is selected and then click “Grant Access”. When you try to run one of the macros, you should see a dialog that says “This workbook contains macros. Do you want to disable macros before opening the file?”. You want to have access to these macros, so click “Enable Macros”. You should now be able to click on each of the buttons to see how the macros work.
Assignment Objectives
• To refresh your knowledge of basic spreadsheet operations, such as cell addressing, formatting, and formulas.
• To expand your understanding of how spreadsheets should be designed and how they can be made useful.
• To introduce you to the idea of using simple macros to enhance the usefulness of a spreadsheet.1
• To make it as easy as possible for you to keep track of your finances and to catch bank errors.
Important Information Before You Start
• You will be working with a workbook that manages a credit card account. We will supply data you can work with, but you are welcome to use data of your own. The example we will show you is based on a workbook used by the course creator (JCB) to manage his Visa account; our hope is that you will find this assignment useful in managing yours.
1 Many contemporary applications have associated macro languages; one purpose of this assignment is to encourage you to think of checking for the existence of a macro language, and considering its use, whenever you find yourself doing something repeatedly in an application. The existence of a macro language might also be a factor in deciding whether to purchase an application.
CS200 Assignment 7 Page 1 of 10
• The underlying premise is that reconciling your credit card account is something you need to do regularly, so that it is cost-effective to minimize the work involved.
• Your task for this assignment is to modify a starter workbook or create one of your own that performs like the example solution.
Assignment Strategy
• This assignment must be done individually.
• You should read over the entire assignment instructions before starting. It will be helpful to
read it over in entirety multiple times while working on the assignment.
Assumptions
You are assumed to have some experience with spreadsheets. Beyond the general idea of what they are and how to use them for simple tasks, here are some specific topics and terms you are assumed to be familiar with already.
• Cell addresses: relative, absolute, and mixed. (Such as A1, $A$1 and A$1 in Excel.)
• Cell ranges. (Such as A1:C10 in Excel.)
• Named cells and ranges.
• Cell formulas, and what happens to them when you copy/paste or fill formulas up/down/left/right.
• Using built-in functions such as SUM() or VLOOKUP() in cell formulas.
• Formatting spreadsheets: using italic and bold labels, justifying cell contents, drawing cell
borders, etc.
What the Spreadsheet will Do
• To automate reconciling your Visa statements when they arrive each month. Are there unauthorized charges? Has an incorrect amount been recorded?2
• To ensure that you always know how much money you owe. Can you afford a new laptop? A trip to Florida? A spiffy new Jaguar?
• To make it easy to re-do a reconciliation for previous months.
• To keep track of credit card receipts, so that you can easily produce them for inspection if a dispute should arise or you need to attach them to a request for reimbursement from your employer.
2 All a merchant needs to post a charge to your credit card is your account number. If you don’t examine your statements closely, you may not notice an unauthorized charge, or may not remember whether the amount is correct. I have, for example, caught a hotel charging me after I checked out for movies I never watched, and caught the New York Times charging me twice for a subscription. [JCB]
CS200 Assignment 7 Page 2 of 10
Your Tasks
Cells and ranges must be named appropriately throughout the assignment.
1. (35%) Formulas to Create.
• Actual Balance (Column K): This column contains a formula that computes how much money you owe after the purchase or payment recorded on the same line, regardless of which earlier transactions have been received (“posted”) by Visa. In other words, it tells you how much money you owe at that point, assuming that all earlier transactions are eventually recorded by the bank (“posted”).
• Statement Balance (Column L): This cell contains a formula that reflects the accumulated effect only of transactions that have reached Visa and appeared on the statement whose number is contained in cell G2, labelled “Last Statement Received.”3
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 discrepancy. By changing the value in cell G2 you can easily cause the Statement Balance column (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.
• Data Entry Sequence Number DES# (Column A): During development it’s convenient for this column to contain sequence numbers reflecting the order in which each row was added to the spreadsheet. When testing you can then easily return the spreadsheet’s data to its original order. In a “deployed” version of the worksheet you would likely leave this column blank. The formula in cell A157 automatically computes the next entry sequence number. Make this cell green.
• File Reference Number FR# (Column B): This is a sequence number that you assign and write on each charge receipt when you enter it into the workbook. You tuck the receipt away in an envelope; the sequence number allows you to locate it easily. The formula in cell B155 automatically computes the next reference number for you — all you have to do is remember to bring the slip home and write the number on it.
3 Recall that if a transaction has not yet appeared on any statement then the statement number for that transaction has the value 100000.
CS200 Assignment 7 Page 3 of 10
(This it not the same thing as the DES# because you don’t have a paper receipt for every transaction, and therefore do not have a file reference number for online purchases, or for payments made by you to your Visa account.)
• CD Charge Column (where the charge is in US dollars) (Column I): If the purchase was made in Canadian funds — the usual case — you enter the amount of the purchase here and the cells in columns G and H are left empty.
If, on the other hand, the purchase was made in US funds, then a formula is entered into this cell that causes it to display the amount entered in column H (CD Funds) if the CD Funds cell is not empty. Failing that, it will display an estimate of the current exchange rate (held in cell G4) times the amount of the charge in US currency from the cell in column G (US Funds).
However, in the latter case, you cannot know exactly how much you have accumulated in Visa charges when you have made US-funds purchases, at least until the next statement arrives.
2. [35%]Createthefollowingsimplemacros:
•
•
• • • •
•
3.
• • •
Top navigation
Bottom navigation
Filtering
Sorting the data by Data Entry Sequence number (DES#)
Sorting the data by Purchase Date (Pur_Date)
Sorting the data by Statement Number (SN) and Posting Order (PO)
Create buttons for all macros and attach the macros to them.
[30%] Create the following complex macros4:
Insert new rows for a US purchase
Insert new rows for a Canadian purchase (entering appropriate formulas where needed) Create buttons for all macros and attach the macros to them.
Submission Instructions
• Rename it yHC_Assign7.zip and submit it to the Assignment 7 DropBox on Learn.
4 You will want to use “R1C1” cell addressing in these formulas, rather than “A1” cell addressing. The difference is discussed on page 12.
CS200 Assignment 7 Page 4 of 10
What do the other Columns Mean?
Trans_Date(ColumnC): Thetransactiondateisthedateyoumadethepurchaseorpayment.
SN (Column D): You assign the next statement number to each successive monthly statement you receive. Thus all the transactions appearing on my April 2006 statement have the statement number 250, and those that appeared on the May 2006 statement have the statement number 251.
***Transactions that haven’t yet appeared on a statement have a very big statement number — 100,000 — entered for them when the transaction is created on the spreadsheet (presumably before the statement arrives!). This number is too wide to fit in column D, so it displays as “###,” which makes such transactions easy to find. 100,000 is also (much) larger than the sequence numbers we assign to the statements we receive (1, 2, …, 250, 251, etc.), and we take advantage of this in computing statement balances (column L).***
PO(ColumnE): Inthepostingordercolumnyourecordtheorderinwhichatransactionappeared on a particular statement. Hence it is left blank when the transaction is first recorded, and you enter values beginning with 1 as you work your way down a monthly statement, locating the corresponding entry in your spreadsheet.
Why bother? Well, your spreadsheet and the statement typically won’t list transactions in the same order. If your spreadsheet’s final statement balance doesn’t match that on the statement, you can use this field (and the statement number field) to sort your spreadsheet so that the transactions are shown in the same order, which makes it much easier to go back and find the discrepancy.
Payee (Column F): This is a short text field, into which you type the person or organization from whom you made a purchase, or identify the nature of any other transaction.
US Funds (Column G): If the purchase was made in US funds, the amount of the purchase in US dollars is entered here. If the purchase was made in Canadian dollars, this cell is left blank.
CD Funds (Column H): This cell is left empty for purchases in Canadian dollars. For U.S. purchases, it is left blank until the Visa statement arrives — the amount in Canadian dollars to which it was converted is shown on the statement, and you enter that amount here.
CD Charge (Column I): The amount of a charge in Canadian dollars goes here. If you made a purchase with US dollars, then this cell will have a formula to calculate the corresponding Canadian charge. (See Question 1 > CD Charge Column).
CD pay’t (Column J): The amount of a payment made to reduce your balance. Only one of columns I and J will contain a value for any particular transaction.
Column M: This provides a bit of space between the statement balance and comments columns, purely for the sake of readability.
Comments (Column N): Additional comments of any kind about the transaction.
The next un-entered sequence number and charge receipt number are shown in the bottom left
corner of the worksheet, in cells A157 and B155, respectively.
CS200 Assignment 7 Page 5 of 10
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 columns 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 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:
• 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.
• Verify also that the transaction amount shown on the statement is identical to the amount entered in the spreadsheet. If the transaction is in US dollars, the amount of the purchase will be shown both in US dollars (which you should compare with the amount you copied from the charge slip to your spreadsheet) and in Canadian dollars. In the latter case, enter the Canadian amount in column H.
• If — horrid thought! — you find a disagreement, you must examine your receipt to determine whose error it is, and either correct your spreadsheet or attack your credit card company and the merchant, as appropriate.
• If you find an item on the statement for which there is no row in your spreadsheet, you have a choice. If you believe the charge is legitimate, then you make a row in the spreadsheet for it. If you don’t recognize the charge, then you call Visa and dispute the charge. For purchases made in person you can demand a copy of the charge slip, showing your signature, and if the merchant can’t produce the receipt, the charge should be removed from your account. For online purchases the situation is less clear. If you argue plausibly, the charge will probably be removed, provided you don’t have a history of making such claims regularly. Often it is
CS200 Assignment 7 Page 6 of 10
worth discussing the charge first with the merchant, who may acknowledge an error and promise to tell Visa to credit your account. (But check your next statement!)
• Finally, you change the last statement number in cell G2 and examine the statement balance at the bottom of the spreadsheet. If it agrees with the ending balance on your statement, you’re done. If not, you need to determine whether it was you or your credit card company that made the mistake.
• To recheck your reconciliation most easily, sort your spreadsheet by statement number and posting order. This will bring together the transactions appearing on each statement and will list them in the order they appear on your statements so that you can easily recheck your work.
• Even when your spreadsheet and your statement agree, it’s still useful to sort your spreadsheet by statement number and posting order — doing so causes all the transactions that haven’t yet been posted to your account by the bank to move to the lower portion of your spreadsheet, beyond data for the last-received statement, where they’re more easily found.
Macros: Making Life Easy
You use this worksheet all the time, so it’s worthwhile automating it.
• The “Top” and “Bottom” buttons scroll you to the top and bottom of their respective spreadsheets, which is where you most often want to go.
• The “S” button for column A sorts the data by Data Entry Sequence Number, which is convenient during development.
• The “Sort” buttons for columns C and D-E sort the data by their respective columns. These buttons call macros that automate the process of selecting the data in the worksheet, calling up the Sort dialog box, and specifying the sort desired.
• The “Filter” button toggles data filtering on and off. Again, this avoids the need to first select the data and then the “Data → Filter → AutoFilter” menu item.
• The “New CD Entry” and “New US Entry” buttons automate the process of inserting a new row at the bottom of the data and entering (or more sensibly, copying) the necessary formulas.
Naming Cells
• The model solution defines a name for the range of cells that contain transaction data (Visa_Data), and this definition includes the blank line immediately below the data—row 150 in Figure 1, for example (though this may differ in your own spreadsheet). Why? Because if you select this row and then select the menu item “Insert → Rows,” Excel will automatically extend the definition of Visa_Data by one row because the row you selected is part of Visa_Data. Why is this a good thing?
Should the row of column headers be part of Visa_Data?
CS200 Assignment 7 Page 7 of 10
Actually, you may find it useful to name a range that includes the headers and a range that excludes both the headers and the “Initial Balance” row. Can you see why?
Design Requirements5
• The Pur_Date values are formatted in Monaco, a constant-width font, so that day names, months, and years will line up vertically. If you use a proportionally spaced font these columns look ugly. They are centered so that they do not jostle up against the dotted lines that separate columns, again for readability.
• What else has been done to enhance the readability of the worksheet?
• Although monetary values should generally be displayed with an appropriate currency symbols (e.g. $), they are omitted on this worksheet to reduce column width and, minimize the need for horizontal scrolling.
What Not to be Confused by
• The date in column C of a given row is the date on which the charge or payment recorded in the rest of the row was made. So when you sort by Pur_Date, you sort on Pur_Date, and re-order data in all the other columns, too. The other Sort buttons behave in the same way.
• The yellow background you see in Figure 1 has been applied to the data for statements 250 and 252 for readability. Doing so is not, however, a requirement for the assignment.
R1C1 vs A1 Cell Addressing
You are probably accustomed to writing cell references
in formulas using “A1” notation, such as
“=$A$3+C3+SUM(F3:G5)”. The advantage of A1 notation
is that it is easy to determine which cells are being
referenced, since the worksheet’s rows and columns are
labeled with row numbers (1,2,3,…) and column letters
(A,B,C,…), regardless of the cell into which the formula
has been entered. The “$” is used to indicate whether
the row or column reference is absolute (the “$” is present) or relative (the “$” is absent).
Relative references such as C6 are recorded within Excel as an offset relative to the cell containing the formula, so that if you copy the formula and paste it into another cell, each relative cell address refers to a new cell located at the same offset relative to the (cell containing the) new formula as it was to the (cell containing the) old formula.
There are circumstances — and this assignment is one of them — when you will find it convenient to represent this offset explicitly, and Excel has a notation for doing so that we will call “R1C1 notation.”
To see the formulas in a spreadsheet represented in this way, select the “Excel → Preferences…” menu item and click in the R1C1 Reference Style check box in the General tab.
5 The questions in this section are for you to think about, not to hand in.
CS200 Assignment 7 Page 8 of 10
The formula =$A$3+C3+SUM(F3:G5), if entered into cell B5 below, would be displayed as = R3C1 + R[-2]C[1] + SUM(R[-2]C[4]:RC[5]
In this formula:
R3C1 is an absolute address referring to the cell in
row 3 of column 1.
R[-2]C[1] is a relative address referring to the cell 2 rows up (up because the row offset is negative — positive row offsets are down) and one row to the column to the right (to the right because the column offset is positive).
You can tell when R1C1 references will be displayed because the column headings on a worksheet will show column numbers (1,2,3,…) instead of letters (A,B,C,…).
Regardless of whether cell formulas are displayed in A1 or R1C1 format, however, you can always enter formulas into a cell using either format. You may find the ability to use R1C1 addresses useful in writing macros that automatically insert formulas into cells of a new data row; it is not, however, essential (i.e. there’s more than one way to do it).
Incidentally, when you choose to record a macro in Excel the rightmost button in the small dialog that appears will tell Excel whether to generate absolute or relative cell references (in either notation) in a macro you are recording.
CS200 Assignment 7 Page 9 of 10
Figure One
CS200 Assignment 7 Page 10 of 10