For your third spreadsheet assignment you are to develop a macro driven credit card program. This credit card program should work for standard credit card accounts.
At the end of this assignment you will find several example views of one possible solution to this assignment. Your solution does not have to look like the example but it your solution must allow the user to:
- keep track of multiple credit card accounts on a single spreadsheet
o your solution must keep track of at least two separate credit card accounts
- enter purchases (or other debits) to a credit card
o debits include finance charges as read from the monthly credit card statement
o your spreadsheet should NOT calculate the amount of the finance charge
- enter payments (or other credits) made to a credit card company
- display all debits and credits for all credit cards
o when all credit cards are displayed the balance column should reflect a single running balance for all transactions made on all cards
- display only the debits and credits for a particular credit card
o when displaying a particular credit card the balance column should reflect the running balance for only the transactions that belong to the card being displayed
o do not use the Excel AutoFilter feature to limit the display, you should use the advanced filter approach as illustrated in the ss3-hints file.
o do not place the criteria range for the advance filter below the credit card transactions, doing this will limit the number of transactions that you can represent.
o Note – some of you may find restricting the running balance to a single credit card difficult. I suggest you allow the running balance to reflect all transactions by all cards on your first pass and, when the rest of the assignment is done, go back and try to restrict the balance.
- highlight and un-highlight the transactions belonging to a particular credit card
o The user should be able to color-code the transactions of a particular card.
The credit card spreadsheet must have the following columns:
- credit card identification code
- date of transaction
- description of transaction
- debit for purchases, finance charges and other debits
- credit for payments and other credits
- balance for the running balance
The credit card spreadsheet must:
- be macro driven
- have a separate macro button for each transaction type for each card
- enter each new transaction at the bottom of the register
o i.e. transactions must be entered in chronological order
- automatically move the cursor to the correct cell locations for each input value
- prompt the user for each value that user must entered
o provide default values when appropriate
- automatically enter formulas
- for example when the user presses the debit button for credit card A the program should
o automatically move the cursor to the empty row at the bottom of the register and place the cursor in the card code column. Note - the cursor should move to the correct position regardless of location of the cursor when the macro button is pressed.
o automatically enter the card code for credit card A
o move the cursor to the date column, prompt the user for the transaction date and offer the current date as a default value and then wait for the user to enter the date or accept the default value
o move the cursor to the description column, prompt the user for the transaction description and then wait for the user to enter the description
o move the cursor to the debit column, prompt the user for the debit amount and then wait for the user to enter the debit amount
o move the cursor to the balance column and automatically enter the balance formula
§ Note – your spreadsheet should be designed so that if the user corrects a transaction amount on the spreadsheet then the correct running balance is still displayed. This simply means that the cells in the balance column should contain formulas.
Terms
of the assignment:
- This is a group project.
- Maximum group size is three (3) members.
- You will turn in the following:
o Your Excel spreadsheet file.
o Your Excel file must be named using the filename conventions, see below.
- The assignment prefix is ss3
- Your spreadsheet file must be sent to me as an email attachment.
- The subject line on the email message must be: ss3 submission
- The assignment is due Friday, April 30.
- A 10% late penalty will be applied for each day the assignment is late.
Filename
Conventions:
The filename conventions are the same as those used in previous assignments.
The above figure shows all transactions for all credit cards.
This figure would also be the result of pressing the SHOW ALL CARDS button.
Note – if you look at this figure in Word you will see that
the Bank of
The above figure shows the result of pressing the SHOW AMERICAN EXPRESS button.