. Durham Auto (100 Points) Start-up file needed for this exercise: None Durham Auto is a family-owned new and used car dealership, specializing in quality GM models for every budget and style. You have been recently hired as a salesperson and you decided to create a simple VBA application to help you calculate auto loan payments based on the following information The interest rates and periodic payments are calculated as follows: 1) Interest Rate If the loan term is less than or equal to 3 years, the interest rate is 1.5% If the loan term is greater than 3 years but less than or equal to 5 years, the interest rate is 2.5%. If the loan term is greater than 5 years, the interest rate is 4%. 2) The loan payment calculations done using a VBA function called Pmt( Rate, NPER.PI). Rate: is a number specifying interest rate per period. For example, if you get a car loan at an annual percentage rate (APR) of 10 percent and make monthly payments, the rate per period is 0.1/12, or 0.0083 NPER: is a number specifying total number of payment periods in the annuity. For example, if you make monthly payments on a four-year car loan, your loan has a total of 4 * 12 (or 48) payment periods. PV: is an amount specifying present value (or loan amount). For example, when you borrow money to buy a car, the loan amount is the present value to the lender of the monthly car payments you will make Note: for the bi-weekly payment you must divide the rate by 26 and multiply the term by 26. Also, for the weekly payment you must divide the rate by 52 and multiply the term by 52. BY 5 6 7 8 9 Va R T Y U P For your reference you can ute the Assignment.im file attached as you complete this assignment Your task is to create an application similar to this file. Note that the code in this file is password protected Task 1: Prepare Excel Macro-Enabled file Refer to Fig. I as you complete Task I. (a) Create a Macro-Enabled workbook named Durham Auto.xlsm, Insert a Documentation worksheet and add appropriate title for the workbook in cell A1. In cell B3 enter your name. In cell 34 enter your student number. In cell BS enter the completion date of your assignment and in cell B6 enter a sentence to describe the purpose of the workbook. In cell A100, enter your ontariotechu.net email address. (b) Add a new worksheet called Auto Loan Calculator. (c) Add a button (from the ActiveX Control group). Change the caption to display Open Calculator (d) Add a button (from the ActiveX Control group). Change the caption to display Reset Worksheet (e) Create a reporting area similar to Fig. 1 (the company logo is included in the assignment document) Save your file Task 2: Create Income Tax Caleulator User Form Refer to Fig. 2 as you complete Task 2. Create a user form similar to the design shown in Fig. 2. Be sure to change the user form caption to Durham Auto Financing Calculator (the company logo is included in the assignment document) 10 & 7 5 6 7 00 9 2 R T . Y U o F G . K 3 4 5 6 7 8 9 10 11 DURHAM AUTO 12 13 14 15 16 17 Auto Loan Information Price Down Payement Term Interest Rate 18 Payment Information Monthly Bi-weekly Weekly 20 21 22 23 24 25 Open Calculator Reset Worksheet Fig. 1. Initial Setup amming DURHAM AUTO Price Down Paymnet Term (Years) Calculate Payment Monthly Bi-weekly Weekly Reset Form Generate Report Exit Fig. 2: Durham Auto Loan Calculator User Form Task 3: Write codes for each button Refer to Fig. 3 as you complete Task 3. (a) The Calculate Payment button should compute and display the monthly, bi-weekly and weekly nayment amounts based on the intammation provided by user (do not forretto format this value) Task 3: Write codes for each button Refer to Fig. 3 as you complete Task 3. () The Calculate Payment button should compute and display the monthly, bi-weekly, and weekly payment amounts based on the information provided by a user (do not forget to format this value). (6) The Reset Form button should clear the user form (three textboxes and three labels) (c) The Generate Report button compute should display the required information onto the Auto Loan Calculator worksheet as shown in Fig. 3. (d) The Exit button should close the user form. Task 4: ActiveX Buttons and Error Handling (a) The Open Calculator ActiveX control button should open the Auto Loan Calculator user form. (b) The Reset Worksheet ActiveX control button should clear the contents of the worksheet (C13:C16 and C19:C21). (c) The Auto Loan Calculator user form should have an error handling mechanism to avoid application malfunction due to empty textboxes. For example, if any of the textboxes is empty, then the program should display an error message as shown in Fig. 4 (d) Test your application Save and close your workbook. NOTE: ALL CALCULATION SHOULD BE DONE THROUGH VBA ELE 5 6 7 8 9 2 3 E R T Y U F G . J L DURHAM RUTO DURHAM AUTO Auto Loan Information $30,000.00 55,000.00 Term (Years Down Payment Term Rute Price 30000 Down Payme 5000 Payment Information MOON SA 520467 Calcutane Payment 120167 Bolly 543.60 More Weekly WY Open Call Ret Form Geboort Fig. 3. Durham Auto Loan Calculator Sample Run DURHAM AUTO IND C i & 5 6 7 8 9 R . Y U ZEGH K DURHAM AUTO Down Payment Term(Years 10000 Fig. 4 Durham Auto Evan Calculator Error Message Submission Guidelines Using the Canvas system, please submit your ONE volumen filo by no later than Nov. 184.2020 11:59PM TO DIR . P 6 7 00 9 0 2 WA R . Y U I P . Durham Auto (100 Points) Start-up file needed for this exercise: None Durham Auto is a family-owned new and used car dealership, specializing in quality GM models for every budget and style. You have been recently hired as a salesperson and you decided to create a simple VBA application to help you calculate auto loan payments based on the following information The interest rates and periodic payments are calculated as follows: 1) Interest Rate If the loan term is less than or equal to 3 years, the interest rate is 1.5% If the loan term is greater than 3 years but less than or equal to 5 years, the interest rate is 2.5%. If the loan term is greater than 5 years, the interest rate is 4%. 2) The loan payment calculations done using a VBA function called Pmt( Rate, NPER.PI). Rate: is a number specifying interest rate per period. For example, if you get a car loan at an annual percentage rate (APR) of 10 percent and make monthly payments, the rate per period is 0.1/12, or 0.0083 NPER: is a number specifying total number of payment periods in the annuity. For example, if you make monthly payments on a four-year car loan, your loan has a total of 4 * 12 (or 48) payment periods. PV: is an amount specifying present value (or loan amount). For example, when you borrow money to buy a car, the loan amount is the present value to the lender of the monthly car payments you will make Note: for the bi-weekly payment you must divide the rate by 26 and multiply the term by 26. Also, for the weekly payment you must divide the rate by 52 and multiply the term by 52. BY 5 6 7 8 9 Va R T Y U P For your reference you can ute the Assignment.im file attached as you complete this assignment Your task is to create an application similar to this file. Note that the code in this file is password protected Task 1: Prepare Excel Macro-Enabled file Refer to Fig. I as you complete Task I. (a) Create a Macro-Enabled workbook named Durham Auto.xlsm, Insert a Documentation worksheet and add appropriate title for the workbook in cell A1. In cell B3 enter your name. In cell 34 enter your student number. In cell BS enter the completion date of your assignment and in cell B6 enter a sentence to describe the purpose of the workbook. In cell A100, enter your ontariotechu.net email address. (b) Add a new worksheet called Auto Loan Calculator. (c) Add a button (from the ActiveX Control group). Change the caption to display Open Calculator (d) Add a button (from the ActiveX Control group). Change the caption to display Reset Worksheet (e) Create a reporting area similar to Fig. 1 (the company logo is included in the assignment document) Save your file Task 2: Create Income Tax Caleulator User Form Refer to Fig. 2 as you complete Task 2. Create a user form similar to the design shown in Fig. 2. Be sure to change the user form caption to Durham Auto Financing Calculator (the company logo is included in the assignment document) 10 & 7 5 6 7 00 9 2 R T . Y U o F G . K 3 4 5 6 7 8 9 10 11 DURHAM AUTO 12 13 14 15 16 17 Auto Loan Information Price Down Payement Term Interest Rate 18 Payment Information Monthly Bi-weekly Weekly 20 21 22 23 24 25 Open Calculator Reset Worksheet Fig. 1. Initial Setup amming DURHAM AUTO Price Down Paymnet Term (Years) Calculate Payment Monthly Bi-weekly Weekly Reset Form Generate Report Exit Fig. 2: Durham Auto Loan Calculator User Form Task 3: Write codes for each button Refer to Fig. 3 as you complete Task 3. (a) The Calculate Payment button should compute and display the monthly, bi-weekly and weekly nayment amounts based on the intammation provided by user (do not forretto format this value) Task 3: Write codes for each button Refer to Fig. 3 as you complete Task 3. () The Calculate Payment button should compute and display the monthly, bi-weekly, and weekly payment amounts based on the information provided by a user (do not forget to format this value). (6) The Reset Form button should clear the user form (three textboxes and three labels) (c) The Generate Report button compute should display the required information onto the Auto Loan Calculator worksheet as shown in Fig. 3. (d) The Exit button should close the user form. Task 4: ActiveX Buttons and Error Handling (a) The Open Calculator ActiveX control button should open the Auto Loan Calculator user form. (b) The Reset Worksheet ActiveX control button should clear the contents of the worksheet (C13:C16 and C19:C21). (c) The Auto Loan Calculator user form should have an error handling mechanism to avoid application malfunction due to empty textboxes. For example, if any of the textboxes is empty, then the program should display an error message as shown in Fig. 4 (d) Test your application Save and close your workbook. NOTE: ALL CALCULATION SHOULD BE DONE THROUGH VBA ELE 5 6 7 8 9 2 3 E R T Y U F G . J L DURHAM RUTO DURHAM AUTO Auto Loan Information $30,000.00 55,000.00 Term (Years Down Payment Term Rute Price 30000 Down Payme 5000 Payment Information MOON SA 520467 Calcutane Payment 120167 Bolly 543.60 More Weekly WY Open Call Ret Form Geboort Fig. 3. Durham Auto Loan Calculator Sample Run DURHAM AUTO IND C i & 5 6 7 8 9 R . Y U ZEGH K DURHAM AUTO Down Payment Term(Years 10000 Fig. 4 Durham Auto Evan Calculator Error Message Submission Guidelines Using the Canvas system, please submit your ONE volumen filo by no later than Nov. 184.2020 11:59PM TO DIR . P 6 7 00 9 0 2 WA R . Y U I P