Project Guide As a part of your course, you will take a project. You will select

Project Guide
As a part of your course, you will take a project. You will select a company (let me know so no one has the same company) and evaluate the financial, operating and competitive performance of the company. You will also estimate intrinsic value of the corporation.
The project has two parts: 1. Excel based work and 2. Written report.
Excel work will consist of A. Financial analysis (ratio and trend analysis (CH 2 &3), B. Valuation (CH 7).
Written report will have 2 parts: A. Show your opinion and findings of financial, operational, and competitive position of the company based on results of ratio and trend analysis. B. Provide your recommendations and valuation of the company.
It is now time to start collecting data for your company as a first step and download them in excel and code them so that you can start your project.
Download financial statements of your company for five years: 2015-2019.
a. Income statement
b. Balance sheet
c. Statement of cashflow
Open them in excel spreadsheet (1) Income statement first, (2) balance sheet below, and (3) statement of cashflow below
a. 1st column is the item name
b. Column 2—-6 are for data from each year.
c. Code (writing the equation to solve the item based on formula) items based on statements. For example: In balance sheet, Total Current Assets (TCA) consists of Cash+A/R+ Inventory. Total Assets (TA) = TCA+NFA (Net Fixed Assets). So, use excel function to write TCA equation and to solve it.
d. Color the coded cells in purple. So that you can show which ones are calculation using excel. Items that are not calculation will remain as is.
Then:
2. Find the growth of the FCF for each year: E.G. (FCF2016-FCF2015)/FCF2015 = g1, (FCF2017-FCF2016)/FCF2016= g2, etc.
3. Call the g1, g2, g3, and g4. (For five year data, you will have 4 g). No g for the first year.
4. Take average of these g: AVG (g) = (g1+g2+g3+g4)/4
Then future FCFs using average g for next 5 years.
5. Call them FCF1, FCF2, FCF3, FCF4, and FCF5
6. For example: You have data until 2019. So FCF1 = FCF of 2020.
FCF1 = FCF19*(1+AVG(g)),
FCF2 = FCF1*(1+AVG(g)), and so on.
7. You will calculate FCF for 2020, 2021, 2022, 2023, and 2024.
8. Then you calculate Horizon Value (HV). For that we consider GDP growth rate as g. As a constant growth rate. Take the average of the gdp gdp growth rate from last 15 years.
To calculate HV, first we need FCF for 2025.
FCF2025 =FCF (2024)*(1+GPD Grwoth rate).
Horizon Value =
Where WACC is the weighted average cost of capital, and g is the GDP growth rate.
THEN:
9. Find the PV of all FCFs of 2020, 2021, 2022, 2023, 2024 and for HV2024. Using PV method. IT is called Vop = Value of Operation.
Vop = PV of FCFs + PV of HV.
10. Then subtract Debt payments, preferred stocks and add (if any) Short term Investments).
It is known as Value of Equity. If you divide this number by No of shares outstanding, you get price per share.
Please note: if you have no short term investment and or preferred stock, you can ignore them.
STEP 3: Time to finish: April 1-April 28 2021. MANDATORY
Complete your written report based on your excel work.
SUBMISSION: 1. Excel file and 2. Word file. You will save your files with your last name as part of the file name.
GUIDE for Written Part:
1. Introduction
a. Objective
b. A brief bio of your company
2. Methodology
a. Provide a discussion/formulas you use for ratio analysis and valuation
3. Discussion
a. Provide detailed discussions of financial analysis based on ratios and trends
b. Show few important graphs of trends
c. Provide your estimation of the value of the company and your recommendation.
4. Conclusion
a. What you found, you can briefly state those results.
b. What you have learned from the exercise, you can write few words.
Source of financial statements: Company’s website, SEC, EDGAR, Yahoo Finance, Bloomberg.
Collect Annual Data.

Leave a Reply

Your email address will not be published.