Capital Budgeting PaperOverviewFor this week’s assignment, review the attached articles and any outside research (at least 3 outside references) you have done related to how the evaluation process and selection of capital projects are affected by mutually exclusive projects, project sequencing, and capital rationing. Pay particular attention to Burns and Walker’s 2009 article, “Capital Budgeting Surveys: The Future is Now,” which is attached .InstructionsWrite a 5-page paper (References and cover pages not included) that summarizes your conclusions about capital budgeting. Include the following:Analyze the major components of capital budgeting process evaluation and selection of capital projects.Explain the rationale and the effect of mutually exclusive projects.Examine some of the foundations and key issues of capital budgeting decision making, including how companies can determine the success of their portfolio of investment projects and decisions on capital rationing. Assess ethical considerations that may arise in capital budgeting.Compare and contrast project sequencing and capital rationing.Explain the calculation and interpretation of net present value (NPV), internal rate of return (IRR), payback period, and profitability index (PI) of a capital project.Explain the calculation and interpretation of the weighted average cost of capital (WACC), including how taxes affect the cost of capital from different capital sources.Based on the article, briefly evaluate the current development on capital budgeting and benefits discussed by Burns and Walker (2009).Be sure your assignment meets the following guidelines. Refer to the attached scoring guide for specific information about how your paper will be evaluated.Written communication: Written communication is free of errors that detract from the overall message.Scholarship: Use at least 5 outside sources to support your main points and analysis.APA formatting: All resources and citations should be formatted according to current APA style and formatting guidelines.Length: 5 typed, double-spaced pages (Introduction and conclusion required)Font and font size: Times New Roman, 12 point.Please pay close attention to the paper scoring guide and ensure that you covered all elements.Any questions or concerns, please let me know

capital_budgeting_article_1__by_burns_and_walker__2009_.pdf

capital_budgeting_article_2.pdf

Don't use plagiarized sources. Get Your Custom Essay on

Capital Budgeting

Just from $13/Page

capital_budgeting_article_3.pdf

paper_scoring_guide.docx

Unformatted Attachment Preview

Capital Budgeting Surveys: The Future is Now

Burns, Richard M;Walker, Joe

Journal of Applied Finance; 2009; 19, 1/2; ProQuest Central

pg. 78

Reproduced with permission of the copyright owner. Further reproduction prohibited without permission.

Reproduced with permission of the copyright owner. Further reproduction prohibited without permission.

Reproduced with permission of the copyright owner. Further reproduction prohibited without permission.

Reproduced with permission of the copyright owner. Further reproduction prohibited without permission.

Reproduced with permission of the copyright owner. Further reproduction prohibited without permission.

Reproduced with permission of the copyright owner. Further reproduction prohibited without permission.

Reproduced with permission of the copyright owner. Further reproduction prohibited without permission.

Reproduced with permission of the copyright owner. Further reproduction prohibited without permission.

Reproduced with permission of the copyright owner. Further reproduction prohibited without permission.

Reproduced with permission of the copyright owner. Further reproduction prohibited without permission.

Reproduced with permission of the copyright owner. Further reproduction prohibited without permission.

Reproduced with permission of the copyright owner. Further reproduction prohibited without permission.

Reproduced with permission of the copyright owner. Further reproduction prohibited without permission.

Capital Budgeting Simulation

Using Excel: Enhancing the

Discussion of Risk in Managerial

Accounting Classes

By Sylwia Gornik-Tomaszewski, DBA, CMA, CFM

EXECUTIVE SUMMARY

An Excel-based capital budgeting

simulation model that contains a

degree of randomness and uncertainty

can be used to expand classroom

any 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), internal 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 budgeting 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 uncertainty is ignored. The risk associated with an investment project 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.

M

discussions of risk in the context of

capital investment analysis. This adds

a level of detail that is often overlooked by most managerial accounting

textbooks.

M A N A G E M E N T A C C O U N T I N G Q U A R T E R LY

12

SUMMER 2014, VOL. 15, NO. 4

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.2

Although the leading textbooks do not delve into

these methods for dealing with uncertainty in investments, the techniques can be explained easily in the

classroom, especially when supplemented with spreadsheet 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 package and not the @Risk add-in.

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 production cost (c), unit selling cost (s), annual depreciation

(D), the firmâ??s marginal tax rate (T), and the firmâ??s required rate of return (k).

To build uncertainty into the model, assume that the

first three variablesâ??p, q, and câ??are random (stochastic) in nature. This requires an estimation of the probability distribution for each of these random variables.

Although a discrete distribution could be used with a

finite, relatively small number of possible values with

known probabilities, it is more convenient to use continuous distributions in a simulation. The RAND function in Excel generates various continuous distributions

commonly used in applications. Assume that the three

random variablesâ??p, q, and câ??are normally distributed.

Cells C14:E15 contain the mean (â®) and standard deviation (â´) for each of the three random variables. As you

can see in Figure 1, the price per unit (p) is normally

distributed with â® = $10 and â´ = $2; the number of

units sold (q) is normally distributed with â® = 2,000 and

â´ = 300; and the unit production cost (c) is normally distributed with â® = $2 and â´ = $0.25. These small numbers are selected on purpose to make the model easy to

understand. Users of the model can use their own

prices, costs, quantities, and determine distributions of

these numbers. It is suggested, however, that certain

basic rules should apply, such as assuming p>c and

â´<â®, to avoid complications that obscure the purpose
and focus of this exercise.
With those assumptions, applying the Excel function
=NORMINV(RAND(),â®,â´) will generate random values for p, q, and c based on normal distribution. For example, the formula =NORMINV(RAND(),C14,C15) in
cell C5 generates the random price per unit (p) from a
normal distribution. The values for q and c are generated in a similar manner from their respective means
and standard deviations.
Now that the data inputs are defined, the next step is
to build the NPV model, seen in cells A17:E26 of
Figure 1. The model assumes that (1) the initial investment necessary to introduce the new product is $10,000
(cell C20), (2) the new product will have a life of five
Basic Simulation Model
A 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 modeling 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 probabilistically independent, meaning the random value in one
cell does not affect random values in other cells.3
It is critical to understand that flux is an important
characteristic of simulation. We will have to get accustomed 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 developing the model is to enter the relevant data needed to
compute these cash flows. This includes data to determine revenues, costs, depreciation, and marginal tax
M A N A G E M E N T A C C O U N T I N G Q U A R T E R LY
13
SUMMER 2014, VOL. 15, NO. 4
Figure 1: Capital Budgeting with Simulation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
131
132
133
134
A
B
C
D
E
NCF
p
q
c
s
D
T
k
Mean:
StDev:
10.16
2,292
2.04
1
2,000
40%
10%
H
p
10
2
price per unit
number of units sold
unit production cost (excluding depreciation)
unit selling cost
annual depreciation
the firm's marginal tax rate
required rate of return
q
2,000
300
The NPV Determination:
NPV
G
CAPITAL BUDGETING WITH SIMULATION
Data:
Item
Investment
F
Year (t)
now
1
2
3
4
5
Amount
(10,000)
14,311
14,311
14,311
14,311
14,311
T ax E ffec t
1
60%
60%
60%
60%
60%
c
2
0.25
A f e r - ta x
NCF t
(10,000)
10,587
10,587
10,587
10,587
10,587
$30,132
Simulation:
Iteration
1
2
3
4
5
6
7
8
9
10
100
Mean
StDev
Min
NPV
$30,132.07
$30,450.64
$20,031.96
$33,898.81
$14,301.81
$34,231.82
$19,130.41
$31,494.15
$28,536.55
$38,796.87
$30,653.14
$15,188.38
$24,329.62
$10,808.35
$1,537.28
Frequency table for 100 iterations
N PV
Frequency
0
0
10,000
8
20,000
30
30,000
30
40,000
27
50,000
4
>50,000

1

100

years (rows 21-25), and (3) no additional investments

will be required during the product lifespan. These assumptions make the annual after-tax cash flows generated from the sale of the new product identical. They

are computed using the formula NCFt = [q(p) â?? q(c) â??

D](1â??T) D; where NCFt is the net cash flow in period

t. Unless a company is a tax-exempt organization, only

the after-tax amount (column E) should be used when

M A N A G E M E N T A C C O U N T I N G Q U A R T E R LY

determining the desirability of an investment proposal.

Depreciation is not a cash flow. Because it affects the

taxes that must be paid, however, it has an indirect effect on the companyâ??s cash flows. The depreciation deduction shields the revenues from taxation and thereby

reduces the amount of taxes that the company must

pay. The reduction in tax payments made possible by

the depreciation tax shield is equal to D â?? T.

14

SUMMER 2014, VOL. 15, NO. 4

In the Excel model, the after-tax net cash flows are

computed in three steps:

(1) The formula in cell C21 is =$C$6*$C$5$C$6*($C$7$C$8)-$C$9.

(2) Cell D21 contains =1-$C$10, which is the tax

effect.

(3) Cell E21 finishes the calculation by multiplying

the annual cash flows by the tax effect and then

adding depreciation. The formula is

=(C21*D21)$C$9.

The formulas used in C21:E21 are copied in rows

22-25. The NPV amount is computed in cell E26 using

=NPV(C11,E21:E25)?. Net cash flows are discounted at the 10% required rate of return (C11) except

for the initial investment (E20), which is incurred now,

at t=0.

mate of an expected NPV of the project, as well as its

risk. With this information, it is possible to compute the

probability of achieving an NPV that is greater or less

than any particular value. The calculation is z = (x â??

Expected NPV)/Standard Deviation, where z is the

number of standard deviations and x is the particular

value we want to check.

For example, if we wanted to compute the probability

of achieving an NPV less than or equal to $0 using the

results in Figure 1, the calculation would be z = ($0 â??

$24,329.62)/$10,808.35. The result is -2.25 standard deviations. By referring to a normal distribution table, we

can determine that the probability of a value less than 2.25 standard deviations from the mean is 1.22%. Thus,

there is a 1.22% chance that the actual NPV will be

negative for this project.

Replications of Simulation

Frequency Table and Histogram

The calculations to this point show only one possible

scenario for the five-year horizon. The $30,132 NPV in

cell E26 indicates that this independent project is

acceptableâ??the NPV represents a $30,000 gain (the return from the project is greater than the cost of capital,

that is the return available by investing the capital elsewhere). The project is expected to add value to the

firm, and it should improve shareholdersâ?? wealth, but

this might be a result of a lucky set of random numbers.

The next step is to check this by replicating the basic

simulation 100 times.

To do this, a data table is created in the range

A30:B131. Column A (A32:A131) labels each iteration,

1 through 100. To begin with the existing NPV calculation, the formula =E26 is entered into cell B31. Next,

highlight cells A31:B131. From the Data tab in the

Excel ribbon, click on the What-If Analysis dropdown

menu and select Data Table. This will bring up the

Data Table dialog box. Leave the Row Input Cell field

blank. For the Column Input Cell, select any blank cell

in the worksheet (such as C32). Click OK. This operation tricks Excel into repeating the NPV model calculations 100 times, each time using new random numbers.

Below the data table, formulas have been added to

show the mean, standard deviation, and the minimum

and maximum values of the 100 iterations (cells

A132:B135). These simulation results provide an esti-

A histogram, a graphical representation of the data distribution, can help us visualize the results from the

model and facilitate further analysis. A histogram is a

summary graph showing a count of the data points

falling in various ranges. Consequently, it is a rough approximation of the frequency distribution of the data.

Users of the model may then examine the histogram for

the general shape of the frequency distribution and its

symmetry. This may give them a better understanding

of the data and help them form expectations regarding

the probability of various outcomes. For instance, in our

example, the users may gain a better understanding of

how probable it is that the project would yield a negative NPV and, therefore, should not be accepted.

Although a histogram option is available in the

Analysis Tools (Data Analysis) in Excel that provides a

one-step method for generating a histogram, that option

does not retain a link to the data that was used to generate it. That is, the histogram will remain static and

will not change anytime the spreadsheet recalculates.

The following method does not have this drawback.

Create a frequency table of the data. This will provide data ranges for our histogram and retain a link to

the data. In Excel, this can be done using the FREQUENCY formula, which calculates how often certain

values occur within a specified range and then returns a

vertical array of numbers. The syntax for the formula is

M A N A G E M E N T A C C O U N T I N G Q U A R T E R LY

15

SUMMER 2014, VOL. 15, NO. 4

Figure 2: Histogram of NPV After 100 Replications

Histogram of NPV

40

35

30

Frequency

25

20

Frequency

15

10

5

0

0

10,000

20,000

30,000

NPV

=FREQUENCY(data_array,bins_array), where data_

array is the data set that contains the values for which

we want to count frequencies and bins_array is an array

containing the intervals we want to use to group the

values.

Because FREQUENCY returns an array, it must be

entered as an array formula. That requires selecting a

range of adjacent cells into which we want the returned

distribution to appear. Furthermore, the number of elements in the returned array will be one greater than the

number of elements in bins_array. The extra element in

the returned array returns the count of any values above

the highest interval.4

The frequency table appears in cells E30:G39 in

Figure 1. For this example, it shows how many NPV

observations are negative, how many are between $0

M A N A G E M E N T A C C O U N T I N G Q U A R T E R LY

40,000

50,000

>50,000

and $10,000, how many are between $10,000 and

$20,000, and so on up to values greater than $50,000.

Cells E32:E37 contain the upper limit for each interval,

so they represent the bins_array. Note that cell E38 lists

>$50,000. This will be the label for the extra element in

the returned array.

Next we need to select the range G32:G38. Type in

=FREQUENCY(B32:B131,E32:E37) and then press

CTRL????, which enters it as an array

formula. If the formula is not entered as an array formula, there will be only one result in cell G32. If

entered correctly, results will appear in each of the cells

selected.

With this frequency table formed, it is now easy to

create a histogram. Select the frequency table cells

(E32:G38) and create a column chart (see Figure 2).

16

SUMMER 2014, VOL. 15, NO. 4

The categories from column E of the frequency table in

Figure 1 serve as the labels for the horizontal axis in

Figure 2. Notice that this method of generating a histogram retains the link to the data. Any changes in the

data will also change the chart. To freeze the results, select and copy the range of cells, then use Paste Values

to replace the formulas with the actual numbers.

tional managerial accounting curriculum.

Use in the Classroom

Additional References

This model was used in several graduate managerial accounting classes. It was usually discussed at the end of

a lecture on capital investment decisions. After a short

introduction that described the model and its purpose,

the model was built in real-time, and simulations were

run. Students also watched the construction of the frequency table and the histogram. They were usually

very interested in the topic and fascinated by the everchanging simulation output. The spreadsheet was later

posted online so students could further explore the intricacies of the model on their own.

To reinforce studentsâ?? learning and their interest in

simulation, a homework project was assigned that required students to develop a different model. Students

should be made aware that when they try to build the

model using their spreadsheets, each result will undoubtedly be different. No two answers will ever be

exactly the same. This fact should stimulate critical

thinking and creativity.

Enterprise Risk Management Initiative Staff, â??Impact

of Risk Management Failures on the Financial

Crisis,â? http://erm.ncsu.edu/library/article/financialcrisis-failures#.Us9gbNJDvNk, January 3, 2011.

Michelle M. Harner, â??Ignoring the Writing on the Wall:

The Role of Enterprise Risk Management in the

Economic Crisis,â? Journal of Business and Technology

Law, October 15, 2009, p. 45.

Jennifer Saranow Schultz, â??An Education in Risk

Management Can Offer a Leg Up,â? The New York

Times, August 20, 2009, p. F2.

Sylwia Gornik-Tomaszewski, DBA, CMA, CFM, is an associate professor in the Department of Accounting and

Taxation of The Peter J. Tobin College of Business at St.

Johnâ??s University in Queens, N.Y. You can contact Sylwia at

(718) 990-2499 or gornikts@stjohns.edu.

Endnotes

1 For example: Charles T. Horngren, Srikant M. Datar, and

Madhav T. Rajan, Cost Accounting, 15th edition, Prentice Hall,

2014; Eric Noreen, Peter C. Brewer, and Ray H Garrison,

Managerial Accounting for Managers, third edition, McGraw-Hill,

2014; Carl S. Warren, James M. Reeve, and Jonathan Duchac,

Managerial Accounting, 12th edition, Cengage Learning, 2014;

and Susan V. Crosson and Belverd E. Needles, Managerial

Accounting, 10th edition, Cengage Learning, 2014.

2 R. Charles Moyer, James R. McGuigan, Ramesh P. Rao, and

William J. Kretlow, Contemporary Financial Management, 12th

edition, Cengage Learning, 2012.

3 Wayne L. Winston and S. Christian Albright, Practical

Management Science: Spreadsheet Modeling and Applications, fourth

edition, Cengage Learning, 2011.

4 Ibid.

5 For example, users may use the RAND function to generate

random numbers from such distributions as: (1) uniform

distribution between a and b (the Excel formula is =a (ba)*RAND(), where the known values are substituted for a and

b); and (2) exponential distribution with mean m (the Excel

function is =-m*LN(Rand()), where the known value is

substituted for m).

Further Uses of the Model

The simple capital budgeting example presented in

Figure 1 can be extended in a variety of ways. For example, more of the input variables could be made random, or the probability distribution for individual variables used in the simulation may assu …

Purchase answer to see full

attachment

Why Work with Us

Top Quality and Well-Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional and Experienced Academic Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Prompt Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission Essays & Business Writing Help

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Reviews

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Reviews

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.