Building the Sales Reporting System Access Database
About the Company
|
Profsr Financial Services, PFS, is a medium-sized business employing some 50 financial advisors (sales representatives) to sell a full-range of investments and insurance to individual clients.
Although the sales representatives function somewhat independently from the company, they are paid commissions and have to pay a variable amount of their commissions to the company as an operating fee. It's this amount called CPA that PFS wants to track first. Whenever a proposal is written for a client, the proposal details are input into the SRS (Sales Reporting System) database and the amounts are used to calculate all the sales statistics. Most sales reports are produced on a weekly and year-to-date basis. In some cases, reports are calculated for a specific period of time, when there's a special promotion, for example. |
Analysis and Design
Here's the analysis we would have gone through with the customer:- The Sales representative is a main entity. However, we do not need to keep much information on the rep. Number and name is almost all. There is a special factor called the "adjustment %" that is used in some commission calculations, so that will have to be recorded.
- The Products entity is very important. There are dozens of different products sold. All products are divided into 4 Product lines: mutual funds, annuities, life insurance and disability insurance.
- Each product is supplied by a Supplier. When talking about products, reps always tie the product to the supplier.
- Every product has a default commission rate and overcommission rate. The commission is the % of the sale paid to the sales rep. Overcommission is the % of the commission paid to the company as administration fees. The commission paid for admin is called CPA by the company. For example, on a sale of $10,000 with comm at 20% and overcomm at 10%, the commission paid to the rep would be $2,000 and the CPA would be $200.
- Every sales transaction involves only one product to one client. For mutual funds there will be an Investment amount which will be used to calculate commissions. For annuities or insurance, commission is calculated on the premium amount.
- However, it is possible for a sales transaction to involve more than one sales rep. In some situations, several sales reps with different expertise may work on one large contract. In those cases the sale is divided among the reps on a percentage of work done.
And here's what our database model will look like once we get it written in Access:
Important points to note:
- mDateSubmit is the transaction date that all calculations are based on.
- mRefused is a Yes/No field that, if 'Yes', means that the client has been refused insurance and in fact the proposal is cancelled.
- If mdateCanceled is not blank, the client has decided to cancel the proposal before it has become effective.
- mComm and mOverComm are the commission % that can override the values in the Products table.
Contents
Next