Investment Banking University is the world's only university dedicated to higher learning in investment banking.
WELCOME
CALL +1 920 264 3054
Top

### Finance with Excel

Finance with Excel

Express your decisions using Excel. Excel is the premier business computational tool

Implement financial analysis using the tool for financial analysis, Excel

Valuation process

Heart of finance is time value of money and discounting

Excel Concepts Needed for Finance

Write down variables (defining the parameters of the decision)

Absolute or relative values copying (=A1) (=\$A\$1) and formulas

Functions (=fx(    ))

Data tables (“sensitivity tables”)

Express Decisions with Excel

Implement financial analysis with Excel

Using a Financial Model for Decision Making: The Investment Decision

Ability to get financing from financial institutions depends on ability to make a financial model for the new or existing business

The financial model projects future earnings from the organization

Predict the future performance of a firm.

Accounting statements report what happened to the firm in the past. A financial model predicts what the firm’s accounting statements will look like in the future. Start by taking the initial accounting statements and inputting them into Excel

Difference between accounting and financial model is in the current assets and current liabilities. In financial model we are concerned only with operating assets and operating liabilities. We exclude financing related

Financial model has three components:

Model parameters (value drivers)

Financing decision assumptions (i.e. Mix between debt and equity, what does firm do with excess cash? Repay debt, payments to shareholders, or as cash balance)

Pro forma financial statements

Cash in the financial model is a plug. The plug is so that the balance sheet balances.

Cash = total liabilities and equity – current assets – net fixed assets

The plug is the balance sheet item that guarantees the equality of the future projected total assets and future projected total liabilities and equity. Every financial model has a plug and the plug is almost always cash, debt, or stock.

Financial Model and Valuation Process:

Assumptions (value drivers)

Existing accounting statements (IS and BS)

Projected financial statements

Free cash flow calculation (FCFs)

Terminal value calculation

Valuation calculation

Sensitivity table for major value drivers to see range of valuation

Once the financial model is complete (i.e. accounting statements have been projected), we can use the model to:

Value the firm by projecting free cash flows (FCFs)

Determine ability of firm to pay it’s debts (i.e. credit analysis)

Using a Financial Model for Decision Making: The Financing Decision

All companies must decide how to finance their activities

Proportion of debt and equity

The discount rate should be appropriate to the riskiness (i.e. variability or beta) of the cash flows being discounted.

Discount rate is also called interest rate, cost of capital, opportunity cost.

Compute annualized IRR

The cost of capital of an investment is related to the risk of the cash flows of the investment. The relationship of individual asset returns to the risk is called the security market line (SML). You can use SML to get the discount rate for individual investments. The SML is used for private companies.

The cost of capital of an organization is related to the risk of the combined riskiness of the investments in the portfolio. The relationship of portfolio returns to the risk is called the capital asset pricing model (CAPM). You use CAPM to get the discount rate (i.e. cost of capital). When the investment is a public security, you use CAPM since the buyer of the security will have a portfolio to diversify away risk.

Portfolio risk is associated with statistics.

Wealth Maximizing Decisions

Investment decision – What is it worth? NPV of strategic alternative

Financing decision – What does it cost? IRR of financing alternative

Cash is King

Wealth maximization has to do with maximizing cash. Cash in the context or organizations is known as cash flow.

Return is a word for cash flows

Cash Flow Definition (FCF)

Profit after taxes

+ Depreciation (noncash expense)

+ Change in net working capital (- increase in current assets and + increase in current liabilities)

Capital expenditures (CAPEX)

+ After-tax interest payments

= Free Cash Flow (FCF)

Role of the Finance Professional

The role of the financial professional is to quantify the cash flows and risk of strategic alternatives available to the individual or organization.

Investment bankers compute the IRR and NPV of strategic alternatives.

Capital Markets

The capital markets is made up of cash flows and discounts

Capital Markets and Information

Information is valuable in determining investment and financing decisions in the capital markets. Overall, markets are weak form efficient meaning that their valuations reflect previous stock price performance (i.e. stock price data) and are sometimes semistrong meaning that valuations incorporate all public information. Capital markets are not strong form efficient meaning that valuations do not reflect private information.

Multiple Investment and Financing Decisions: Portfolio

When there is multiple investment and financing decisions, we have something called a portfolio. The discount rate can be decreased by diversifying with a portfolio. When the discount rate is decreased, the valuation of the portfolio increases as cash flows have maintained more value.

A corporation/organization is simply a portfolio of sources and uses

Modeling a Strategic Alternative

Put all variables (“value drivers”) at the top of the spreadsheet

Never use a number where a formula will also work

Blue for hard codes

Finance: Exchanging Value Through Time

Assets have a time dimension

Future value function =FV( )

Value in the future of a sum of money compounded into the future

Present value =PV( )

Value today of future payments discounted to present

Net present value (NPV) =-First payment + NPV( )

Incremental wealth increase earned by a strategic alternative. NPV tells you economic value of an investment today. Always use NPV in the investment decision.

Internal rate of return (IRR) =IRR( )

Compound rate of return earned by a strategic alternative

VIII. Rate of Return vs. Cost of Capital

What is the asset’s IRR?

Compare to the cost of capital (Effective annual interest rate – which is the annualized IRR used to compare financing alternatives aka Compound Annual Growth Rate (CAGR))

Cost of Capital

Calculate IRR of financing alternatives to determine cost of capital

Need to get IRR in annual terms to facilitate comparison. May have to start with monthly IRR then annualize

Annualized IRR = (1 + Monthly IRR)^n-1

Finding a Value in a Financial Model

When we want to find a value by setting a particular value to another cell, we use:

Goal seek – Alt, A, G

Financing Alternatives: Loan Amortization

=PMT( )

To calculate the debt payment per period

=IPMT( )

To calculate the interest portion of the payment of debt

=PPMT( )

To calculate the principal portion of the payment

VIII. Financing Alternatives: Direct Comparison

IRR of differential cash flows tells you the cost of the option

IRR tells you the cost of the financing alternative

CAGR is Effective Annual Interest Rate (EAIR) to allow for comparison

Analyzing the Strategic Alternative: Sensitivity Table

Data Table is Alt, A, W, T

Tells you how output changes with incremental changes in the inputs (i.e. variables)

The Financing Alternative: Nominal vs. Real Cost

In determining the true cost of a financing alternative, it is important to use the real rate of interest which incorporates inflation. The real rate of interest is determined by using the real cash flows.

Inflation acts as a discount rate

Strategic Alternatives Analysis

For each strategic alternative, compute the NPV and IRR, then have decision rules for investing including:

Minimum NPV

Hurdle rate (IRR)

You are using NPV and IRR to make investment decisions but you need the discount rate. The discount rate is associated with the financing decision

Cash Flows and Risk

Are cash flows riskless (i.e. treasury bills) or are they risky (i.e. market portfolio)

Cost of Capital and Opportunity Cost

The returns of similar investments should be used as the cost of capital

The Discount Rate

An organization’s discount rate is the cost of equity and cost of debt. The cost of the total capital structure is known as the Weighted Average Cost of Capital (WACC):

WACC = rE* (E/(E+D)) + rD (1-Tc)*(D/(E+D))

Value of Equity

The value of equity is the present value of all future dividends

Sources & Uses

Uses                          Sources

Free Cash Flows          WACC

CAPM to get cost of equity

Accounting Statements: Statement of Cash Flows

The purpose of the statement of cash flow is to explain the increase in the cash accounts on the balance sheet as a function of the firm’s operating, investing, and financing activities.

Valuation Methods: Total Enterprise Value (TEV) vs. DCF

Market valuation:

Total Enterprise Value (TEV) = MVE + MVD + Preferred – Cash

1. DCF Method (intrinsic value) = PV(FCFs) @ WACC + liquid assets

Accounting Value vs. Finance Value

Accounting value of firm is backward looking and thus incorrect to use in valuation. Finance value is forward looking and consistent with the fact that the owner of an organization or security has claims on the future cash flows of the business.

FCF and DCF

Free cash flow (FCF) calculations is DCF

Portfolio Analysis and the Capital Asset Pricing Model (CAPM)

Discount rate is a measure of risk associated with:

Horizon

Safety

Liquidity

We get the discount rate by analyzing the distribution of an investment’s returns. We get the standard deviation which is a measure of variance in returns. Standard deviation is a component to finding the discount rate:

=STDEVP( )

What does the frequency distribution look like?

Determine risk measure known as beta and plug this into CAPM to get the discount rate of equity. Derive the cost of debt and then calculate WACC to get the discount rate of the firm.

Ex Ante vs. Ex Post Returns

Ex Ante is the expected return

Ex Post is the actual return

VIII. Statistics for Portfolios

=Average( )

To get mean return

=Varp( )

To get variance of returns

=Stdevp( )

To get standard deviation of returns

=Covar( )

To get covariance between two sets of returns

=Correl( )

To get correlation between two sets of returns

Trendline (regression) – click on points of XY graph and right click to Add Trendline with linear regression and display equation and R-squared on chart

Portfolio Returns and The Efficient Frontier

Statistics are used to determine acceptable and unacceptable portfolios

Diversification lowers standard deviation of the portfolio

Are the returns correlated? If no, then add security to the portfolio (i.e. diversify)

The efficient frontier is the set of all portfolios that are on the upward-sloping part of the graph starting with the minimum variance portfolio (i.e. the market portfolio). Choose the portfolio that is on the efficient frontier.

The Efficient Frontier and the Optimal Portfolio

The best investment portfolio is made up of the risk free asset and a risky asset representing the market (i.e. the market portfolio)

Determine the market portfolio (the portfolio with the highest attainable sharpe ratio)

Market portfolio is the best combination of risky assets available to the investor

Security Market Line & CAPM

The security market line says that the expected return of an asset is a function of the asset’s beta (i.e. sensitivity to the market).

Only relevant risk is systematic risk since the investors will all be diversified

Security Market Line & Investment Performance

The security market line says that the expected return of an asset is a function of the asset’s beta (i.e. sensitivity to the market).

Only relevant risk is systematic risk since the investors will all be diversified

Security Market Line & Investment Performance

The security market line says that the expected return of an asset is a function of the asset’s beta (i.e. sensitivity to the market).

Only relevant risk is systematic risk since the investors will all be diversified

VIII. Security Market Line & Investment Performance Continued

Investment performance:

Market portfolio proxy is S&P 500

Beta is measure of riskiness of security

Alpha measures excess return

Market portfolio proxy is S&P 500

Beta is measure of riskiness of security

Alpha measures excess return

It is about investment performance versus the risk involved in the investment

CAPM & Investment Performance

Use CAPM to get the discount rate of equity and compare to cost of financing alternatives

Is there risk adjusted overperformance or underperformance?

Is performance commensurate with risk?

Excess Return

Excess return is the investment’s spread over the one year treasury (i.e. risk free rate)

Use regression equation to determine if underperformance (negative alpha) or overperformance (positive alpha)

When regressing asset’s returns against the market portfolio, alpha measures excess returns over the market portfolio

Beta & R^2

High beta is an aggressive stock

Low beta is a defensive stock

R^2 is percentage of variability that is market related risk when returns are regressed on the market portfolio

Diversification increases R^2 of the portfolio and decreases nonsystematic risk

Alpha and Efficient Markets

In efficient markets, there is no alpha and investments earn their risk-adjusted return

CAPM and the Cost of Capital

CAPM = rf + Beta [ E(rm) – rf]

In CAPM, use Beta of asset to calculate cost of equity

WACC is the discount rate based upon the capital structure of the investment

Valuing Securities in Efficient Markets

Market efficiency and the role of information in determining asset prices

Publicly available information should be reflected in market price