Academic journal article Journal of Accountancy

Expect the Unexpected: Risk Assessment Using Monte Carlo Simulations: With Software Such as Microsoft Excel, CPAs Can Perform Statistical Simulations to Assess the Potential Upside and Risk of Business Decisions

Academic journal article Journal of Accountancy

Expect the Unexpected: Risk Assessment Using Monte Carlo Simulations: With Software Such as Microsoft Excel, CPAs Can Perform Statistical Simulations to Assess the Potential Upside and Risk of Business Decisions

Article excerpt

Modern technology provides managers and accountants the means to effectively and efficiently perform increasingly complex quantitative analyses related to decision-making and financial reporting. With software such as Microsoft Excel, CPAs can perform statistical "Monte Carlo" simulations to assess business decisions and accounting estimates, evaluating not only their expected values, but also their potential upside and downside risks. This article provides a brief description of the Monte Carlo technique, demonstrates how it can be performed using Excels features, and illustrates its use in a common business application.

MONTE CARLO: A BRIEF DESCRIPTION

The Monte Carlo simulation technique, named for the famous Monaco gambling resort, originated during World War II as a way to model potential outcomes from a random chain of events. It is particularly useful when an outcome is the product of multiple random variables (i.e., sources of uncertainty) and is readily adaptable to model any situation that involves uncertainty. The simulation includes a mathematical formula that reflects the evaluated outcome based on random variables known to influence the outcome, places a value on each variable from its identified range of variation, and then computes an outcome.

Today, limited only by computing power and software constraints, CPAs can run this basic calculation and repeat it thousands, tens of thousands, or even millions of times, with each computation using an alternative set of randomly generated values for the determinant variables. The resulting output creates a range of possible outcomes from which one can assess the likelihood of a specific outcome, or for the application described in this article, the reasonableness of an accounting estimate based upon its modeled frequency of occurrence.

The example below demonstrates the use of a Monte Carlo simulation in Microsoft Excel for two decisions involving a loan covenant--a scenario familiar to financial managers, accountants, and auditors. The technique, however, could apply to myriad situations where a variety of outcomes are possible and the objective is to assess the likelihood of a specific outcome or the reasonableness of an accounting estimate.

A DEMONSTRATION FROM MANAGERIAL FINANCE

Example Co. is privately held and has financing from a bank loan. As a condition of the loan, the lender requires that Example Co. maintain an interest coverage ratio, defined as earnings before interest and taxes (EBIT) divided by interest expense, of 1.5. While Example Co. is financially sound and expects to be profitable, its profits face two major sources of uncertainty. First, revenues fluctuate due to variations in product mix and sales volume. Second, the firm's costs present an element of uncertainty due to factors such as variations in the sales mix, efficiencies in operations, changes to input prices, etc.

To estimate EBIT and compute the interest coverage ratio, the CFO analyzes the company's cost structure. Historically, variable costs average 35% of revenues, but this percentage randomly fluctuates from one period to the next. The firm also has fixed operating costs of $5 million. The interest charged by the lender is also fixed with an annual amount of $500,000, as the net borrowings on the loan are not expected to change.

Based on prior years (e.g., a couple of years of sales data) and current market information, revenues for the current period are projected to be $10 million. The expected EBIT is $1.5 million, computed as ($10,000,000 x (1 - 0.35) --$5,000,000), and the expected interest coverage ratio is 3.0 ($1,500,000 / $500,000). Based only on these estimates, the firm appears likely to meet the lender's covenant. Rather than relying on the relatively large margin of safety reflected in this single calculation, the CFO uses Excel to further quantify the likelihood of a covenant violation by performing a Monte Carlo simulation of EBIT and the resulting interest coverage ratio. …

Search by... Author
Show... All Results Primary Sources Peer-reviewed

Oops!

An unknown error has occurred. Please click the button below to reload the page. If the problem persists, please try again in a little while.