1- The application must inform the user if he is eligible to request a loan or not as per to the following mandatory conditions (All are to be satisfied):
a. At least 6500 monthly paid salary.
b. At least 3 years of service at his current position.
c. Other monthly financial obligations + requested installments shall not exceed 33% of the monthly paid salary.
d. Age not less than 25 and no more than 65.
e. The minimum amount of the requested loan must not be less than 15000 SAR f. The maximum payback period is 60 months.
2- The application must calculate a set of outputs as per to the user inputs, the user must have the ability to choose the local bank name from a list of local banks but he should not be given the permission to manually enter the interest rate, instead the application must use a predefined table of interest rates used at each local bank (you can assume the interest rates if you are not sure about it but make sure to list all local banks). The outputs to calculated are:
a. The monthly installment.
b. The total interest amount.
c. The total loan amount
3- The application must give the user the ability to choose an option if he would like to authorize the bank to withdraw his end of service benefits in case he wasn’t able to pay back the loan amount, if he selected this option then either of the following is going to be applied:
a. The interest rate will be reduced to 2.5% flat rate on the condition that the user has completed more than 10 years of service.
b. The interest rate will be reduced to 3.5% flat rate on the condition that the user has completed more than 5 years of service.
4- The application must allow the user to export the final calculations as a PDF document by using a button inside the sheet, the printed PDF must show all the details of the loan including the bank name and all the other calculations in one page only (landscape Orientation)
5- The application should allow the user to view, sort and filter the predefined table of interest rates used at each local bank without being able to modify, edit or delete any of the rates.
6- If condition C was the only reason of ineligibility then the application must provide the user with the maximum allowed loan amount that he can request in order to be eligible for the loan request and automatically do the reaming calculation accordingly (The monthly installment, The total interest amount, The total loan amount)
7- The application must allow the user to analyze the loan plan based on four options: Future Value, Rate per Month, Months and Monthly Payment so he can change any of these values and see how the other values will change.
Basic technical requirements:
1- The user should be given the permission to enter, modify or delete only the data that he is requested to provide without giving him the permission to modify or delete other sections of the Excel sheet or to alter the structure of the whole excel workbook.
2- The user should be given the permission to only enter/select allowed data types or formats without giving him the permission to enter other data types or formats.
3- Indicative messages are to be given to the user to guide him on each step and to inform/warn/stop him about each error. Marking Criteria:
• Covering all the requirements - 50% of the total mark
• Using attractive colors, backgrounds, styles, font .. etc - 25% of the total mark
• Using professional English (typos-free & correct grammar) - 15% of the total mark
• Ease of use and good guidance to the user - 10% of the total mark