ENGM 515

Advanced Engineering
[Link]
Mohammed
Aman
Time Value of Money Applications on Excel

1-2
 Topics

1. NVP
2. PW, AW, and FW
3. Breakeven Analysis Using one-way data tables – for one input variable
4. Breakeven Analysis Using Goal and Seek

1-3
 EXCEL FUNCTIONS
Functions for Specific Applications:
=NPV(rate,value1,value2,…)
Net present value (or discounted cash flow) measures the worth of a stream
of cash flows, taking into account the time value of money.
F is the cash flow ($)
i is the discount rate
t is the number of time
periods into the future,
where t = 0, 1, …, n

2-4
 EXCEL FUNCTIONS

Example: Using the NPV Function

=NPV(rate,value1,value2,…)
Cell B8:
=NPV(B6, C4:H4) – B5

Solution:
See Excel Spreadsheet! 2-5
 ANALYZING UNCERTAINTY USING EXCEL
Data Tables
 Data Tables summarize the impact of one or two inputs on a
specified output.
 Excel data table types:
One-way data tables – for one input variable
Two-way data table – for two input variables
To construct a data table:
 Data
 What-If Analysis
 Data Table

8-6
 ANALYZING UNCERTAINTY USING EXCEL

Goal Seek
Goal Seek allows you to alter the data used in a formula in order to
find out what the results will be.
 Set cell contains the formula that will return the result you're
seeking.
 To value is the target value you
want the formula to return.
 By changing cell is the location
of the input value that Excel
can change to reach the target.

8-7
 ANALYZING UNCERTAINTY USING EXCEL
Example: Finding the Breakeven Point in the Outsourcing Model (using Goal Seek)
Find the value of demand at which manufacturing cost equals purchased cost
Set cell: B19 To value: 0
By changing. cell: B12.

The breakeven volume is 1000 units.

8-8
ANALYZING UNCERTAINTY USING EXCEL
Example

Solution:
See Excel Spreadsheet!
8-9
ANALYZING UNCERTAINTY USING EXCEL
Example

Solution:
See Excel Spreadsheet!
8-10
 ANALYZING UNCERTAINTY USING EXCEL
Example: A One-Way Data Table for Uncertain Demand

Data
Table tool
computes
these
values

-Create a column of demand values (column E).

-Enter =C22 in cell F3 (to reference the output cell).
-Highlight the range E3:F11.
-Choose Data Table.
-Enter B8 for Column input cell. (tells Excel that column E is demand values)
8-11
 ANALYZING UNCERTAINTY USING EXCEL

Example (continued): A One-Way Data Table for Uncertain Demand

The Data Table tool computes the profit

values in column F (below $240,000).

8-12
 ANALYZING UNCERTAINTY USING EXCEL

Example (continued): A One-Way Data Table with Multiple Outputs

Create a second output, revenue:

-Enter =C15 in cell G3.
-Highlight E3:G11.
-Choose Data Table
-Proceed as in the previous example.
-Excel computes the revenues values.

8-13
 ANALYZING UNCERTAINTY USING EXCEL

Example (continued): A Two-Way Data Table for the Profit Model

Evaluate the impact of both unit price and unit cost

-Create a column of unit prices (F5:F15).
-Create a row of unit costs (G4:J4).
-Enter =C22 in cell F4.
-Select F4:J15.
-Choose Data Table.
-Enter B6 for Row input cell. Data Table tool
-Enter B5 for Column input cell. computes
these cell
values.

8-14
ANALYZING UNCERTAINTY USING EXCEL

Example (continued): A Two-Way Data Table for the Profit Model

8-15
 SPREADSHEET MODELING AND
ENGINEERING
Example 2.10 Modeling Net Income on a Spreadsheet
 Gross profit: The money your goods or services earned after
subtracting the total costs to produce and sell them.
 Operating expenses: An expenditure that a business incurs as a
result of performing its normal business operations.
 Net operating income: The profitability of an asset or an
investment after subtracting operating expenses.
 Earnings before taxes: The money retained by the firm before
deducting the money to be paid for taxes.
 Net income: The amount of accounting profit a company has left
over after paying off all its expenses.

COPYRIGHT © 2013 PEARSON

EDUCATION, INC. PUBLISHING AS
PRENTICE HALL 2-16
 SPREADSHEET MODELING AND
ENGINEERING
Example 2.10 Modeling Net Income on a Spreadsheet
 Gross profit = sales – cost of goods sold
 Operating expenses = administrative expenses
+ selling expenses
+ depreciation expenses
 Net operating income = gross profit
 – operating expenses
 Earnings before taxes = net operating income
– interest expense
 Net income = earnings before taxes – taxes

COPYRIGHT © 2013 PEARSON

EDUCATION, INC. PUBLISHING AS
PRENTICE HALL 2-17
SPREADSHEET MODELING AND
ENGINEERING
Simple Spreadsheet Model
for Computing Net Income

Figure 2.15
COPYRIGHT © 2013 PEARSON
EDUCATION, INC. PUBLISHING AS
PRENTICE HALL 2-18
 SPREADSHEET MODELING AND
ENGINEERING
Data-Model Format for Computing Net Income

Figure 2.16
COPYRIGHT © 2013 PEARSON
EDUCATION, INC. PUBLISHING AS
PRENTICE HALL 2-19
EXCEL FUNCTIONS

2-20
 LOGIC-DRIVEN MODELING
The Economic Value of a Customer

V = value of a loyal customer

R = revenue per purchase
F = purchase frequency (number visits per year)
M = gross profit margin
D = defection rate (proportion customers not
returning each year)

COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS

8-21
PRENTICE HALL
 LOGIC-DRIVEN MODELING
The Economic Value of a Customer
Example:
What is the average value of a loyal customer (VLC) in a target market
segment if the average purchase price is $75 per visit, the frequency of
repurchase is six times per year, the contribution margin is 10%, and the
average customer defection rate is 25%?
Solution:

COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS

8-22
PRENTICE HALL
 LOGIC-DRIVEN MODELING
The Economic Value of a Customer
Example:
Use the base case data in the previous question, analyze how the value of a
loyal customer (VLC) will change if the average customer defection rate
varies between 15% and 40% (in increments of 5 percent) and the frequency
of repurchase varies between 3 and 9 times per year (in increments of 1 year).
Sketch graphs (or use Excel charts) to illustrate the impact of these
assumptions on the VLC.

Solution:
See Excel Spreadsheet!

COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS

8-23
PRENTICE HALL
LOGIC-DRIVEN MODELING
A Profit Model
Develop a decision model for predicting profit in face of
uncertain demand.

P = profit
R = revenue
C = cost

p = unit price
c = unit cost
F = fixed cost
S = quantity sold
D = demand
Q = quantity
produced Figure 8.1

COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS

8-24
PRENTICE HALL
LOGIC-DRIVEN MODELING

A Profit Model
Cost = fixed cost + variable cost
C = F + cQ
Revenue = price times quantity sold
R = pS

Quantity sold = Minimum{demand,

quantity produced}
S = min{D, Q}
Profit = Revenue − Cost
P = p*min{D, Q} − (F + cQ)
COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS
8-25
PRENTICE HALL
LOGIC-DRIVEN MODELING

Example 8.3 New-Product Development

 Moore Pharmaceuticals needs to decide whether to
conduct clinical trials and seek FDA approval for a
newly developed drug.
Estimated figures:
 R&D cost = $700 million
 Clinical trials cost = $150 million
 Market size = 2 million people
 Market size growth = 3% per year

COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS

8-26
PRENTICE HALL
 LOGIC-DRIVEN MODELING
Example 8.3 (continued) New-Product Development
Additional estimated figures
 Market share = 8%
 Market share growth = 20% per year (for 5 years)
 Revenue from a monthly prescription = $130
 Variable cost for a monthly prescription = $40
 Discount rate for net present value = 9%
Moore Pharmaceuticals wants to determine net present
value for the next 5 years and to determine how long it
will take to recover fixed costs.
Solution:
See Excel Spreadsheet!
COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS
8-27
PRENTICE HALL
LOGIC-DRIVEN MODELING

Example 8.6 A Retirement-Planning Model

 Start work at age 22, earning $50,000 per year.
 Expect a salary increase of 3% per year.
 Required to contribute 8% to retirement.
 Employer contributes 35% of that amount.
 Expect an annual return of 8% on the portfolio.
Determine the value of the retirement account when
the employee is 50 years old.

Solution:
See Excel Spreadsheet!
COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS
8-28
PRENTICE HALL
 DATA-DRIVEN MODELING
Example 8.7
Modeling Retail Markdown Pricing Decisions
 In the spring, a department store introduces a new line of
bathing suits that sells for $70.
 The store purchases 1000 of these bathing suits.
 During the prime selling season, the store sells an average
of 7 units per day at full price (40 days).
 On 10 sale days, the price is discounted 30% and sales
increase to 32.2 units per day.
 Around July 4th, the price is marked down 70% to sell off
remaining inventory.
 Determine total revenue from the bathing suits.

COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS

8-29
PRENTICE HALL
 DATA-DRIVEN MODELING
Example 8.7 (continued)
Modeling Retail Markdown Pricing Decisions

Assume a linear trend model between sales and price:

daily sales = a – b(price)
7 = a – b(70)
32.2 = a – b(49)
Therefore, a = 91, b = 1.2

Revenue from full retail sales = units sold * days * price

Revenue from clearance sales = leftovers * price
Solution:
See Excel Spreadsheet!
Figure 8.7

COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS

8-30
PRENTICE HALL
 ANALYZING UNCERTAINTY AND
MODEL
Data Tables ASSUMPTIONS
 Data Tables summarize the impact of one or two inputs on a
specified output.
 Excel data table types:
One-way data tables – for one input variable
Two-way data table – for two input variables
To construct a data table:
 Data
 What-If Analysis
 Data Table

Figure 8.14

COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS

8-31
PRENTICE HALL
 ANALYZING UNCERTAINTY AND
MODEL
Example 8.11 ASSUMPTIONS
A One-Way Data Table for Uncertain Demand

Create a column of demand

values (column E).
Enter =C22 in cell F3 Data
Table tool
(to reference the output cell). computes
these
Highlight the range E3:F11. values

Choose Data Table.

Enter B8 for Column input cell.
(tells Excel that column E is
demand values)
Figure
8.15a

Figure 8.14
COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS
8-32
PRENTICE HALL
 ANALYZING UNCERTAINTY AND
MODEL ASSUMPTIONS
Example 8.11 (continued)
A One-Way Data Table for Uncertain Demand

The Data Table tool

computes the profit
values in column F
(below $240,000).

Figure 8.15b

COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS

8-33
PRENTICE HALL
 ANALYZING UNCERTAINTY AND
Example
MODEL 8.12 ASSUMPTIONS
One-Way Data Tables with Multiple Outputs
Create a second output, revenue.

Enter =C15 in
cell G3.
Highlight E3:G11.
Choose Data Table
Proceed as in the
previous example.
Excel computes the
revenues values.

Figure 8.15

COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS

8-34
PRENTICE HALL
 ANALYZING UNCERTAINTY AND
MODEL
Example 8.13 ASSUMPTIONS
A Two-Way Data Table for the Profit Model
Evaluate the impact of both unit price and unit cost

Create a column of
unit prices (F5:F15).
Create a row of unit
Data Table tool
costs (G4:J4). computes
Enter =C22 in cell F4. these cell
Select F4:J15. values.
Choose Data Table.

Figure 8.17a

Enter B6 for Row input cell.

Enter B5 for Column input cell.

COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS

8-35
PRENTICE HALL
 ANALYZING UNCERTAINTY AND
MODEL
Example 8.13ASSUMPTIONS
(continued)
A Two-Way Data Table for the Profit Model

Figure 8.17b

COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS

8-36
PRENTICE HALL
ANALYZING UNCERTAINTY AND
MODEL ASSUMPTIONS

Goal Seek
Goal Seek allows you to alter the data used in a
formula in order to find out what the results will be.
 Set cell contains the formula that will return the
result you're seeking.
 To value is the target value you
want the formula to return.
 By changing cell is the location
of the input value that Excel Figure 8.21
can change to reach the target.

COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS

8-37
PRENTICE HALL
 ANALYZING UNCERTAINTY AND
MODEL
Example 8.15 ASSUMPTIONS
Finding the Breakeven Point in the Outsourcing
Model (using Goal Seek)
Find the value of demand
at which manufacturing
cost equals purchased cost
Set cell: B19
To value: 0
By changing
cell: B12.

Figure 8.21

The breakeven volume is 1000

units. Figure 8.22

COPYRIGHT © 2013 PEARSON EDUCATION, INC. PUBLISHING AS

8-38
PRENTICE HALL
 SPREADSHEET MODELING AND
ENGINEERING
Example 2.10 Modeling Net Income on a Spreadsheet
 Gross profit: The money your goods or services earned after
subtracting the total costs to produce and sell them.
 Operating expenses: An expenditure that a business incurs as a
result of performing its normal business operations.
 Net operating income: The profitability of an asset or an
investment after subtracting operating expenses.
 Earnings before taxes: The money retained by the firm before
deducting the money to be paid for taxes.
 Net income: The amount of accounting profit a company has left
over after paying off all its expenses.

COPYRIGHT © 2013 PEARSON

EDUCATION, INC. PUBLISHING AS
PRENTICE HALL 2-39
 SPREADSHEET MODELING AND
ENGINEERING
Example 2.10 Modeling Net Income on a Spreadsheet
 Gross profit = sales – cost of goods sold
 Operating expenses = administrative expenses
+ selling expenses
+ depreciation expenses
 Net operating income = gross profit
 – operating expenses
 Earnings before taxes = net operating income
– interest expense
 Net income = earnings before taxes – taxes

COPYRIGHT © 2013 PEARSON

EDUCATION, INC. PUBLISHING AS
PRENTICE HALL 2-40
SPREADSHEET MODELING AND
ENGINEERING
Simple Spreadsheet Model
for Computing Net Income

Figure 2.15
COPYRIGHT © 2013 PEARSON
EDUCATION, INC. PUBLISHING AS
PRENTICE HALL 2-41
 SPREADSHEET MODELING AND
ENGINEERING
Data-Model Format for Computing Net Income

Figure 2.16
COPYRIGHT © 2013 PEARSON
EDUCATION, INC. PUBLISHING AS
PRENTICE HALL 2-42
EVALUATING ALTERNATIVES
BASED ON AW ANALYSIS

1-43
 EVALUATING ALTERNATIVES
BASED ON PW ANALYSIS
For alternatives with un-equal lives the rule is:
PW of the alternatives must be compared for equal service
(i.e., alternatives must end at the same time)
2 approaches:
1- LCM -- Evaluate the alternatives over the least common
multiple of lives, (e.g., lives of 4 and 6, use n = 12), and assume
re-investment at same cash flow estimates
2- Specified Study period -- assume a planning horizon and
evaluate the alternatives over this number of years
(The LCM procedure is used unless otherwise specified) 1-44
EVALUATING ALTERNATIVES
BASED ON PW ANALYSIS

1-45
 EVALUATING ALTERNATIVES
BASED ON PW ANALYSIS
 Since the leases have different terms (lives), compare them over the
LCM of 18 years.
 For life cycles after the first, the first cost is repeated in year 0 of
the new cycle, which is the last year of the previous cycle.
 These are years 6 and 12 for location A and year 9 for B.
A
6 years 6 years 6 years

Cycle 1 for A Cycle 2 for A Cycle 3 for A

B
9 years 9 years

Cycle 1 for B Cycle 2 for B

18 years
1-46
EVALUATING ALTERNATIVES
BASED ON PW ANALYSIS

1-47
 EVALUATING ALTERNATIVES
BASED ON PW ANALYSIS

PWA = -15,000 - 15,000(P/F,15%a,6) + 1000(P/F,15%,6) - 15,000(P/F,15%,12) +

1000(P/F,15%,12) + 1000(P/F,15%,18) - 3500(P/A,15%,18) = $-45,036

PWB = -18,000 - 18,000(P/F,15%,9) + 2000(P/F,15%,9) + 2000(P/F,15%,18) -

3100(P/A,15 %,18) = $-41,384 Select “B”: Largest PW Cost @ 15% 1-48
 EVALUATING ALTERNATIVES
BASED ON PERMANENT
INVESTMENT ANALYSIS
CAPITALIZED COST (CC)- Present worth of a project that lasts forever.
 Government or public projects;
 Roads, dams, bridges, project that basically possess infinite life;

Infinite analysis period:

 “n” in the problem is either very long, indefinite, or infinity.

A A
AW = PW(i) = CC(i) P CC 
i
i

(i.e. P = A(P/A,i,n), If “n” approaches , then P = A/i  CC= A/i )

For example, in order to be able to withdraw $50,000 per year forever

at i = 10% per year, the amount of capital required is 50,000/0.10 = $500,000
1-49
EVALUATING ALTERNATIVES
BASED ON PERMANENT
INVESTMENT ANALYSIS

1-50
EVALUATING ALTERNATIVES
BASED ON PERMANENT
INVESTMENT ANALYSIS

1-51
 EVALUATING ALTERNATIVES
BASED ON B/C ANALYSIS
Must identify each cash flow as either benefit, disbenefit, or cost:
Benefit (B) -- Advantages to the public
Disbenefit (D) -- Disadvantages to the public
Cost (C) -- Expenditures by the government (Note: Savings to
government are subtracted from costs)

benefits - disbenefits B-D

B/C = =
costs C

- All + signs, costs included

- Salvage has – sign; subtracted from costs
1-52
 EVALUATING ALTERNATIVES
BASED ON B/C ANALYSIS

1. Determine equivalent total cost for each alternative

2. Order alternatives by increasing total cost
3. Identify B and D for each alternative
4. Calculate B/C for each alternative and eliminate all with B/C < 1.0
5. Determine incremental costs and benefits for first two alternatives
6. Calculate (∆B - ∆D) / ∆C ; if >1.0, higher cost alternative becomes
defender
7. Repeat steps 5 and 6 until only one alternative remains
1-53
 EVALUATING ALTERNATIVES
BASED ON B/C ANALYSIS
Must select one of two alternatives using i = 10% and ∆B/C ratio
Alternative X Y
First cost, $ 320,000 540,000
M&O costs, $/year 45,000 35,000
Benefits, $/year 130,000 170,000
Disbenefits, $/year 20,000 45,000
Life, years 10 20
1. AW of costsX = $97,080 and AW of costsY = $98,428
2. Order: X, Y AWx = 320K * (A/P, 10%, 10) + 45K
=97K
3. B and D for each alternative are given.
4. B/C for X: (130,000-20,000)/ 97,080 = 1,13 > 1 (keep)
B/C for Y: (170,000-45,000)/ 98,428 = 1,27 > 1 (keep)
5. Incremental values: ∆B = 170,000 – 130,000 = $40,000
∆D = 45,000 – 20,000 = $25,000
∆C = 98,428 – 97,080 = $1,348
6. Y vs. X: (∆B - ∆D) / ∆C = (40,000 – 25,000) / 1,348 = 11.1 > 1
Eliminate X, Select Y 1-54
 EVALUATING ALTERNATIVES
BASED ON B/C ANALYSIS

Not Feasible
A B C D E F G
Benefits 174 180 136 80 136 178 89
Disbenefits 4 60 10 8 10 2 14
Costs 100 120 90 80 70 110 50

(B-D)/C : 1.7 1.0 1.4 0.9 1.8 1.6 1.5

Cost Order: G E C A F B
E-G: [(136-89)-(10-14)] / (70-50) = 51/20 = 2.55 Keep E
C-E: [(136-136)-(10-10)] / (90-70) = 0/20 = 0 Keep E

A-E: [(174-136)-(4-10)] / (100-70) = 44/30 = 1.47 Keep A 1-55

 EVALUATING ALTERNATIVES
BASED ON B/C ANALYSIS

Not Feasible
A B C D E F G
Benefits 174 180 136 80 136 178 89
Disbenefits 4 60 10 8 10 2 14
Costs 100 120 90 80 70 110 50

(B-D)/C : 1.7 1.0 1.4 0.9 1.8 1.6 1.5

F-A: [(178-174)-(2-14)] / (110-100) = 6/10 = 0.6 Keep A

B-A: [(180-174)-(60-4)] / (120-100) = -50/20 = -2.5 Keep A

1-56
 EVALUATING ALTERNATIVES BASED
ON REPLACEMENT ANALYSIS
Replacement needed when any of the following situation occur:
•Reduced Performance
•Altered Requirements
•Obsolescence
Defender is Asset that currently installed.
Challenger is Asset that has potential replacement of the defender.
Together, Defender (D) and Challenger (C)
Constitute mutually exclusive alternatives;
Select one and reject the other.

Set up equation AWD = AWC except use RV in place of P for

the defender; then solve for RV
Replacement value (RV) is market/trade-in value of defender
that renders AWD and AWC equal to each other
1-57
 EVALUATING ALTERNATIVES BASED
ON REPLACEMENT ANALYSIS

An asset purchased 2 years ago for $40,000 is harder to maintain than

expected. It can be sold now for $12,000 or kept for a maximum of 2 more
years, in which case its operating cost will be $20,000 each year, with a
salvage value of $10,000 at the end of year two. A suitable challenger will
have an initial cost of $65,000, an annual cost of $15,000, and a salvage
value of $18,000 after its 5 year life. Determine the RV of the defender that
will render its AW equal to that of the challenger, using an interest rate of
10% per year. Recommend a course of action.

Solution: Set AWD = AWC

- RV(A/P,10%,2) - 20,000 + 10,000(A/F,10%,2) = - 65,000(A/P,10%,5) -15,000
+18,000(A/F,10%,5)
RV = $24,228 1-58
 EVALUATING ALTERNATIVES BASED
ON BREAKEVEN ANALYSIS

- If Q > QBE , choose the alternative with lower variable cost (or highe
fixed cost).
- If Q < QBE , choose the alternative with higher variable cost (or lowe
fixed cost).
- IfAW
Use Q =orQPW
BE ,analysis
choosetoany alternative.
express the total costBoth have
of each the same
alternative total cost.
as a function of
the common variable.

1-59
EVALUATING ALTERNATIVES BASED
ON BREAKEVEN ANALYSIS

1-60
 EVALUATING ALTERNATIVES BASED
ON BREAKEVEN ANALYSIS

1. Let Q represent the number of tons per year.

The annual variable cost for the operator of the auto machine = 3

The annual fixed cost for the auto machine is

Total cost for the auto machine is:

1-61
 EVALUATING ALTERNATIVES BASED
ON BREAKEVEN ANALYSIS

Therefore, the annual variable cost (VC) = 6Q

The annual fixed cost is:

Total cost is:

1-62
 EVALUATING ALTERNATIVES BASED
ON BREAKEVEN ANALYSIS

3. Equate the two cost relations and solve for Q.

4. If the output is expected to exceed 1,127 tons

per year, purchase the auto-feed machine, since it
has lower variable cost. 1-63
End

1-64