Budget Wisely With Computer Models
MBA and Dan Otto
The increasing amount of data, available not only from your
financial department but from personnel, marketing and operations, facilitates
monitoring and tracking various law firm indicators. However, the quantity of
data can be overwhelming at times. This is especially true when trying to make
a decision with conflicting information.
One of the best ways to make
complicated decisions, where a lot of data is available, is to use computer
modeling techniques. A computer model, which is a specific set of
variables and their interrelationships designed to represent a situation, helps
assess data and simplify the decision-making process. Computer modeling is
fast becoming one of the preferred methods in making decisions.
Computer modeling is no
longer relegated to the financial department. In fact, computer modeling
is an asset in every functional area: in human resources when deciding to hire
additional personnel and at what level, in payroll for calculating bonuses, in
marketing when deciding on which industries to target, in operations for
determining capacity and growth scenarios, in strategic planning for identifying
potential partners, and in finance when deciding on which potential clients have
a low risk of defaulting on their fees. Using available data or estimates,
computer modeling is helping law firms make better decisions.
A variety of computer modeling tools are available:
spreadsheets like Excel, Lotus and Quartro-Pro; databases such as Access,
Fox-Pro and DBASE; programming languages like Visual Basic, C++, SAS and UNIX;
and commercial tools such as Pro-Formas for Professionals, @Risk and iThink.
There are four basic steps in building a computer model:
State the problem. The first step in computer modeling is
to determine the exact problem and state it as a question. For example, Joe
Smith is the managing partner of a law firm. His firm is growing so fast that
he has more work coming in the door than the staff can handle. He needs to hire
additional people, but he doesn’t know at which level. Should he hire a senior
attorney, junior attorney, paralegal, secretary or clerk?
All of his employees claim to be very busy and need more
help. But Mr. Smith realizes that fast growth may go hand-in-hand with bloated
growth and inefficiency. He doesn’t want to hire more people just because his
current employees are busy. He would like some way to demonstrate the
appropriate level of person who should be hired, so that he can grow efficiently
and still maintain his high profit margin.
The second step is to define and collect the data.
Critical to answering the question, which has been posed, is to develop a list
of relevant data that will affect the decision. In this case, Mr. Smith needs
to know from all the employees who are doing work for the firm, what tasks they
perform and how much time they spend on each task every day. Although gathering
this information may be a daunting project, the results will be well worth the
Next, choose the modeling tool. Select the
tool based on your customization needs and the amount of data that you will be
processing. If the data is voluminous, a database may be necessary; however, if
you have a small project, a spreadsheet may be the tool of choice. If an
off-the-shelf tool suits your needs, then by all means, use it. But if you
require a considerable amount of customization, then a commercial tool may not
suffice and you may have to create the model yourself or outsource the work.
In our example, Mr. Smith selects the flexible Excel
spreadsheet, because it’s easy to use with good explanatory graphs that can be
Build the model. Now you are ready to start building your
computer model, which will answer the question that you posed. There are six
components of a model:
1. Input sheet, where the user enters and modifies the
Mr. Smith’s input data are the various tasks, the level of
employees completing the tasks, the amount of time each task takes and the order
that the tasks are completed. Critical to the analysis is not only the level of
employees who do the work now, but who should do the work in the future. So Mr.
Smith designates levels for each task based on who should do the work (support
staff, secretary, paralegal, junior attorney and senior attorney) not who
actually performed the work. Employees track their time on various tasks during
the data gathering period. Another input is the forecasted growth of work by
practice area and the associated effort by level of person required to complete
2. Results sheet,
which indicates the numerical conclusions based on the inputted data.
Mr. Smith creates a results sheet, which is automatically
populated from the input sheet. He uses color-coded cells for people who are
performing tasks that are above or below their level of competence.
From the model, Mr. Smith
sees that attorneys are performing paralegal work and secretaries are doing
clerical work. For example, attorneys are doing all of the research work,
some of which could and should be done by paralegals. The results sheet
also indicates that the secretaries are photocopying and preparing messenger
packages -- tasks that should be performed by the clerical staff. The
result is that the firm is paying salaries to people who are performing simple
tasks for which they are overqualified. Furthermore, the firm’s profits
are eroding with people who are not aligned with the appropriate level of task.
3. Charts and graphs are the
visual representations of the results.
If a picture is worth a thousand words, then a graph is
worth at least as much. A graph visually depicts the statistics on the results
sheet. Using the graphs, Mr. Smith easily sees that the number and level of
employees that he should hire based on his current workload and his forecast of
future work. The charts indicate the relationships or ratios between the levels
of personnel. For example, one graph reflects the number of paralegals for each
attorney based on the tasks’ levels of work that was identified in the data
4. Changing variables and “What if?” scenarios.
The advantage of using a computer model is that the data is
not static. If you change the data, then the results sheet and the graphs and
charts can change automatically. A computer model allows the user to change
variables, to alter scenarios and to calculate sensitivities that may affect
Mr. Smith can calculate how
an increase in work will affect his hiring decisions. He can change the
input data to reflect growth scenarios and the graphs automatically are updated
so that he can see how the alterations will impact the firm. This tool
gives him the flexibility to experiment with various modifications to determine
how the results will be affected.
5. “Sanity check,”
which determines if the inputs and calculations make sense.
Every computer model needs a sanity check to ensure that
formulas and calculations are realistic. The user can’t lose sight of the logic
in developing the computer model. One small error in a formula can throw a
computer model into the realm of improbability.
Mr. Smith reviews the tasks and their time allocations for
reasonableness. He does not accept the computer model as the “end all,” but
instead examines the inputs and the formulas to ensure accuracy. He is
satisfied that all his conclusions are sensible.
6. Usability -- the
menus, macros and other automation features that make using computer model easy
and simple. With Visual Basic programming, which runs in the background
and is not apparent to the user, a computer models can be an uncomplicated tool
for attorneys and managers to use.
Mr. Smith designs a few
simple interfaces that facilitate use of the computer model. He designs a
menu that will provide easy access to the various spreadsheets, such as input
sheet and results sheet. He explains the computer model to his office
manager, who is able to continually update the model and provide reports on a
A major advantage of the
computer model for the law firm is its ability to answer complicated questions.
If you are stymied by a question that appears unanswerable, chances are that a
computer model can assist your assessment and provide a solution to your
dilemma. As more unknowns become facts and as assumptions change to
reality, the computer model evolves and adapts, which creates a dynamic and
robust tool for decision-making.