Module 9 Assignment: Create Macro for Car Loan Spreadsheet

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.)
      Screenshot of the downloaded file for this assignment
    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.
          Record Macro Dialog box. The Macro name is CarLoans. The Shortcut key is Ctrl+Shift+ L. Store this macro in This workbook. Description: Weekly report of car loans to format and calculate monthly payment amount.
        2. Change the title row to any color, change the font color if needed, and bold the titles.
          Screenshot of the assignment file where row one has been changed to have a green background and bold text.
        3. Change all the currency columns to a currency format (this includes the PV, Annual Income, Total Payment).
          Screenshot of the assignment file where the PV, Annual Income, and Total Payment columns have been changed to a currency format.
        4. Change the Interest Rate column to a percentage (%).
          Screenshot of the assignment file where the interest rate column has been changed to a percent format.
        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.
          Screenshot of the assignment file with a new Column E with the heading PMT. The column has monthly payment information for each loan.
        8. Add filters to column titles and sort the monthly payment amount from the highest to the lowest.
          Screenshot of the assignment file with filter arrows on every heading cell in the first row.
        9. Use conditional formatting in the ‘Ownership Type’ column to highlight all car leases in red.
          Screenshot of the assignment file. All cells in column K that read Lease are highlighted 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.
      Screenshot of the assignment file. All changes that had been manually made to Week 1 have been made to Week 2.
    6. Save your work by selecting Save As and choose Excel Macro-Enabled Workbook.
      Screenshot of a dropdown menu with the Excel Macro-Enabled Workbook (*xlsm) selected.
    7. Submit the document in your course online.