Note: Descriptions are shown in the official language in which they were submitted.
CA 02539652 2006-03-15
INTERNET-BASED MARKETING AND SALES APPLICATION AND
METHOD FOR TARGETED MARKETING OF A PRODUCT AND/OR
SERVICE
Field of the Invention
[0001] The present invention relates generally to marketing and sales
and in particular to a distributed computer network embodying a marketing and
sales application, to a method for managing a rollover and to a method for
verifying data in a spreadsheet.
Background of the Invention
[0002] It is well known that marketing activities often translate directly
into business opportunities. Although many businesses take advantage of
changes in marketing techniques, some well-established business sectors have
failed to do so. The insurance industry is an example of such a business
sector
and has typically made use of a shotgun marketing approach to target potential
prospects. During this approach, a service such as Dunn & Bradstreet is hired
by an insurer to develop a list of potential prospects. The prospects in the
list
are passed along to a telemarketer who is responsible for calling the
prospects
to determine if they are interested in the insurance offered by the insurer.
In
many instances a significant number of prospects in the developed list are
inappropriate. The costs associated with enlisting a telemarketer to call the
prospects in the list can be significant. Thus, having lists of prospects with
significant numbers of inappropriate prospects is problematic. Also, there is
no
control of data collected by the telemarketer relating to the called
prospects. As
will be appreciated there is a need for improved marketing and sales
techniques in this and other business sectors.
[0003] Additionally, in many industries, it is not uncommon for a product
and/or service provider to make a decision to discontinue a line of business
representing an insurance product. Where the product is insurance, the insurer
may be obligated to honor the current policies for the remainder of the term,
which is typically one year. In such instances, policyholders are advised that
the insurer will discontinue coverage after the end of the policyholders'
policy
year. The policy year end dates are typically distributed over the course of
the
CA 02539652 2006-03-15
-2-
year. Where an agent, commonly referred to in the insurance industry as an
"insurance broker" or simply "broker", has placed business with the current
insurer, the broker is generally interested in placing the block of insurance
business, generally referred to as a book of insurance, with another insurer
himself; otherwise the individual policyholders may seek alternate insurers
themselves or via other brokers, thus removing the broker from the revenue
stream. In such cases, it can be advantageous for a broker to proactively
contact an alternate insurer for the insurance product sought, such that the
broker is able to provide policyholders with a readily available alternative.
Further, the broker may be in a position to benefit the policyholders by
obtaining
discounted rates for the insurance to be purchased, additional features for
the
insurance, or some other benefit where the broker is looking to transfer a
block
of business. Often, the discounts are related to the portion of the block of
business that the broker believes will actually transfer to the new insurer.
[0004] This transfer of a block of business is commonly referred to as a
"rollover". Once an agreement between the broker and the new insurer has
been established, the broker markets the insurance provided by the new
insurer to the policyholders ("prospects"). The broker typically contacts each
policyholder near to the policy expiry date of the current insurance policy to
allow the policyholder to quickly obtain continued insurance coverage and
thereby provide seamless coverage to the policyholder.
[0005] Presently, brokers typically use internal tools or information
management systems to manage the reminders for the policy expiry date
notices. In the case of a rollover, the broker can either set reminders in
some
type of database system in which the data is maintained for each policyholder
or can export the data to an information management system such as a
personal information manager. By using such a system however, the insurer
must rely on the broker for updated information and cannot monitor the updated
status of the rollover.
[0006] Additionally, where the broker is responsible for a number of
marketing campaigns and rollovers, manual intervention is typically required
to
produce useful reports and summaries to assist the broker in managing the
CA 02539652 2006-03-15
-3-
campaigns and rollovers. Further, where an insurer is operating a number of
campaigns and rollovers, the insurer is not able to view their current status
and
relies on information reports provided by the brokers.
[0007] It is therefore an object of the present invention to provide a novel
distributed computer network embodying a marketing and sales application, a
method for managing a rollover and a method for verifying data in a
spreadsheet.
Summary of the Invention
[0008] Accordingly, in one aspect there is provided a distributed
computer network comprising:
an item provider computer system maintaining at least one
dataset in a database, said at least one dataset identifying a block of
business
comprised of a list of prospects that have purchased products from another
item provider through an agent, said item provider computer system running a
marketing and sales application, said agent having indicated interest in
transferring said block of business from said other item provider to said item
provider; and
at least one agent client computer system communicating with
said item provider computer system over a secure Internet connection to
enable said agent to interact with said marketing and sales application, said
agent being provided with on-line access to said dataset to track said block
of
business in said database.
[0009] In one embodiment, the item provider computer system includes
at least one web server and at least one database server. The said at least
one web server communicates with the at least one agent computer system.
The at least one database server accesses the database in response to the at
least one web server and provides data to the web server for display by the at
least one agent computer system. The database can maintain a plurality of
different datasets representing a plurality of different blocks of business.
The
CA 02539652 2006-03-15
-4-
item provider computer system communicates with a plurality of agent client
computer systems.
[0010] The agent can access the item provider computer service to
indicate whether a prospect obtains products from the item provider. The item
provider is able to track a transfer rate equal to the portion of business
transferred from the other item provider to the item provider over all of the
block
of business.
[0011] When the item is insurance, the gathered data that is used to
determine when the agent contacts each prospect, is the expiry date ("ex-
date")
of each prospect's existing insurance policy with the other item provider. The
agent contacts each prospect a fixed number of days prior to the ex-date.
[0012] The items can be inputted by the item provider and can be
verified by the agent.
[0013] In another aspect, there is provided a distributed computer
network comprising:
an item provider computer system maintaining at least one
campaign in a database, said at least one campaign identifying a first list of
prospects for purchasing an item, said item provider computer system running
a marketing and sales application, said marketing and sales application
allowing an item provider user to electronically appoint an agent for said at
least
one campaign, said item provider computer system additionally maintaining at
least one rollover in said database, said rollover identifying a second list
of
prospects that have purchased said item from another item provider via said
agent, said agent being interested in selling said item from said item
provider to
said prospects in said second list; and
at least one agent client computer system communicating with
said item provider computer system over a secure Internet connection to
enable said agent to interact with said marketing and sales application, said
agent being provided with on-line access to said second list of prospects in
said
database, and said agent being provided with on-line access to said first list
of
prospects in said database when said agent is electronically appointed.
CA 02539652 2006-03-15
-5-
[0014] The items can have an expiry date, wherein each of the
prospects in the first list have previously purchased the items, and wherein
the
expiry dates for the first and second lists of prospects are maintained by the
item provider computer system. Further, the item provider computer system
can be operable to present a combined list of the prospects on the first and
second lists in date order.
[0015] In a further aspect, there is provided an Internet-based marketing
and sales application to facilitate targeted marketing of an item supplied by
an
item provider, said marketing and sales application comprising:
means for maintaining at least one dataset in a database, said at
least one dataset representing a block of business purchased from another
item provider through an agent, said agent having indicated interest in
transferring said block of business from said other item provider to said item
provider; and
means for managing said dataset electronically to track what
portion of said block of business is transferred to said item provider.
[0016] The block of business can be comprised of a plurality of
insurance policies issued to a number of policyholders. Further, the means for
managing can include means to track expiry dates associated with the
insurance policies.
[0017] In a still further aspect, there is provided a method for managing a
rollover comprising the steps of:
receiving a dataset for a rollover, said dataset comprising a
number of policyholders that purchased insurance policies from another
insurance provider and managed by an agent;
storing said dataset in a computer system; and
providing said agent with access to said dataset stored in said
computer system to indicate whether each of said policyholders will purchase a
corresponding insurance policy from an insurance provider.
[0018] The dataset can include an expiry date for each of the insurance
policies and premiums corresponding to the insurance policies from the other
insurance provider.
CA 02539652 2006-03-15
-6-
[0019] The dataset is stored in a database and the step of providing is
performed by providing the agent access to a web server in communication with
the database.
[0020] The method can include the step of receiving transfer information
from the agent indicating whether the policyholders will purchase insurance
policies from the insurance provider.
[0021] In accordance with a still yet further aspect, there is provided a
method for determining a secondary value having a second-highest priority in a
list of values in a spreadsheet, comprising:
determining a primary value having a highest priority in said list of
values;
determining if more than one of said values in said list is equal to
said primary value; and
determining said secondary value to be equal to said primary
value if more than one of said values in said list is equal to said primary
value,
and as one of said list of values not equal to said primary value having a
relative highest priority if only one of said values in said list is equal to
said
primary value.
[0022] In still yet another aspect, there is provided a method for verifying
data in a spreadsheet, said data including a set of records, comprising:
determining if each of a subset of said set of records having a first
common trait share a second common trait; and
indicating whether said subset of said sets of records share said
second common trait.
[0023] In still yet another aspect, there is provided a method of verifying
data in a spreadsheet, comprising:
placing a set of records in said spreadsheet;
locating a subset of said records corresponding to an entity;
checking whether said records in said subset are valid; and
providing a visual notification when at least one of the following
conditions is met: the number of said records in said subset is undesirable,
and
at least one of said records in the subset are invalid.
CA 02539652 2006-03-15
-7-
Brief Description of the Drawings
[0024] Embodiments will now be described more fully with reference to
the accompanying drawings in which:
Figures 1 a to 1 c are flowcharts showing the steps performed
during a method for targeted marketing of a product and/or service;
Figures 2a to 2c show an exemplary marketing material mailer;
Figure 3 is an exemplary script used by a telemarketer during
performance of the method of Figures 1 a to 1 c.
Figure 4 is an exemplary lead sheet used by a telemarketer
during performance of the method of Figure 1 a to 1 c.
Figure 5 shows an exemplary thank you letter mailer;
Figure 6 is a schematic block diagram of a database structure;
Figure 7 is a diagram illustrating a distributed computer
architecture including an insurer computer system, a telemarketer computer
system and broker computer systems communicating over the Internet;
Figures 8a to 8jj are screen shots of web pages accessible to an
administrator user accessing a marketing and sales application running on the
insurer computer system illustrated in Figure 7;
Figures 9a to 9g are screen shots of web pages accessible to a
telemarketer user accessing the marketing and sales application;
Figures 10a to 10o are screen shots of web pages accessible to a
broker user accessing the marketing and sales application;
Figure 11 is a flowchart showing the steps performed during
another method for targeted marketing of a product and/or service;
Figures 12a to 12z are screen shots of web pages accessible to
an administrator user accessing a marketing and sales application carrying out
the method of Figure 11 and running on the insurer computer system illustrated
in Figure 7;
Figures 13a to 13k are screen shots of web pages accessible to a
broker user accessing the marketing and sales application;
CA 02539652 2006-03-15
-8-
Figure 14 is a schematic diagram of the layout of a Microsoft
Excel workbook for analyzing the performance of policies generated as a result
of the targeted marketing method of Figure 1;
Figure 15 shows a portion of a data import sheet of the workbook
of Figure 14;
Figures 16a to 16d show a portion of an annual summary sheet
of the workbook of Figure 14;
Figure 17 shows a portion of a price summary sheet of the
workbook of Figure 14;
Figure 18 shows a portion of a policy summary sheet of the
workbook of Figure 14;
Figure 19 shows a portion of a campaign summary sheet of the
workbook of Figure 14;
Figures 20a to 20c show a portion of a broker summary sheet of
the workbook of Figure 14;
Figure 21 shows a portion of a new policies summary sheet of the
workbook of Figure 14;
Figure 22 shows a formula used to validate data in the workbook
of Figure 14;
Figure 23 shows a formula used to determine the second-highest
loss from a range of values in the workbook of Figure 14;
Figure 24 shows a data validation calculation in accordance with
another aspect of the present invention;
Figure 25 shows a data validation formula from the data
validation calculation of Figure 24; and
Figure 26 shows another data validation formula from the data
validation calculation of Figure 24.
Detailed Description of the Embodiments
[0025] A distributed computer network embodying a marketing and sales
application for targeted marketing of a product and/or service (herein
referred
generically to as "marketed item") and methods for managing a rollover and for
CA 02539652 2006-03-15
-9-
verifying data in a spreadsheet are provided. In one method, a campaign for
the item is established. The campaign identifies a list of prospects within a
specified geographical region. A broker and a telemarketer are appointed to
the campaign. The telemarketer contacts the prospects in the list to determine
the prospects potentially interested in the item ("actual prospects"). The
appointed broker is provided with a list of the actual prospects so that the
appointed broker may contact the actual prospects in an attempt to sell the
item
to the actual prospects. As will be appreciated, by providing the appointed
broker with a list of actual prospects, the appointed broker is given targeted
leads. In this embodiment, the marketed item is insurance. As mentioned
previously, the insurance industry has been slow to change marketing
techniques used to offer insurance to prospective clients ("prospects"). The
method for targeted marketing of insurance will now be generally described
with
particular reference to Figures 1 a to 1 c.
[0026] Initially, a campaign for marketing and selling insurance to a
particular target group within a specified geographical region is conceived by
the insurer (step 20). The target group relates to a particular industry
sector
and will typically include a plurality of prospects operating businesses
within the
specified geographical region. Once the target group of the campaign is
determined, a broker for that particular target group is appointed by the
insurer
(step 22). The target group and appointed broker are then verified to ensure
they are profitable (step 24). A number of different brokers may be considered
during step 22 before the profitability threshold of step 24 is met. If a
profitable
target group and broker combination for the campaign cannot be determined at
step 24, the campaign is dropped (step 26). After a profitable target group
and
broker combination is determined at step 24, a starting prospect list for the
campaign is developed that includes potential prospects in the target group
(step 28). The starting prospect list is developed using a number of list
resources such as Info Canada, Dunn & Bradstreet, association lists, brokers
lists and other list sources that identify potential prospects within the
specified
geographical region. The prospect list identifies the prospects by name,
address and other relevant information available.
CA 02539652 2006-03-15
-10-
[0027] Once the starting prospect list has been completed, the starting
prospect list is expanded using Internet searches, library resources and/or
other
research (step 30). The expanded prospect list is then run through a two-stage
cleaning process. During the first cleaning stage, the expanded prospect list
is
examined to locate and remove listed prospects that are already clients of the
insurer (step 34). During the second cleaning stage, the modified prospect
list
is examined to locate and remove listed prospects that are already clients of
the appointed broker (step 38). Prospects deleted from the expanded prospect
list of course, create a starting market for the target group insurance. With
the
prospect list cleaned, underwriters of the insurer review the risks associated
with the target group and draft policies and marketing material (step 40).
[0028] Following step 40, a three stage marketing process is performed.
Initially, marketing material is mailed directly to the prospects on the
cleaned
prospect list (step 44). The marketing material is generated and mailed by the
insurer but is on the letterhead of the appointed broker. Figures 2a to 2c
show
an exemplary marketing material mailer.
[0029] After the marketing material has been mailed, the cleaned
prospect list is given to an appointed telemarketer (step 46). The appointed
telemarketer in turn contacts the prospects on the cleaned prospect list to
determine which prospects on the cleaned prospect list are prospects that are
actually interested in the target group insurance ("actual prospects"). Actual
prospects are asked a series of questions following a script such as that
shown
in Figure 3 (step 48). Lead sheets are completed by the telemarketer based on
the information gathered in response to the series of questions as shown in
Figure 4. The completed lead sheets are returned to the insurer allowing the
insurer to identify the actual prospects listed on the cleaned prospect list
(step
50). The collected data is also used to enhance the information in the
prospect
list allowing more detailed information concerning prospects to be maintained.
Thank you letters are then generated and mailed to the actual prospects by the
insurer but on the letterhead of the appointed broker (step 52). The thank you
letters inform the actual prospects that they will be contacted by the
appointed
CA 02539652 2006-03-15
-11-
broker a fixed number of days prior to the expiry date (ex-date) of their
current
insurance policy. An exemplary thank you letter mailer is shown in Figure 5.
[0030] Once the telemarketing has been completed and with the actual
prospects from the cleaned prospect list known, the appointed broker is
provided with the list of actual prospects (i.e. targeted leads) so that the
appointed broker may contact the actual prospects at the appropriate times to
set up appointments (step 54). In this manner, producers employed by the
appointed broker may meet with the actual prospects in an attempt to sell the
actual prospects the target group insurance. When an actual prospect agrees
to purchase the target group insurance, the insurer is notified so that a
policy
may be written (step 56). If a policy for the prospect is written, the policy
information is added to the data associated with the prospect. If the actual
prospect declines to purchase the target group insurance, depending on the
reason for declining the target group insurance, the actual prospect may be
contacted in the future if the actual prospect is considered to be a cold lead
(step 58). Prospects that are considered to be dead leads are not pursued
further.
[0031] To facilitate performing the above method, an Internet-based
marketing and sales application is maintained by the insurer and is accessible
to telemarketers and appointed brokers over the Internet. The marketing and
sales application data is maintained in a database 80 (see Figure 6) and
includes data relating to all campaigns 82 being pursued by the insurer, the
broker 84 appointed to each campaign 80, the telemarketer 86 appointed to
each campaign 80 and the prospects 88 (both cleaned and actual) associated
with each campaign 80.
[0032] Specifically, the database stores a profile for each campaign
being pursued by the insurer, a profile for each broker appointed to a
campaign
and a profile for each prospect associated with each campaign. Each
campaign profile identifies the campaign by target group, target area
identifying
the specified geographical region, campaign description, the broker appointed
to the campaign, the campaign start date, the campaign end date and general
commentary.
CA 02539652 2006-03-15
-12-
[0033] Each broker profile identifies the broker name, the broker agency
number, and contact information.
[0034] Each prospect profile identifies the prospect by name, contact
information, ex-date if the prospect has not been sold insurance and policy
number if the prospect has been sold insurance.
[0035] Turning now to Figure 7, a distributed computer architecture
supporting the marketing and sales application is shown and is generally
identified by reference numeral 100. Computer architecture 100 includes an IIS
web server 102, a SQL database server 104 and a policy administration system
105 in communication over a local area network located at the insurer's
business location. The web server 102 runs the Internet-based marketing and
sales application, which includes active server page (ASP) files containing
code
written in Visual Basic (VB) script, Java script and Hypertext Markup Language
(HTML). The SQL database server 104 supports the SQL database 80 that
acts as the backend repository for the marketing and sales application data.
The policy administration system 105 stores policy data and permits
management thereof. A telemarketer client computer system 106 and broker
client computer systems 108 communicate with the web server 102 over the
Internet 110 via web browsers running on the client computer systems 106 and
108. As is well known, the Internet 110 provides a backbone of high-speed
data communication lines between host computers that route data and
messages using the Transmission Control Protocol/Internet Protocol (TCP/IP)
suite of protocols. The telemarketer client computer system 106 and the broker
client computer systems 108 may be stand-alone personal computers, local
area networks or Intranets.
[0036] During communications between the web server 102 and the
client computer systems 106 and 108, the web server 102 sends ASP files to
the client computer systems 106 and 108 in response to requests generated by
the web browsers. The ASP files received by the client computer systems 106
and 108 cause the web browsers to display formatted web pages to the users
of the client computer systems 106 and 108. This enables users to access and
interact with the marketing and sales application through a web-based
graphical
CA 02539652 2006-03-15
-13-
user interface (GUI). Although only one telemarketer client computer system
106 and two broker client computer systems 108 are shown, those of skill in
the
art will appreciate that this is for ease of illustration only. The web server
102
may of course communicate with virtually any number of telemarketer client
computer systems 106 and broker client computer systems 108.
[0037] As mentioned above, the marketing and sales application
supports and is used extensively during performance of the above-described
method for targeted marketing of an item. Data in the database 80 that is
relevant to the telemarketers and appointed brokers is made accessible to them
via web browsers over secure Internet connections. Data in the database 80
that is relevant to the insurer is similarly accessible via web browsers over
secure Intranet connections. As is common, the marketing and sales
application provides for staged access to data in the database 80 based on
assigned user security level. At the administrator level, unrestricted access
to
data in the database 80 is permitted. Various other security levels ranging
from
significant data access to read only access are also provided for; however,
since this type of staged security data access is well known and unrelated to
the present invention, further specifics will not be described herein.
[0038] During performance of the method of Figures 1 a to 1 c, once it is
verified that the target group and appointed broker for a campaign are
profitable, the campaign is entered into the marketing and sales application
by
completing a formatted web page. Once saved, the campaign profile is stored
in the database 80. The prospects on the expanded prospect list associated
with the campaign are also entered into the marketing and sales application by
completing formatted web pages. Once saved, the prospect profiles are stored
in the database 80.
[0039] Once the expanded prospect list has been cleaned to remove
existing clients of the insurer and the broker at steps 34 and 38, the
marketing
and sales application is conditioned to generate automatically a marketing
material mailer similar to that shown in Figures 2a to 2c, for each of the
prospects on the cleaned prospect list.
CA 02539652 2006-03-15
-14-
[0040] The marketing and sales application is also used to select the
telemarketer to be used during the campaign via a formatted web page. Once
the telemarketer has been selected, the marketing and sales application
permits the selected telemarketer to access relevant prospect data thereby to
enable the telemarketer to contact and question the prospects on the cleaned
prospect list.
[0041] The marketing and sales application permits the telemarketer to
input data to the marketing and sales application based on the questionnaire
results so that the marketing and sales application can identify the actual
prospects in real time and automatically generate the thank you letter mailers
shown in Figure 5.
[0042] Once the actual prospects are known and identified by the
marketing and sales application, the marketing and sales application permits
the appointed broker to access relevant prospect data thereby to enable
producers of the appointed broker to contact the actual prospects in an
attempt
to sell the target group insurance.
[0043] The graphical user interface of the marketing and sales
application that exposes data in the database 80 to the insurer
(administration
level), the appointed broker and the appointed telemarketer and provides for
the above-described functionality will now be described with particular
reference to Figures 8a to 10o.
Insurer Web Pages Excluding Rollovers
[0044] Figure 8a shows the administrator home web page 200 that is
presented to an administrator user when the administrator user logs into the
marketing and sales application. As can be seen, a series of hyperlinks are
provided across the top of the web page 200, namely a home hyperlink 202, a
campaigns hyperlink 204, a users hyperlink 206, a brokers hyperlink 208, a
reports hyperlink 210, an updates hyperlink 212, a queries hyperlink 214 and a
search hyperlink 216. The home hyperlink 202 is linked to the administrator
home web page 200. The remaining hyperlinks 204 to 216 are linked to other
CA 02539652 2006-03-15
-15-
web pages as will be described. The hyperlinks 202 to 216 appear across the
top of each of the insurer web pages to facilitate navigation. In addition to
the
above described hyperlinks, the administrator home web page 200 lists the
total number of prospects, the online users accessing the marketing and sales
application and the total number of portfolio rollovers. The total number of
prospects for all campaigns being pursued by the insurer is broken down into a
variety of categories. These categories include the number of prospects
awaiting insurer cleanup 217, the number of prospects identified as clients of
the insurer during cleanup 218, the number of prospects awaiting broker
cleanup 220, the number of prospects identified as clients of brokers during
cleanup 222, the number of prospects awaiting telemarketing 224, the number
of prospects currently in telemarketing 226, the number of prospects
considered to be cold leads 228, the number of prospects considered to be
dead leads 230, the number of ex-date leads 232, the number of appointment
leads 234, the total number of other leads 236 and the number of policies
written 238. The total number of portfolio rollovers is also broken down into
a
variety of categories. These categories include the number of accounts written
233, the number of ex-date leads 235, the number of accounts not written 237
and the number of unique rollover account names 239. The prospect and
rollover categories 217 to 239 are presented as hyperlinks linked to quick
statistics detail web pages.
[0045] Figure 8b shows the quick statistics detail web page 241 that is
linked to hyperlink 217. As can be seen, web page 241 lists the prospect 241 a
that is identified as awaiting cleanup by the insurer. This data requires
scrubbing to prepare it for insurer and broker analysis. Each identified
prospect
is presented as a hyperlink linked to an associated prospect entry web page
that exposes the prospect's profile.
[0046] Figure 8c shows the prospect entry web page 243 that is opened
in response to selection of the prospect 241 a on web page 241. The web page
243 allows viewing and editing of the prospect's data. The prospect's data
includes an expiry date for the prospect's current policy, if any. The expiry
date
CA 02539652 2006-03-15
-16-
can be selected by means of a date selection applet that is triggered by the
selection of a date button 243a.
[0047] Figure 8d shows a popup browser window 245 arising from the
selection of the date button 243a of web page 243. The popup window 245
allows a user to select the expiry date for the prospect.
[0048] Figure 8e shows the quick statistics detail web page 240 that is
linked to hyperlink 218. As can be seen, web page 240 lists the prospects that
are identified as being existing clients of the insurer during the first
cleaning
stage (step 34). Each identified prospect is presented as a hyperlink linked
to
an associated prospect web page similar to that shown in Figure 8p that
exposes the prospect's profile.
[0049] Figure 8f shows the quick statistics detail web page 242 that is
linked to hyperlink 220. Web page 242 presents a list of prospects awaiting
cleanup by brokers. Each identified prospect is presented as a hyperlink
linked
to an associated prospect web page.
[0050] Figure 8g shows the quick statistics detail web page 244 that is
linked to hyperlink 222. As can be seen, web page 244 lists the prospects that
are identified as being existing clients of the brokers during the second
cleaning
stage (step 38). Each identified prospect is presented as a hyperlink linked
to
an associated prospect web page.
[0051] Figure 8h shows the quick statistics detail web page 246 that is
linked to the hyperlink 224. Web page 246 presents a list of the prospects
awaiting telemarketing. The prospects are listed together with their assigned
brokers and associated campaigns. Each identified prospect is presented as a
hyperlink linked to an associated prospect web page.
[0052] Figure 8i shows the quick statistics detail web page 248 that is
linked to the hyperlink 226. Web page 248 presents a list of the prospects
currently in telemarketing. Similar to web page 246, the prospects are listed
together with their assigned brokers and associated campaigns. Each
identified prospect is presented as a hyperlink linked to an associated
prospect
web page.
CA 02539652 2006-03-15
-17-
[0053] Figure 8j shows the quick statistics detail web page 250 that is
linked to hyperlink 228. Web page 250 lists prospects considered to be cold
leads. Similar to web pages 246 and 248, the prospects are listed together
with
their assigned brokers and associated campaigns. Each identified prospect is
presented as a hyperlink linked to an associated prospect web page.
[0054] Figure 8k shows the quick statistics detail web page 252 that is
linked to hyperlink 230. Web page 252 lists prospects considered to be dead
leads together with contact information. A select box is associated with each
prospect allowing the prospect to be selected and removed from the list by
clicking on the delete bar 254. Each identified prospect is presented as a
hyperlink linked to an associated prospect web page.
[0055] Figure 81 shows the quick statistics detail web page 256 that is
linked to hyperlink 232. Web page 256 lists the prospects from whom ex-date
information has been acquired. The prospects are listed together with their
assigned brokers, campaigns and ex-dates. Each identified prospect is
presented as a hyperlink linked to an associated prospect web page.
[0056] Figure 8m shows the quick statistics detail web page 258 that is
linked to hyperlink 234. Web page 258 lists the prospects with whom
appointments have been set up. The prospects are listed together with their
assigned brokers, campaigns and appointment times and dates. Each
identified prospect is presented as a hyperlink linked to an associated
prospect
web page.
[0057] Figure 8n shows the quick statistics detail web page 260 that is
linked to hyperlink 236. Web page 260 lists prospects derived from other leads
such as referrals and mail-ins. The prospects are listed together with their
assigned brokers and associated campaigns. Each prospect is presented as a
hyperlink linked to an associated prospect web page.
[0058] Figure 8o shows the quick statistics detail web page 262 that is
linked to hyperlink 238. Web page 262 lists the prospects for whom insurance
policies have been written. Each prospect is listed together with the assigned
broker and policy information. The policy information includes the written
policy
amount, the actual policy amount, the assigned policy symbol, the assigned
CA 02539652 2006-03-15
-18-
policy number and the assigned policy model. The policy information can be
updated by the user and saved by selecting the save bar 264. Each broker is
presented as a hyperlink linked to an associated broker web page that exposes
the broker's profile. Each prospect is presented as a hyperlink linked to an
associated prospect web page.
[0059] Figure 8p shows the current campaign home web page 266 that
is accessed by selecting the campaigns hyperlink 204. The web page 266
identifies each campaign being pursued by the insurer, by broker, target
group,
target area, start date, mail-in date and required work. The required work
identifies which marketing stage the campaign is at. The campaigns in the list
are presented as hyperlinks linked to associated campaign web pages similar
to that shown in Figure 8n that expose the campaign profiles. Depending on
the marketing stage, the listed marketing stage may be presented as text or as
a hyperlink linked to an associated web page. Selection of the hyperlink 270
allows the user to create and save a new campaign.
[0060] Figure 8q shows the campaign web page 268 linked to the eighth
campaign listed on the current campaign home web page 266. As can be
seen, the campaign web page 268 identifies the campaign target group, the
specified geographical region and appointed broker. Web page 268 also
identifies the total number of prospects associated with the campaign. The
prospects are broken down into the same categories as on the administrator
home web page 200. Some of the prospect categories are presented as
hyperlinks linked to web pages similar to those shown in Figures 8e, 8f, 8j,
81
and 8o. The campaign data can be updated and saved by the user by
selecting the save changes bar 269. A blank campaign web page 290 (see
Figure 8v) can be opened by selecting the create new campaign bar 270 on
web page 266 allowing the user to create and save new campaigns.
[0061] Figure 8r shows a prospects search web page 272 that is opened
in response to selection of the view prospects bar 274 on web page 268. The
prospects search web page 272 lists all of the prospects in the campaign.
Each prospect is presented as a hyperlink linked to an associated prospect web
page.
CA 02539652 2006-03-15
-19-
[0062] Figure 8s shows a new prospect input/upload web page 276 that
is opened in response to selection of the add prospect bar 278 on web page
268. The web page 276 allows prospects to be browsed and uploaded or
allows a blank prospect web page to be opened by selecting the manual entry
hyperlink 280. Figure 8t shows a blank new prospect web page 282 that allows
the user to create and save new prospects for the campaign.
[0063] Figure 8u shows an assign telemarketer web page 286 that
allows the user to assign a specific telemarketer to a campaign by selecting a
check box associated with the desired telemarketer (step 46).
[0064] Figure 8v shows a campaign entry web page 290 that allows a
user to enter in the specific details for a new campaign.
[0065] Figure 8w shows a brokers web page 292 that is opened in
response to selection of the brokers hyperlink 208. The web page 292
identifies each broker by agency number, broker name, telephone number,
email address and latest update. The agency numbers are presented as
hyperlinks linked to associated brokers web pages. Figure 8x shows the broker
web page 294 associated with the ninth broker listed on web page 292. A new
broker web page 296 is shown in Figure 8y and is opened in response to
selection of a create new broker bar 298 on web page 292 allowing the user to
create and save a new broker.
[0066] Figure 8z shows a reports home web page 300 that is opened in
response to selection of the reports hyperlink 210. The web page 300 includes
five hyperlinks linked to reports web pages, namely an all active campaigns
summary hyperlink 302, an all active campaigns summary breakdown hyperlink
304, an all active brokers summary hyperlink 306, a year to date report
hyperlink 308 and an expiry dates summary hyperlink 310. Also shown on web
page 300 are seven hyperlinks 312 to 319 linked to portfolio rollover content
that will be discussed further with reference to Figures 11 to 131.
[0067] Selection of hyperlink 302 opens an all active campaigns
summary web page 320 as shown in Figure 8aa. The web page 320 lists each
campaign pursued by the insurer, the broker appointed to handle each
campaign and statistical information relating to each campaign. The brokers
CA 02539652 2006-03-15
-20-
are presented as hyperlinks linked to associated broker web pages. The
campaigns are also presented as hyperlinks linked to associated campaign
web pages.
[0068] Selection of hyperlink 304 opens an all active campaigns
summary web page 322 as shown in Figure 8bb. The web page 322 lists all
active campaigns pursued by the insurer broken down by appointed broker
together with statistical information relating to the campaigns. The brokers
are
presented as hyperlinks linked to associated broker web pages.
[0069] Selection of hyperlink 306 opens an all active brokers summary
web page 324 as shown in Figure 8cc. The web page 324 lists each active
broker appointed by the insurer and presents statistical information relating
to
each active broker's performance over all campaigns handled by that broker.
[0070] Selection of hyperlink 308 opens a year to date report by
producer web page 326 as shown in Figure 8dd. The web page 326 lists each
producer handling campaigns and presents statistical information relating to
each producer's performance over all campaigns handled by that producer.
The brokers are presented as hyperlinks linked to associated broker web
pages. The producers are also presented as hyperlinks linked to associated
producer web pages (not shown). Selecting a radio button on the web page
326 allows a similar year to date web page 328 to be opened that is based on
prospect as shown in Figure 8ee.
[0071] Selection of hyperlink 310 opens an expiry dates calendar web
page 330 as shown in Figure 8ff. The web page 330 lists each campaign
pursued by the insurer, the broker appointed to each campaign and the number
of expiry dates acquired from actual prospects for the campaign on a month by
month basis.
[0072] Figure 8gg shows a search web page 332 that is opened in
response to selection of the update hyperlink 212. The search web page 332
allows the user to perform a search of the database 80 based on prospect
name. Figure 8hh shows a search result web page 334 presenting the results
of such a search.
CA 02539652 2006-03-15
-21 -
[0073] The marketing and sales application also includes query and
search features that can be accessed by selecting the query and search
hyperlinks 214 and 216 respectively. Selecting the query hyperlink 214 opens
a query web page 336 that allows the database 80 to be searched based on a
variety of qualifiers as shown in Figure 8ii. Selecting the search hyperlink
216
opens a prospect search web page 338 that allows the database 80 to be
searched based on prospect as shown in Figure 8jj.
[0074] As will be appreciated, the marketing and sales application GUI
allows the insurer to review, update and maintain all relevant data associated
with all of the campaigns pursued by the insurer. Since the marketing and
sales application generates campaign statistical information, the marketing
and
sales application allows telemarketer and broker performance to be effectively
monitored by the insurer.
Telemarketer Web Pages
[0075] Figure 9a shows the telemarketer home web page 400 that is
presented to the telemarketer user when the telemarketer user logs into the
marketing and sales application. As can be seen, a pair of hyperlinks are
provided across the top of the web page 400, namely a telemarketer home
hyperlink 402 and an appointments made hyperlink 404. The home hyperlink
402 is linked to the telemarketer home web page 400. The hyperlink 404 is
linked to another web page as will be described. The hyperlinks 400 and 402
appear across the top of each of the telemarketer web pages to facilitate
navigation. In addition to above described hyperlinks, the telemarketer home
web page 400 presents a list of the campaigns the insurer has appointed to the
telemarketer (step 46). The appointed broker for each campaign as well as the
campaign prospect list and status are also listed. The campaign status
identifies the status of each campaign as either complete or in progress. The
campaign prospect list is presented as a hyperlink either to a statistics web
page if the campaign has been completed or a view prospects list web page if
the campaign has not been completed.
CA 02539652 2006-03-15
-22-
[0076] The campaign web page 406 associated with the first campaign
listed on web page 400 is shown in Figure 9b. The completed campaign web
page 406 identifies the total number of prospects for the associated campaign
that were called broken down into a variety of categories. These categories
include the number of prospects awaiting a first call, the number of prospects
awaiting a follow-up call, the number of appointments made, the number of ex-
dates received, the number of cold leads and the number of dead leads.
[0077] The campaign web page 408 associated with the thirteenth
campaign listed on web page 400 is shown in Figure 9c. Similar to the
campaign web page 406, the campaign web page 408 identifies the total
number of prospects for the associated campaign to be called broken down into
the same variety of categories. In addition, the campaign web page 408 lists
each of the prospects as either awaiting a first call or awaiting a follow-up
call.
The prospects are presented as hyperlinks linked to associated prospect web
pages.
[0078] Figure 9d shows the prospect web page 410 for the second
prospect listed on the campaign web page 408. As can be seen, the prospect
web page in addition to information data fields includes three prospect status
radio buttons 412 to 416 that are used to set the status of the prospect.
Specifically, the prospect status radio buttons include a generate lead radio
button 412, a reject prospect radio button 414 and a save & follow up later
radio
button 416. When the save & follow up later radio button is selected 416, the
prospect remains in the awaiting a follow-up call category.
[0079] The generate lead radio button 412 is selected when the
telemarketer sets up an appointment with the prospect and/or receives an ex-
date from the prospect (i.e. the prospect is identified as an actual
prospect).
When the generate lead radio button 412 is selected, the telemarketer is
prompted to enter the appointment time and date and/or ex-date into a dialog
box 418 as shown in Figure 9e. The reject prospect radio button 414 is
selected when the telemarketer fails to set up an appointment with the
prospect
and/or fails to receive an ex-date from the prospect. When the reject prospect
CA 02539652 2006-03-15
-23-
radio button 414 is selected, the telemarketer is prompted to enter a
rejection
reason via a dialog box 420 as shown in Figure 9f.
[0080] Figure 9g shows an appointment list web page 422 that is opened
in response to selection of the appointments made hyperlink 404. As can be
seen, the appointment list web page 422 identifies all of the appointments
made by the telemarketer by date, prospect name, province, telephone
number, campaign and assigned broker.
[0081] As will be appreciated, the marketing and sales application GUI
allows the telemarketer to manage efficiently each campaign the telemarketer
handles. The GUI also permits the telemarketer to input relevant appointment
and/or ex-date information into the marketing and sales application allowing
actual prospects to be determined by the marketing and sales application in
real time. This of course permits faster lead turnaround time.
Broker Web Pages
[0082] Figure 10a shows the brokers home web page 500 that is
presented to the broker user when the broker user logs into the marketing and
sales application. As can be seen, a series of hyperlinks are provided across
the top of the web page 500, namely a broker home hyperlink 502, a reporting
hyperlink 504, and a search for prospect hyperlink 506. The broker home
hyperlink 502 is linked to the brokers home web page 500. The other
hyperlinks 504 and 506 are linked to other web pages as will be described.
The hyperlinks 502 to 506 appear across the top of each of the broker web
pages to facilitate navigation. In addition to the above described hyperlinks,
the
brokers home web page 400 lists the campaigns that have been assigned to
the broker as well as outstanding appointments. Each campaign in the list is
identified by name, leads, prospect clean up and policies written. The leads
are
presented as hyperlinks linked to associated lead web pages. The policies
written are also presented as hyperlinks linked to associated policy web
pages.
Each outstanding appointment in the list is identified by prospect, producer,
CA 02539652 2006-03-15
-24-
campaign, appointment date and status. The prospects are presented as
hyperlinks linked to associated prospect web pages.
[0083] The lead web page 510 associated with the first campaign in the
list presented on web page 500 is shown in Figure 10b. As can be seen, the
lead web page 510 identifies the total number of prospect leads for the
associated campaign broken down into a variety of categories. These
categories include the number of outstanding appointments, the number of
completed appointments that are awaiting further instruction, the number of
other leads, the number of rejections, the number of policies written and the
number of dead leads. The number of policies written is presented as a
hyperlink linked to an associated policies web page. The lead web page 510
also lists the outstanding appointments shown on the broker home web page
500 as well as a list of ex-dates and referrals. The ex-dates and referrais in
the
list are identified by prospect, producer and ex-date. The prospects are
presented as hyperlinks linked to associated prospect web pages.
[0084] The policies written web page 512 associated with the first
campaign in the list presented on web page 500 is shown in Figure 10c. The
policies written web page 512 lists prospects for whom new policies have been
written as well as premium information concerning the written policies. The
prospects are presented as hyperlinks linked to associated prospect web
pages.
[0085] Turning now to Figure 10d, the prospect web page 514
associated with the third prospect in the policies written list presented on
web
page 512 is shown. As can be seen, the prospect web page 514 includes
identification and policy data fields as well as four selectable radio buttons
520
to 526. The radio buttons include a business written radio button 520, a
business not written radio button 522, an appointment complete awaiting
further
information radio button 524 and a save & follow up later radio button 526.
When the save & follow up later radio button 526 is selected, the prospect
remains in the outstanding appointments category. When the appointment
complete awaiting further information radio button 524 is selected, the
prospect
CA 02539652 2006-03-15
-25-
is placed in the completed appointments that are awaiting further instruction
category.
[0086] The business not written radio button 522 is used when the
broker fails to land the prospect. When the business not written radio button
is
selected 522, the broker is prompted to enter a rejection reason via a dialog
box 530 as shown in Figure 10e. The prospect is then placed in the rejections
category.
[0087] Figure 10f shows the prospect web page 532 associated with a
prospect in the appointment list presented on web page 500. As can be seen,
the prospect web page 532 is similar to web page 514 and identifies the date
of
the outstanding appointment. Since a policy has not been written for this
prospect, the policy data fields are incomplete.
[0088] Figure lOg shows the reporting home page 540 that is accessed
by selecting the reporting hyperlink 504. As can be seen the reporting home
page 540 includes an all active campaigns summary web page hyperlink 542,
an all active brokers summary web page hyperlink 544, a monthly reports by
producer or prospect hyperlink 546 and an expiry dates summary hyperlink
548.
[0089] Selecting hyperlink 542 opens the all active campaigns summary
web page 550 as shown in Figure 10h. The web page 550 lists all of the
campaigns handled by the broker together with statistical information for each
campaign. The statistical information includes the total number of prospects
in
the campaign, the number of prospects in the campaign after cleaning, the
number of ex-dates acquired, the number of appointments made, the number
of quotes provided to prospects, the number of policies written, the value of
the
policies, the number of ex-dates acquired vs. the number of prospects after
cleaning, the number of appointments vs. the number of acquired ex-dates, the
number of quotes vs. the number of appointments and the number of written
policies vs. the number of quotes. The campaigns are presented as hyperlinks
linked to associated campaign web pages.
[0090] Selecting hyperlink 544 opens the all active brokers summary
web page 552 as shown in Figure 10i. The web page 552 lists each of the
CA 02539652 2006-03-15
-26-
brokers used by the insurer and presents statistical information for the
brokers
across all of the campaigns handled by the brokers.
[0091] Selecting hyperlink 546 opens the monthly reports by producer
web page 554 as shown in Figure 10j. The web page 554 lists each producer
associated with the broker. Statistical information associated with each
producer is also listed. The statistical information includes the number of ex-
dates acquired by the producer, the number of appointments set up by the
producer, the number of rejections, the number quotes made by the producer,
the number of policies sold by the producer, the amount quoted by the broker
for that producer, the amount of business sold to the insurer for the producer
as
well as the appointment vs. ex-date, quote vs. appointments and sold vs. quote
ratios. The monthly report by prospect web page 556 is opened by selecting
the radio button 558 on web page 554 as shown in Figure 10k. The web page
556 lists each prospect, the producer associated with the prospect and the ex-
date associated with the prospect. The monthly report by prospect web page
556 also lists for each prospect, the appointment date, quoted premium, actual
premium, underwriter policy number, competing quote and rejection reason.
[0092] Selecting hyperlink 548 opens the expiry dates calendar web
page 558 as shown in Figure 101. The web page 558 lists the number of ex-
dates on a month by month basis for each of the campaigns handled by the
broker.
[0093] Figure 10m shows the search home web page 560 that is
accessed by selecting the hyperlink 506. The search home web page 560
allows searches to be performed based on prospect name or campaign.
[0094] Figure 10n shows a new prospect entry web page 562 that is
completed by the broker when a new prospect for a campaign is acquired
through a referral or other lead. In addition to contact information data
fields,
the new prospect entry web page 562 includes an ex-date policy data field.
The ex-date information is entered into this data field using the date
selector
dialog box 564 shown in Figure 10o. When a new prospect is created by a
broker, the new prospect becomes active 70 days in advance of the ex-date.
CA 02539652 2006-03-15
-27-
Rollovers
[0095] As mentioned previously, a rollover refers to a transfer of a block
of business from one insurer to another insurer. Often rollovers occur as a
result of an insurer discontinuing an insurance product. The expiry dates of
the
policies usually govern when rollovers must take place and as a result, it is
useful and beneficial to track this information. Accordingly, when a rollover
occurs, i.e., the broker determines that a new insurer should be found for a
block of business consisting of an insurance product purchased by a group of
policyholders, the broker contacts a new insurer, establishes that it is
desirable
to direct the policyholders to purchase the insurance product from the new
insurer and then remits a dataset to the web server 102 of Figure 7 (see step
604 in Figure 11) consisting of a number of details of the policyholders,
including, for example, name, address and annual premium. Upon receipt of
the dataset from the broker, the web server 102 performs data integrity
analysis
to determine if the data contained in the dataset appears to be valid.
Alternatively, the broker can remit the dataset to the insurer via portable
storage
such as an optical media disk. In turn, the insurer can register the dataset
with
the web server 102. Once the dataset is deemed valid, the dataset is
registered with the SQL database server 104.
[0096] Upon registration of the dataset with the SQL database server
104, the dataset is made accessible to the broker via the web server 102 at
step 608. The broker then is able to view and modify the data in the dataset
to
manage the rollover. This can include updating the data, tracking the expiry
dates of the existing policies for each prospect, booking appointments to meet
or follow up with the prospect, etc. If the insurer deems that it does not
want to
issue a policy to a prospect, this information can be entered into the
computer
system. If the prospect obtains insurance from another insurer, this
information
can be entered into the computer system, along with the reason for the
prospect's decision, if one is provided. Accordingly, if the prospect
purchases
insurance from the insurer, this can be noted in the computer system.
CA 02539652 2006-03-15
-28-
[0097] Also, when the dataset is entered into the database, the insurer is
able to access the dataset to confirm that the data provided matches any
external accounts of the block of business at step 612. Further, the insurer
is
then able to track the progress of the rollover by being presented with the
status
of each prospect, the overall statistics, etc.
[0098] In such a manner, the computer system allows the broker to
manage the rollover, allows the insurer to track the progress and success of
the
rollover and provides a central database evidencing the performance of the
rollover to both the broker and the insurer.
Insurer Web Pages for Rollovers
[0099] As mentioned above, the prospect categories related to rollovers
are presented as hyperlinks 233, 235, 237 and 239 linked to quick statistics
detail web pages.
[00100] Figure 12a shows the quick statistics detail web page 702 that is
linked to hyperlink 233. As can be seen, web page 702 lists the prospects that
have purchased insurance from the insurer. Each identified prospect is
presented as a hyperlink linked to an associated prospect web page similar to
that shown in Figure 12b that exposes the prospect's profile.
[00101] Figure 12b shows the prospect detail web page 704 that is
opened in response to selection of a prospect on web page 702. The web
page 704 allows viewing and editing of the prospect's data and shows the
rollover status of the prospect. In the illustrated example, the prospect's
record
has previously been updated to reflect that the prospect has purchased
insurance from the insurer. The prospect's data includes an expiry date for
the
prospect's current policy.
[00102] Figure 12c shows the quick statistics detail web page 706 that is
linked to hyperlink 235. As can be seen, web page 706 lists the prospects from
whom ex-date information has been acquired. Each identified prospect is
presented as a hyperlink linked to an associated prospect web page.
CA 02539652 2006-03-15
-29-
[00103] Figure 12d shows the quick statistics detail web page 708 that is
linked to hyperlink 237. As can be seen, web page 708 lists the prospects who
either have rejected an offer of insurance from the insurer or whom the
insurer
has rejected for insurance. Again, each identified prospect is presented as a
hyperlink linked to an associated prospect web page.
[00104] Figure 12e shows the rollover entry web page 268 linked to the
sixteenth item listed on the current campaign home web page 266. As can be
seen, the rollover web page 268 identifies the rollover target group and the
broker. Web page 268 also identifies the total number of prospects associated
with the campaign. The prospects are broken down into the same categories
as on the administrator home web page 200, but as the data has been provided
by the broker and represents prospects insured through another insurer, the
prospect list does not have to be cleaned in same manner as the data for a
campaign as discussed previously. The rollover data can be updated and
saved by the user by selecting the save changes bar 269. The add prospects
hyperlink 710 can be used to enter new prospects into the rollover.
[00105] Figure 12f shows the ex-date lead prospect web page 712 for the
rollover illustrated in Figure 12e. As can be seen, web page 712 lists the
prospects from whom ex-date information has been acquired. Each identified
prospect is presented as a hyperlink linked to an associated prospect web
page.
[00106] Figure 12g shows the prospect detail web page 704 that is
opened in response to selection of a prospect on web page 712. The web
page 704 allows viewing and editing of the prospect's data and shows the
rollover status of the prospect. In the illustrated example, the prospect's
record
indicates the expiry date for the prospect's current policy and that the
prospect
is an expiry date lead.
[00107] Figure 12h shows the cold lead prospect web page 716 for the
rollover illustrated in Figure 12e. As can be seen, web page 716 lists the
prospects from whom ex-date information has not been acquired. These
prospects may be entities who have not purchased the type of insurance in
question from the broker, but who are believed to be interested in the type of
CA 02539652 2006-03-15
-30-
insurance. Again, each identified prospect is presented as a hyperlink linked
to
an associated prospect web page.
[00108] Figure 12i shows the cold lead prospect web page 718 for the
rollover illustrated in Figure 12e. As can be seen, web page 718 lists the
prospects that have purchased insurance from the insurer. Again, each
identified prospect is presented as a hyperlink linked to an associated
prospect
web page.
[00109] Figure 12j shows the prospect input web page 720 that is linked
to hyperlink 710. Input page 720 allows a user to upload a file containing new
prospect data or to manually enter in the new prospect data by selecting the
manual entry hyperlink 721.
[00110] Figure 12k shows a data verification web page 722 showing
uploaded data entered through page 720. A user can visually inspect the data
uploaded to verify its integrity before incorporating the data into the
rollover
using the save button 723.
[00111] Figure 121 shows a prospect entry web page 724 that is linked to
hyperlink 721 on page 720. Page 724 presents fields allowing a user to
manually enter the data for a prospect into the rollover.
[00112] Referring again to Figure 8z, a number of hyperlinks are
presented to allow a user to view various pages presenting reports with
respect
to the rollovers.
[00113] Selection of hyperlink 312 on page 300 in Figure 8z opens an all
active rollovers summary web page 726 as shown in Figure 12m. The web
page 726 lists each rollover pursued by the insurer, the broker managing each
rollover and statistical information relating to each rollover. In this case,
illustrated web page 726 only shows one active rollover. The brokers are
presented as hyperlinks linked to associated broker web pages. The rollovers
are also presented as hyperlinks linked to associated campaign web pages.
[00114] Selection of hyperlink 313 on page 300 opens an active rollovers
summary graph web page 728 as shown on Figure 12n that presents a
graphical representation of the success of each rollover registered in the
database. While web page 728 shows only one rollover with no success thus
CA 02539652 2006-03-15
-31-
far, this is for ease of illustration. Web page 728 in fact presents each
rollover
in the database and its success to date.
[00115] Selection of hyperlink 314 opens an expiry dates calendar web
page 730 as shown in Figure 12o. The web page 730 lists each rollover
pursued by the insurer, the broker responsible for each rollover and the
number
of expiry dates for the campaigns on a month by month basis.
[00116] Selection of hyperlink 316 on page 300 opens an all active
rollovers summary web page 732 as shown in Figure 12p. The web page 732
lists all active rollovers pursued by the insurer broken down by managing
broker
together with statistical information relating to the campaigns. The brokers
are
presented as hyperlinks linked to associated broker web pages.
[00117] Selection of hyperlink 317 opens an all active rollovers details
web page 734 as shown in Figure 12q. The web page 734 lists each active
campaign's active prospects and presents detailed information relating to each
prospect. Web page 734 has a dropdown menu 735 allowing a user to filter the
prospects shown. In this case, all records have been selected to be shown.
[00118] Figure 12r shows web page 734 with a selection being made via
dropdown menu 735 to show all prospects that purchased insurance from the
insurer. Similarly, Figure 12s shows web page 734 with a selection being made
via dropdown menu 735 to show all prospects that were declined insurance by
the insurer.
[00119] Selection of hyperlink 318 opens an all active rollovers summary
web page 736 as shown in Figure 12t. The web page 736 lists all active
rollovers pursued by the insurer broken down by managing broker together with
statistical information relating to the rollovers. The brokers are presented
as
hyperlinks linked to associated broker web pages.
[00120] Selection of hyperlink 319 opens an all active rollover campaigns
prospect details web page 738 as shown in Figure 12u. Similar to web page
734, web page 738 includes a dropdown menu to allow a user to filter the
prospects shown for each active rollover.
[00121] Selecting the update hyperlink 212 or search hyperlink 216 from
any page opens a prospect search web page 740 as shown in Figure 12v that
CA 02539652 2006-03-15
-32-
is equal to web page 332 and operable to search the rollover data when the
appropriate dropdown menu 741 item is selected. A user enters a string to
search for in the prospect data via search box 742.
[00122] The results of the search illustrated in Figure 12v are presented in
search results web page 744 as shown in Figure 12w. The prospects returned
as a result of the search are presented via hyperlinks, allowing a user to
edit
the data for the returned prospects.
[00123] Upon selection of the first hyperlinked prospect of Figure 12w,
database update web page 746 as shown in Figure 12x is presented, allowing
a user to view and/or modify the prospect's data.
[00124] Selection of hyperlink 214 from any rollover-directed page opens
a rollover query builder web page 748 as shown in Figure 12y. The web page
748 allows a user to build a query of the rollovers in the database by
selecting
filters for one or more fields. An exemplary query is shown specifying that
all
prospects of a specific rollover are to be returned. Figure 12z shows a query
results web page 750 presenting the results of the query of Figure 12y.
Broker Web Pages for Rollovers
[00125] Figure 13a shows a brokers home web page 500 assembled for
broker managing of both campaigns and a rollover. The brokers home web
page 500 shows a list of campaigns 754, including a rollover 756. The
campaigns in the list as well as the rollover are presented as hyperlinks.
Additionally, a list of outstanding appointments 758 is shown, comprising both
campaign and rollover appointments. Thus, by viewing home web page 500, a
broker is able to monitor all campaign and rollover activities.
[00126] By selecting the rollover 756, a user is presented with a rollover
summary web page 760 as shown in Figure 13b showing the number of
prospects that are unsettled (i.e., with expiry dates), that have purchased
insurance from the insurer and that will not be purchasing insurance from the
insurer, presented as hyperlinks 762, 764 and 766. Further, a list of
prospects
with expiry dates is immediately presented. Each prospect's name is a
CA 02539652 2006-03-15
-33-
hyperlink, allowing a user to select the prospect to view and edit the
prospect's
data.
[00127] Upon selection of the prospect's name, the prospect entry web
page 768 shown in Figure 13c, similar to the web pages illustrated in Figures
12b, 12g and 121, is presented to the user. A user may then view and edit the
prospect's data.
[00128] Figure 13d shows the web page 768 that is presented when the
"Business Not Written" option has been selected from the status dropdown list
770. Upon this selection, a rejection reasons popup window 772 appears,
enabling the user to enter the reason why insurance will not be provided to
the
prospect. As noted previously, the reasons can include rejection by either the
prospect or the insurer.
[00129] Upon selection of the hyperlink 764 on web page 760, the user is
presented with the rollover accounts written web page 774 shown in Figure
13e. Web page 774 lists the prospects of the rollover in question that have
purchased insurance from the insurer as a result of the rollover.
[00130] Similarly, upon selection of the hyperlink 766 on web page 760,
the user is presented with the rollover accounts not written web page 776 as
shown in Figure 13f. Web page 776 lists the prospects that will not purchase
insurance from the insurer as a result of the rollover.
[00131] Figure 13g presents the same broker reports web page 540 as
shown in Figure 10g, but for a different broker. Upon selection of hyperlink
543, the user is presented with the rollover campaigns summary web page 778
as shown in Figure 13h. Web page 778 presents a list of all rollovers being
managed by the broker in question, along with various rollover statistics.
[00132] Upon selection of hyperlink 545, the user is presented with the
rollover expiry dates calendar web page 780 as shown in Figure 13i, similar to
web page 730 of Figure 12o. The web page 780 lists each rollover pursued by
the broker and the number of expiry dates for the campaigns on a month by
month basis. Also shown is a list of prospects with upcoming expiry dates.
[00133] Upon selection of hyperlink 547, the user is presented with the all
rollover campaigns web page 782 as shown in Figure 13j, similar to web page
CA 02539652 2006-03-15
-34-
732 of Figure 12p. The web page 780 lists all rollovers pursued by the broker
and various statistics for each rollover.
[00134] Upon selection of hyperlink 549, the user is presented with the all
rollover campaigns details web page 784 as shown in Figure 13k. The web
page 784 lists all prospects of all rollovers pursued by the broker and
various
details for each prospect.
[00135] As will be appreciated, the marketing and sales application GUI
allows the broker to manage appointments for the various handled campaigns
and rollovers and monitor performance overall and by producer. The GUI also
allows the broker to compare their performance with other brokers appointed by
the insurer.
Policy Performance Analysis
[00136] In order to monitor the effectiveness of targeted marketing, it is
desirable to analyze the performance or profitability of the policies
generated
using the marketing and sales application. The profitability can be
characterized by policy retention, attrition, claims, etc. Policy data
required to
determine such characteristics is stored in the policy administration system
105
that is separate from the marketing and sales application executed on the web
server 102. The policy data includes, but is not limited to, policy renewal
data,
policy premium data, claim data and policy termination data. By combining the
policy data with the marketing and sales data, the performance of the policies
generated as a result of such campaigns can be measured. The policy
performance can be summarized by broker, by campaign, by year, etc.
[00137] Figure 14 shows a Microsoft Excel workbook 800 into which the
policy data and marketing and sales data are imported for analysis. The
workbook 800 has a plurality of worksheets, among which are a data import
sheet 804, a set of annual summary sheets 808, a price increase summary
sheet 812, a policy summary sheet 816, a campaign summary sheet 820, a
broker summary sheet 824 and a new policies summary sheet 828. The data
import sheet 804 receives policy data that is inputted for processing. Each of
CA 02539652 2006-03-15
-35-
the annual summary sheets 808 looks up data for the appropriate years in the
data import sheet 804, validates it and summarizes the projected and actual
policy activity for each broker/campaign combination for a particular calendar
year. Each of the price summary sheet 812, the policy summary sheet 816, the
campaign summary sheet 820, the broker summary sheet 824 and the new
policies summary sheet 828 references the data processed in the annual
summary sheets 808. In turn, the price summary sheet 812 provides a
summary of the price trends for policies issued as a result of a campaign. The
policy summary sheet 816 provides an overall analysis of the performance of
the policies generated as a result of all of the campaigns. The campaign
summary sheet 820 summarizes the performance of the policies generated as
a result of each campaign by calendar year of issuance. The broker summary
sheet 824 summarizes the performance of the policies generated as a result of
each broker by calendar year of issuance. The new policies summary sheet
828 summarizes the performance of the policies generated as a result of each
campaign and by each broker that are newly issued in the current calendar
year.
[00138] Figure 15 illustrates a section of the data import sheet 804. The
policy data is exported from the policy administration system into a Microsoft
Excel workbook, which is, in turn, imported into a range of the data import
sheet
804. The layout of the policy data is in a specified form and, as a result,
each
field of the policy data is placed into a pre-defined column of the data
import
sheet 804 when imported.
[00139] Figures 16a to 16d illustrate a section of one of the annual
summary sheets 808; in particular, for policies generated as a result of
campaigns during the 2000 calendar year. A first portion 850 of each of the
annual summary sheets 808 is initially populated using the data from the
marketing and sales application. The fields populated using the marketing and
sales data include a broker field 854, a campaign field 856, a policyholder
name and particulars field 858, a policy expiry date field 860, a policy
number
field 862 and an expected premium field 864.
CA 02539652 2006-03-15
-36-
[00140] A second portion 870 of each of the annual summary sheets 808
is populated using the policy data imported into the data import sheet 804.
For
the 2000 calendar year, the second portion 870 includes a booked premium
field 872, a total losses field 874 and a written loss ratio field 876 for the
2000
calendar year. For the 2001 calendar year, the same annual summary sheet
808 contains a premium renewal field 878, a percent price increase field 880,
a
total losses field 882 and a written loss ratio field 884. For the 2002
calendar
year, the same annual summary sheet 808 includes a premium renewal field
886, a percent price increase field 888, etc. Each annual summary sheet also
contains a total premium field 890, a total losses field 892 and a written
loss
ratio field 894. A new annual summary sheet 808 is generated for each policy
year.
[00141] Figure 17 illustrates a section of the price increase summary
sheet 812. As shown, the price trends between consecutive calendar years for
policies issued as a result of a campaign are summarized by campaign and by
broker.
[00142] Figure 18 illustrates a section of the policy summary sheet 816.
The policy summary sheet 816 provides measures of the overall performance
of the policies generated as a result of all of the campaigns and brokers.
Breakdowns are provided for each calendar year, for new policies, and for
renewed policies. The measures include the number of policies, the total
premium, the total losses, the loss to premium ratio (as a percent), the
number
of losses, the losses to policies ratio (as a percent), the maximum loss, the
second highest loss, the number of cancelled policies and the retention rate.
[00143] Figure 19 illustrates a section of the campaign summary sheet
820. Like the policy summary sheet 816, the campaign summary sheet 820
provides measures of the performance of the policies generated as a result of
each campaign during each calendar year. Breakdowns are provided for each
calendar year, for new policies, and for renewed policies. The measures
include the number of policies, the total premium, the total losses, the loss
to
premium ratio (as a percent), the number of losses, the losses to policies
ratio,
CA 02539652 2006-03-15
-37-
the maximum loss, the second highest loss, the number of cancelled policies
and the retention rate.
[00144] Figures 20a to 20c illustrate a portion of the broker summary
sheet 824. Again, like the policy summary sheet 816, the broker summary
sheet 824 provides measures of the performance of the policies generated by
each broker during each calendar year as a result of the campaigns.
Breakdowns are provided for each calendar year, for new policies, and for
renewed policies. The measures include the number of policies, the total
premium, the total losses, the loss to premium ratio, the number of losses,
the
losses to policies ratio, the maximum loss, the second highest loss, the
number
of cancelled policies and the retention rate.
[00145] Figure 21 illustrates a section of the new policies summary sheet
828. Like the policy summary sheet 816, the new policies summary sheet 828
provides measures of the performance of the new policies generated as a
result of each campaign and each broker in the current calendar year.
Breakdowns are provided for the current calendar year for new policies. The
measures include the number of policies, the total premium, the total losses,
the loss to premium ratio, the number of losses, the maximum loss, the second
highest loss and the number of cancelled policies.
[00146] The workbook 800 performs data validity checks of the sales and
marketing data imported from the sales and marketing application and the
policy administration system. These checks are performed in the annual
summary sheets 808.
[00147] Referring back to Figures 16a to 16d, sales and marketing data
that is imported into the data import sheet 804 from the sales and marketing
application is first verified and then presented in the second portion 870 of
the
annual summary sheets 808. Each of the fields of the second portion 870 is
updated dynamically in response to the importation of the policy data into the
data import sheet 804. If the policy data is inconsistent with the policy data
from the sales and marketing application, notification text in the booked
premium field 872, and also in the premium renewal and total losses fields,
CA 02539652 2006-03-15
-38-
provides an indication of the inconsistency and the general cause of the
inconsistency.
[00148] The workbook 800 employs data-validation formulas in the annual
summary sheets 808 to validate the imported policy data and present results if
the data is validated. The formulas employ a set of nested conditions that
check the records entered into the data import sheet 804. The records entered
into the data import sheet 804 determine whether the data entered into the
data
import sheet 804 corresponds to the entities for which data is required and
vice-
versa. In the annual summary sheets 808, the entities are individual policies.
[00149] A cell 896 shows data retrieved from the data import sheet 804
for the amount of booked premium for the 2000 calendar year. If, however, an
error is detected in the data in the import data sheet 804 for the policy
number
with which the cell 896 is associated (i.e. cbc 0601082), a notification
message
is visually indicated instead of the amount of booked premium. In this manner,
a user of the workbook 800 can quickly determine if there are errors in the
data
in the data import sheet 804.
[00150] Primary sources of inconsistency in the policy data and the sales
and marketing data include missing or duplicate entries for a policy,
incorrect
policy numbers, incorrect policy anniversary dates and text strings other than
"cancelled policy", which is the only expected and allowable text string. Such
inconsistencies are errors and should be rectified.
[00151] Figure 22 illustrates an example of a formula 900 from the annual
summary sheet 808 for the 2000 calendar year shown in Figures 16a to 16d.
The formula forms part of a record for a policy number. The principal purpose
of the formula is to retrieve the booked premium for the 2000 calendar year
for
the particular policy number from the policy data. Alternatively, if a record
for a
referenced policy is marked as cancelled, the formula will visually indicate a
cancelled policy message. The policy numbers come from the marketing and
sales application. The formula 900 checks if the data corresponding to the
entity (i.e., policy number) is present in the data import sheet 804 and is
valid,
and checks whether the number of occurrences of the entity in the summary is
correct. If there is more than one occurrence of the policy number in an
annual
CA 02539652 2006-03-15
-39-
summary sheet 808, the policy data for the policy number may be accidentally
double-counted.
[00152] As can be seen, the formula 900 uses a set of nested
IF(condition,then,else) functions to serially perform a number of checks on
the
data. In a first condition 902, it is determined whether the number of records
in
the data import sheet 804 that match the policy with which the formula 900 is
associated is not equal to one. The first condition 902 checks all of the
policy
numbers in the data import sheet 804 using an array calculation. If no records
in the data import sheet 804 match the policy number with which the formula
900 is associated, data for the specific policy number is deemed to be
missing.
Alternatively, if two or more records in the data import sheet 804 match the
policy number with which the formula 900 is associated, the data in the data
import sheet 804 for the specific policy is deemed to be suspect and
unreliable.
In both cases, any data that could be returned by a formula may not be
accurate and it is desirable to correct the data instead of relying on
potentially-
inaccurate results reported by the workbook 800. As a result, if the first
condition 902 is true, a first error message 904 is visually displayed in the
cell
896.
[00153] If the first condition 902 is false, i.e., no error is returned, a
second condition 906 is checked. The second condition 906 checks all of the
policy numbers in the annual summary sheet 808 for the 2000 calendar year
using an array calculation. If no records in the annual summary sheet 808 for
the 2000 calendar year match the policy number with which the formula 900 is
associated, data for the policy number present in the data import sheet 804
may not be properly reported through the annual summary sheet 808.
Alternatively, if two or more records in the annual summary sheet 808 match
the policy number with which the formula 900 is associated, data in the data
import sheet 804 for the specific policy may be reported more than once,
potentially leading to inaccurate results. Once again, in both cases, data
that
could be returned by the formula may not be accurate and it is desirable to
flag
the data as requiring attention instead of relying on potentially-inaccurate
CA 02539652 2006-03-15
- 40 -
results reported by the workbook 800. As a result, if the second condition 906
is true, a second error message 908 is visually displayed in the cell 896.
[00154] If the second condition 906 is false, i.e., no error is returned, a
third condition 910 is checked. The third condition 910 checks to see if the
record in the data import sheet 804 corresponding to the policy number with
which the formula 900 is associated is marked as a cancelled policy. If the
record in the data import sheet 804 corresponding to the policy number with
which the formula 900 is associated is marked as cancelled, a cancelled policy
message 912 is visually indicated to denote that the policy has been
cancelled.
[00155] If the third condition 910 is false (that is, none of the records in
the data import sheet 804 corresponding to the policy number with which the
formula 900 is associated are marked as cancelled), a fourth condition 914 is
checked. The fourth condition 914 checks to see if the record in the data
import sheet 804 corresponding to the policy number with which the exemplary
formula 900 is associated has an appropriate policy anniversary date. If the
record in the data import sheet 804 corresponding to the policy number with
which the formula 900 is associated does not have an appropriate policy
anniversary date (that is, if the fourth condition 914 is not true), a third
error
message 916 is visually indicated.
[00156] If the fourth condition 914 is true, a fifth condition 918 is checked.
The fifth condition 918 checks to see if the record in the annual summary
sheet
808 with which the formula 900 is associated includes the appropriate policy
issue date. If the record in the annual summary sheet 808 with which the
formula 900 is associated does not include the appropriate policy issue date,
a
fourth error message 920 is visually indicated.
[00157] If the record in the annual summary sheet 808 with which the
formula 900 is associated includes the appropriate policy issue date, a result
922 is calculated and indicated. The result 922 is the booked premium for the
2000 calendar year retrieved from the corresponding record in the data import
sheet 804. Note that the records for which result 922 is calculated do not
contain the text string "Cancelled policy", but may contain other text
strings.
During the calculation of result 922, an intermediate result is firstly
determined
CA 02539652 2006-03-15
-41 -
by multiplying the calculation by one. If the intermediate result is a number,
the
result 922 is simply equal to the intermediate result. If, instead, the
intermediate resuit contains a text string, a fifth error message, "#VALUE",
relating to the calculation error in the cell is displayed.
[00158] For a record containing a text string, the result 922 would be
equal to zero if the intermediate result is not multiplied by one. This would
cause a zero premium to be calculated for records containing unexpected text
strings. It is therefore desired to flag records containing unexpected text
strings
so that the unexpected text strings can be examined and rectified.
[00159] In this manner, multiple error checks are performed on the
consistency of the data from the marketing and sales application and the
policy
data from the policy administration system and provide a visual indication of
errors present therein.
[00160] In order to analyze performance of the campaigns, it is helpful to
have detailed information about premiums realized and losses incurred.
Referring again to Figures 20a to 20c, a portion of the broker summary sheet
824 is shown indicating various statistics for each individual broker for each
calendar year and a summary across all calendar years. In particular, the
statistics include the number of policies, the total premium realized, the
total
losses incurred, the written loss ratio, the number of losses or paid-out
claims,
the ratio of claims to the number of policies issued, the maximum single loss,
the second-highest single loss, the number of cancelled policies and the
retention rate. The information for each broker for each year is retrieved
from
the respective annual summary sheets 808.
[00161] When such summaries are prepared, providing both the
maximum and the second-highest single loss can provide a better
understanding of other loss metrics. While the calculation of the maximum
single loss is not generally difficult, determining the second-highest single
loss
presents challenges. For purposes of illustration, the determination of an
overall maximum loss 1004 and an overall second-highest loss 1008 for broker
"B4" over all calendar years as shown in Figure 20c will now be described.
CA 02539652 2006-03-15
-42-
[00162] The overall maximum single loss 1004 for the broker "B4" across
all calendar years is determined using the MAX(list/array) function available
in
Microsoft Excel. As the overall maximum single loss 1004 would also be the
maximum single loss for the policies issued by the broker "B4", only the list
of
maximum single losses for this broker is required as an argument for the
MAX(list/array) formula to determine the overall maximum single loss 1004.
[00163] The calculation of the overall second-highest single loss 1008 is,
however, more complex. The overall second-highest single loss 1008 can
occur in the maximum single loss column for records corresponding to the
broker "B4" (i.e., as the maximum single loss for a different calendar year
than
the one in which the overall maximum single loss occurred). Alternatively,
unlike the overall maximum single loss 1004, the overall second-highest single
loss 1008 can occur in the second-highest single loss column for the
particular
calendar year in which the maximum single loss occurred. In either of these
cases, it is possible that the overall second-highest single loss 1008 can be
equal to the overall maximum single loss 1004.
[00164] Figure 23 illustrates a formula 1012 used to determine the overall
second-highest single loss 1008. A first condition 1014 is used to select
those
entries corresponding to a particular broker. A second condition 1016 is used
to determine whether there is more than one loss equal to the overall maximum
single loss 1004 for the particular broker. The condition is determined by
checking how many entries in the maximum single loss and second-highest
single loss columns are equal to the overall maximum single loss 1004 for that
particular broker using an array function. If it is determined that there is
more
than one loss in the maximum single loss and second-highest single loss
columns equal to the overall maximum single loss 1004 for that particular
broker, the overall second-highest single loss 1008 is set equal to the
overall
maximum highest loss 1008 at 1020. Otherwise, those loss entries for the
broker "B4" that are not equal to the maximum single loss are located in the
maximum single loss and second-highest single loss columns using the
condition 1024, and the highest value from these entries is selected as the
CA 02539652 2006-03-15
-43-
overall second-highest single loss at 1028. This is, again, determined using
an
array function.
[00165] Another application of data-validation formuias similar to that
shown in Figure 22 is illustrated in Figure 24, wherein an exemplary scenario
is
employed to validate data and present results. An exemplary data set 1100 is
shown comprising five records of data, each having two fields. In order to
validate the data to ensure that each record having a first characteristic
also
has a second characteristic, analysis is performed on the data set 1100 via a
set of calculations 1104. The calculations 1104 include a first check
calculation
1108, a second check calculation 1112, a third check calculation 1116, a sub-
aggregator calculation 1120 and an aggregator calculation 1124. By employing
a set of formulas to perform individual checks and then aggregate these
checks, the number of checks performed in generating a result can be higher
than otherwise possible due to the limitations that presently exist for
formula
entry boxes in such spreadsheet applications.
[00166] Figure 25 illustrates a first check formuia 1200 performing the first
check calculation 1108. An initial condition 1204 is used to determine whether
the data set 1100 includes a record with a particular characteristic; that is,
whether any of the records in the data set 1100 has the string "bro" in the
first
field. The initial condition 1204 employs an array formula to check a
plurality of
records simultaneously, then totals the number of records that have the
particular characteristic. If it is determined that one or more records have
the
particular characteristic (i.e. the string "bro" in the first field), a set of
nested
conditions is used to determine whether those records have a common second
characteristic in a second field. The records are iteratively checked to
determine whether there is a one-to-one relationship between the
characteristics of the first field and the characteristics of the second
field. In a
first nested condition 1208, it is determined whether the records with a
particular first characteristic (i.e., the string "bro") match those records
with both
the first characteristic and a particular second characteristic. If there is a
match, the particular second characteristic (i.e., the string "a") is
outputted at
1212. Otherwise a second nested condition 1216 is checked, wherein it is
CA 02539652 2006-03-15
-44-
determined whether the records with a particular first characteristic (i.e.,
the
string "bro") match those records with both the first characteristic and
another
particular second characteristic (i.e., the string "b"). If there is a match,
the
particular second characteristic is outputted at 1220. The first nested
condition
1208, the second nested condition 1216, a third nested condition 1224 and a
fourth nested condition 1232 are checked in sequence upon failure of the
preceding condition. If it is determined that there are no records that have
the
particular characteristic in the first field, "false" is returned. In the case
where
the records having a common selected second characteristic share a common
first characteristic other than "a", "b", "c" or "d", all of the conditions
1208, 1216,
1224 and 1232 fail and "false" is returned. In the case where the records do
not share a common second characteristic, all of the conditions 1208, 1216,
1224 and 1232 fail and "false" is returned.
[00167] Figure 26 illustrates an aggregation function 1240 that performs
the aggregator calculation 1124. A first condition 1244 is checked to
determine
if there is an occurrence of the string "bro" in the data set 1100. The first
condition 1244 uses an array calculation to check the entire data set 1100 at
the same time. If there is an occurrence of the string "bro" in the data set
1100,
a second condition 1248 is checked to determine if the sub-aggregator
calculation 1120 returns "FALSE"; that is, if any of the eight particular
characteristics from the first and second check calculations 1108, 1112 were
not found consistently beside each occurrence of the string "bro" in the data
set
1100. If the sub-aggregator calculation 1120 retums "FALSE", the results of
the third check calculation 1116 are returned at 1252. Otherwise, the results
of
the sub-aggregator calculation 1120 are returned.
[00168] Such formulas are also used, among other things, for referencing
loss values for each record. Using such a formula structure, a plurality of
checks can be performed to verify the consistency of the data.
[00169] By importing data in ASCII text format into the data import sheet
804 of the workbook 800, the imported data is automatically incorporated in
calculated results. The data imported into the data import sheet 804 is first
summarized by policy year in the annual summary sheets 808, which use a
CA 02539652 2006-03-15
-45-
number of formulas to reference the data for each policy year in the data
import
sheet 804. All of the reference calculations performed in fields 812 to 828
are
automatically calculated from the data in the annual summary sheets 808
through various formulas. The workbook contains tens of thousands of linked
formulas and their number increases in each subsequent year that the
workbook is employed.
[00170] While a method of calculating the overall second-highest loss has
been described, the same methodology can be used to calculate a secondary
value having a second-highest priority in a list of values using other
prioritizations. For example, the second-lowest value in a list of values can
be
calculated.
[00171] Although the above discussion has been made with particular
reference to the marketing and sale of insurance, those of skill in the art
will
appreciate that the systems and methods described herein may be used to
market and sell other items.
[00172] Also, although embodiments have been described, those of skill
in the art will appreciate that variations and modifications may be made
without
departing from the spirit and scope thereof as defined by the appended claims.
