Financial analysis with microsoft excel / Timothy R. Mayes, Metropolitan State College of Denver, Todd M. Shank, University of South Florida - St. Petersburg.

Cover title: Financial Analysis with MicrosoftŸcel{u0CB0}13.

Includes index.

Machine generated contents note: ch. 1 Introduction to Excel 2013 -- Spreadsheet Uses -- Starting Microsoft Excel -- Parts of the Excel Screen -- The File Tab and Quick Access Toolbar -- The Home Tab -- The Formula Bar -- The Worksheet Area -- Sheet Tabs -- Status Bar -- Navigating the Worksheet -- Selecting a Range of Cells -- Using Defined Names -- Entering Text and Numbers -- Formatting and Alignment Options -- Formatting Numbers -- Adding Borders and Shading -- Entering Formulas -- Copying and Moving Formulas -- Mathematical Operators -- Parentheses and the Order of Operations -- Using Excel's Built-In Functions -- Using the Insert Function Dialog Box -- 'Dot Functions' in Excel 2013 -- Using User-Defined Functions -- Creating Graphics -- Creating Charts in a Chart Sheet -- Creating Embedded Charts -- Formatting Charts -- Changing the Chart Type -- Creating Sparkline Charts -- Printing -- Using Excel with Other Applications -- Quitting Excel -- Best Practices for Spreadsheet Models -- Summary -- Problems -- Internet Exercise -- ch. 2 The Basic Financial Statements -- The Income Statement -- Building an Income Statement in Excel -- The Balance Sheet -- Building a Balance Sheet in Excel -- Improving Readability: Custom Number Formats -- Common-Size Financial Statements -- Creating Common-Size Income Statements -- Creating a Common-Size Balance Sheet -- Building a Statement of Cash Flows -- Using Excel's Outliner -- Common-Size Statement of Cash Flows -- Summary -- Problems -- Internet Exercise -- ch. 3 The Cash Budget -- The Worksheet Area -- Using Date Functions -- Calculating Text Strings -- Sales and Collections -- Purchases and Payments -- Collections and Disbursements -- Calculating the Ending Cash Balance -- Repaying Short-Term Borrowing -- Using the Cash Budget for What-If Analysis -- The Scenario Manager -- Adding Interest and Investment of Excess Cash -- Calculating Current Borrowing -- Using the Formula Auditing Tools to Avoid Errors -- Calculating Current Investing -- Working Through the Example -- Summary -- Problems -- ch. 4 Financial Statement Analysis Tools -- Liquidity Ratios -- The Current Ratio -- The Quick Ratio -- Efficiency Ratios -- Inventory Turnover Ratio -- Accounts Receivable Turnover Ratio -- Average Collection Period -- Fixed Asset Turnover Ratio -- Total Asset Turnover Ratio -- Leverage Ratios -- The Total Debt Ratio -- The Long-Term Debt Ratio -- The Long-Term Debt to Total Capitalization Ratio -- The Debt to Equity Ratio -- The Long-Term Debt to Equity Ratio -- Coverage Ratios -- The Times Interest Earned Ratio -- The Cash Coverage Ratio -- Profitability Ratios -- The Gross Profit Margin -- The Operating Profit Margin -- The Net Profit Margin -- Return on Total Assets -- Return on Equity -- Return on Common Equity -- DuPont Analysis -- Analysis of EPI's Profitability Ratios -- Financial Distress Prediction -- The Original Z-Score Model -- The Z-Score Model for Private Firms -- Using Financial Ratios -- Trend Analysis -- Comparing to Industry Averages -- Company Goals and Debt Covenants -- Automating Ratio Analysis -- Economic Profit Measures of Performance -- Summary -- Problems -- Internet Exercise -- ch. 5 Financial Forecasting -- The Percent of Sales Method -- Forecasting the Income Statement -- Forecasting Assets on the Balance Sheet -- Forecasting Liabilities on the Balance Sheet -- Discretionary Financing Needed -- Using Iteration to Eliminate DFN -- Other Forecasting Methods -- Linear Trend Extrapolation -- Regression Analysis -- Statistical Significance -- Summary -- Problems -- Internet Exercises -- ch. 6 Break-Even and Leverage Analysis -- Break-Even Points -- Calculating Break-Even Points in Excel -- Other Break-Even Points -- Using Goal Seek to Calculate Break-Even Points -- Leverage Analysis -- The Degree of Operating Leverage -- The Degree of Financial Leverage -- The Degree of Combined Leverage -- Extending the Example -- Linking Break-Even Points and Leverage Measures -- Summary -- Problems -- Internet Exercise -- ch. 7 The Time Value of Money -- Future Value -- Using Excel to Find Future Values -- Present Value -- Annuities -- Present Value of an Annuity -- Future Value of an Annuity -- Solving for the Annuity Payment -- Solving for the Number of Periods in an Annuity -- Solving for the Interest Rate in an Annuity -- Deferred Annuities -- Graduated Annuities -- Present Value of a Graduated Annuity -- Future Value of a Graduated Annuity -- Uneven Cash Flow Streams -- Solving for the Yield in an Uneven Cash Flow Stream -- Nonannual Compounding Periods -- Continuous Compounding -- Summary -- Problems -- ch. 8 Common Stock Valuation -- What Is Value? -- Fundamentals of Valuation -- Determining the Required Rate of Return -- A Simple Risk Premium Model -- CAPM: A More Scientific Model -- Valuing Common Stocks -- The Constant-Growth Dividend Discount Model -- The Two-Stage Growth Model -- Three-Stage Growth Models -- Alternative Discounted Cash Flow Models -- The Earnings Model -- The Free Cash Flow Model -- Relative Value Models -- Preferred Stock Valuation -- Summary -- Problems -- Internet Exercise -- ch. 9 Bond Valuation -- Bond Valuation -- Valuing Bonds Between Coupon Dates -- Using Excel's Advanced Bond Functions -- Bond Return Measures -- Current Yield -- Yield to Maturity -- Yield to Call -- Returns on Discounted Debt Securities -- The U.S. Treasury Yield Curve -- Bond Price Sensitivities -- Changes in the Required Return -- Changes in Term to Maturity -- Comparing Two Bonds with Different Maturities -- Comparing Two Bonds with Different Coupon Rates -- Duration and Convexity -- Duration -- Modified Duration -- Visualizing the Predicted Price Change -- Convexity -- Summary -- Problems -- Internet Exercise -- ch. 10 The Cost of Capital -- The Appropriate'Hurdle' Rate -- The Weighted Average Cost of Capital -- Determining the Weights -- WACC Calculations in Excel -- Calculating the Component Costs -- The Cost of Common Equity -- The Cost of Preferred Equity -- The Cost of Debt -- Using Excel to Calculate the Component Costs -- The After-Tax Cost of Debt -- The Cost of Preferred Stock -- The Cost of Common Stock -- The Role of Flotation Costs -- Adding Flotation Costs to Our Worksheet -- The Cost of Retained Earnings -- The Marginal WACC Curve -- Finding the Break-Points -- Creating the Marginal WACC Chart -- Summary -- Problems -- Internet Exercise -- ch. 11 Capital Budgeting -- Estimating the Cash Flows -- The Initial Outlay -- The Annual After-Tax Operating Cash Flows -- The Terminal Cash Flow -- Estimating the Cash Flows: An Example -- Calculating the Relevant Cash Flows -- Making the Decision -- The Payback Method -- The Discounted Payback Period -- Net Present Value -- The Profitability Index -- The Internal Rate of Return -- Problems with the IRR -- The Modified Internal Rate of Return -- Sensitivity and Scenario Analysis -- NPV Profile Charts -- Scenario Analysis -- The Optimal Capital Budget -- Optimal Capital Budget Without Capital Rationing -- Optimal Capital Budget Under Capital Rationing -- Other Techniques -- Summary -- Problems -- ch. 12 Risk and Capital Budgeting -- Review of Some Useful Statistical Concepts -- The Expected Value -- Measures of Dispersion -- Using Excel to Measure Risk -- The Freshly Frozen Fish Company Example -- Introducing Uncertainty -- Sensitivity Analysis -- Scenario Analysis -- Calculating the Expected NPV from the Scenarios -- Calculating the Variance and Standard Deviation -- Calculating the Probability of a Negative NPV -- Monte Carlo Simulation -- The Risk-Adjusted Discount Rate Method -- The Certainty-Equivalent Approach -- Summary -- Problems -- ch.

13 Portfolio Statistics and Diversification -- Portfolio Diversification Effects -- Determining Portfolio Risk and Return -- Portfolio Standard Deviation -- Changing the Weights -- Portfolios with More than Two Securities -- Creating a Variance/Covariance Matrix -- Calculating the Portfolio Standard Deviation -- The Efficient Frontier -- Locating Portfolios on the Efficient Frontier in Excel -- Charting the Efficient Frontier -- The Capital Market Line -- Charting the Capital Market Line -- Identifying the Market Portfolio -- Utility Functions and the Optimal Portfolio -- Charting Indifference Curves -- The Capital Asset Pricing Model -- The Security Market Line -- Summary -- Problems -- Internet Exercise -- ch. 14 Writing User-Defined Functions with VBA -- What Is a Macro? -- Two Types of Macros -- The Visual Basic Editor -- The Project Explorer -- The Code Window -- The Parts of a Function -- Writing Your First User-Defined Function -- Writing More Complicated Functions -- Variables and Data Types -- The If-Then-Else Statement -- Looping Statements -- Using Worksheet Functions in VBA -- Using Optional Arguments -- Using ParamArray for Unlimited Arguments -- Debugging VBA Code -- Breakpoints and Code Stepping -- The Watch Window -- The Immediate Window -- Creating Excel Add-Ins -- Best Practices for VBA -- Summary -- Problems -- ch. 15 Analyzing Datasets with Tables and Pivot Tables -- Creating and Using an Excel Table -- Removing Duplicate Records from the Table -- Filtering the Table -- Sorting and Filtering Numeric Fields -- Using Formulas in Tables -- Using Pivot Tables -- Creating a Pivot Table