FIN511 Project

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.

STEP 1  Time to finish:  February 11-February 28 2021. MANDATORY

1. Download financial statements of your company for five years: 2015-2019.

Income statement

Balance sheet

Statement of cashflow

2. Open them in excel spreadsheet (1) Income statement first, (2) balance sheet below, and (3) statement of cashflow below

1^st column is the item name

Column 2----6 are for data from each year.

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.

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.

Source of financial statements: Company's website, SEC, EDGAR, Yahoo Finance, Bloomberg.

Collect Annual Data.

Step 2  Time to finish:  February 28-March 31 2021. MANDATORY

• For each of the five years data, Calculate Free Cash Flow (FCF using).

You can follow any one of the methods:

Method 1:

Method 2:

Method 3:

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:

• Introduction
• Objective
• A brief bio of your company
• Methodology
• Provide a discussion/formulas you use for ratio analysis and valuation
• Discussion
• Provide detailed discussions of financial analysis based on ratios and trends
• Show few important graphs of trends
• Provide your estimation of the value of the company and your recommendation.
• Conclusion
• What you found, you can briefly state those results.
• What you have learned from the exercise, you can write few words.

RUBRIC FOR PROJECT

BEST

• All Excel coding, ratio calculations, and valuation calculations are all accurate.
• All calculations (coded values) are properly colored.
• Key ratio trends are graphed, labelled and presented with clear explanations
• Excel file is saved as excel NOT CSV so that calculations are verified.
• Written report strictly follows the guideline provided.
• All equations used in calculations for ratios and valuations are all shown.
• All ratio results are clearly explained whether it indicates financial, operational, and competitive positions are increasing/decreasing.
• Based on valuation result, you clearly explained where it stands based on current stock price and whether you recommend to buy/sell/hold the stock.

AVERAGE

• All Excel coding, ratio calculations, and valuation calculations are mostly accurate.
• All calculations (coded values) are properly colored.
• Key ratio trends are graphed, labelled and presented with clear explanations
• Excel file is saved as excel NOT CSV so that calculations are verified.
• Written report strictly follows the guideline provided.
• Majority of equations used in calculations for ratios and valuations are not properly shown.
• Majority ratio results are not  explained whether it indicates financial, operational, and competitive positions are increasing/decreasing.
• Based on valuation result, you vaguely explained where it stands based on current stock price and whether you recommend to buy/sell/hold the stock.

POOR

• All Excel coding, ratio calculations, and valuation calculations are mostly inaccurate.
• All calculations (coded values) are properly colored.
• Key ratio trends are graphed, labelled and presented with clear explanations
• Excel file is saved as excel NOT CSV so that calculations are verified.
• Written report strictly follows the guideline provided.
• All equations used in calculations for ratios and valuations are not properly shown.
• All ratio results are not  explained whether it indicates financial, operational, and competitive positions are increasing/decreasing.
• Based on valuation result, you did not explained where it stands based on current stock price and whether you recommend to buy/sell/hold the stock.