Hello,
Thank you for the prompt reply.
Here are more details about the database and how we intend to use it:
Requirements for the system.
1. Database system.
The system should have the ability to reduce the amount of time taken to upload the new clients into the system. Records must be updated automatically and available to the administrators.
The database must also allow for the capturing of clients details easily and this should include saving of PDF and other forms of correspondence between the clients and the office. This should facilitate client support such as instant access to all the client details when they call to inquire.
Data access should be limited in that the office workers cannot erase the database and only have access to certain segments of the database, such as new client’s entry and access to public data such as names and insurance firms. The administrators however should have the ability to access all the information such as the amounts paid.
Reporting on predetermined time when the client details must be completed fully, such as when all contract details must be verified, when there are certain details missing. Incomplete entries should be reported to the admin within a predetermined timeframe.
Since the firm makes money by receiving compensation from the Insurance firms, there should be financial reports that details which clients have made payments above a certain amount to the Insurance Company and thus a commission is due from the Insurance firm. The insurance firm’s sends out the payment bill to the firm’s account, and the firm would like to have a system that verifies that the payments are accurate. This would mean that the system should be able to calculate the amount due, from the all the clients whom the Insurance firms have been satisfied with. The process works like this: the call agents make an appointment for a consultant to make an insurance pitch to a client, the client sign on with an insurance firm of his/her choice, the consultants submit the contract to the Firm(VIZ), the firm collects the clients details and saves them on the database(currently Excel) then forwards the contract to the Insurance Company, the Insurance company then verifies that the client details are correct and once they receive the payments from the client, they send a confirmation of acceptance to the Firm. The Firm then calculates manually the amounts that are due to them based on a pre-agreed compensation scheme from the Insurance Company. At the end of the month the Insurance Company send the amount due to the Firm and the Firm makes payments to the Consultants. Herein lays the challenges- the Firm does not want the consultants to see how much the Firm makes since they are paid only a portion of the amount. The administrators then must make a payment report which details the salaries payable to each consultant. This is a long and complicated process since there are 6 insurance Companies and around 10 Consultants and it involves a tedious process of collecting all the clients that the consultants signed up, those that there was a compensation due from them, and also the follow up with the insurance Firms to confirm the amounts due, since each Company has its own compensation scheme. Also sometimes, the compensation that was paid out is recalled-in the instances where the client cancels the contract with the Insurance Company or they do not activate their membership. This means finding out the client, the consultant and making the subsequent deduction.
Thus there is need to ensure data security since the payment details must be stored electronically. The idea is to have a system whereby, each consultant can be able to see all the clients that they have signed up, have a link to a table that only they can access to view the salary that is due to them, and the Firm directors must have an archive of all the information about the payment system, when who was paid, what they were paid and etc.
The IT platform must facilitate the following:
Easy follow up of the clients, by both the office and the consultants, e.g when certain details are missing there should be an automated report that specifies that certain action must be taken. This report must be sent to both the consultant and the Firm.
Automated payment reports to the consultant who can then be able to view these details remotely.
Allow for the creation of consultants tiered working options, such as teams, team leaders and juniors, who receive different compensation rates.
Enhanced client support program, such as when the clients call, the office can have immediate access to the client details.
Employee monitoring, such as which employee handled a particular client, what was the outcome of the consultation, internet usage, database usage. Basically Big Brother, so that when things go wrong, they know who to hang!
When the date back office workers send out to the Insurance contracts, they save the date under the field Versand nach KK, ideally within a week the office should receive confirmation of acceptance of the client by the insurance company. The backoffice workers then this data in the field Aufnahmebestätigung. Therefore we would like a function, that within 10 days, after the entry of the data under the field Versand nach, there is a report generated that shows which entries do not have an entry in the field Aufnahmebestätigung.
Also, when we receive the reply from the insurance company, we have to update the client details. There are 4 possible replies from the Insurance Company: Acceptance of the client, Rejection, Incomplete dossier or Other. This information is entered under the field bearbeitsstand. We would like a function, that when the information entered in this field, if it’s Rejection/Incomplete/Other, the system prompts for a message asking for comments for this entry. E.g If I input Incomplete, there should be a message function that allows me to input the reason for why the entry is Incomplete, e.g No I.D copy attached.
The Financial Reporting Function:
We would like to use this function to prepare payments to the Beraters. We receive a commission from the insurance firms and we pay the Beraters a portion of this amount. This is based on different plans from the 5 insurance firms. The compensation rates are as follows:
1. If the insurance firm is Group Mutuel, we pay the Berater 60% of the commission we receive.
2. If the insurance firm is Swica, and the commission we receive is more than CH 300.-, we pay the berater CH 200.-, if the amount is less, they receive 0.-
3. If the insurance firm is Helsana, Progress, Visana, Avanex, SanSan and the commission we receive is more than CH 300.-, we pay the berater 250.- and if less amount is less they receive 0.-
4. If the insurance firm is Concordia, and the client is between the ages of 19-25 and the amount we receive is less than CH 300.-, we pay the berater 0.- and if the vice versa, we pay the consultant CH 250.-
5. If the client has signed up for the following hospitalisation options:
1) HalbPrivat & Flex, we pay an additional CH 50.-
2) Privat, we pay the berater an additional CH 100.-
6. If we receive a Storno, we deduct it from the consultants in the following scale:
a. 100% in the first year of the berater working for us.
b. 66% in the second year of the berater working for us.
c. 33% in the third year of the berater working for us.
Thus we would like to have the application, pick the age of the client and use it in the calculation of the berater compensation, pick the hospital insurance that the client has signed up for, which is entered in the field Spitalversicherung.
Also in entering the data, there should be automation of the choices in the fields KVG Modell and VVG, since each option is unique to the Insurance firm that offers it.
Thus in the report, we would like to prepare a payment schedule for each of the berater. We would like them to see all the clients that they have signed up, each amount that is due to them from us. However, the Berater should not see the commission that we have received from the insurance firm. Only the company directors should be able to see this amount. There are only 3 of them.
The reports should be scheduled, to be sent out to the beraters at the end of each month.
Search 
