Excel-Driven Financial Analysis & Company Valuation
Budget: $30 – $250 USD
This project focuses on applying various financial models and Excel skills to analyze and evaluate publicly traded companies. Below is a summary of the key tasks and Excel skills required:
Problem 1: Company Valuation
Selection of Companies: Choose two publicly traded companies from different sectors/industries.
Data Collection: Gather accounting, stock price, bond, and relevant data for each company. (Use stock history function on excel for stock prices)
Financial Models:
Beta Calculation: Use regression or other techniques to compute Beta.
Cost of Equity: Apply CAPM to estimate the cost of equity.
Cost of Debt: Use bond information to calculate the cost of debt.
WACC: Use weighted averages to compute the company’s WACC.
Valuation Models:
FCFF: Calculate Free Cash Flow to Firm (FCFF) for each company, value the companies, and conduct a sensitivity analysis.
FCFE: Compute Free Cash Flow to Equity (FCFE), value the companies, and perform sensitivity analysis.
Relative Valuation: Compare the companies to others in their sectors using financial multiples (e.g., P/E, EV/EBITDA) to estimate values.
Reconciliation: Reconcile values from FCFF, FCFE, and relative models to determine a target stock price.
Recommendation: Provide buy, sell, or hold recommendations based on your analysis.
Required Excel Skills:
Data Analysis: Use functions like VLOOKUP, HLOOKUP, and data validation.
Financial Functions: Use the NPV, IRR, and other financial functions to calculate cost of capital and valuation metrics.
Sensitivity Analysis: Implement Excel’s Data Table and Scenario Analysis tools.
Solver: Use Solver for optimization tasks like finding the minimum risk portfolio.
Problem 2: Portfolio Construction
Data Collection: Collect monthly price data for 30 securities in one chosen sector from July 2018 to present.
Return Analysis: Calculate expected return, standard deviation, variance, and beta for each stock.
Correlation Matrix: Use Excel’s Data Analysis Toolpak to generate a correlation matrix of stock returns.
Portfolio Optimization:
Calculate the expected return and standard deviation for an equally weighted portfolio.
Use Solver to find the minimum variance portfolio, subject to constraints (sum of weights = 1, no short-selling).
Optimize for the maximum return at a given standard deviation.
Efficient Frontier: Graph the efficient frontier to show risk-return tradeoffs.
Required Excel Skills:
Data Analysis Toolpak: For statistical analysis, including variance and correlation.
Solver: For optimization problems.
Graphing: Create charts to display the efficient frontier.
Problem 3: Option Pricing and Greeks
Implied Volatility: Use macros and the Goal Seek function to calculate implied volatility for call and put options.
Black-Scholes Model: Recalculate option prices using the Black-Scholes model.
Option Valuation: Identify undervalued or overvalued options.
Merton’s Model: Use Merton’s Dividend-adjusted pricing model and calculate Greeks (Delta, Gamma, Vega, Theta, and Rho).
Required Excel Skills:
Goal Seek: For finding implied volatility.
Macros: To automate calculations for options pricing.
Statistical Analysis: Use Excel functions to calculate Greeks.
Problem 4a: Custom Functions
User-defined Functions: Write custom Excel functions to calculate variance, weighted averages, and WACC.
Duration and Beta: Create a macro to compute Macaulay duration and unlevered beta.
Required Excel Skills:
VBA (Visual Basic for Applications): To write custom functions and macros.
Financial Calculations: Implement complex formulas in custom functions.
Problem 4b: Financial Statement Analysis (10 points)
Ratio Calculation: Compute liquidity, asset management, debt management, profitability, and valuation ratios for two companies over four years.
Comparison to Industry Averages: Compare these ratios to industry benchmarks.
Performance Analysis: Evaluate financial strengths and weaknesses based on the ratios.
Required Excel Skills:
Data Entry and Calculation: Use formulas to compute financial ratios.
Charts: Create charts to visualize ratio comparisons and performance trends.
Problem 5: Fama-French 3 Factor Model
Alpha and Beta: Use the Fama-French 3 Factor Model to compute the alphas and betas for 30 securities.
Required Excel Skills:
Regression Analysis: Implement regression analysis to estimate beta and alpha for securities.
Problem 6: Excel Training (15 points)
Training Session: Prepare a step-by-step lecture on a financial modeling topic such as time value of money, stock valuation, or option pricing.
Required Excel Skills:
Presentation and Training: Create clear, instructional content with examples for teaching others Excel modeling techniques.
General Excel Skills Needed:
Advanced Formulas: Using functions like INDEX, MATCH, IF, and financial functions.
Data Analysis Tools: For regression, correlation, and other statistical tests.
Solver: For optimization problems like portfolio selection.
Pivot Tables: To summarize and analyze large datasets.
Macros: For automating repetitive tasks and calculations.
Graphs and Charts: For visualizing financial data and analysis results.
This project requires a solid understanding of Excel and financial modeling, particularly in valuation, portfolio optimization, and option pricing. You'll need to be proficient in advanced Excel functions, data analysis tools, and VBA for custom functions and macros.
Problem 1: Company Valuation
Selection of Companies: Choose two publicly traded companies from different sectors/industries.
Data Collection: Gather accounting, stock price, bond, and relevant data for each company. (Use stock history function on excel for stock prices)
Financial Models:
Beta Calculation: Use regression or other techniques to compute Beta.
Cost of Equity: Apply CAPM to estimate the cost of equity.
Cost of Debt: Use bond information to calculate the cost of debt.
WACC: Use weighted averages to compute the company’s WACC.
Valuation Models:
FCFF: Calculate Free Cash Flow to Firm (FCFF) for each company, value the companies, and conduct a sensitivity analysis.
FCFE: Compute Free Cash Flow to Equity (FCFE), value the companies, and perform sensitivity analysis.
Relative Valuation: Compare the companies to others in their sectors using financial multiples (e.g., P/E, EV/EBITDA) to estimate values.
Reconciliation: Reconcile values from FCFF, FCFE, and relative models to determine a target stock price.
Recommendation: Provide buy, sell, or hold recommendations based on your analysis.
Required Excel Skills:
Data Analysis: Use functions like VLOOKUP, HLOOKUP, and data validation.
Financial Functions: Use the NPV, IRR, and other financial functions to calculate cost of capital and valuation metrics.
Sensitivity Analysis: Implement Excel’s Data Table and Scenario Analysis tools.
Solver: Use Solver for optimization tasks like finding the minimum risk portfolio.
Problem 2: Portfolio Construction
Data Collection: Collect monthly price data for 30 securities in one chosen sector from July 2018 to present.
Return Analysis: Calculate expected return, standard deviation, variance, and beta for each stock.
Correlation Matrix: Use Excel’s Data Analysis Toolpak to generate a correlation matrix of stock returns.
Portfolio Optimization:
Calculate the expected return and standard deviation for an equally weighted portfolio.
Use Solver to find the minimum variance portfolio, subject to constraints (sum of weights = 1, no short-selling).
Optimize for the maximum return at a given standard deviation.
Efficient Frontier: Graph the efficient frontier to show risk-return tradeoffs.
Required Excel Skills:
Data Analysis Toolpak: For statistical analysis, including variance and correlation.
Solver: For optimization problems.
Graphing: Create charts to display the efficient frontier.
Problem 3: Option Pricing and Greeks
Implied Volatility: Use macros and the Goal Seek function to calculate implied volatility for call and put options.
Black-Scholes Model: Recalculate option prices using the Black-Scholes model.
Option Valuation: Identify undervalued or overvalued options.
Merton’s Model: Use Merton’s Dividend-adjusted pricing model and calculate Greeks (Delta, Gamma, Vega, Theta, and Rho).
Required Excel Skills:
Goal Seek: For finding implied volatility.
Macros: To automate calculations for options pricing.
Statistical Analysis: Use Excel functions to calculate Greeks.
Problem 4a: Custom Functions
User-defined Functions: Write custom Excel functions to calculate variance, weighted averages, and WACC.
Duration and Beta: Create a macro to compute Macaulay duration and unlevered beta.
Required Excel Skills:
VBA (Visual Basic for Applications): To write custom functions and macros.
Financial Calculations: Implement complex formulas in custom functions.
Problem 4b: Financial Statement Analysis (10 points)
Ratio Calculation: Compute liquidity, asset management, debt management, profitability, and valuation ratios for two companies over four years.
Comparison to Industry Averages: Compare these ratios to industry benchmarks.
Performance Analysis: Evaluate financial strengths and weaknesses based on the ratios.
Required Excel Skills:
Data Entry and Calculation: Use formulas to compute financial ratios.
Charts: Create charts to visualize ratio comparisons and performance trends.
Problem 5: Fama-French 3 Factor Model
Alpha and Beta: Use the Fama-French 3 Factor Model to compute the alphas and betas for 30 securities.
Required Excel Skills:
Regression Analysis: Implement regression analysis to estimate beta and alpha for securities.
Problem 6: Excel Training (15 points)
Training Session: Prepare a step-by-step lecture on a financial modeling topic such as time value of money, stock valuation, or option pricing.
Required Excel Skills:
Presentation and Training: Create clear, instructional content with examples for teaching others Excel modeling techniques.
General Excel Skills Needed:
Advanced Formulas: Using functions like INDEX, MATCH, IF, and financial functions.
Data Analysis Tools: For regression, correlation, and other statistical tests.
Solver: For optimization problems like portfolio selection.
Pivot Tables: To summarize and analyze large datasets.
Macros: For automating repetitive tasks and calculations.
Graphs and Charts: For visualizing financial data and analysis results.
This project requires a solid understanding of Excel and financial modeling, particularly in valuation, portfolio optimization, and option pricing. You'll need to be proficient in advanced Excel functions, data analysis tools, and VBA for custom functions and macros.