WIXLIB

Financial Analysiswith Microsoft Excel SIXTH EDITIONTimothy R. MayesMetropolitan State College of DenverTodd M. ShankUniversity of South Florida – St. PetersburgAustralia Brazil Japan Korea Mexico Singapore Spain United Kingdom United States

This is an electronic version of the print textbook. Due to electronic rights restrictions,some third party content may be suppressed. Editorial review has deemed that any suppressedcontent does not materially affect the overall learning experience. The publisher reserves the rightto remove content from this title at any time if subsequent rights restrictions require it. Forvaluable information on pricing, previous editions, changes to current editions, and alternateformats, please visit www.cengage.com/highered to search by ISBN#, author, title, or keyword formaterials in your areas of interest.

Financial Analysis with Microsoft Excel Sixth EditionTimothy R. MayesVP/Editorial Director: Jack W. CalhounEditor-in-Chief: Joe SabatinoExecutive Editor: Mike ReynoldsDevelopmental Editor: Adele Scholtz 2012, 2010 South-Western, Cengage LearningALL RIGHTS RESERVED. No part of this work covered by the copyright hereinmay be reproduced, transmitted, stored, or used in any form or by any meansgraphic, electronic, or mechanical, including but not limited to photocopying,recording, scanning, digitizing, taping, web distribution, information networks,or information storage and retrieval systems, except as permitted under Section107 or 108 of the 1976 United States Copyright Act, without the prior writtenpermission of the publisher.Marketing Manager: Nathan AndersonMarketing Coordinator: Suellen RuttkaySenior Marketing Communication Manager: Jim OverlyContent Project Management: PreMediaGlobalSupervising Media Editor: Scott FidlerManufacturing Planner: Kevin KluckFor product information and technology assistance, contact us atCengage Learning Customer & Sales Support, 1-800-354-9706For permission to use material from this text or product,submit all requests online at www.cengage.com/permissionsFurther permissions questions can be emailed topermissionrequest@cengage.comProduction Service: PreMediaGlobalSenior Art Director: Michelle KunklerCover Designer: Stratton DesignCover Image: iStockphoto.com/RonTech2000Rights Acquisition Director: Audrey PettengillRights Acquisition Specialist, Text and Image: Sam MarshallAll figures, tables, and exhibits are author created and Cengage owned unlessotherwise noted.Screenshots are 2011 Microsoft Corporation.Microsoft Office Excel icon is 2011 Microsoft Corporation.Microsoft Office Excel is a registered trademark of the MicrosoftCorporation in the United States and/or other countries.Library of Congress Control Number: 2011938350ISBN-13: 978-1-111-82624-6ISBN-10: 1-111-82624-2South-Western5191 Natorp BoulevardMason, OH 45040USACengage Learning products are represented in Canada by Nelson Education, Ltd.For your course and learning solutions, visit www.cengage.comPurchase any of our products at your local college store or at our preferredonline store www.cengagebrain.comPrinted in the United States of America1 2 3 4 5 6 7 15 14 13 12 11

For my mother, Patricia Evansiii

This page intentionally left blank

ContentsCHAPTER 1Introduction to Excel 2010 1Spreadsheet Uses 2Starting Microsoft Excel 2Parts of the Excel Screen 3The File Tab and Quick Access ToolbarThe Home Tab 4The Formula Bar 6The Worksheet Area 6Sheet Tabs 7Status Bar 73Navigating the Worksheet 8Selecting a Range of Cells 9Using Defined Names 10Entering Text and Numbers 11Formatting and Alignment Options 11Formatting Numbers 13Adding Borders and Shading 14Entering Formulas15Copying and Moving Formulas 17Mathematical Operators 19Parentheses and the Order of Operations 19v

ContentsUsing Excel’s Built-In Functions21Using the Insert Function Dialog Box 22New “Dot Functions” in Excel 2010 24Using User-defined Functions 25Creating Graphics27Creating Charts in a Chart Sheet 27Creating Embedded Charts 28Formatting Charts 29Changing the Chart Type 31Creating Sparkline Charts 33Printing 34Saving and Opening Files35Saving Worksheets for the Internet35Using Excel with Other Applications 36Quitting Excel 36Best Practices for Spreadsheet Models 37Summary 38Problems 39Internet Exercise 42CHAPTER 2The Basic Financial Statements 43The Income Statement44Building an Income Statement in Excel 44The Balance Sheet49Building a Balance Sheet in Excel 49Improving Readability: Custom Number FormatsCommon-Size Financial Statements 54Creating Common-Size Income Statements 54Creating a Common-Size Balance Sheet 56Building a Statement of Cash FlowsUsing Excel’s Outliner 62Summary 64Problems 65Internet Exercise 67vi5751

ContentsCHAPTER 3The Cash Budget69The Worksheet Area 71Sales and Collections 73Purchases and Payments 74Collections and Disbursements 76Calculating the Ending Cash Balance78Repaying Short-Term Borrowing 81Using the Cash Budget for What If Analysis 82The Scenario Manager 84Adding Interest and Investment of Excess Cash87Calculating Current Borrowing 89Using the Formula Auditing Tools to Avoid Errors 90Calculating Current Investing 94Working Through the Example 95Summary 98Problems 99CHAPTER 4Financial Statement Analysis ToolsLiquidity Ratios105106The Current Ratio 107The Quick Ratio 108Efficiency Ratios109Inventory Turnover Ratio 109Accounts Receivable Turnover RatioAverage Collection Period 110Fixed Asset Turnover Ratio 111Total Asset Turnover Ratio 112Leverage Ratios110113The Total Debt Ratio 114The Long-Term Debt Ratio 114The Long-Term Debt to Total Capitalization Ratio 115The Debt to Equity Ratio 115The Long-Term Debt to Equity Ratio 116Coverage Ratios116The Times Interest Earned RatioThe Cash Coverage Ratio 118Profitability Ratios117118The Gross Profit Margin119vii

ContentsThe Operating Profit Margin 119The Net Profit Margin 119Return on Total Assets 120Return on Equity 121Return on Common Equity 121Du Pont Analysis 122Analysis of EPI’s Profitability Ratios 123Financial Distress Prediction124The Original Z-Score Model 125The Z-Score Model for Private FirmsUsing Financial Ratios126126Trend Analysis 127Comparing to Industry Averages 127Company Goals and Debt Covenants 129Automating Ratio Analysis 129Economic Profit Measures of PerformanceSummary 134Problems 137Internet Exercise 139CHAPTER 5Financial Forecasting132141The Percent of Sales Method142Forecasting the Income Statement 142Forecasting Assets on the Balance Sheet 146Forecasting Liabilities on the Balance Sheet 147Discretionary Financing Needed 149Using Iteration to Eliminate DFNOther Forecasting Methods 154151Linear Trend Extrapolation 154Adding Trend Lines to Charts 156Regression Analysis 158Statistical Significance 162Summary 165Problems 165Internet Exercisesviii167

ContentsCHAPTER 6Break-Even and Leverage Analysis 169Break-Even Points170Calculating Break-Even Points in Excel 172Other Break-Even Points 173Using Goal Seek to Calculate Break-Even PointsLeverage Analysis 175175The Degree of Operating Leverage 177The Degree of Financial Leverage 179The Degree of Combined Leverage 181Extending the Example 182Summary 184Problems 185Internet ExerciseCHAPTER 7187The Time Value of MoneyFuture Value189190Using Excel to Find Future Values 191Present Value 192Annuities 194Present Value of an Annuity 194Future Value of an Annuity 196Solving for the Annuity Payment 198Solving for the Number of Periods in an Annuity 199Solving for the Interest Rate in an Annuity 201Deferred Annuities 202Graduated Annuities 204Present Value of a Graduated Annuity 205Future Value of a Graduated Annuity 208Uneven Cash Flow Streams209Solving for the Yield in an Uneven Cash Flow Stream 211Nonannual Compounding Periods 212Continuous Compounding 214Summary 216Problems 217ix

ContentsCHAPTER 8Common Stock Valuation 221What Is Value? 222Fundamentals of Valuation 223Determining the Required Rate of Return224A Simple Risk Premium Model 225CAPM: A More Scientific Model 225Valuing Common Stocks228The Constant-Growth Dividend Discount Model 229The Two-Stage Growth Model 234The Three-Stage Growth Model 236Alternative Discounted Cash Flow ModelsThe Earnings Model 238The Free Cash Flow Model238241Relative Value Models 245Preferred Stock Valuation 247Summary 249Problems 251Internet Exercise 252CHAPTER 9Bond Valuation 255Bond Valuation256Valuing Bonds Between Coupon Dates 258Using Excel’s Advanced Bond Functions 260Bond Return Measures263Current Yield 264Yield to Maturity 264Yield to Call 266Returns on Discounted Debt Securities 268The U.S. Treasury Yield CurveBond Price Sensitivities 272270Changes in the Required Return 273Changes in Term to Maturity 275Comparing Two Bonds with Different Maturities 277Comparing Two Bonds with Different Coupon Rates 279Duration and Convexity280Duration 281Modified Duration 283x

ContentsVisualizing the Predicted Price ChangeConvexity 286Summary 288Problems 291Internet ExerciseCHAPTER 10284293The Cost of Capital 295The Appropriate “Hurdle” Rate296The Weighted Average Cost of Capital 297Determining the Weights 298WACC Calculations in Excel 299Calculating the Component Costs 300The Cost of Common Equity 301The Cost of Preferred Equity 302The Cost of Debt 302Using Excel to Calculate the Component Costs304The After-Tax Cost of Debt 304The Cost of Preferred Stock 306The Cost of Common Stock 306The Role of Flotation Costs 307Adding Flotation Costs to Our Worksheet 308The Cost of Retained Earnings 309The Marginal WACC Curve 310Finding the Break-Points 310Creating the Marginal WACC Chart 315Summary 316Problems 317Internet ExerciseCHAPTER 11319Capital Budgeting 321Estimating the Cash Flows 322The Initial Outlay 323The Annual After-Tax Operating Cash Flows 324The Terminal Cash Flow 325Estimating the Cash Flows: An Example 325Calculating the Relevant Cash FlowsMaking the Decision 333331xi

ContentsThe Payback Method 333The Discounted Payback Period 335Net Present Value 337The Profitability Index 340The Internal Rate of Return 341Problems with the IRR 342The Modified Internal Rate of Return 344Sensitivity Analysis346NPV Profile Charts 346Scenario Analysis 348The Optimal Capital Budget350Optimal Capital Budget Without Capital Rationing 351Optimal Capital Budget Under Capital Rationing 353Other Techniques 359Summary 359Problems 360CHAPTER 12Risk and Capital Budgeting 365Review of Some Useful Statistical Concepts366The Expected Value 366Measures of Dispersion 368Using Excel to Measure Risk371The Freshly Frozen Fish Company Example 371Introducing Uncertainty375Sensitivity Analysis 375Scenario Analysis 381Calculating the Expected NPV from the Scenarios 383Calculating the Variance and Standard Deviation 383Calculating the Probability of a Negative NPV 385Monte Carlo Simulation 386The Risk-Adjusted Discount Rate Method 393The Certainty-Equivalent Approach 394Summary 397Problems 399CHAPTER 13Portfolio Statistics and Diversification 403Portfolio Diversification Effectsxii404