Cost Estimation & Financial Analysis

Cost Estimation & Financial Analysis

Final Examination – Spring 2021

General Information and Instructions

Test Value and Point Structure
The final exam is worth 20% of your final grade. It is graded on a 100-point scale.

General

1. Read every question carefully.
2. If you are unclear about any requirements:
a. E-mail your questions well in advance of the due date. I may post an announcement answering questions that would be of interest to the class.
3. Late submissions:
a.
Final exam due by 7:10PM local time, Monday, 03 May
. Late exams will be penalized 10pts per day, starting Monday evening.
4. Name your files in the following formats (include your name):
a. Lastname_ENMA600_FinalExam_Report.pdf
b. Lastname_ENMA600_FinalExam_Spreadsheet.xlsx
c. If you have multiple spreadsheets append a number to the naming format in b
5. Solutions should be presented in report format that shows ALL your work (including tables, graphs, calculations, etc.). You must include your spreadsheet(s). No spreadsheet, no credit.

6. Clearly state assumptions.
7. All work shall be your own – no sharing or copying.

Question #1 NPV

(3) A. Which cash flow below generates the Largest NPV?
Which cash flow below generates the Largest IRR?
What is the value of the Largest NPV?
(3) B. Which cash flow below generates the Lowest NPV?
Which cash flow below generates the Lowest IRR?
What is the value of the Lowest IRR?
(2) C. Name two forces that were at play that caused one cash flow to generate the Largest NPV and the other cash flow to generate the Lowest NPV?
(1) D. If you were the business owner, which cash flow (CF1 through CF10) would you prefer, assuming
you can replicate the investment as many times as $5,000,000 in available capital could cover?

 

MARR

Initial Investment
YR0

YR1

YR2

YR3

YR4

YR5

YR6

YR7

NPV

IRR

CF1

8%

-$1,000,000

$100,960

$460,030

$372,525

$174,955

$80,456

$79,055

$52,000

NPV1

IRR1

CF2

7%

-$500,000

$141,327

$209,404

$202,468

$280,864

$379,055

$379,055

$379,055

NPV2

IRR2

CF3

13%

-$450,000

$102,494

$126,316

$189,814

$263,310

$355,364

$355,364

$55,364

NPV3

IRR3

CF4

12%

-$250,000

$70,663

$104,702

$101,234

$140,432

$189,527

$189,527

$189,527

NPV4

IRR4

CF5

11%

-$400,000

$117,920

$174,721

$168,934

$234,346

$316,274

$316,274

$316,274

NPV5

IRR5

CF6

17%

-$250,000

$41,372

$109,404

$252,017

$280,864

$379,055

$379,055

$300,055

NPV6

IRR6

CF7

15%

-$500,000

$176,659

$261,755

$253,085

$351,081

$473,819

$473,819

$473,819

NPV7

IRR7

CF8

19%

-$100,000

$20,018

$14,472

$14,324

$15,999

$18,096

$180,096

$100,069

NPV8

IRR8

CF9

15%

-$1,000,000

$852,000

$279,055

$180,456

$174,955

$372,525

$560,030

$509,603

NPV9

IRR9

CF10

18%

-$500,000

$147,216

$218,129

$210,904

$292,567

$394,849

$394,849

$394,849

NPV10

IRR10

Question # 3 Forecasting

Question #2 NPV/MARR:

(2) A. Which of the four NPV charts below, generated by 100 iterations, represents the highest probability for a positive (profitable) outcome?

Chart A. Chart C.

Chart B. Chart D.

B. Suppose you see two charts representing 100 iterations. The charts look nearly identical… with one exception; one utilizes a MARR of 10% and the other a MARR of 25%.

(2) B-1. Generally, which of the two projects would be considered the more “risky”; the one utilizing a
MARR 10% or MARR 25%?

(2) B-2. Recent market conditions made it possible for the company to drop MARR from 25% down to 15%. If all cash flows remain the same, what do you suspect the NPV of the project will do – increase or decrease?

Question #3 Breakeven & EOQ

Part A:
The production manager obtained the following estimated costs/revenues for producing a new orbital impact wrench. Their plan is to produce that particular model wrench for only 5 years.

Annual Sales = 10,000 units/year
Sales Price = $450/unit
Variable Cost = $295/unit (no learning is involved)
Initial Investment = $1,500,000
Annual maintenance = $250,000 (to refresh tools after each 12-months of use)

MARR = 16%
MACRS-5
Ordinary Tax Rate: 29%

(2) A.1. Does the company break even during year 1, Yes or No?

(2) A.2. How many units does it take the company to make/sell to break-even the first time?

(3) A.3. How much total profit does the company make over the 5-year period?

(2EC) EC. Calculate the NPV for the project over the 5-year period
(must include depreciation of original investment & taxes)

Part B:
(2) Calculate the Economic Order Quantity (EOQ) for the following scenario:
Procurement Processing Cost: $5,000
Daily Demand: 100
Total Inventory Holding Cost: $1,460.00/year

Question #4 TVM – Compounding

You invested $100,000 today.

If you realize 7.2% interest annually over thirty (30) years, what is the Future Value of your original investment of $100,000 if your interest was compounded:

(2) A. Annually?

(2) B. Monthly?

(2) C. Daily?

(2) D. Continuously?

(2) E. Utilizing the “Rule of 72” what is the estimated value of your $100,000 investment
after 30 years? (must show all intermediate calculations to receive full credit).

Question #5 TVM – Future Value & Payments

Suppose you want to start saving on a monthly basis so that you can purchase a $60,000 car using cash 60 months from today (starting with $0 saved).

(4) A. How much do you need to save each month, earning 6.0% interest annually, in order to accumulate a Future Value (FV) of $60,000 in five years’ time?

(4) B.1 Suppose you don’t wait and purchase the $60,000 car today. If you financed the entire $60,000 (no fees or taxes added), how much would the monthly payments be if you pay 10% interest annually and pay off the $60,000 loan in 60 month’s time?
B.2. What is the total interest paid over the life of the loan?

(2) C.1. In total, how much do you save if you “saved” the money (scenario A) versus taking a
loan and making monthly payments over the five years (scenario B)?
C.2. Explain in 30 words or less, why does that occur & what forces are at play?

Note:
Both savings and loan payments are compounded monthly
Page | 2

Question #6 IRR/PP/ROI/NPV

Evaluate the following cash flow streams for Projects Alpha & Beta by calculating the IRR, PP, ROI, & NPV.

Assume a MARR of 25%

0

1

2

3

4

5

Project Alpha

-$1,500,000

$500,000

$600,000

$700,000

$800,000

$900,000

Project Beta

-$2,500,000

$1,000,000

$1,000,000

$1,000,000

$1,000,000

$1,000,000

(2) A. IRRA = IRRB =

(2) B. PPA = PPB =

(2) C. ROIA = ROIB =

(2) D. NPVA = NPVB =

(2) E. Based upon your analysis above, which of the two investments would you pursue, Project Alpha
or Project Beta? [based upon the most number of favorable outcomes above only]

EC: 2 points extra credit

Determine the MARR that will make both investments equal in NPV?
F1. What is that MARR? (four decimal places required, for example 15.1234%)

Question #7 Ratio Analysis

Answer each of the ten (10) True or False questions below (1 point each):

A. Normally, an analyst would believe that a garment company with a current ratio T or F
of 3 to 1 was in serious liquidity trouble

B. The acid test ratio is regarded primarily as a measure of a company’s long term liquidity T or F

C. Gross Profit Margin is a measure of the profit percentage per dollar of sales T or F

D. Inventory Turnover is computed by dividing cost of goods sold by total assets T or F

E. Normally a relatively low Inventory Turnover is desirable T or F

F. The Current Ratio is regarded as fundamental measurement of a company’s liquidity T or F

G. The Debt to Asset ratio measures the extent to which borrowed funds have been used to finance T or F
the acquisition of assets

H. The four classifications of ratio analysis are liquidity ratios, fixed asset ratios, profitability ratios and T or F
efficiency ratios

I. A Quick Ratio of 1.5 to 1 is normally considered satisfactory T or F

J. Liquidity ratios measure the ability of a business to meet long term obligations T or F

Question #8 Risk / Risk Premiums / Cost of Capital

Answer each of the four questions below:

(2) A. Risk Premium:
· An investor requires a 16% return
· An investment has a 25% chance of failure
Determine the Risk Premium required to ensure the investor achieves their 16% return

(3) B. Cost of Capital:
· A company has $2,000,000 in debt
· The same company has $5,000,000 in equity
· Their cost of debt is 10%
· Their cost of equity is 20%
What is the company’s cost of capital? (at least two decimal places)

(3) C. Risk:
Which of the following indicates a desirable outcome?

a. IRR0
c. NPV<0 (2) D. Sunk/Opportunity Cost: A cost that should be included in future investment decisions is a ________________ Cost Choose one: Sunk or Opportunity Question #9 Monte Carlo Simulation The CFO for a regional manufacturer has been utilizing the attached Monte Carlo simulation model (see excel file titled “Final_Q9”, green tab Q#9 Monte Carlo and has been happy with its performance over the last couple years. Key performance parameters are as follows: Production Growth Rate: 15% Unit Sale Price: $1,100/unit Raw Material & Labor Cost: $800.00/unit MARR 18% She believes the Monte Carlo forecasted NPVs are stable as designed (see blue tab titled "#9 NPV Original Output"). The chart shows a high probability for profits (~ 75%), and for strong profits, greater than $1,000,000 (~ 40%). Recent economic events have caused the CFO to revise two key parameters. Revise Base Production Growth Rate down to 13% (from 15%) Revise MARR up to 20% (from 18%) (2) A. In 25 words or less each, describe the individual impacts upon NPV you expect to see as a result of: 1. Going to a Base Production Growth Rate of 13% (from 15%) 2. Adjusting MARR to 20% (from 18%) (2) B. In 25 words or less each, based upon the changes made in Part A, what type of market changes is the CEO likely expecting? Specifically address why would the CFO reduce Production Growth; be VERY specific (you must discuss at least TWO (2) scenarios the CFO could be facing). (6) C. Implement the adjustments directed by the CEO and rerun the Monte Carlo simulations utilizing the Yellow tab titled "#9 Revised NPV Output". Update the chart with the new, sorted values. Compare and contrast the two NPV charts, before and after the adjustments (must show both charts side by side in your report). 1. In 50 words or less, provide a summary that addresses at least THREE (3) key observations. (2) Extra Credit: As the analyst brought onboard, you recognize that market conditions provide an environment where “raw materials & labor cost” may be economically sourced, more often (+10% more often), than previously forecasted. Which two cells would you have to adjust in the model to reflect this change? Note: I am looking for you to cite two particular cells in your “Uncertainty Tables” in Tab titled “Q9 Monte Carlo” that will need to be revised (i.e. C45, Exx, etc.). Question #10 Profile of the Economy, Risk/Variance, & Mergers Part 1: Answer each of the ten (10) True or False questions below (1 point each): A. Unemployment went from a five-decade low of 3.5% & rose to 24.7% in 2020 T or F B. Publicly-held debt surpassed 81% of GDP T or F C. Federal Government deficit exceeded $770 billion in fiscal year 2020 T or F D. In 2020, food price inflation increased, doubling the rate from the previous year T or F E. Due to the effects of the pandemic on the economy, the FED cut target lending rates to 0 to ¼ point T or F F. Combining stocks with different phases of seasonal variance reduces portfolio variance T or F G. Return variance is the only form of risk an investor needs to be concerned about when T or F assembling a portfolio H. Systematic risk can be eliminated through diversification T or F I. Portfolio variance will never be greater than the investment with greatest variance T or F J. As the number of non-correlated stocks increase in a portfolio, overall variance will likely decrease T or F Part 2: Match the 6 types of mergers below to its description: (1 point each): Congeneric Merger Conglomeration Horizontal Merger Market Extension Merger Product Extension Merger Vertical Merger A merger between two companies that: K. have no common business areas, is known as a ____________________. L. serve the same customer base, but in different ways, is known as a ___________________. M. are in direct competition, with similar products in similar markets, is known as a ___________________. N. one company purchases one of its suppliers, is known as a _____________________. O. sell different, but related, products in the same market, is known as a __________________. P. sell the same product in different markets, is known as a ___________________.