ASSIGNMENT 1: LOGBOOK EXERCISE 1 Dashboard Modelling Due Date: 26th March 2023 LOAN CALCULATOR DETAILS 1. Product type 2. Branch Name 3. Customer Name 4. Employer Name 5. Date 6. Employee Number 7. Statutory income and allowances fields i. Basic Salary ii. Transport Allowance iii. Housing Allowance iv. Other permanent allowance (three fields) v. Total Income 8. Statutory deductions fields i. PAYE ii. NAPSA Contribution iii. Other permanent deductions iv. Total Deductions 9. Net Take Home 10. 40% Debt service ratio (Amount available for loan servicing) 11. Loan instalment with banks 12. Loan instalment with other microfinance institutions 13. Loan deductions with other financial institutions 14. Available amount for BFSL equated monthly instalment 15. Maximum BFSL Loan Amount 16. Requested Loan Amount 17. New BFSL equated monthly instalment 18. New Take Home after BFSL Loan 19. Debt service ratio (%) 20. Interest Margin 21. Bank of Zambia Policy Rate 22. Effective annual interest rate 23. Loan tenure in months 24. Processing fee 25. Insurance fee Notes 26. Loans above 40% debt service ratio must be declined 27. Client should have a minimum net salary of K1,500.00 before any loan deduction Short term allowances must not be considered as permanent allowances, acting allowances, leave pay, overtime etc. 28. Other permanent deductions include medical fees, union fees, etc. 29. Insurance premiums to be correctly inputted as table below TENURE RATES 1-12 0.84 13-36 1.31 37-48 2.1 49-60 2.31 30. Processing fee is 2.5% of loan amount minimum being K350.00 thus if the 2.5% is less than K350.00 then apply K350.00 as a processing fee 31. Where the loan is above 40% debt service ratio a rejection message will pop up thus the amount or tenure must be adjusted Only mandatory allowances will be allowed 32. Loan Interest rates: The proposed annual rate is 17% p.a 33. Repayment/amortization schedule: The Loan calculator should produce the amortization schedule as per attached screen shot and also the Key fact statement. 34. Repayment/amortization schedule: The DSR loan calculator should produce the amortization schedule as per attached screen shot and the Key fact statement. Key fact statements should have the follows. i. The loan principal amount ii. The interest repayable iii. Total repayable amount iv. Processing and insurance amount v. Duration of the loan vi. The loan equated monthly instalment. vii. Loan amount received after deductions (processing and insurance fee) viii. Total cost of the loan this includes processing and insurance fees and interest rate. ix. Payment frequency -must be monthly. x. Number of payments (loan tenor) xi. Date of first payment due Required: 1. Develop a model using Dashboard Modelling, Absolute and Relative Referencing, and Macros that will assist the Company give out Loans effectively with all the details stipulated above. (50 marks) 2. Write a brief report (1000 words) that describes how the main tools where used to develop the model, the possible implications of the Outputs of the model and the possible controls that the institution might require to optimize its operations