Faculty of Business and Information Technology Assignment 3 Value: 7.5%, Due: Nov. 18th @ 11:59 PM This assignment should be completed individually. We take academic integrity seriously. Any student...

hi, I have an assignment for the course "business computer application", it's excel work



Faculty of Business and Information Technology Assignment 3 Value: 7.5%, Due: Nov. 18th @ 11:59 PM This assignment should be completed individually. We take academic integrity seriously. Any student found to be involved in plagiarism or cheating will be penalized in accordance to the Ontario Tech University Calendar, Section 5.15. To assert that you have not given or received, or used unauthorized assistance, write the following pledge in the comment textbox when you submit your files through Canvas system. "I have not given, received, or used any unauthorized assistance." 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, PV). 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 26 and multiply the term by 26. For your reference you can use the Assignment3.xlsm 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. 1 as you complete Task 1. (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 B4 enter your student number. In cell B5 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 Calculator 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). Fig. 1: Initial Setup 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 payment amounts based on the information provided by a user (do not forget to format this value). (b) 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. Fig. 3: Durham Auto Loan Calculator Sample Run Fig. 4: Durham Auto Loan Calculator Error Message Submission Guideline: Using the Canvas system, please submit your ONE solution file by no later than Nov. 18th, 2020 at 11:59PM.