Financial Management & Markets

Instructions:

• The Case Study 2 (CS2) is due by 11:00 pm CST, November 03, 2020.

• To complete the CS2, you must download the Excel file: Case_Study_2_Format.

o Module 2 > Module 2 Assignments > Case Study 2

• Total scores for the CS2 are 30 points. And, you must submit it through Blackboard to earn credits.

• Any late submission will not be accepted.

• Please read FAQs on the last page of the instruction before you post a thread in the Blackboard forum.

   Part 1: Read the following information

“JIN Corporation” is about to make an investment. Among many potential projects, there is one very profitable. Before the company makes decision, you are asked to evaluate the project with the following information.

1. The initial investment is $2,150,000 which must be made at the beginning of the year (Year 0). • Year 0 is the same as the beginning of “Year 1.” Please take a look at FAQ section.

2. The project will last for four years (Year 1, 2, 3, and 4).

3. The project will generate the free cash flows (FCF) of $650,000 annually for four years.

• We assume that the FCF will be generated at the end of each year. 4. The cost of capital (a.k.a. discount rate) is 9% per annum.

• The project must be financed. So, the company will raise $2,150,000 by borrowing from investors.

• Therefore, you need to make interest payments to the investors, which is 9% of the amount of debt

($2,150,000).

• $2,150,000, which is the debt, is the capital that your company can use for the investment and 9% is

the interest cost. This is why we can call the discount rate as the cost of capital. We will talk more

about the cost of capital in Chapter 11 and 13.

• All cash flows will occur at the end of the year except the initial investment ($2,150,000).

  P1-Q1: Using the above information, find the NPV (Net Present Value).

• Requirement (4 points):

1. Use the Excel template (“Case_Study_2_Format” uploaded on Blackboard) => Spreadsheet: Part 1

2. Fill the boxes (yellow boxes) by using the following formula to compute the PV and NPV.

• You can complete P1-Q1 by using Equation

Present value at Year 0 (or Beginning of Year1) of CF (which is the investment) in Year 0: PV = 𝐹𝐶𝐹0 (1+𝑟)0

  Present value at Year 0 (or Beginning of Year1) of CF in Year 1: PV = Present value at Year 0 (or Beginning of Year1) of CF in Year 2: PV = Present value at Year 0 (or Beginning of Year1) of CF in Year 3: PV = Present value at Year 0 (or Beginning of Year1) of CF in Year 4: PV =

𝐹𝐶𝐹1

(1+𝑟)1 𝐹𝐶𝐹2

(1+𝑟)2 𝐹𝐶𝐹3

(1+𝑟)3 𝐹𝐶𝐹4

(1+𝑟)4

− 𝐼𝑛𝑖𝑡𝑖𝑎𝑙 𝐼𝑛𝑣𝑒𝑠𝑡𝑚𝑒𝑛𝑡0

𝐹𝐶𝐹 𝐹𝐶𝐹 𝐹𝐶𝐹 NPV = 1 + 2 + 3

+ ⋯ +

𝐹𝐶𝐹 𝑛

(1+𝑟)𝑛

    (1+𝑟)1 (1+𝑟)2 (1+𝑟)3

, where r is the discount rate (or the cost of capital) and 1, 2, 3, ..., n are the year.

1

 

FIN 601: Financial Management and Markets Instructor: Dr. Jinsuk Yang Case Study #2

• You can complete P1-Q1 by using Excel Function

The formula for the present value is: “=PV(rate, nper, pmt, [fv], [type])”

, where rate if “The interest rate per period (or discount rate or cost of capital)”, nper is the total number of payment periods, pmt is the payment made (or paid to you) each period, fv is (optional) a cash balance you want to attain after the last payment is made (if omitted, assume to be zero), and type is also optional when payments are due: 0 equals end of period and 1 equal beginning of period (Default setting is 0).

For our case study,

• rate is 9% or 0.09

• nper is

o 1 if we compute the present value at the beginning of year 1 of the FCF generated at the end of year 1

o 2 if we compute the present value at the beginning of year 1 of the FCF generated at the end of year 2

o 3 if we compute the present value at the beginning of year 1 of the FCF generated at the end of year 3

o 4 if we compute the present value at the beginning of year 1 of the FCF generated at the end of year 4

• pmt is 0

• fv is 650,000 (which we have at the end of each year)

• type is 0 because all cash flows (FCFs) will occur at the end of year

• Important:

▪ If you use the Excel function, it will give you negative values (as PVs). On the other hand, if you use the equation provided above, your PV is positive. The story behind the negative values from Excel function is that you need to use (or invest) your money (which is PV) to receive the FV. Using money is “cash outflow” (which is negative values) and receiving money is “cash inflow” (which is positive value). However, for the CS2, we stick with “time value of money” concept, which is simply “what is the today’s value of a future amount?” rather than “how much do we need to invest today to receive a future amount?”. So, if you use the Excel function to find PV, please add “ – (negative) ” sign in front of “PV.”

▪ Briefly, “= -PV(rate, nper, pmt, [fv], [type])”

If you are not familiar with Excel program, please visit the following sites:

https://www.excelfunctions.net/basic-excel.html https://www.youtube.com/watch?v=rwbho0CgEAE https://www.youtube.com/playlist?list=PLiMtZPx84E9mfoqEcHOXjgdrwEpmeD4xG

P1-Q2: Do we accept the project based on your NPV?

• Requirements (1 point):

1. Use the Excel template (“Case_Study_2_Format” uploaded on Blackboard) => Spreadsheet: Part 1

2. Answer the above question.

       2

 

FIN 601: Financial Management and Markets Instructor: Dr. Jinsuk Yang Case Study #2

 Part 2: Read the following information

In Part 1, you computed the NPV to decide if the project is acceptable or not. Now, instead of looking at the NPV, you want to find out how much of FCF ($650,000 per year) is paid to the capital suppliers. To do so, it is recommended to see the distribution of free cash flows.

Please use the following information to complete the Part2 – Q1:

• Year 1: The capital supplied by investors is considered as a long-term debt. You need to make interest payment to the investors or creditors. The company should use the FCF to arrange this payment. After making the interest payment, the net free cash flow (FCF after interest payment) will be used to pay off the principal, which is $2,150,000. (This is actually depending on the company’s decision. But, as far as you know, your company wants to pay off all debts ASAP using FCF.) Excess wealth is the amount of cash on hand after paying everything.

• Year 2: The principal will be changed because your company paid a certain amount of money in year 1. This change will affect the interest payment that your company must make in year 2. Again, the change in the interest payment in year 2 will affect FCF after the interest payment and payment to principal.

• Year 3: Similar to year 2.

• Year 4: Similar to year 3 except for one thing. After making the interest payment, the company

will have a large amount of cash on hand, which is more than enough to pay off the final principal. Please use the following information to complete the Part2 – Q2:

• At the beginning, it is assumed that the project will generate $650,000 annually for four years. What if the project fails to do so? Now, things are changed, and the project is not expected to generate $650,000 annually. Based on your own research and analysis, the project is expected to provide the following cash flows stream. You are required to find out the distribution of free cash flows generated by the project as you did in P2-Q1. Use the following information to complete P2- Q2. (Note: Any unpaid interest obligation must be fully paid in the next period with the interest on the amount of unpaid interest.)

o Year 1: $100,000 o Year 2: $300,000 o Year 3: $1,000,000 o Year 4: $1,200,000

Please use the following information to complete the Part2 – Q3:

• Now, you are asked not to worry about the distribution of free cash flows.

• Find NPV using the annual cash flow information provided in Part2 – Q2:

o Year 1: $100,000 o Year 2: $300,000 o Year 3: $1,000,000 o Year 4: $1,200,000

    3

 

FIN 601: Financial Management and Markets Instructor: Dr. Jinsuk Yang Case Study #2 P2-Q1: Complete the table (fill the yellow boxes P2-Q1) in the spreadsheet (Part 2) in the workbook of “Case Study 2”

posted on the Blackboard. And, answer the following question: Do we accept the project or not?

• Requirements (5 point):

1. Use the Excel template (“Case_Study_2_Format” uploaded on Blackboard) => Spreadsheet: Part 2

2. Fill the boxes (find the excess wealth)

3. Once you find the excess wealth, compute the PV of the excess wealth by using:

⚫ PV formula: PV = 𝐸𝑥𝑐𝑒𝑠𝑠 𝑤𝑒𝑎𝑙𝑡h𝑛 (1+𝑟)𝑛

⚫ The interest rate “ r ” in the formula is the same as “Cost of capital”, which is 9%. ⚫ “ n ” is the year when you have the final excess wealth.

◼ For example, our case study use “4 years” as the life of project. And, at the end of each year we have the excess wealth that carries over to the next year (most of time, it would be zero because we use all our excess wealth to pay off the principal at the end of each year). And, at the end of 4th year, we have our final excess wealth (which can be positive or negative). So, “n” is 4.

4. Answer the following question: Do we accept the project or not?

P2-Q2: Complete the table (fill the yellow boxes P2-Q2) in the spreadsheet (Part 2) in the workbook of “Case Study 2” posted on the Blackboard. And, answer the following question: Do we accept the project or not?

• Requirements (5 point):

1. Use the Excel template (“Case_Study_2_Format” uploaded on Blackboard) => Spreadsheet: Part 2

2. Fill the boxes (find the excess wealth)

You can use the equation given in the P2-Q1.

3. Answer the following question: Do we accept the project or not?

P2-Q3: Complete the table (fill the yellow boxes P2-Q3) in the spreadsheet (Part 2) in the workbook of “Case Study 2” posted on the Blackboard. And, answer the following question: Do we accept the project or not?

• Requirements (5 point):

1. Use the Excel template (“Case_Study_2_Format” uploaded on Blackboard) => Spreadsheet: Part 2

2. Fill the boxes (find the excess wealth)

You can use the equation given in the P1-Q1.

3. Answer the following question: Do we accept the project or not?

       4

 

FIN 601: Financial Management and Markets Instructor: Dr. Jinsuk Yang Case Study #2

 Part 3: Read the following information

You are still dealing with the same project. And, your supervisor asked you to stick with the free cash flow stream given at the beginning of the analysis: $650,000 annually. You are asked to find at which rate the NPV is equal to zero. In addition, you are asked to analyze the impact of the change in the cost of capital.

Please use the following information to complete the Part3 – Q1:

• To analyze the impact, you are using 9 different discount rates: 4%, 5%, 6%, 7%, 8%, 9%, 10%, 11%, and 12%.

• After analyzing, you are required to find “IRR.”

• In addition, you are asked to confirm if the NPV is equal to zero with the discount rate, which is

equal to the IRR.

   P3-Q1: Complete the tables (fill the yellow boxes P3-Q1) in the spreadsheet (P3). And, complete the graph (once you fill up all the empty boxes, the graph will be drawn automatically).

• Requirements (10 point):

1. Use the Excel template (“Case_Study_2_Format” uploaded on Blackboard) => Spreadsheet: Part 3

2. Fill the boxes (find the NPV with different discount rates)

You can use the equation given in the P1-Q1.

3. Fill the boxes (find the IRR and NPW when the discount rate is equal to IRR).

You can use the Excel function or the calculator.

Excel function to find IRR is:

“=IRR(values, [guess])”

, where values are initial investment and all FCF for 4 years. You do not need to input your guess. Once you find “IRR”, please find the NPV using the IRR. If you don’t know how to use the Excel function for IRR, please visit the following site and watch the video.

https://www.youtube.com/watch?v=Ug74NbL81CE

If you use the excel function, you must modify one thing: the sign of “initial investment”. The sign must be “negative” so that the excel function recognizes it as the investment (or spending).

Submission Guidelines:

o Your Case Study 2 must be in Excel file format.

a. You must use for everything except the “IRR” in P3-Q1.

b. Your file name must be “ ”

c. No space between words (don’t forget put the hyphen “ – ” between words)

d. You must save the file as “Save as type: Excel Workbook (.xlsx)”, not “CSV” or any other

formats.

o Failure to comply with the “submission guidelines” will result in penalty: - 2 points.

    two decimal points

 CS2-first name-last name

 5

 

FIN 601: Financial Management and Markets Instructor: Dr. Jinsuk Yang Case Study #2 FAQ:

• What is “Year 0”?

If we want to earn returns, we must invest $ in a certain project (or investment) at the beginning so that we can receive returns at the end of each term. The beginning of the first term can be considered as “Year 0.” Please take a look at the image below:

Here, we assume we invest at the beginning of 2018. And, we receive returns at the end of each year for next 5 years.

• Are we allowed to post answers in the “Module Forum Q&A”? Posting answers is prohibited and is considered as cheating.

• What should the graph look like?

If you compute all the NPV with the different discount rates, the graph will have an intersection between

8% and 9%.

• Any other questions:

Please use the “Module 2 Forum Q&A”