CSCI 112
Fall 2025 - Assignment 5
Submit deadline: 11:30am, 3 November 2025, Monday

Objective:

• Design and develop a VBA program with loop statements

References:

• Lab 7
• VBA Repetition (I)
• VBA Repetition (II)

Problem Description

The following is a vastly simplified definition of machine learning: "Machine learning is an application branch of artificial intelligence (AI) that learn from past experience/data to build predictive models."

One form of such predictive models is called a decision tree model. One of the application domains of the decision tree model is in the bank to determine whether to approve a customer's mortgage application.

(Disclaimer: I made up the mortgage evaluation model used in this assignment. Don't use this model in real life.)

One of such a decision tree model is shown below:

This model can be translated into the following set of the rules:

• The bank determines whether to approve a customers mortgage application based on the customer's age, yearly income, credit rating and mortgage amount.
  
• If the customer's credit rating is poor, the mortgage application is denied (the bank will not lend the money).
  
• If the customer's credit rating is good, then we look at the ratio of the mortgage amount divided by the customer's yearly income.
  
• If the ratio is less than or equal to 3.0, the application is approved.
  
• If the ratio is greater than 3 but less than or equal to 10, then we look at the customer's age.
  If the customer's age is less than or equal to 50, then the application is approved; Otherwise, it's undecided, which means that we will gather more information to decide whether to approve this application.
  
• If the ratio is greater than 10, we also need to look at the customer's age.
  If the customer's age is greater than 50, then the application is denied. If the customer's age is less than or equal to 50, then the application is undecided.

Besides the rules shown in the model, if an application misses data about the customer's credit rating, age and/or yearly income, the application should be undecided. If an application misses the mortgage amount, the application should be denied.

Your tasks

• Download the template file A5-input.xlsx.
• Open the template file you just downloaded and you should see a list of mortgage applications starting from row 4. Each mortgage application shows the customer's information of credit rating, age and yearly income, and the mortgage amount the customer wants to borrow from the bank. (Note that some mortgage applications contain some missing data (shown as Unknown), these applications should also be handled properly.)
• Design and implement a VBA program that processes all the mortgage applications and write the evaluation result (one of the approved, denied or undecided) to the column F of each application.
• When you write the result to the column F, color the cell to green if the result is "Approved", yellow if the result is "Undecided" and red if the result is "Denied".
  The VBA code to color a cell (for example, cell F14) is:

     Cells(14, "F").Interior.ColorIndex = 4 ' color to green
     Cells(14, "F").Interior.ColorIndex = 6 ' color to yellow
     Cells(14, "F").Interior.ColorIndex = 3 ' color to red
  

• Save your work to a Macro-enabled Excel file with a meaningful name.

How to submit:

To submit your macro enabled solution Excel file, first login to your VIU Learn account, find the CSCI 112 course page, click on the "Assessment" drop-down menu, click on the "Assignments" item, then click on the folder named "A5". Then you can click on the "Add a File" button to browse and upload your solution file.

If you have trouble to make the VIU Learn submission work, please email me to let me know and attach your Excel solution file with your email if necessary.