When you create a data form in Excel, you can develop different administrative procedures because there you can create a database and connect it with formulas of a financial nature, in this article you will learn how to create a database of financial formulas that aim to control borrowers who have applied for a loan whose status can be: Discounted, late, canceled payers.
How do you take advantage of a system that manages loans and receipts?
With this system we will show you, you will have the ability to do the following:
- You have control over payments made by debtors or customers
- Change interest rates according to the agreements you conclude with the client
- Pay attention to people who have not paid at this time and charge them interest for the moratorium
- Generate a list of the total amounts to be received or received
- You have a list of debtors, payers or creditors
How to make the management system using Microsoft Excel?
After downloading Microsoft 365, you will be able to access many tools, such as Excel. To create this spreadsheet in Excel, the first thing you need to do is know how to handle the following:
- Create sheets in Excel according to the tabs
- Enter formulas and properties in cells
- Create filters in Excel so you can select the desired client
What are you going to do?
- Open a folder indicating «Management and collection system»
- Open Excel
- Save the data in the «Management and collection system» folder
It’s good to remember that even if you want to, you can create a chart with specific data in Excel which allows you to view values more easily.
Open a new sheet in Excel called «Client»
Once Excel is open at the bottom of the tab below, you’ll call the sheet «client» (without parentheses). And on the same sheet you have to create the following fields in each cell.
- ID Code: This code is what will differentiate one customer from another
Code control rule: If the person applied for a loan and I cancel it, this is a code, if I apply for another loan being the same person, it carries a different code, when you use a report it will indicate the number of loans that the same person has.
- Name and surname (here you will place the debtor)
- Identity card, DNI, passport or identity document
- Phone: the customer’s phone number
- Work: the place where the client works to locate him
- When you place this data in the shadow of the cells and enter a table
- When you enter the table you will notice that the layout changes
Enter the details of all your customers.
After you have made the header in the new Excel sheet, you need to fill in the required data in the table, remember: Do not repeat the codes even if the customer’s name and data are the same, there may be a different condition.
Create a new spreadsheet called ‘spreadsheet’
On this sheet, all interest, principal and amounts agreed with customers will be calculated and are as follows:
- ID code: It is the same code as the previous one
- Credit Date: The date I apply for the loan
- Credit amount: the amount that was delivered to the customer
- Tip: If the fees are weekly, monthly or daily
- Interest rate: the interest agreed with the client
- Amount paid: The amount the customer has to pay
- Debt: The amount that the loan applicant has to pay
- Status: Here you will put the date of the canceled rate, if it is not canceled, you must put Blackberry in red
- Once this header is finished, shadow and enter the table (you will notice the change)
- The configuration to be calculated is as follows:
- Debt = loan amount (1 + interest rate / 100) ^ raised on time
- To find the amount paid is equal = Debt / amount of payments
Repeat the customer code in the table according to the number of installments, ie if there are twelve (12) installments must repeat the rates.
You will be able to place the following:
- If the customer cancels, he will put the date on which he canceled.
- If the client does not cancel, he will put the word MORA in red and when he cancels he will put the date in red.
Find a customer’s name in the «spreadsheet»
Searching for text in an Excel sheet is simple, so by searching for your customer’s name, you’ll be able to access their data more easily. Put the filter in the header and there you will know the amount of quotas canceled by the customer code.
Select the credit amount for each customer
If you want to know the amount of credits that the customer has made, you must use the same filter, only by knowing the customer’s code, section it and it will indicate the canceled rates with the payment dates, If you have not made payments, you will see it in red.
How to manage the collection over time?
If you have uploaded the fees correctly, you will be able to detect the fees that the customer has canceled and if you want detail collection management Only by opening the table of customers you will be able to detect the phone number and address in order to complete the collection.