USA: +1(669)289-8683 
Sign in
Please see attached assignment instructions.
Due Date: Monday, September 5, 2013 by 10 PM PST
Module2: Assignment 1 – Case Analysis 1
By Thursday, September 5, 2013, read Case Analysis 1 for Module 2 and answer the questions based
on it.
Read the following Case Analysis 1 for Module 2 (see attached):
Davis, H. M., Wood, D. D. (2005, Jan-Feb). A commission-based management spreadsheet model:
Strategies to increase stockholder returns for an insurance agency. Journal of Education for Business, 80
(3), 139-144 (AN 16069874)
Submit your answers in Microsoft Word, double-spaced, in Times New Roman 12 pt. font. Cite all sources
and be sure to use the current APA standards when formatting your paper.
All written assignments and responses should follow APA rules for attributing sources and Grading
Criteria below:
Grading Criteria
Maximum
Points
Demonstrated an understanding of the topics being discussed.
4
Met the criteria for the correct responses to the assigned questions. 4
Participation Criteria
Used vocabulary relevant to the topics under discussion. 4
Participated in the discussion by asking a question, providing a
statement of clarification, providing a point of view with rationale,
challenging a point of discussion, or making a relationship between
one or more points of the discussion.
4
Justified ideas and responses by using appropriate examples and
references from texts, websites, and other references or personal
experience.
4
Wrote in a clear, concise, and organized manner; demonstrated
ethical scholarship in accurate representation and attribution of
sources, displayed accurate spelling, grammar, and punctuation.
4
Total 24
http://libproxy.edmc.edu/login?url=http://search.ebscohost.com/login.aspx?direct=true&db=afh&AN=16069874&site=ehost-live
http://libproxy.edmc.edu/login?url=http://search.ebscohost.com/login.aspx?direct=true&db=afh&AN=16069874&site=ehost-live
http://libproxy.edmc.edu/login?url=http://search.ebscohost.com/login.aspx?direct=true&db=afh&AN=16069874&site=ehost-live
ommission-based organizations’
values are affected by factors that
are not typical of manufacturing or
other retail business entities. One such
example is an insurance agency, which
exemplifies three factors germane to a
commission-based business. First, an
agency acts as an intermediary by pro-
viding the service of arranging insur-
ance coverage between an insurer and
an insured party. Thus, one of the
agency’s most valuable assets is its
client list. Second, the agency has the
fiduciary responsibility of either collect-
ing or arranging for the payment of pre-
miums by the insured to the insurer.
Third, an agency business typically is
not capital intensive, and owners gener-
ally take most of the profits of the
agency as bonuses or salary.
Our purpose in this article is to show
how a simple spreadsheet model can be
used to demonstrate the impact of dif-
ferent operating and capital manage-
ment strategies on the financial perfor-
mance of a commission-based business
such as an insurance agency. The model
is easy to develop and understand and is
flexible enough to allow for numerous
strategies. Instructors can use the model
to isolate the impact of a single strategy
or measure the impact of a combination
of strategies on performance.
The objective of the manager of a fee-
based business is to coordinate the
resources available in such a way as to
maximize financial performance. Man-
agement must determine growth, operat-
ing expenses, investment opportunities,
cash management opportunities, and the
level of profit retention. All of these fac-
tors affect financial performance and will
be considered in the model.
A typical business has various mea-
sures of financial performance that are
used in evaluating its health. Although
various measures have been developed
for evaluation of the productivity and
profitability of a commission-based
business, in this article we focus on the
rate of return on equity (ROE). Owners
and managers affect the numerator of
ROE by controlling growth, operating
expenses, investment opportunities, and
cash management opportunities. Own-
ers and managers affect the denomina-
tor of ROE by determining the profit
retention rate and, thus, the equity posi-
tion of the business. Successful business
owners should strive to maximize ROE,
which serves as a proxy for maximizing
the value of a business.
The Model
The model is a spreadsheet model that
can be used for any commission-based
business, such as an insurance agency,
travel agency, food brokerage operation,
and so forth. An insurance agency sells
insurance products and provides services
through commission-based salespersons
and salaried customer service representa-
tives. We based our example on an insur-
ance agency.
The model starts with a base case and
simulates 5 years of financial informa-
tion based on nine separate inputs that
can vary from year to year. The format of
the model is similar to an abbreviated
profit-and-loss statement generated by
any typical automated agency manage-
ment system. The model is appropriate
for any size or type of commission-based
business, and an individual easily can
adapt it by changing the input variables.
A Commission-Based Management
Spreadsheet Model:
Strategies to Increase Stockholder
Returns for an Insurance Agency
HARRY M. DAVIS
DAVID D. WOOD
Appalachian State University
Boone, North Carolina
January/February 2005 139
C
ABSTRACT. Strategic financial
management is an increasingly impor-
tant aspect of all small businesses.
Commission-based entities specifical-
ly face diminishing commission per-
centages, increasing expenses, and the
complexity of technological advances.
More and more managers are realizing
the importance of strategic planning
and the need for financial forecasting.
In this article, the authors describe a
spreadsheet model that demonstrates
the financial impact of various busi-
ness strategies for an insurance
agency. The model demonstrates the
effect that various strategic initiatives
have on the financial performance of a
base case scenario. The model is
applicable to other commission- or
fee-based entities, such as travel agen-
cies, food brokers, real estate agen-
cies, and other organizations.
Some relationships are inherent to the
model. We determined these by examin-
ing benchmarking-type publications pro-
duced by the insurance industry.1
In Table 1, we show the spreadsheet
containing 8 columns, which present,
consecutively, the row number, the item,
the current financial situation, and the
projected financial situations for the
next 5 years. The first 9 rows are the
input items, and the next 14 rows are the
determinants of the model. The user
specifies the items in the first 9 rows for
the current period as well as for the 5
years of projections. The data in Table 1
represent the base case.
The first input is the premium growth
rate. Commission-based businesses re-
ceive commissions for the sale of an item
or service. Agencies receive a commis-
sion that is a percentage of the premiums
written by the agency. Thus, the revenue
growth of the agency is tied directly to
premium growth. Premiums may in-
crease because of new business written
by the agency and because of insurance
rate increases implemented by insurance
companies. Premium levels may decrease
when the insurance market is extremely
competitive. This input value should rep-
resent the expected commission growth
of the particular business.
The attrition rate, given in row 2, is
the percentage of premiums that will no
longer be insured by the agency in the
given year. Attrition is caused by compe-
tition as well as by insurance needs that
no longer exist. The attrition rate could
be applied to any commission-based
business. The premium growth rate
minus the attrition rate gives the net
growth in premiums for the agency.
Commission rate, defined as the per-
centage of the sales price that is received
by the business, is presented in row 3.
Commission rates can vary across insur-
ance companies and between different
types of insurance. For demonstration
purposes, we use a single or average
commission rate in our model.
In row 4, we show the average invest-
ment rate, which is the rate of return that
a business is able to earn on its invest-
ments. Commission-based businesses
typically are given an amount of time to
remit sales dollars minus the commis-
sion. This lag allows the business to
earn investment income on the sales
dollars that it has collected but not yet
remitted.
A commission-based business may
pay its salespeople a percentage of the
commission that the business receives.
This percentage payment to salespeople
is classified in the model as commission
expense and is provided in row 5. The
percentage usually varies from 20% to
50% of total commissions for an
agency.
Other staff and clerical employees
typically are paid on a salary basis. Staff
salaries, the item shown in row 6, pre-
sents those salaries as a percentage of
total income. As a business grows and
its total income increases, additional
staff need to be added.
Operating expenses are shown in row
7 as a percentage of total income. The
model assumes that the required level of
operating expenses is a function of the
level of total income. Typically, expenses
are directly related to total income.
Our model provides other income in
row 8. Businesses often earn other
income from several sources, and one can
input these into the model. These sources
include investment, fees-for-service
activities, consulting, rental services,
property management, and other activi-
ties that provide nonoperating income.
140 Journal of Education for Business
TABLE 1. Mountain Insurance Agency: Base Case (Thousands of Dollars)
Year (projected)
Row Item Current 1 2 3 4 5
1 Premium growth rate 5.0% 5.0% 5.0% 5.0% 5.0% 5.0%
2 Attrition rate 5.0% 5.0% 5.0% 5.0% 5.0% 5.0%
3 Commission rate 13.0% 13.0% 13.0% 13.0% 13.0% 13.0%
4 Average investment rate 6.0% 6.0% 6.0% 6.0% 6.0% 6.0%
5 Commission expense (%) 25.0% 25.0% 25.0% 25.0% 25.0% 25.0%
6 Staff salaries 22.0% 22.0% 22.0% 22.0% 22.0% 22.0%
7 Operating expenses (%) 48.0% 48.0% 48.0% 48.0% 48.0% 48.0%
8 Other income $100 $ 100 $100 $100 $100 $100
9 Profit retention rate 20.0% 20.0% 20.0% 20.0% 20.0% 20.0%
10 Premiums $10,000 $10,000 $10,000 $10,000 $10,000 $10,000
11 Total commissions 1,300 1,300 1,300 1,300 1,300 1,300
12 Investments 1,250 1,252 1,255 1,257 1,259 1,262
13 Investment income 75 75 75 75 76 76
14 Total income 1,475 1,475 1,475 1,475 1,476 1,476
15 Commissions to salespeople 325 325 325 325 325 325
16 Salaries 325 325 325 325 325 325
17 Operating expenses 708 708 708 708 708 708
18 Total expenses 1,358 1,358 1,358 1,358 1,358 1,358
19 Net income (pretax) 117 117 117 117 118 118
20 Dividends to owners 94 94 94 94 94 94
21 Addition to retained earnings 23 23 23 23 24 24
22 Equity 750 774 797 821 844 868
23 Return on equity 15.60% 15.12% 14.68% 14.25% 13.98% 13.59%
As with any business, net income can
be either paid out in dividends to own-
ers or reinvested in the business. The
profit retention rate, given in row 9, is
the percentage of net income that is
reinvested in the agency in each period.
Once the items in the first 9 rows are
specified, the model generates the num-
bers in the remaining 14 rows of the
spreadsheet, with the exception of only
premiums and equity in the “Current”
column. The initial level of those two
items must be specified.
Premiums, shown in row 10, reflect
revenues (sales) that the agency has
generated for the insurance companies
that it represents. The business is
responsible for billing the customer and
collecting the premiums. The commis-
sion is subtracted from the premiums,
which are then remitted to the appropri-
ate companies. The agency typically has
up to 45 days to remit the insurance
company’s portion. Alternatively, the
insured may be billed directly by the
insurance company and remit payment
directly to the company. Although this
direct bill method relieves the business
of the collection responsibility, it also
results in the lost opportunity of earning
investment income on premiums being
held for the company. The model
assumes that all premiums are billed
and collected by the business, but the
user could change this assumption.
Total commissions, the item represent-
ing the portion of the premium that the
business keeps, comprises the vast
majority of total income. To derive total
commissions, presented in row 11, one
multiplies the number in row 3 by the
number in row 10.
The business’s investments are shown
in row 12 and are the portion of the pre-
mium that is held plus liquid assets that
are on hand. The model inherently
assumes that the business will hold 10%
of any addition to retained earnings as liq-
uid assets. We show investment income in
row 13—it is simply investments times
the average investment rate. The sum of
total commissions, investment income,
and other income equals total income.
Total income is shown in row 14.
One can calculate commissions to
salespeople, provided in row 15, by
multiplying commission expense (%)
by the total commissions. One calcu-
lates salaries and operating expenses by
multiplying the input percentage for
each expense category by total income.
The item “total expenses” is given in
row 18. Net income (pretax) is the num-
ber found by subtracting the number in
row 18 from that in row 14—net income
is shown in row 19.
The “addition to retained earnings”
item (row 21) is the profit retention rate
times net income. To obtain equity (row
22), the amount in row 21 is added to
the number in row 22 each period. Prof-
its that are not retained are paid as divi-
dends to owners; this number is shown
in row 20.
Commission-based businesses can
have a wide array of capital levels.
Insurance agencies are not capital inten-
sive and thus start with a low level of
equity. This amount is the equity that
the owners or agency principals have
invested in the business plus the
retained earnings, which are driven by
the profit retention rate. Obviously, the
level of equity is largely a management
decision and has tremendous impact on
financial performance. The ultimate
measure of financial performance is the
ROE, which is shown in row 23. It is net
income divided by equity.
Simulations
A base case must be initially estab-
lished for the business. It may be entire-
ly hypothetical; however, preferably it
represents an actual business’s financial
data. We developed the base case pre-
sented in Table 1 from industry averages
for insurance agencies.
Importantly, the premium growth rate
and attrition rate are set to cancel each
other, so there is effectively no growth in
the base case. The other input items are
constant throughout the 5 years. Given
no growth in premiums and the other
constant input items, the net income
amount in row 19 is constant throughout
the 5 years of projections. The profit
retention rate in row 9 is set at 20% and
means that the owners take 80% of the
profits out of the agency. Because equity
in row 22 is growing as a result of a pos-
itive profit retention rate, the ROE in
row 23 declines from 15.60% in the cur-
rent period to only 13.59% in Year 5.
Clearly, a continuously declining ROE
does not represent sufficient financial
performance.
Once one establishes the base case,
one can develop different strategies to
measure their impact on performance.
Although numerous strategies are possi-
ble, in this article we discuss the follow-
ing five strategies for improving perfor-
mance:
1. Reduce operating expenses and
staff salaries
2. Decrease the commission expense
3. Increase the premium growth rate
4. Increase the profit retention rate
5. Combine several strategies
The first three strategies deal with
operating the business in terms of oper-
ating costs and growth. The fourth strat-
egy deals with leveraging equity, and
the last strategy is a combination. Only
the input item required by the specific
strategy is changed, leaving the other
input as shown in the base case.
Strategy 1 reduces the staff salaries
rate from 22% to 20% and the operating
expenses percentage from 48% to 45%.
We show the results in Table 2. Note the
reduction in the numbers representing
(a) salaries, shown in row 16, and (b)
operating expenses, shown in row 17.
The decreases lead to an increase in net
income owing to lower costs. The ROE
for the “Current” column increases
because total expenses drop. Note that
the ROE falls in the remaining periods
because net income remains constant
while equity increases.
Strategy 2 calls for a decrease in
commission expense, which is at the
discretion of the owners or managers.
The rate is decreased from 25% to 23%,
and we show the results in Table 3.
Because this item is a significant per-
centage of total income, any decrease
leads to a large increase in net income,
as shown in row 19. Business managers
must watch this expense item closely to
operate with a high level of financial
performance. As we have seen with the
previous strategy, there is an initial
improvement in net income, but the lack
of sustained growth results in a decreas-
ing ROE over time.
Strategy 3 increases the premium
growth rate above the attrition rate so
that the overall size of the business
grows. In Table 4, we show the rate
January/February 2005 141
142 Journal of Education for Business
TABLE 3. Mountain Insurance Agency: Decrease Commission Expense (Thousands of Dollars)
Year (projected)
Row Item Current 1 2 3 4 5
5 Commission expense (%) 23.0% 23.0% 23.0% 23.0% 23.0% 23.0%
15 Commissions to salespeople 299 299 299 299 299 299
16 Salaries 325 325 325 325 325 325
17 Operating expenses 708 708 708 708 708 708
18 Total expenses 1,332 1,332 1,332 1,332 1,332 1,332
19 Net income (pretax) 143 143 143 143 144 144
20 Dividends to owners 115 115 115 115 115 115
21 Addition to retained earnings 29 29 29 29 29 29
22 Equity 750 779 807 836 865 894
23 Return on equity 19.01% 18.36% 17.72% 17.11% 16.65% 16.11%
TABLE 2. Mountain Insurance Agency: Change Operating Expense and Staff Salaries (Thousands of Dollars)
Year (projected)
Row Item Current 1 2 3 4 5
6 Staff salaries 20.0% 20.0% 20.0% 20.0% 20.0% 20.0%
7 Operating expenses (%) 45.0% 45.0% 45.0% 45.0% 45.0% 45.0%
14 Total income 1,475 1,475 1,475 1,475 1,476 1,476
15 Commissions to salespeople 364 364 364 364 364 364
16 Salaries 295 295 295 295 295 295
17 Operating expenses 664 664 664 664 664 664
18 Total expenses 1,323 1,323 1,323 1,323 1,323 1,323
19 Net income (pretax) 152 152 152 152 153 153
20 Dividends to owners 122 122 122 122 122 122
21 Addition to retained earnings 30 30 30 30 31 31
22 Equity 750 780 811 841 872 902
23 Return on equity 20.27% 19.49% 18.74% 18.01% 17.55% 16.96%
TABLE 4. Mountain Insurance Agency: Change the Premium Growth Rate (Thousands of Dollars)
Year (projected)
Row Item Current 1 2 3 4 5
1 Premium growth rate 20.0% 20.0% 20.0% 20.0% 20.0% 20.0%
10 Premiums $10,000 $11,500 $13,225 $15,209 $17,490 $20,114
11 Total commissions 1,300 1,495 1,719 1,977 2,274 2,615
12 Investments 1,250 1,440 1,658 1,909 2,197 2,529
13 Investment income 75 86 99 115 132 152
14 Total income 1,475 1,681 1,919 2,192 2,506 2,867
15 Commissions to salespeople 325 374 430 494 568 654
16 Salaries 325 370 422 482 551 631
17 Operating expenses 708 807 921 1,052 1,203 1,376
18 Total expenses 1,358 1,551 1,773 2,028 2,322 2,660
19 Net income (pretax) 117 130 146 164 184 207
20 Dividends to owners 94 105 117 131 147 165
21 Addition to retained earnings 24 26 29 33 37 41
22 Equity 750 776 805 838 875 916
23 Return on equity 15.60% 16.75% 18.14% 19.57% 21.03% 22.60%
increasing from 5% each year to 20%.
This results in the large increase in net
income in each period. Most important,
the ROE increases in each period as the
percentage increase in profits is greater
than the percentage increase in equity.
The increase in ROE points to the
importance of growth and leveraging
the business’s equity for a greater return
to the owners or stockholders. To lever-
age the additional equity, as shown in
Table 4, the business must grow with a
positive retention rate.
Strategy 4, shown in Table 5, results
in an increase from 20% to 50% in the
profit retention rate. The increase leads
immediately to a decrease in ROE,
because equity is now growing even
faster than in the base case presented in
Table 1. This strategy highlights the
problem with retaining earnings and the
impact on the ROE. To maintain a high
level of ROE, the business must grow to
leverage the equity for the owners.
Strategy 5 is a combination strategy
that calls for an increase from 20% to
50% in the profit retention rate and an
increase from 5% to 20% in the premium
growth rate. We show the results of this
strategy in Table 6. Net income increases
dramatically as a result of the large per-
centage increase in premiums. Clearly,
premium growth is the profit generator
for this business. In contrast to Strategy
4, Strategy 5 results in an increase in the
ROE throughout, even with the higher
retention rate. The difference is the high
rate of premium growth that allows the
business to leverage the increased equity
base. This difference leads to an increas-
ing ROE. Clearly, a commission-based
business must grow if it is going to retain
earnings and provide a high ROE.
Conclusions
The simulations that we have
described in this article show that a
commission-based business such as an
insurance agency can influence finan-
cial performance greatly in several
January/February 2005 143
TABLE 6. Mountain Insurance Agency: Combination Strategy (Thousands of Dollars)
Year (projected)
Row Item Current 1 2 3 4 5
1 Premium growth rate 20.0% 20.0% 20.0% 20.0% 20.0% 20.0%
9 Profit retention rate 50.0% 50.0% 50.0% 50.0% 50.0% 50.0%
10 Premiums $10,000 $11,500 $13,225 $15,209 $17,490 $20,114
11 Total commissions 1,300 1,495 1,719 1,977 2,274 2,615
12 Investments 1,250 1,443 1,666 1,921 2,214 2,551
13 Investment income 75 87 100 115 133 153
14 Total income 1,475 1,682 1,919 2,192 2,506 2,868
15 Commissions to salespeople 325 374 430 494 568 654
16 Salaries 325 370 422 482 551 631
17 Operating expenses 708 807 921 1,052 1,203 1,377
18 Total expenses 1,358 1,551 1,773 2,029 2,323 2,661
19 Net income (pretax) 117 131 146 163 183 207
20 Dividends to owners 59 65.4 73.0 82 92 103
21 Addition to retained earnings 58.5 65.5 73.0 81.5 91.5 103.5
22 Equity 750 815 888 970 1,062 1,165
23 Return on equity 15.60% 16.07% 16.44% 16.80% 17.23% 17.77%
TABLE 5. Mountain Insurance Agency: Change the Profit Retention Rate (Thousands of Dollars)
Year (projected)
Row Item Current 1 2 3 4 5
9 Profit retention rate 50.0% 50.0% 50.0% 50.0% 50.0% 50.0%
14 Total income 1,475 1,475 1,476 1,476 1,476 1,477
15 Commissions to salespeople 325 325 325 325 325 325
16 Salaries 325 325 325 325 325 325
17 Operating expenses 708 708 708 709 709 709
18 Total expenses 1,358 1,358 1,358 1,358 1,358 1,359
19 Net income (pretax) 117 117 118 118 118 118
20 Dividends to owners 59 59 59 59 59 59
21 Addition to retained earnings 58.5 58.5 59 59 59 59
22 Equity 750 809 868 927 986 1,045
23 Return on equity 15.60% 14.46% 13.59% 12.73% 11.97% 11.29%
ways. Controlling or reducing operating
expenses and commission expense
directly improves the bottom line.
Increasing the rate of growth in premi-
ums is even more powerful as a strategy
to increase income. Retaining profits
increases return to owners, provided
those funds are leveraged through com-
mission growth for the agency.
The spreadsheet model that we have
presented allows the business manager
to measure the impact of various strate-
gies on financial performance. The
model shows that different strategies
affect performance to different degrees.
The manager must determine the appro-
priate strategic plan that will generate
the input values that result in the most
desirable financial performance.
NOTE
1. See the Growth and Performance Standards
study from the Academy of Producer Insurance
Studies (2000).
REFERENCE
Academy of Producer Insurance Studies. (2000).
Growth and performance standards (GPS).
Austin, TX: Author.
ADDITIONAL READINGS
Business Management Group. (2002). Owner, exec-
utive & producer survey. Hartford, CT: Author.
Davis, H. M. (2001, Spring). A commercial bank
management spreadsheet model. Journal of
Financial Education, 27, 72–77.
Doucette, N. (2002). Sharing the wealth. Rough
Notes, 145(10), 182–184.
Festervand, T. A., Murrey, J. H., Jr., & Norman, E.
J. (1995). Strategic intelligence systems and the
independent insurance agent: Present status and
future directions. Journal of Insurance Issues,
18(1), 57–74.
Haridgree, D. W., & Howe, V. (1990). The insurer
selection process by independent insurance
agents. Journal of Insurance Issues, 13(1),
27–46.
Korsgaden, T. (2002). Growing your multiline
agency. Advisor Today, 97(10), 26.
Schellhorn, C. D., & Scordis, N. A. (2002). Insur-
ers’ expansion into banking: A look at operating
returns. Journal of Insurance Issues, 25(1), 1–23.
Schulte, R. (1999). Building a life insurance prac-
tice that can be sold. Journal of Financial Ser-
vice Professionals, 53(3), 38–48.
Smith, R. C. (2002). Agency survivors: Winners
must out-sell, out-market, out-compete. Nation-
al Underwriter; Property & Casualty/Risk &
Benefits Management Ed., 106(40), 44.
144 Journal of Education for Business
The article focuses on the Return on Equity (ROE)as the benchmark for assessing a business’s financial health.
Do you agree with this approach? (Support your response with 2 – 4 examples of financially healthy companies.).
Additionally, this article presents a spreadsheet analysis for commission-based businesses. What approach would you implement for a manufacturer?
How would it differ for a service organization, such as a CPA firm, staffing firm, or consulting firm?
