Answer question on Excel, calculator exactly number
hw_7___engineering_economics__1_.pdf
Unformatted Attachment Preview
Introduction to Engineering
Engineering Economics
This homework will walk through the procedure of cost-benefit analysis of a relatively simple cash flow.
You will evaluate two design alternatives based on both a comparison of the net present value and the
internal rate of returns. Remember that the steps that you are going to walk through here are
conducted at the end of an arduous process of coming up with a reasonable estimate of costs and
benefits. For public expenditures this would likely include discussions with and possibly polling of the
public and public interest groups. These are typically referred to as “stakeholders”. Years ago,
stakeholder interests were largely ignored, however in today’s political environment they may be
decisive.
As discussed in class, there are two fundamental means of comparing design alternatives. The
equivalence method simply calculates the Net Present Value for each design alternative at a specified
discount rate. If no alternative has a Net Present Value greater than 0, then the do-nothing alternative
is preferable. If one or more Net Present Value is greater than 0, then the design alternative with the
higher Net Present Value is selected.
For the rate of return method, the alternatives are compared based on the internal rate of return of the
incremental costs and benefits between alternatives and the institution’s project planning discount rate.
The internal rate of return is the discount rate for which a project’s Net Present value are equal to zero.
We’ll work out an example with two alternatives to explain further how this works.
Design Alternative 1
Cash Flow Item
Amount
Initial cost
$1,000,000
Annual maintenance
$40,000 per year in year 1 with increase of $60,000
per year in subsequent years
Annual Benefit
$375,000 per year
Project Life
10 years
Bunker Hill Community College
Prof. Jacobs
Spring 2019
Intro to Engg
ENR-101
Design Alternative 2
Cash Flow Item
Amount
Initial cost
$1,500,000
Annual maintenance
$40,000 per year in year 1 with increase of $60,000
per year in subsequent years
Annual Benefit
$450,000 per year
Project Life
10 years
Procedure:
Create Cash Flow Diagrams
1. Tabulate the cash flows for design alternatives 1 and 2. Make sure to note costs as negative
values and benefits as positive.
2. Plot the cash flow for alternative 1 as a bar chart. (Hint: In my experience, if you are doing this
in Excel and select the project year and cash flow as two columns of data and then insert a
column chart, you’ll find that it will create a bar chart with the year and the cash flow as two
categories. In order to get Excel to plot the project year on the x-axis, I found that I needed to
select Insert -> Recommended Charts and then selected the 2nd item, which was a bar chart in
which the year 0 coincides with the first bar from the left.) Here’s what I get for alternative 1.
Cash Flow Diagram for Alternative 1
$400,000.00
$200,000.00
$0.00
0
1
2
3
4
5
6
7
8
9
10
($200,000.00)
($400,000.00)
($600,000.00)
($800,000.00)
($1,000,000.00)
($1,200,000.00)
3. Plot the cash flow for alternative 2 as a bar chart.
Bunker Hill Community College
Prof. Jacobs
Spring 2019
Intro to Engg
ENR-101
Rank Alternatives using Equivalence Method
4. Using the table below, calculate P/G for a discount rate of 7 percent and a duration of ten years.
Present
Value of
Converts
Symbol
Formula
Single Payment
To P from F
( ⁄ ) ,
1
(1 + )
Uniform Series
To P from A
( ⁄ ) ,
(1 + ) − 1
(1 + )
Uniform
Gradient
To P from G
( ⁄ ) ,
(1 + ) − 1
−
2 (1 + )
(1 + )
5. Calculate P/A for a discount rate of 7 percent and a duration of ten years.
6. Calculate P/G for a discount rate of 3 percent and a duration of ten years.
7. Calculate P/A for a discount rate of 3 percent and a duration of ten years.
8. Calculate the Net Present Value for Alternative 1 for a discount rate of 7 percent. Please show
your work in a table with the following format.
Type
Amount
(1000’s $)
Factor
Factor Amt.
Present Value
(1000’s of $)
One-Time Initial
Annuity
P/A0.05,8
Gradient
P/G0.05,8
Net Present Value
9. Calculate the Alternative 1 Net Present Value for a discount rate of 3 percent.
10. Comment on the difference between the Alternative 1 Net Present Values calculated with
discount rates of 7 percent and 3 percent.
11. Calculate the Net Present Value for Alternative 2 for a discount rate of 7 percent.
Bunker Hill Community College
Prof. Jacobs
Spring 2019
Intro to Engg
ENR-101
12. Which alternative is preferable based on the equivalence method of comparing alternatives for
a discount rate of 7 percent?
Alternatives using Rate of Return Method
13. Calculate the Net Present Value for Alternative 1 for discount rates of 3, 4, 5, 6, 7, 8, 9, 10, 11,
12, and 13 percent. You can do this in one of two ways. In the first, use Excel’s built-in NPV
function (i.e. “=NPV(B22,C$9,C$10)” where cell B22 contains the interest rate and C$9 and C$10
contains the cash flow for years 0 and 1). Alternatively, you may calculate P/A and P/G as you
did above and then multiplying these factor values times the components of the cash flow.
14. Create a chart of the Alternative 1 Net Present Value versus the discount rate.
15. What is the Internal Rate of Return of Alternative 1? (See definition for internal rate of return
above and in lecture notes.)
16. Is Alternative 1 an acceptable project given a discount rate of 7 percent?
17. Calculate the Net Present Value for Alternative 2 for the same range of discount values and
create a chart showing the Net Present Value versus the discount value.
18. What is the Internal Rate of Return of Alternative 2?
19. Can you determine which alternative is preferable based on the calculated rates of return? Why
or why not?
20. Tabulate the incremental costs and benefits between design Alternatives 1 and 2.
21. Calculate the internal rate of return based on the incremental costs/benefits between design
Alternatives 1 and 2. (Use the same method as you did for Alternatives 1 and 2.)
22. What is the preferred design alternative, presuming a discount rate of 7 percent?
Bunker Hill Community College
Prof. Jacobs
Spring 2019
Intro to Engg
ENR-101
…
Our essay writing service fulfills every request with the highest level of urgency.
attachment
