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