5. Fact Pattern: Question 5:Congratulations on your new position as loan officer at the Commercial Bank and Trust Company. You are dedicated to building long-term relationships with your clients, and you want to be able to answer their questions quickly if they call after obtaining a loan. The bank’s system is a little slow, so you have started your own list of information frequently needed by your client. You are aware that it is extremely important that the customer’s information be protected. You process home mortgage loans for the bank’s customers. The interest rate varies depending on the term of the note. The bank writes mortgages for 10, 15, 20, and 30 years. Once you know the term, you use the associated rate to calculate the loan payment. You then store the customer’s information including the payment date and the payment amount in your list for quick reference. Respond fully to the following questions regarding this task: A - (TCO 2) You have set up an Excel worksheet to determine the loan payment. It is set up so that you enter the term of the note, and it finds and returns the correct interest rate. Then, you enter the interest rate and other required information to get the actual payment. Describe the Excel tools used for these tasks, and the information required to obtain the payment. B - (TCO 10) You know that your list of information on the loans will quickly become very long. As these are long-term loans, the information will not change a lot, but it is important that it remain confidential. You want to be able to pull up an individual record by street address and zip code, or by phone number. A friend has suggested that you build your list in Access. What would be the benefit of doing this, particularly in the banking environment? Would this meet your needs?C - (TCO 10) You want to use the Access form wizard to create an input form for your list. Describe the process of creating the form, and the special tools you might use to make it easier to use and less prone to errors.D - (TCO 10) You have decided that you would like to be able to pull a list of your clients with high interest rates if the rates fall enough to make refinancing attractive. Explain how you would obtain a list of loans with interest rates greater than a specific percentage, and then print the information for your use in making these calls. Be specific!
HelloCustomer Try these answers out, let me know if they work out for you!
A: If the excel spreadsheet is setup as a calculator to process a single quote at a time, then you wont need any VB Macros in place to complete this, unless you are going to use combo boxes or more complicated forumlas that cant be handeled using the 1 line provided by excel by default. You would have to setup your excel spread sheet so that your values line up in the same column, with the information you are asking for next to it, either on the left or right side of the value's you will be entering and calculating. There would be a row that asks the term of the note, with a cell next to it where you enter the value. The cell after it could be used to enter possible values for the end user to input so they know what they can and can not use, to prevent the user from using invalid values, you would need to use a drop down box, in this case you would have to enable the developer tab, add the combo box, and assign a VB Macro to add the values and calculate the end results later after all the values have been entered. So the tools you would use are the function bar to enter forumulas, the cells provided by default in excel, most likely you would be using some of the Font tools to modify font color, size, and other attributes like Bold, Italics, etc... You would potentially use form controls off of the developer tab (Combo Box, and Button) to fancy things up a bit. If you used those controls, you would then be using Macro's to create Visual Basic code to process the formula. The information required to obtain the payment is on you, im not a financial kinda guy and I dont know what information is necessary to calculate a loan payment. I do know that you would need the cost of the loan, the term of the loan, and the interest rate, but im unsure as to how the interest rate is applied to come out with a payment plan.
B: By building your database in access, you can protect the customers information in multiple ways. The information would be stored in an access database that is encrypted and password XXXXX Access databases are used my many major corporations because of the safety it provides for the files. Because of the way Access is built, you can prevent any information from ever showing up on the screen, except for what you specifically ask for. You can have access start up with a search form, and you can make the results limited to the persons name, and last 4 of their social even though the entire social is stored in the database. Or instead of the social, you could use their date of birth. This provides an extra security barrier so that if anybody is watching your screen (i.e. the customer), they will not be able to see any pertinent information on somebody else. Once selected, you would be able to open that users information, and still, even with that, you get to design the forms however you like, so you could still set it up to conceal any information unless a certain button or area is clicked, then it would show the information. There are an endless possibility of options for this. Banking environments needs an onslaught of security to keep account information confidential. Access provides everything you need to make this possible with all the access restrictions, passwords, encryption, and programmability to show only whats necessary.
C: To use the form wizard in access, you first have to have your table headers created so you know what fields need to be inputted by the user of the form. When the wizard starts, you have to select the fields that you will need to type information into and move them to the next box. Next you get to choose a format for your data. This information can be modified when finished, but you would want to choose the format that matches what you think would be best, XXXXX XXXXX how you want your form to look. Next is the style of the form, which picking something in a bank environment would need to be professional, mostly matching the colors and style of the company so that it doesnt stand out too much (for the prying eyes). Next is the form name, which should be specific to what the form does to prevent confusion of the end user when looking for that specific form. To make the form easier to use, it would be best to have everything line up, so that as i said before about the values you would be entering into an excel spreadsheet, how they would line up in 1 column, that would make things very simple in this case. Also some VB macros could be used to check the entered values and make sure they are within limits before the information can be submitted. This would prevent errors (or create some, depending on the programmer).
D: I cant be as specific as you will probably need to be, but to answer this question, you will most likely be able to take what i say and expand on it for a perfect answer. You would have to create a VB Macro on a button to make this happen. The VB Macro would have to programmatically sort your table by the Rates column. Then you would have a For~Next loop that would check the rates from top to bottom. If the sorting method was used to have the higher numbers on top, then the process would check the first rate, if its greater then or equal to the specified percentage, then it could store the data in an array and move on to the next, when it finds a rate that is less then the specified value, the loop would end, and you would programatically create an excel or word document within your code and insert all the information as necessary so that you would only be seeing a list of individuals with interest rates at or above the specified value. The data would show the persons name, phone number, and interest rate. More would be shown if necessary for the call list.
Sorry, I already got a answer. Thank you