Industrial Engineering Chapter 2 Excel Notes Solutions Following Are Some Thoughts About The Problems And Solutions

Unlock access to all the studying documents.

View Full Document

NOTES – CHAPTER 2 SOLUTIONS

FOLLOWING ARE SOME THOUGHTS ABOUT THE PROBLEMS AND SOLUTIONS IN THIS

CHAPTER THAT MAY BE OF HELP TO FACULTY AND STUDENTS.

THIS IS, PERHAPS, THE MOST CRITICAL CHAPTER IN THE BOOK IN THAT IT DEALS

WITH THE “TIME VALUE OF MONEY (TVM),” A FUNDAMENTAL CONCEPT USED

THROUGHOUT THE REST OF THE BOOK.

THERE ARE 162 BASE PROBLEMS, MANY WITH MULTIPLE PARTS, AND MULTIPLE

SOLUTIONS FOR MANY OF THOSE PARTS ARE PRESENTED IN THIS SOLUTION

MANUAL

THERE ARE FIVE DIFFERENT METHODS THAT ARE SEEN MULTIPLE TIMES IN THESE

SOLUTIONS. THEY INCLUDE USE OF (1) SIMPLE AND COMPOUND INTEREST

FORMULAS, (2) INTEREST TABLES SUCH AS THOSE IN APPENDICES A AND B, AS

WELL AS “ELECTRONIC” INTEREST TABLES (SEE ITEM 5 BELOW), (3) USE OF

MYRIAD EXCEL FUNCTIONS, (4) SEARCH PROCEDURES USING EXCEL’S “SOLVER”

AND “GOAL SEEK” TOOLS, AND (5) BRUTE FORCE TABULAR APPROACHES

THE COMPOUND INTEREST FORMULAS ARE OFTEN THE MOST ONEROUS TO USE,

GIVEN THAT THEY REPRESENT THE FUNDAMENTAL MATHEMATICS OF TVOM.

THE INTEREST TABLES REPRESENT THE MOST COMMON APPROACH USED

HERETOFORE IN “ENGINEERING ECONOMY” COURSES; THEY USUALLY VASTLY

SIMPLIFY THE MATHEMATICS INVOLVED BY INCORPORATING ONE OR MORE

COMPOUND INTEREST FORMULAS INTO A SINGLE TABULATED “FACTOR”

AVAILABLE IN APPENDICES A AND B. SINCE THE INTEREST TABLES ARE FOR ONLY

A FINITE (ALTHOUGH VERY LARGE) SET OF INTEREST RATES (i AND j) AND TIME

HORIZONS (n), THEY MAY NOT BE AVAILABLE FOR A SPECIFIC SET OF i, j, OR n

NEEDED. “ELECTRONIC” INTEREST TABLES ARE AVAILABLE, AND HAVE BEEN USED

WHEN UNAVAILABLE TABULATED VALUES OF i, j, AND/OR n HAVE BEEN NEEDED.

PLEASE SEE NOTES 13 AND 14 BELOW FOR MORE DETAIL ON THE USE OF

“ELECTRONIC” INTEREST TABLES.

PROBLEMS 26, 49, 80, 118, AND 150 ASK FOR THE DEVELOPMENT OF CERTAIN

INTEREST FACTORS SUCH AS ARE PRESENTED IN THE INTEREST TABLES. THESE

SERVE TO CONVINCE THE STUDENT THAT THERE IS NO “MAGIC” INVOLVED IN THE

INTEREST TABLES. IN ADDITION, THE FORMULAS USED IN DEVELOPING THE

INTEREST FACTORS HAVE BEEN VERY USEFUL TO THE AUTHORS IN “COPY AND

PASTE” OPERATIONS IN THE DEVELOPMENT OF THE CHAPTER 2 SOLUTIONS.

THE EXCEL FUNCTIONS HAVE NOT TRADITIONALLY BEEN WIDELY USED IN

TEACHING “ENGINEERING ECONOMY.” THEY HAVE, HOWEVER, BEEN USED IN THE

WORLD OF FINANCE. THE EXCEL FUNCTIONS ARE WELL DESIGNED, AND PROVIDE

A VERY PRECISE TOOL FOR USE IN SOLVING A WIDE ARRAY OF PROBLEMS

INVOLVING TVM. BEWARE! FOR THE NOVICE, THESE EXCEL FUNCTIONS MAY

APPEAR CONFUSING, COMPLEX, AND NOT WORTH THE TIME TO LEARN. THEY ARE,

HOWEVER, QUITE STRAIGHTFORWARD AND CONSISTENT, ONCE ONE GETS PAST

THE INITIAL TRIALS OF LEARNING SOMETHING NEW. THEY ARE LIKELY TO BE

YOUR FAVORITE METHOD OF PROBLEM SOLUTION IF YOU USE THEM TO SOLVE OR

CHECK YOUR SOLUTIONS IN THIS CHAPTER.

SOMETIMES, YOU WILL NEED TO SOLVE FOR A MONETARY VALUE, AN INTEREST

RATE, A TIME INTERVAL, A GRADIENT, OR WHATEVER, NEEDED TO MATCH OTHER

PARAMETERS OF A PROBLEM (E.G., WHAT INTEREST RATE MAKES AN INVESTMENT

OF $100 WORTH $150 IN THREE YEARS). WHILE IT IS OFTEN POSSIBLE AND

CONVENIENT TO USE ANALYTICAL APPROACHES (E.G., ALGEBRA), SOMETIMES

THESE SOLUTIONS ARE DIFFICULT AND CUMBERSOME (ANALYTICALLY

INTRACTABLE OR NEARLY SO). SEARCH PROCEDURES CAN BE VERY HANDY IN

THESE CASES. EXCEL PROVIDES “SOLVER” AND “GOAL SEEK,” BOTH OF WHICH

CAREFUL! ACTUALLY, MOST PROBLEMS CAN BE SOLVED USING THE BRUTE

FORCE APPROACH – IN FACT, IT IS QUITE COMMONLY USED IN INDUSTRY! FOR

NOW, WHILE LEARNING THIS MATERIAL, USE THE BRUTE FORCE APPROACH ONLY

AS A LAST RESORT OR AS A PROBLEM CHECK AS HAS BEEN DONE IN THESE

SOLUTIONS. TO USE THE BRUTE FORCE APPROACH ALONE IN CHAPTER 2 IS TO

NOT LEARN THE OTHER APPROACHES.

IN THIS CHAPTER, WITH MULTIPLE APPROACHES USED IN SOLVING MANY

PROBLEMS, THERE ARE OFTEN SLIGHTLY DIFFERENT SOLUTIONS ACHIEVED.

THESE ARE NOT WRONG! USUALLY, IF NOT ALWAYS, THE INTEREST FORMULA

APPROACH AND THE EXCEL FUNCTION APPROACH WILL BE PRECISELY THE SAME

BECAUSE THERE IS VIRTUALLY NO ROUND OFF ERROR WITHIN THE COMPUTER.

THE INTEREST TABLE APPROACH WILL BE SLIGHTLY DIFFERENT, DUE TO THE

PRESENTATION OF INTEREST FACTORS TO FIVE PLACES AFTER THE DECIMAL IN

MOST CASES. MANY TABLES USE ONLY FOUR PLACES. EVEN WITH FIVE PLACES,

THERE WILL BE SOME MINOR DISCREPANCIES WHEN COMPARED TO THE EVEN

MORE PRECISE APPROACHES. NOTE THAT SUCH DIFFERENCES ARE ALMOST

ALWAYS INCONSEQUENTIAL BECAUSE THE ESTIMATES REQUIRED IN ECONOMIC

EVALUATIONS ARE USUALLY ONLY APPROXIMATIONS ANYWAY.

EVEN THOUGH MANY PROBLEMS HAVE MULTIPLE PARTS, IT IS NOT INTENDED

THAT ALL PARTS OF A PROBLEM NECESSARILY BE ASSIGNED AND WORKED. IN

MANY CASES, THE LEARNING THAT TAKES PLACE CAN BE ACHIEVED BY WORKING

ONLY ONE OR TWO PARTS. SEE, FOR EXAMPLE, PROBLEM 79.

P= $10,000.00

n= 24 MONTHS

r= 6% NOMINAL COMPOUNDED MONTHLY

i= 0.500000000% PER MONTH

INTEREST TABLE APPROACH:

A= =$10,000*(A|P 6%/12,24)

=$10,000*(A|P 0.5%,24)

=10000*0.04432

=$10,000*(A|P 0.5%,24)

=10000*0.044320610

$443.21

WHEN A SOLUTION MAKES USE OF BOTH INTEREST TABLES AND EXCEL

FUNCTIONS, THE ANSWERS WILL OFTEN BE SOMEWHAT DIFFERENT DUE TO

ROUNDOFF ERROR IN THE BOOK TABLES. IN GENERAL, THE EXCEL FUNCTIONS

CARRY MANY PLACES WITHIN THE COMPUTER, THEREBY MAKING THEM “EXACT”

(REMEMBER, SINCE MOST THINGS IN ENGINEERING ECONOMIC ANALYSIS ARE

ESTIMATED, IT IS SOMEWHAT LUDICROUS TO REFER TO MANY SOLUTIONS AS

“EXACT”). SOMEWHERE IN BETWEEN THE PRECISION OF EXCEL FUNCTIONS AND

THE BOOK’S TABLES ARE “ELECTRONIC” INTEREST TABLES. A SEPARATE

WORKSHEET WITH ELECTRONIC INTEREST TABLES IS AVAILABLE ON THE BOOK

WEB SITE. USING THAT WORKSHEET, THE USER MAY SELECT THE NUMBER OF

PLACES FOLLOWING THE DECIMAL TO USE. HERE IS THE PROTOCOL FOLLOWED

IN THE SOLUTIONS TO CHAPTER 2:

IF THE VALUES OF i, j, AND n NEEDED ARE AVAILABLE IN THE BOOK’S

TABLES, THEY ARE USED HEREIN TO EMULATE THE PROCEDURE USED

BY SOMEONE USING THEM, THIS IS THE TRADITIONAL APPROACH TO

ENGINEERING ECONOMIC ANALYSIS. NOTE THAT MOST FACTORS ARE

PRESENTED TO 5 PLACES AFTER THE DECIMAL.

IF THE VALUES OF i, j, AND n NEEDED ARE NOT AVAILABLE IN THE BOOK’S

TABLES, THE ELECTRONIC TABLES ARE USED. IN THIS CASE, THE

NUMBER OF PLACES AFTER THE DECIMAL MUST BE DECIDED UPON FOR

BOTH THE INPUT VALUES OF i AND/OR j AND ALSO FOR THE FACTOR

VALUE ITSELF. HEREIN, 9 PLACES FOLLOWING THE DECIMAL ARE USED

IN ALL CASES. THE REASON FOR 9 PLACES IS THAT THIS NUMBER IS

SIMILAR TO THE NUMBER OF PLACES AVAILABLE ON MANY SCIENTIFIC

AND FINANCIAL CALCULATORS.

AS A FIRST EXAMPLE, LET’S DETERMINE THE MONTHLY PAYMENT THAT

IS EQUIVALENT TO A PRESENT VALUE OF $10,000 IF INTEREST IS 6%

NOMINAL, COMPOUNDED MONTHLY OVER A PERIOD OF 24 MONTHS.

P= $10,000.00

n= 24 MONTHS

r= 7% NOMINAL COMPOUNDED MONTHLY

i= 0.583333333% PER MONTH

INTEREST TABLE APPROACH:

A= =$10,000*(A|P 7%/12,24)

OOPS! THERE IS NO BOOK INTEREST TABLE FOR 7%/12 = 0.583333333%

ELECTRONIC INTEREST TABLE APPROACH:

A= =$10,000*(A|P 7%/12,24)

=$10,000*(A|P 0.583333333%,24)

=10000*0.044772579

$447.73

EXCEL PMT FUNCTION APPROACH:

A= =PMT(7%/12,24,-10000)

$447.73

AS A SECOND EXAMPLE, LET’S DETERMINE THE MONTHLY PAYMENT

THAT IS EQUIVALENT TO A PRESENT VALUE OF $10,000 IF INTEREST IS

7% NOMINAL, COMPOUNDED MONTHLY OVER A PERIOD OF 24 MONTHS.

NOT LINEAR, AND INTERPOLATION OFTEN INVITES UNNECESSARY

ERRORS. THE TWO APPROACHES USED DID ACHIEVE THE SAME

AFTER THE DECIMAL IN THE COMPUTER THAN THE 9 PLACES USED HERE

WITH THE ELECTRONIC INTEREST TABLE APPROACH.

NOTE THAT THE INTEREST TABLE APPROACH SOLUTION IS SLIGHTLY

DIFFERENT ($0.01) FROM THE OTHER TWO APPROACHES. WHILE THIS IS

INCONSEQUENTIAL, THE DISCREPANCY MAY BE MUCH LARGER, IN

ABSOLUTE TERMS, DEPENDING UPON THE MONETARY FIGURES

INVOLVED, THE NUMBER OF FACTORS INVOLVED, THE SPECIFIC

INTEREST RATES INVOLVED, AND SO ON.

FE PROBLEM 2.1

b. 19 YEARS

rationale: (F|P 6%,n) = 3.00000; n EQUALS APPROXIMATELY 19 YEARS

FE PROBLEM 2.2

b. F = P(1+i)^n

rationale: SEE EQUATION 2.5

FE PROBLEM 2.3

b. $165

rationale: $400 = X(P|A 15%,2) + $200(P|F 15%,3); X = $165

FE PROBLEM 2.4

d. $10,043

rationale: $5,000(1+0.065)^15 – $1,500(1+0.065)^10 = $10,043

FE PROBLEM 2.5

c. $9,542

rationale: $3,000(1+0.075)^16 = $9,542

FE PROBLEM 2.6

b. $15,692

rationale: $5,000(F|P 10%,12) = $15,692

FE PROBLEM 2.7

a. GRADIENT SERIES

rationale: DEFINITION OF GRADIENT SERIES

FE PROBLEM 2.8

c. $4,843,380

rationale: $3,000,000 + $300,000(P|A 10%,10) = $4,843,380

FE PROBLEM 2.9

c. 26.82%

rationale: (1+0.24/12)^12 – 1 = 0.2682; 26.82%

FE PROBLEM 2.10

a. TRUE

rationale: ORIGINAL PMT = $5,000(A|P i%,n); IF THE $5,000 DOUBLES, THE

FACTOR DOESN’T CHANGE SO PMT DOUBLES

FE PROBLEM 2.11

c. $3,940

rationale: (20)$2,500 = (X)(F|A 1%,12); X = $3,940

FE PROBLEM 2.12

b. IF THE NUMBER OF COMPOUNDING PERIODS PER YEAR IS ONE

rationale: EFFECTIVE = (1 + NOMINAL/1)^1 – 1 = NOMINAL

FE PROBLEM 2.13

b. 15.5 YEARS

rationale: $100,000 = $5,000(P|A 3%,n); n EQUALS APPROXIMATELY 31

SEMIANNUAL PERIODS; 15.5 YEARS

A B

PROBLEM 2.1

THE OFFER IS NOT FAIR AND EQUITABLE BECAUSE THE BUYER IS VIOLATING THE DCF

RULES. SPECIFICALLY, THE BUYER IS ADDING AND SUBTRACTING MONEY AT

DIFFERENT POINTS IN TIME.

THE WORTH OF THE DEAL RIGHT NOW IS $200 + $100/(1+0.05) = $295.24

THE WORTH OF THE DEAL 1 YEAR FROM NOW IS $200*(1+0.05) + $100 = $310.00

OR $295.24*(1+0.05) = $310.00

THE WORTH OF THE BUYER’S OFFER RIGHT NOW IS $300/(1+0.05) = $285.71

THE WORTH OF THE BUYER’S OFFER 1 YEAR FROM NOW IS $300.00

A B

PROBLEM 2.2

a FAIR AMOUNT NOW = $500 + $500/(1+0.06) = $971.70

b FAIR AMOUNT 1 YEAR FROM NOW = $500*(1+0.06) + $500 = $1030.00

OR $971.70*(1+0.06) = $1030.00

A B

PROBLEM 2.3

DCF STANDS FOR “DISCOUNTED CASH FLOW.”

THE MOVEMENT OF MONEY FORWARD OR BACKWARD IN TIME

AN EXPLICIT METHOD FOR CONSIDERING THE TIME VALUE OF MONEY