Excel and Simulation for Accountants

• Published date: 01 April 2018
• DOI: http://doi.org/10.1002/jcaf.22328
• Author: Clarence Goh
• Date: 01 April 2018

133

© 2018 Wiley Periodicals, Inc.

Published online in Wiley Online Library (wileyonlinelibrary.com).

DOI 10.1002/jcaf.22328

f

e

a

t

u

r

e

a

r

t

i

c

l

e

Excel and Simulation

for Accountants

Clarence Goh

EXCEL AND

SIMULATION FOR

ACCOUNTANTS

In Formula One,

where races are often

won or lost by mar-

gins of fractions of

a second, the skill

of the driver is as

important as the

race strategy devised

by team strategists.

While luck certainly

plays an important part in the

success of any race strategy,

many other factors such as

fuel load, tire degradation,

driver ability, and weather

conditions can also influence

race outcomes.

To help them devise win-

ning race strategies, F1 teams

are increasingly turning to

sophisticated simulators which

can run thousands of race sim-

ulations while changing vari-

ables in each simulated race. By

running numerous simulations,

race strategists gain insights

into likely race outcomes when

different combinations of vari-

able inputs are implemented,

thus helping them devise the

race strategy that would give

them the greatest chance of

success in the actual race.

Simulation is an extremely

useful decision-making tool

not only in F1 but also for

accountants. While many com-

panies have invested heavily in

developing sophisticated soft-

ware to help them run simula-

tions, simulations can also be

run on tools such as Excel. In

this article, I will use a simple

example to demonstrate how

an accountant could use Excel

to run simulations.

CONDUCTING SIMULATION

IN EXCEL

Simulation is a math-

ematical technique for solving a

problem by perform-

ing a large number

of trial runs (called

simulations) and

inferring a solution

from the collective

results of the trial

runs. In simulation,

uncertainty in a

business situation is

explicitly incorpo-

rated into a model

via random variables.

In this example,

we will look at Baker Limited,

a fictitious insurance company

which provides a health insur-

ance plan to its customers. In

particular, we examine how

Baker can use a simulation

model to determine how much

money it should accrue in the

coming year (2017) to pay for

customer insurance claims.

It is now December 31,

2016. As of December 2016,

Baker has 15,222 customers

who each pay $200 per month

in premiums. Average insur-

ance claim per customer in the

month was $185. Although

Baker does not expect to

raise premiums in 2017, it

expects the number of custom-

ers signed on to the health

Simulation is an extremely useful decision-mak-

ing tool for accountants. While many companies

have invested heavily in developing sophisticated

software to help them run simulations, simula-

tions can also be run on tools such as Excel. In

this article, I will use a simple example to demon-

strate how an accountant could use Excel to run

simulations. © 2018 Wiley Periodicals, Inc.

Editorial Review