In this assignment, we will record a macro and run it to modify a spreadsheet of car loan data received on a weekly basis. After recording a macro for the first week, run the macro on a new set of data for week two.

To complete this assignment, download the car loan file here. Follow the directions, then submit your assignment. If you get stuck on a step, review this module and ask your classmates for help in the discussion forum.

1. Open the workbook and save the new Module 8 assignment file to the Rowan folder on your desktop as BA132_LastName_CarLoans.xlsx, replacing “LastName” with your own last name. (Example: BA132_Hywater_Memo) It is a good idea to save your work periodically.
2. Scenario: Each week you receive a worksheet filled with car loan data. To make it easier to analyze on a weekly, record a macro and change the worksheet look, calculate the monthly loan payment amount and identify which loans are leases. (Hint: Read through all the instructions first and practice going through the steps before starting the macro recording.)
   
3. Record Macro: Open the spreadsheet and from the View tab or the Developer tab start a macro recording. Name the macro whatever you would like, add a short-cut key (optional), and write a description for it.
   1. Once the recording is started walk through these steps, then stop the recording.
      
   2. Change the title row to any color, change the font color if needed, and bold the titles.
      
   3. Change all the currency columns to a currency format (this includes the PV, Annual Income, Total Payment).
      
   4. Change the Interest Rate column to a percentage (%).
      
   5. Add in a new column to the right of the Interest Rate column and call it PMT.
   6. Financial Function: In the new column use the PMT function and calculate the monthly payment amount for the first loan. (Remember the rate needs to be divided by 12 to get the monthly payment and place a minus sign before the PV cell (-B2) if you don’t want the negative red numbers.)
   7. Autofill the rest of the column with the formula to discover each loan’s monthly payment amount.
      
   8. Add filters to column titles and sort the monthly payment amount from the highest to the lowest.
      
   9. Use conditional formatting in the ‘Ownership Type’ column to highlight all car leases in red.
      
   10. Select the entire table and autofit the columns to be wide enough to fit all the data
   11. Stop the macro recording.
4. Select ‘Week 2’ worksheet tab containing a new week of information. (Note: Make a copy of this tab first in case you need to redo the Macro. Remember once a Macro is run there is no going back with ‘Undo Typing’ option.)
5. Run the new macro and watch the new information change almost instantly.
   
6. Save your work by selecting Save As and choose Excel Macro-Enabled Workbook.
   
7. Submit the document in your course online.

Contribute!

Improve this pageLearn More