In this lab we will learn more advanced uses for Microsoft Excel, and, in particular, how to incorporate macros in spreadsheets. For this, you will be creating two new Banking Account spreadsheets that demonstrate these abilities. In addition to this, you will also be creating and working with a Bank Inventory spreadsheet that will handle statistics found in the previous two Account sheets.
In order to get started, we first introduce several new Excel utilities: Cell Referencing, Macros, and Range Values, which are explained below in turn.
- Find and check the Visual Basic toolbar under menu View->Toolbars...
- Begin recording a macro by clicking on the Record Macro button (the one with the red dot on it).
- Type a macro name in the Macro Name form box that is relevent to your particular task (e.g., bolding and changing font to 16 could be called Font16_Bold). By choosing Options>>, you can pick a shortcut key for your macro. (For example, the default is "e". Hence, [Ctrl-e] will run your macro on the active worksheet.) You can also place your macro in your own personal macro workbook if you wish. (Note that because you are working in the lab, you will have to save this to your disk if you want to reuse your macro.) Click OK to begin recording steps.
- You are then free to manipulate your spreadsheet as you wish. A Macro Stop button should have appeared also (which has a black box on it). You will want to click on this (or the one in the Visual Basic toolbar) to stop recording steps for your macro.
- Once you have created a macro, you can run it by clicking on the Macro Play button (the one with the white arrow pointing right). All of your recorded steps should be reaccomplished.
- You may also look at how the computer interpretted your commands by looking at the Macro. By going to the Window menu, you will be able to go to the window that your macro is recorded in. Look through the code and see if you can understand what the computer is doing, and why it phrased it the way it did.
You will now be asked you to create new worksheets for two bank accounts. Your sheets should tally a growing and shrinking balance based on the amounts deposited or withdrawn over a given year, taking into account an annual interest rate.You should do the following: Start a Macro Recording for a new spreadsheet, and give it a shortcut key sequence of [Ctrl-b]. (Make sure to name your macro appropriately.) When creating your sheet, deposit a set amount each month (from your monthly wage) and make withdrawals for rent, food, school, automobile, and other expenses. You may decide to vary these expenses over each month. The interest rate should not be fluctuant, and should remain stable for the given year. Set this to 6%. Fill in the column data for the following columns in your sheet: Deposits,Withdrawals, Interest, and Balance.
Place a Total heading at the bottom of your sheet and sum up the totals for each of the above columns except Balance. Put a title on the top of your sheet called "Banking Account Log". Format your sheet by making each of the column headings (and the totals) bolded and underlined. Center the column headings and make the balance after each month have red font color.
When you are finished with this, click on the Stop Macro button on the Visual Basic macro toolbar to finish your macro.
Now, go ahead and begin a new spreadsheet (of identical format) for a similar banking account, substituting different values for the monthly deposits, and withdrawals. (You may want to create this one on its own sheet to correspond to your macro.) We will assume that both the account created in Part I and this one exist under the same bank, so the interest rate will remain the same. This time, however, use your newly created bank account macro to accomplish the task much more expediently. If you have saved your macro with the suggested shortcut, you may begin this process on your new spreadsheet by typing the sequence [Ctrl-b]. (Alternately, you may go under Tools-->Macro to select your named macro and then click on Run, or click the Play button on the macro toolbar.) If you have specified your macro correctly and given enough starting info on your spreadsheet for it to manipulate, your new account spreadsheet should be of identical form to your first one, with different account statistics.
You are now ready to tally the information from the two bank account spreadsheets into a single bank inventory.As a bank, there are certain pieces of information that would interest you, and the spreadsheet you create should reflect this. More specifically, you would want to know: who owns each account, how much money they have in the account, the interest rate of their account, and the amount of money they have deposited or withdrawn every quarter (3 month period), with associated interest.
Additionally, you would want to know the maximum deposit and withdrawl that was made at your bank over this past year. Sounds as though this might involve a formula, huh???
Finally, a chart should be added, or appropriate size, location and style, that shows the balances every month for all of the bank accounts. This will give the bank further ideas of when people need their money, so they can have it handy.
This spreadsheet should be made to look nice, and be presentable at a business meeting. It is up to you how to make it look, but points will be deducted for having badly presented information. Additionally, the instructor must come around and ensure that you have formulas in the correct spots. A few warnings regarding this point are:
- The bank sheet should have no numbers inputted whatsoever. Everything is generated from formulas based on numbers from itself, or the account sheets
- By now formulas should be familiar enough to you, that you no longer need to add each cell by hand in a column. Some type of automatic summing should take place
- An absolute reference should be added with respect to the interest rate of the account. The interest rate should only be located in one place for each account, and not added in each time.
This assignment was made to be difficult on purpose, and most of the lab will be devoted to working on it. The TA will be glad to answer any question for you, but only after you have tried using the help features to find the information first. This assignment was meant to mimic an assignment you may get in the real world, with all the headaches associated with it.
Additional Exercises