### 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

Black for links and outputs

** **

**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

- 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:

Risk adjusted performance; excess returns?

**Risk Adjusted 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