Bond and Stock Valuation Template

PRINCIPLES OF MANAGERIAL FINANCE BRIEF EDITION PMF PROBLEM SOLVER Index By Category Bond and Stock Valuation Basic Bond Valuation Book Value Liquidation Value P/E Multiplier Constant Growth Financial Statements All Ratios Liquidity Activity Debt Profitability Time Value of Money PV - Single Amount PV - Mixed Stream PV - Annuity FV - Single Amount FV - Annuity Deposits to a Sum Cost of Capital After Tax Cost of Debt Cost of Common Stock Cost of Preferred Stock Weighted Avg. Cost of Capital Weighted Mrg Cost of Capital Capital Budgeting Net Present Value Internal Rate of Return Payback Period Annualized Net Present Value Initial Investment General Budgeting Cash Budget Alphabetical Index Activity After Tax Cost of Debt All Ratios Annualized Net Present Value Basic Bond Valuation Book Value Cash Budget Constant Growth Cost of Common Stock Cost of Preferred Stock Debt Deposits to a Sum FV - Annuity FV - Single Amount Initial Investment Internal Rate of Return Liquidation Value Liquidity Net Present Value P/E Multiplier Payback Period Profitability PV - Annuity PV - Mixed Stream PV - Single Amount Weighted Avg. Cost of Capital Weighted Mrg Cost of Capital © 2003 by Addison Wesley Longman. Copyright © 2003 KMT Software, Inc. All Rights Reserved. © 2003 by Addison Wesley Longman Printed: 3/26/2008 Basic Bond Valuation Par Value of the Bond Years to Maturity Required Rate of Return Coupon Rate of Bond Payment of interest: Annual Semi-annual Value of the bond is: $1,000 10 12.000% 10.000% $887.00 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Book Value Total Assets Total Liabilities Number of Common Shares Outstanding The book value per share is: $6,000,000 $4,500,000 100,000 $15.00 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Price/Earnings (P/E) Multiple Average P/E Ratio for Firms in the Industry Earnings per Share The value per share is: 7.0 $2.60 $18.20 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Liquidation Value Liquidation Value of Assets Total Liabilities Book Value of Preferred Stock Number of Common Shares Outstanding The liquidation value per share is: $5,250,000 $4,500,000 0 100,000 $7.50 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Constant Growth Estimated Dividend per Share Expected Annual Growth Rate of Dividends Investor's Required Rate of Return The value per share is: $1.50 7.00% 15.00% $18.75 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Financial Ratios (All) Income Statement Revenue (Sales) Less: Cost of goods sold Gross profits Less: Operating Expenses Selling expense General and administrative Lease expenses Depreciation expense Other Total operating expenses Operating profits Less: Interest expense Net profits before taxes Less: Taxes Net profits after taxes Less: Preferred stock dividends Earnings available for common stockholders $3,074 2,088 $986 $100 194 35 239 $568 $418 93 $325 94 $231 10 $221 Balance Sheet Assets Current assets Cash Marketable securities Accounts receivable Inventories Other Total current assets Gross Fixed assets (at cost) Land and buildings Machinery and equipment Furniture and fixtures Vehicles Other Total gross fixed assets Less: Accumulated depreciation Net fixed assets Total assets Liabilities and Stockholders' Equity Current liabilities Accounts payable Notes payable Accruals Other Total current liabilities Long-term debts Total liabilities Stockholders' equity Preferred stock Common stock Paid-in capital Retained earnings File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman $363 68 503 289 $1,223 $2,072 1,866 358 275 98 $4,669 2,295 $2,374 $3,597 $382 79 159 $620 $1,023 $1,643 $200 191 428 $1,135 Printed: 3/26/2008 Total stockholders' equity Total liabilities and stockholders' equity Additional Data Needed for Ratios Principal Payments made during period Lease Payments made during period Tax Rate Common Shares Outstanding (in 000s) Market Price per Share of Common Stock $1,954 $3,597 $71 $35 29% 76 $32.25 Liquidity Analysis and Ratios Net Working Capital Current Ratio Quick Ratio $603 1.97 1.51 Activity Ratios Inventory Turnover Average Collection Period Fixed Asset Turnover Total Asset Turnover 7.22 58.91 1.29 0.85 Debt Ratios Debt Ratio Debt-equity Ratio Times Interest Earned Fixed-payment Coverage Ratio 45.7% 52.4% 4.5 1.9 Profitability Ratios Gross Profit Margin Operating Profit Margin Net Profit Margin Return on Assets (ROA) Return on Equity (ROE) Earnings Per Share (EPS) Price/Earnings (P/E) Ratio 32.1% 13.6% 7.5% 6.4% 11.8% $2.90 11.1 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Liquidity Analysis and Ratios Net Working Capital Current Assets Current Liabilities The net working capital is: Current Ratio Current Assets Current Liabilities The current ratio is: Quick Ratio Current Assets Inventory Current Liabilities The quick ratio is: $ $1,223,000 $620,000 603,000 $1,223,000 $620,000 1.97 $1,223,000 $289,000 $620,000 1.51 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Activity Ratios Inventory Turnover Cost of Goods Sold Inventory The inventory turnover is: Average Collection Period Accounts Receivable Annual Sales The average collection period is: Average Payment Period Accounts Payable Annual Purchases Days in year The average payment period is: Total Asset Turnover Sales Total Assets The total asset turnover is: $2,088,000 $289,000 7.22 $503,000 $3,074,000 58.91 $382,000 $1,461,600 360 94.09 $3,074,000 $3,597,000 0.85 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Debt Ratios Debt Ratio Total Liabilities Total Assets The debt ratio is: Debt-equity Ratio Long-term debt Stockholders' Equity The debt-equity ratio is: Times Interest Earned Earnings Before Interest and Taxes Interest Expense The times interest earned ratio is: Fixed-payment Coverage Ratio Earnings Before Interest and Taxes Interest Expense Principal Payments Lease Payments Total Preferred Dividends Tax Rate The fixed-payment coverage ratio is: $1,643,000 $3,597,000 45.7% $1,023,000 $1,954,000 52.4% $418,000 $93,000 4.5 $418,000 $93,000 $71,000 $35,000 $10,000 29% 1.87 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Profitability Ratios Gross Profit Margin Sales Cost of Goods Sold The gross profit margin is: Operating Profit Margin Operating profit Sales The operating profit margin is: Net Profit Margin Net Profits After Taxes Sales The net profit margin is: Return on Assets (ROA) Net Profits After Taxes Total Assets The return on assets is: Return on Equity (ROE) Net Profits After Taxes Stockholders' Equity The return on equity is: Earnings Per Share (EPS) Earnings Available to Common Stockholders' Common Shares Outstanding The earnings per share are: Price/Earnings (P/E) Ratio Market Price per Share of Common Stock Earnings Per Share The price/earnings ratio is: $3,074,000 $2,088,000 32.1% $418,000 $3,074,000 13.6% $231,000 $3,074,000 7.5% $231,000 $3,597,000 6.4% $231,000 $1,954,000 11.8% $221,000 76,262 $2.90 $32.25 $2.90 11.1 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Present Value - Single Amount Future Amount (at the end of n periods) Number of Periods (n) Interest Rate Per Period (per n periods) The present value is: $1,700.00 8 8.00% $918.46 Calculating an Interest Rate Future Amount (at the end of n periods) Present Value Number of Periods (n) The interest rate per period is: $1,700.00 $918.46 8 8.00% Calculating the Number of Periods Future Amount (at the end of n periods) Present Value Interest Rate Per Period (per n periods) The number of periods are: $1,700.00 $918.46 8.00% 8 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Present Value of a Mixed Stream Periodic Discount Rate Cash Flow 400 800 500 400 300 9.00% Period (n) 1 2 3 4 5 6 7 8 9 10 The present value is: $1,904.76 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Present Value of an Annuity Choose one of the following options: Ordinary Annuity Annuity Due Payment Per Period Number of Periods Interest Rate Per Period (per n periods) The present value of the annuity is: $700.00 5 8.00% $2,794.90 Calculating an Interest Rate Payment Per Period Present Value Number of Periods (n) The interest rate per period is: $700.00 $2,794.90 5 8.00% Calculating the Number of Periods Payment Per Period Present Value Interest Rate Per Period (per n periods) The number of periods is: $700.00 $2,794.90 8.00% 5 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Future Value - Single Amount Compounding Choices (select one): Annual Semi-annual Quarterly Bi-monthly Present Value Number of Periods (n) Interest Rate Per Period (per n periods) The future value is: $100.00 2 8.00% $116.64 Calculating an Effective Interest Rate Compounding Frequency (select one): Annual Semi-annual Quarterly Bi-monthly Nominal Interest Rate Number of Years The effective rate per period (per n periods) is: 8.00% 2 8.16% Calculating the Number of Periods Present Value Future Value Interest Rate Per Period (per n periods) The number of periods is: $100.00 $116.64 8.00% 2 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Future Value of an Annuity Choose one of the following options: Ordinary Annuity Annuity Due Payment Per Period Number of Periods Interest Rate Per Period (per n periods) The future value of the annuity is: $1,000.00 5 7.00% $5,750.74 Calculating an Interest Rate Payment Per Period Future Value Number of Periods (n) The interest rate per period is: $1,000.00 $5,750.74 5 7.00% Calculating the Number of Periods Payment Per Period Future Value Interest Rate Per Period (per n periods) The number of periods is: $1,000.00 $5,750.74 7.00% 5.00 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Deposits to a Sum Select the type of annuity: Ordinary Annuity Annuity Due Amount to be Accumulated Number of years Annual Interest Rate The periodic deposit is: $100,000 10 9.00% $6,582.01 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 After Tax Cost of Debt Par value of bond Cash inflow from sale (per bond) Coupon rate Number of periods to maturity Tax rate The approximate cost is: The after tax cost is: $1,000 $960 9.00% 20 40.0% 9.39% 5.63% File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Cost of Common Constant Growth Model Current per Share Market Value of Stock Dividend per Share Projected Growth Rate of Dividends The cost of common stock is: $50.00 4 5.00% 13.00% Capital Asset Pricing Model Risk Free Rate of Return Market Rate of Return Beta The cost of common stock is: 7.00% 11.00% 1.50 13.00% Cost of New Issue Current per Share Market Value of Stock Dividend per Share Growth Rate of Dividends Flotation Cost per Share The cost of common stock is: $50.00 $4.00 5.00% $5.50 13.99% File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Cost of Preferred Stock Annual Preferred Stock Dividend Net Proceeds from Sale of Preferred Stock The cost of preferred stock is: $8.70 $82.00 10.61% File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Weighted Average Cost of Capital Long-term debt proportion Preferred stock proportion Common stock/Equity proportion Cost of debt Cost of preferred stock Cost of equity/stock The weighted average cost of capital is: 40.00% 10.00% 50.00% 5.60% 10.60% 13.00% 9.80% File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Weighted Marginal Cost of Capital Long-term debt proportion Preferred stock proportion Common stock/Equity proportion Range of New Financing Lower Upper Source of Bound Bound Capital Cost $0 $600,000 Debt 5.60% Preferred 10.60% Common 13.00% Weighted average cost of capital Lower Upper Source of Bound Bound Capital Cost $600,000 $1,000,000 Debt 5.60% Preferred 10.60% Common 14.00% Weighted average cost of capital Lower Bound $1,000,000 Upper Source of Bound Capital Cost and beyond Debt 8.40% Preferred 10.60% Common 14.00% Weighted average cost of capital 40.00% 10.00% 50.00% Weighted Cost 2.24% 1.06% 6.50% 9.80% Weighted Cost 2.24% 1.06% 7.00% 10.30% Weighted Cost 3.36% 1.06% 7.00% 11.42% File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Net Present Value Initial Investment (enter as positive number) Discount Rate Years 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Cash Flows 28,000 12,000 10,000 10,000 10,000 $45,000 10% The net present value is: $10,924 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Internal Rate of Return Years 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Initial Investment --> Cash Flows ($45,000) 28,000 12,000 10,000 10,000 10,000 The internal rate of return is: 21.65% File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Payback Period Years 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 The payback period is: Initial Investment --> Cash Flows ($45,000) 1,000 1,000 1,000 1,000 1,000 1,000 40,000 6,000 6.975 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Annualized Net Present Value Discount Rate Number of years Years 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 10.00% 6 Cash Flows ($85,000) 35,000 30,000 25,000 20,000 15,000 10,000 Initial Investment enter as negative --> The net present value is: The annualized NPV is: $17,285 $3,969 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Initial Investment Cost of new asset Installation costs After-tax proceeds from sale of old asset Tax on sale of present machine Change in net working The initial investment is: $380,000 20,000 280,000 84,160 17,000 $221,160 File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008 Cash Budget (all numbers in $000) Oct $210 213 ($3) $50 $47 25 $0 $22 Nov $320 418 ($98) $47 ($51) 25 $76 Dec $340 305 $35 ($51) ($16) 25 $41 Jan Feb Mar Total cash receipts Less: Total cash disbursements Net cash flow Add: beginning cash Ending cash Less: Minimum cash balance Required total financing Excess cash balance File: $ASQBond and Stock Valuation Template.xls.xls © 2003 by Addison Wesley Longman Printed: 3/26/2008