Capital Budgeting Simulation Using Excel: Enhancing the Discussion of Risk in Managerial Accounting Classes
Sylwia Gornik‐Tomaszewski · Management accounting quarterly · 2014
EXECUTIVE SUMMARYAn Excel-based capital budgeting simulation model that contains a degree of randomness and uncertainty can be used to expand classroom discussions of risk in the context of capital investment analysis. This adds a level of detail that is often over-looked by most managerial accounting textbooks.Many leading managerial accounting textbooks provide only limited coverage of risk in the context of capital budgeting, often referred to as capital investment analysis. Capital budgeting chapters usually focus on widely used decision models, such as net present value (NPV), inter- nal rate of return (IRR), accounting rate of return (ARR), profitability index (PI), and the payback period (PB), and are often enhanced with analyzing tax implications in capital bud- geting decisions.1 But only the deterministic versions of these models, in which no randomness is involved, are covered. Cash flows are forecasted as single figures, and their uncer- tainty is ignored. The risk associated with an investment proj- ect is expressed in the selected discount rate (required rate of return). The lesson being told, therefore, is that the greater the risk that is associated with an investment, the greater the return that is required.In practice, however, several different techniques are used to deal with the uncertainty of investment projects. Firms might combine NPV with PB when analyzing the total risk of a project. They also might use sensitivity analysis, scenario analysis, risk-adjusted discount-rate approach, or simulation. These techniques are applied following the assumption that it is relevant to consider a project's total risk when evaluating the project and when the returns from the project are positively correlated with the returns from the firm as a whole.2Although the leading textbooks do not delve into these methods for dealing with uncertainty in invest- ments, the techniques can be explained easily in the classroom, especially when supplemented with spread- sheet applications. An educational model developed in Excel can provide a simple simulation of the NPV of an investment project whose outcome is uncertain. The model is easy to use, requiring only a basic Excel pack- age and not the @Risk add-in.Basic Simulation ModelA simulation model is a computer model that imitates a real-life situation. When applicable in capital budgeting, the simulation approach generally is more feasible for analyzing large projects because the technique requires estimates to be made of the probability distribution of each cash flow element.Simulation uses random numbers to drive the model- ing process. All spreadsheet packages are capable of generating random numbers between 0 and 1. In Excel, random numbers are generated by entering the formula =RAND() in a cell. The random numbers are uniformly distributed-that is, any number between 0 and 1 has the same chance of occurrence. Also, different random numbers generated in one spreadsheet are probabilisti- cally independent, meaning the random value in one cell does not affect random values in other cells.3It is critical to understand that flux is an important characteristic of simulation. We will have to get accus- tomed to constantly changing numbers because all the cells containing the RAND function will change each time we press the recalculate key (F9) or do anything to affect calculation.Figure 1 presents a simple capital budgeting model involving the introduction of a new product. We will need to determine annual after-tax cash flows generated by the new product. Therefore, the first step in devel- oping the model is to enter the relevant data needed to compute these cash flows. This includes data to deter- mine revenues, costs, depreciation, and marginal tax rate. Also needed is the firm's required rate of return to discount the after-tax net cash flow to present value. Rows 5 to 15 of the spreadsheet contain assumed data on price per unit (p), number of units sold (q), unit pro- duction cost (c), unit selling cost (s), annual depreciation (D), the firm's marginal tax rate (T), and the firm's re- quired rate of return (k). …