CFM Excel Templates:
Financial Analysis and Valuation Package to Accompany Contemporary Financial Management - 7th edition
R. Charles Moyer - James R. McGuigan - William J. Kretlow
Introduction The CFM Excel Templates is a set of twenty financial valuation and analysis templates designed to accompany the 7th 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 damaged. A brief description of each template follows. The name given is parentheses is the file name for DOS users. The full template name is used with the Macintosh system.
2
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 one to thirty-five 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.
3
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 nonconstant 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 warrant. 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
4
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 per share. 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 charges.
5
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 financing arrangement.
6