FIN 601: Financial Management and Markets Instructor: Dr. Jinsuk Yang Project Report Part 2
Instruction for Project Part 2 Report: Total of 60 points
The Final Report is due by 11:00 pm CST, December 07, 2020.
You must submit “Project Part 2” through Blackboard to earn credits.
Any late submission will not be accepted.
Use your own words. If you obtain some information from other websites, interpret those with your own words! Any plagiarism will not be tolerated (you will be received zero credits for your project and, as a result, automatically fail the class. Read the following article http://www.usi.edu/media/5599778/academic-integrity-policies-and-procedures-fall-2016.pdf )
Download all the necessary files from Blackboard
To complete the Project Part 2, please download the following files
•Project Pt2-CoD.pdf
•Project Pt2-Qs.xlsx
•Project Pt2 – Inst.pdf
There are 11 spreadsheets in Project Pt2-Qs.xlsx.
•Income Stmt, Balance Sheet, Cash Flow Stmt, and Supplement:
•They contain the given information used to complete Project Part 2.
•Other spreadsheets:
•You must fill up yellow cells in several tables.
Cost of Debts Spreadsheet (10 points)
We already studied and learned how to compute the weighted average cost of debt from Module 4 (Module_4_CoD_Example).
It is your responsibility to watch and follow lecture videos in all the modules. If you do not know how to complete the “Cost of debt” part of the Project Pt 2, please watch again the lecture videos from Module 4. The videos include the example and explain how to fill up boxes in great detail. The only thing different from Module_4 and Project Part 2 is that
we are using a different company with a different period.
Please read comments in cells.
IMPORTANT!
oUsing the Excel file (file name: Project Pt 2-Qs) posted on Blackboard, you can find the cost of debts spreadsheet. The data related to Costco’s debts is provided; therefore, you should use the data posted (file name: Project pt2-CoD), not Intel
Corp. For the “Cost of Debt” in WACC, you use “YTM based on MV”, the last cell.
oPlease read comments in cells.
oMake sure that you are correctly converting “$ in millions” into “$”
▪ In the files, I demonstrate how to do so.
Cost of Equity Spreadsheet (15 points)
We already studied and learned how to compute the cost of equity from the Module 4 (Module_4_CoE_Example). All the equations or Excel functions to find average,
1 | P a g e
FIN 601: Financial Management and Markets Instructor: Dr. Jinsuk Yang Project Report Part 2
variance, standard deviation, covariance, beta, and the required rate of returns are provided in “Module_4_CoE_Example_Instruction”. The lecture videos also demonstrate how to use the Excel “Regression” function and to compute “Required return” by using the CAPM model.
It is your responsibility to watch and follow lecture videos in all the modules. If you do not know how to complete the “Cost of Equity” part of the Project Pt 2, please watch again the lecture videos from Module 4. The videos include the example and explain how to fill up boxes in great detail. The only thing different from Module_4 and Project Part 2 is
that we are using a different company with a different period.
Use the following information to compute the “Required rate of return”: Risk free rate of return: 0.02 & Market risk premium: 0.035
CAPM: Required return = Risk free rate of return + (beta)*(Market risk premium)
IMPORTANT!
oPlease express all values in cells as raw numbers with 6 decimal points, not in %.
(The cell format is already set up “6 decimal points” except “Variance” which has 8 decimal points. Please don’t change the format.)
oPlease read comments in cells.
WACC Spreadsheet (5 points)
We already studied and learned how to compute the “WACC” from Module 4 (Module_4_WACC_Example). Please watch the lecture videos from Module 4. The videos include the example and explain how to fill up boxes in great detail. The only thing different from Module_4 and Project Part 2 is that we are using a different company
with a different period.
It is your responsibility to watch and follow lecture videos in all the modules. If you do not know how to complete the “WACC” part of the Project Pt 2, please watch again the
lecture videos from Module 4.
Answers to some questions can be found in other spreadsheets in the same Excel workbook.
1.Find the capital raised by issuing equity: Q3
a.In the workbook (Project Pt2-Qs), the data related to the market price per share and the shares outstanding are provided (please look at the “Supplement” spreadsheet).
b.Compute the market capitalization (a.k.a. “Total market value of equity”)
i.The market capitalization is nothing but the product of “shares outstanding” and “market price per share.”
ii.The amount of market capitalization is the capital raised by issuing equity.
2.Compute the required rate of return on the stock: Q4
a.The required rate of return on the stock is what we called “Cost of Equity”
b.We will use the CAPM to compute it. Use the following information:
2 | P a g e
FIN 601: Financial Management and Markets Instructor: Dr. Jinsuk Yang Project Report Part 2
Risk free rate of return: 0.02 & Market risk premium: 0.035
CAPM: Required return = Risk free rate of return + (beta)*(Market risk premium)
c.You already have this information in your “Cost of Equity” spreadsheet.
3.Find the capital (in the market value) raised by issuing bonds: Q5
a.This is also called the “Total market value of bonds.”
b.You already have this information in your “Cost of Debt” spreadsheet.
4.Compute the cost of debt: Q6
a.It is already computed in your spreadsheet “Cost of Debt.”
b.This is also known as the “Weighted average cost of debt.”
It is because, in the “Cost of debt” spreadsheet, we use the “% of Total MV” and multiply this value to “YTM (of the last sale)” when computing each bond type and we sum up all the “YTM based on MV” when computing the cost of debt.
5.Compute the total market value of total capital: Q7
a.This is nothing but the sum of answers to Q3 and Q5.
6.Find the weight (Read Chapter 13: Section 13.8): Q8 and Q9
a.To compute WACC, you must find out the weight of each capital raised by the company.
b.For the weight of capital raised by issuing equity:
=
=+ c. For the weight of capital raised by issuing bonds:
=
=
+
7.Find the WACC: Q10
a. To compute WACC after the corporation tax, please use the following formula:
= ( ℎ × ) + ( ℎ ×× (1 − ))
,where “Cost of debts (CoD)” is the weighted average cost of debts based on the market value and “cost of equity (CoE)” is the required rate of return on the stock computed by using CAPM.
3 | P a g e
FIN 601: Financial Management and Markets Instructor: Dr. Jinsuk Yang Project Report Part 2
To be accurate, if a company issues “Preferred stock”, we must include an additional cost of equity (only related to the preferred stock) in the equation.
= ( ℎ × ) + ( ℎ × )
+( ℎ ×× (1 − ))
,where “CoP” stands for the cost of preferred stock, implying the “required rate of return on the preferred stock” and ℎ is the weight of capital raised by issuing the preferred stock. However, we assume that Costco Company doesn’t issue the preferred stock. Please read the textbook for the WACC (including the preferred stock).
b.We use “0.22267” as the tax rate. And, it is from the “FCFE & FCFF” spreadsheet.
c.To understand how to find this rate, please look at the formula in the “Formula” column.
FCFE & FCFF Spreadsheet
Use the formula given in the spreadsheet (Formula) and fill the yellow cells in the Table.
•Tips:
FCFE: There are two values you must find. And, these values should be the same. If you have different values, you might want to read the formula and watch the lecture videos again. And, please double-check your computation.
FCFF: There are four values you must find. And, these values should be the same. If you have different values, you might want to read the formula and watch the lecture videos again. And, please double-check your computation.
Forecast FCFEs & FCFFs Spreadsheet (10 points)
We assume that, from 2020 to 2024 (over the next 5 years), your company will grow at 0.031 (3.1%). The company will grow forever at 0.015 (1.5%) from 2024 (2024 to 2025, 2025 to 2026, 2026 to 2027, and forever…).
•Table 1:
Fill the empty boxes. And, please read a comment posted in each cell.
•Table 2:
From 2020 to 2024, we will use the growth rate of 0.031 for the FCFE and FCFF.
For example:
FCFE (or FCFF) in the year 2019 will grow by 0.031 to the year 2020. So, the FCFE (or FCFF) in the year 2020 can be computed by multiply FCFE (or FCFF) in the year
2019 to “1 + growth rate (0.031)”.
FCFE (or FCFF) in the year 2020 will grow by 0.031 to the year 2021. So, the FCFE (or FCFF) in the year 2021 can be computed by multiply FCFE (or FCFF) in the year
2020 to “1 + growth rate (0.031)”.
From 2025, we will use the growth rate of 0.015 for the FCFE and FCFF.
For example:
4 | P a g e
FIN 601: Financial Management and Markets Instructor: Dr. Jinsuk Yang Project Report Part 2
FCFE (or FCFF) in the year 2024 will grow by 0.015 to the year 2025. So, the FCFE (or FCFF) in the year 2025 can be computed by multiply FCFE (or FCFF) in the year 2024 to “1 + growth rate (0.015)”.
FCFE (or FCFF) in the year 2025 will grow by 0.015 to the year 2026. So, the FCFE (or FCFF) in the year 2025 can be computed by multiply FCFE (or FCFF) in the year 2025 to “1 + growth rate (0.015)”.
Why do we use two different growth rates?
It will be great if a company can maintain the growth rate (Average of geometric mean growth rate over the past 4 years). However, companies have competitors and lose their customers (resulting in less profits) over the long run. So, the growth rate becomes lower.
Read a comment posted in each cell.
•Table 3:
Compute the “Terminal Value” by using the constant growth model. Please read
“Chapter 9 => Case 3 (Differential Growth)”. In Example 9.3, the price at the end of Year 5 (capturing all the upcoming dividends that grow at g2) is what we consider as
“Terminal Value.”
For the terminal value of FCFEs, the discount rate is “Cost of Equity”
2025, 2026, 2027, … …
=”Estimated FCFE in 2024″ × (1 + ℎ )
− ℎ
” ”
=− ℎ
*Since the FCFE in the year 2024 will grow by 0.015 to the year 2025, please use 1.5% as the growth rate in the equation.
For the terminal value of FCFFs, the discount rate is “WACC”
2025, 2026, 2027, … …
= “Estimated FCFF in 2024″ × (1 + ℎ )
− ℎ
= ” ”
− ℎ
*Since the FCFF in the year 2024 will grow by 0.015 to the year 2025, please use 1.5% as the growth rate in the equation.
Read a comment posted in each cell.
5 | P a g e
FIN 601: Financial Management and Markets Instructor: Dr. Jinsuk Yang Project Report Part 2
Discounted Free Cash Flows Spreadsheet (10 points)
•From the previous step, you must have the future FCFEs and FCFFs and the terminal value.
•Now, let’s compute the present value of those future cash flows.
•We computed “Cost of debt”, “Cost of equity”, and “WACC” based on the information on October, 2019. However, for the simplicity, we are going to assume that we are estimating the value of the business at the beginning of 2020. And, each future FCFE and FCFF comes at the end of each year.
•Table 2:
Use the Cost of Equity (it is in “Table 1”) to compute the PV of each FCFE
Use the WACC (it is in “Table 1”) to compute the PV of each FCFF
After computing each PV of each FCFE, FCFF and Terminal value, find the total PV in 2020 (at the beginning of the Year 2020).
Read a comment posted in each cell.
Capitalized Free Cash Flows Spreadsheet (10 points)
•Table 2:
We assume that future FCFEs and FCFFs will be constant.
So, we use the perpetuity equation to compute the PV.
Read Chapter 4
Use the Cost of Equity (it is in “Table 1”) to compute the PV of FCFE
Use the WACC (it is in “Table 1”) to compute the PV of FCFF
Read a comment posted in each cell.
•Table 3:
We assume that the future FCFEs and FCFFs will grow at 0.015 (1.5%) forever.
So, we use the constant growth model to compute the PV.
Read Chapter 4
The discount rate for the FCFE
Use the Cost of Equity to compute the PV of FCFE
The discount rate for the FCFF
Use the WACC to compute the PV of FCFE
Read a comment posted in each cell.
6 | P a g e
FIN 601: Financial Management and Markets Instructor: Dr. Jinsuk Yang Project Report Part 2
What you have done is the simplified approach to value the business. To do so, you have applied or used:
Financial statements
•Free cash flow
•Free cash flow to equity
•Free cash flow to the firm The time value of money
•Discounting multiple periods of future cash flows
•Discounting with constant growth model
•Discounting with the perpetuity Cost of equity
Cost of debts WACC
The last two spreadsheets provide information, which is the total present value in the Year 2020 (at the beginning). This is what we call the implied value of a business. However, the business valuation must be done by using a very sophisticated approach with more steps than what we did and by licensed professionals.
•Requirements o Excel file
1.In your excel file, you must have 11 spreadsheets.
2.Name your file as “PP2-first name-last name”
3.You must save the file as “Save as type: Excel Workbook”, not “CSV” or any other formats.
•FAQ
o Do I visit the FINRA website and collect data? No, you use the data provided by the instructor.
7 | P a g e
