CFM Excel Templates:
Financial Analysis and Valuation Package to Accompany
Contemporary Financial Management - 9th edition
R. Charles Moyer - James R. McGuigan - William J. Kretlow
The CFM Excel Templates is a set of 20 financial valuation and analysis templates
designed to accompany the ninth edition of Contemporary Financial Management. Each
template is designed to perform a specific financial valuation or analysis and requires
only a very basic understanding of the Microsoft Excel program.
When a template is opened, the cursor appears on the first cell that requires data input.
All cells that require data inputs are highlighted. The templates are simple to follow and
once data for a problem are entered, the template automatically calculates the solution.
The templates are flexible in that data can be entered as numbers or as formulas
containing numbers. The formulas in the templates can be changed at the user’s
discretion. But remember, once a formula is changed, the old formula is lost. If a mistake
is made and the original formulas are lost, simply exit the program and re-enter. This will
bring up a new template and the data input process can be started over.
The templates will function best if you first install them on the hard-drive of your
computer. Keep a backup copy of the templates in case the originals are lost or somehow
A brief description of each template follows. The name given in parentheses is the file
name for DOS users. The full template name is used with the Macintosh system.
Black-Scholes Option Pricing Template (OPTION.XLT)
The Black-Scholes Option Pricing Template values a call option on a share of common
stock and gives the hedge ratio based on the Black-Scholes Option Pricing Model. The
template assumes that the option being valued is a European call and the underlying stock
does not pay dividends.
Bond Refunding Analysis Template (BONDREF.XLT)
The Bond Refunding Analysis Template compares the costs and benefits of a bond
refunding transaction. The template determines the present value of refunding a bond by
comparing the present value of the costs incurred from refunding the bond to the present
value of the benefits or interest savings from the issuance of a new bond.
Bond Valuation Template (BONDVAL.XLT)
The Bond Valuation Template calculates the price of a bond as well as the yield to
maturity under certain assumptions. It assumes the bond is bought on an interest payment
date, all periodic cash flows are received on the date specified, and reinvested at the
required rate or return. The price of the bond is determined based on the interest payments
and the required rate of return for the investor.
Capital Budgeting Template (CAPBUDG.XLT)
The Capital Budgeting Template is designed to analyze a variety of capital budgeting
projects. The template can analyze projects with lives from 1 to 35 years. The template
can be used on equipment, real estate, or combination projects. It provides a variety of
decision variables: net present value, internal rate of return, payback, profitability index,
terminal value, net present value adjusted for reinvestment, internal rate of return adjusted
for reinvestment, infinite chain net present value, certainty equivalent net present value,
and risk-adjusted net present value. The template allows the choice of either straight-line,
or MACRS depreciation or the user can input their own depreciation schedule.
Cash Budgeting Template (CASHBUDG.XLT)
The Cash Budgeting Template provides a framework to analyze cash receipts and
disbursements for each period over the budgeting period. The template is flexible and can
be used as a weekly or monthly budget.
Commercial Paper Analysis Template (COMPAPER.XLT)
The Commercial Paper Analysis Template calculates the effective interest cost of
commercial paper financing arrangement.
Common Stock Valuation Template (CSTOCKVL.XLT)
The Common Stock Valuation Template calculates the price of a common share of stock
based on zero growth, constant growth, or non-constant growth in dividends. The
template can also be used to calculate a stock value based on free cash flow. If non-
constant growth is used, the template requires the user to enter the growth rate for the first
ten periods and then assumes a constant growth for all other periods. After the choice of
dividend growth is made, the template calculates the price for the share of stock based on
the investor’s required rate of return.
Compensating Balance Analysis Template (COMPBAL.XLT)
The Compensating Balance Analysis Template computes the effective interest rate on a
loan that requires a compensating balance.
Convertible Securities Analysis Template (CONVSEC.XLT)
The Convertible Securities Analysis Template computes the conversion value and the
straight bond value of a convertible security. It also computes the theoretical value of a
Cost of Capital Template (COSTCAP.XLT)
The Cost of Capital Template calculates a cost of capital schedule based on up to four
sources of debt. The template requires the user to input the cost of each source of debt,
but calculates the cost of preferred and common stock. The template gives the user the
break-points and marginal cost of capital for each break.
Credit Standards Analysis Template (CREDSTD.XLT)
The Credit Standards Analysis Template determines the bottom-line effect of extending
credit to a new group of customers. The template compares the marginal costs of credit
extension to the marginal benefits received from the additional sales.
Credit Terms Analysis Template (CREDTERM.XLT)
The Credit Terms Analysis Template determines the bottom-line effect of changing the
credit terms of a company. The template compares the marginal costs of changing credit
terms to the marginal benefits received from the change.
Factoring Accounts Receivable Analysis Template (FACTORAR.XLT)
The Factoring Accounts Receivable Analysis Template calculates the marginal benefit
received from the factoring of its receivables. The template compares the marginal
benefits to the marginal costs and calculates an effective annual cost for factoring.
Financial Statement Analysis Template (FINSTMTA.XLT)
The Financial Statement Analysis Template calculates commonly used financial ratios.
The ratios are calculated from financial statement data that the user enters. There is also
an area to enter industry ratios for comparison purposes.
Indifference Point Analysis Template (INDIFFPT.XLT)
The Indifference Point Analysis Template eliminates the algebra involved in calculating
the indifference point by hand. The template provides a means of comparing different
means of financing by providing the level of operating income at which earnings per
share is equal under either alternative. It also provides the probability that the firm’s
operating income is less or greater than the indifference point assuming the operating
income is normally distributed. This allows the user to assess the risk of each alternative.
The template also determines the probability that the firm’s operating income is less than
the interest payment for each financing alternative.
Leverage and Breakeven Analysis Template (LEVB-E.XLT)
The Leverage and Breakeven Analysis Template allows you to analyze the impact of a
firm’s use of operating and financial leverage on expected risk and return of a firm. The
template computes the break-even dollar level of sales, generates an income statement in
a format to highlight operating and financial leverage, computes the degrees of operating,
financial and combined leverage, and analyzes the impact of a change in sales on earnings
Loan Amortization Analysis Template (LOANAMOR.XLT)
The Loan Amortization Analysis Template calculates an amortization schedule for loans.
The template gives the user the choice of payment periods and can calculate a three-year
monthly payment loan, eighteen-year semi-annual loan, or a 36-year annual loan.
Lockbox Analysis Template (LOCKBOX.XLT)
The Lockbox Analysis Template calculates the profitability of a decentralized collection
system. The template is flexible and can handle many different kinds of fee schedules. It
allows for compensating balances, fixed fees, or computes the cost of the lockbox based
on the number of checks processed each year. It can also handle any combination of
Preferred Stock Valuation Template (PFDSTOCK.XTL)
The Preferred Stock Valuation Template calculates the price of a share of preferred stock
based on the current and expected dividend and the purchaser’s required rate of return.
Revolving Credit Analysis Template (REVCREDT.XLT)
The Revolving Credit Analysis Template computes the effective cost of a revolving credit