Corporate FinanCial analysis with MiCrosoFt ® exCel This page intentionally left blank Corporate FinanCial analysis with MiCrosoFt exCel® Francis J. clauss New York Chicago San Francisco Lisbon London Madrid Mexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto Copyright © 2010 by The McGraw-Hill Companies, Inc. All rights reserved. Except as permitted under the United States Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of the publisher. ISBN: 978-0-07-162884-6 MHID: 0-07-162884-3 The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-162885-3, MHID: 0-07-162885-1. All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trademarked name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of infringement of the trademark. Where such designations appear in this book, they have been printed with initial caps. McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate training programs. To contact a representative please e-mail us at [email protected]. This publication is designed to provide accurate and authoritative information in regard to the subject matter covered. It is sold with the understanding that neither the author nor the publisher is engaged in rendering legal, accounting, or other professional service. If legal advice or other expert assistance is required, the services of a competent professional person should be sought. —From a Declaration of Principles Jointly Adopted by a Committee of the American Bar Association and a Committee of Publishers TERMS OF USE This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and to the work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use; any other use of the work is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these terms. THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the functions contained in the work will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or for any damages resulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that result from the use of or inability to use the work, even if any of them has been advised of the possibility of such damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise. Contents Preface vii Introduction: An Overview of Financial Management xi 1: Corporate Financial Statements 1 2: Analysis of Financial Statements 35 3: Forecasting Annual Revenues 69 4: Turning Points in Financial Trends 123 5: Forecasting Financial Statements 167 6: Forecasting Seasonal Revenues 185 7: The Time Value of Money 213 8: Cash Budgeting 251 9: Cost of Capital 291 10: Profit, Break-Even, and Leverage 317 11: Depreciation and Taxes 343 vi ❧ Contents 12: Capital Budgeting: The Basics 363 13: Capital Budgeting: Applications 401 14: Capital Budgeting: Risk Analysis with Scenarios 435 15: Capital Budgeting: Risk Analysis with Monte Carlo Simulation 455 Epilogue 485 Index 489 Preface In today’s global economies, spreadsheets have become a multinational language. They are the tools of choice for analyzing data and communicating information across the boundaries that separate nations. They have become an important management tool for developing strategies and assessing results. Spreadsheets have also become an important tool for teaching and learning. They have been widely adopted in colleges and universities. They have the advantage of being interactive, which makes them ideal for teaching on the Internet as well as self-learning at home. Corporate Financial Analysis with Microsoft Excel teaches both financial management and spread- sheet programming. Chapters are organized according to the essential topics of financial management, beginning with corporate financial statements. The text discusses management principles and provides clear, step-by-step instructions for using spreadsheets to apply them. It shows how to use spreadsheets for analyzing financial data and for communicating results in well-labeled tables and charts. It shows how to be better managers and decision makers, not simply skilled spreadsheet programmers. The text assumes no more knowledge of computers and spreadsheets than how to turn a computer on, how to use a mouse, and how to perform the arithmetic operations of addition, subtraction, multipli- cation, and division. The first chapter begins with instructions for such basic spreadsheet actions as enter- ing text and data, using cell references to express the relationships between items on spreadsheets and to calculate values, editing and formatting entries, and so forth. By the end of the text, the reader will have a viii ❧ Preface working knowledge of a variety of financial functions available in Excel for such things as the time value of money and the payoffs of capital investments. He or she will also know how to use Excel’s powerful tools for forecasting, doing sensitivity analysis, optimizing decisions, and using Monte Carlo simulation to evaluate risks. In short, anyone who studies the text will acquire a toolbox of spreadsheet skills that will help him or her understand and apply the principles of financial management—and be better prepared for a successful career in the business world. Models Rather Than Solutions Corporate Financial Analysis with Microsoft Excel shows how to create models that provide realistic infor- mation. Unlike pocket calculators, which are limited in their output, spreadsheet models can supply solu- tions over a wide range of conditions and assumptions. Models help identify what must be done to achieve desired results, determine the best strategies and tactics for maximizing profits or minimizing losses, iden- tify conditions that must be avoided, or prepare for what might happen. Learning from models is cheaper, faster, and less hazardous than learning from real life. Spreadsheet models make this possible. Managing Risks Global competition puts a premium on the ability to handle risk. Although it may not appear as a separate item in a CFO’s job description, risk assessment underlies all financial decisions. Risk is a high-stakes game of “What if?” analysis. Corporate Financial Analysis with Microsoft Excel shows how to use Monte Carlo simulation and other spreadsheet tools to gamble like a professional—without the cost. A bit of intelligent programming is the only ante needed to play the game. Spreadsheets help define the risks due to uncertain customer demands, the ups and downs of business cycles, changes by competitors, and other conditions outside a manager’s control. In place of expensive experiments or learning in the school of hard knocks, you can use spreadsheet models to assess the risks and impacts of contemplated actions without actually taking them. Teamwork Increased worldwide competition and a market-driven economy have forced corporations to restructure their functional hierarchies in ways that promote teamwork. Rigid hierarchies that once divided finance, marketing, production, quality control, and other business functions are disappearing. In their place, functions and responsibilities are being shared in tighter alliances between areas of specialization. These changes extend outside corporate walls to subcontractors and suppliers. The Enabling Role of IT Information technology (IT) is the essential tool that enables a corporation to think globally and act locally. IT is the backbone of today’s management information systems that corporations use to achieve higher levels of teamwork. Spreadsheets, databases, and special software are the “nuts and bolts” of ERP and other systems that link computer networks and telecommunication systems and that create extended teams. Preface ❧ ix Better Than Algebra Most students are already familiar with spreadsheets by the time they enter college or complete their fresh- men year. It is safe to say they understand the basic principles of spreadsheets better than those of algebra. Row and column labels transform the values in a spreadsheet’s cells into concrete concepts rather than the abstract notations of algebraic formulas. They help one visualize the logical relationships between vari- ables much better than equations with Xs and Ys. Spreadsheets simply provide a better way than algebra to learn any subject that involves understanding numbers. Communicating Spreadsheets are used to prepare tables and charts for making presentations that can be easily understood by others and that justify recommended courses of action. Spreadsheets are much more than sophisticated calculators. They are “digital storytellers” that can help you get your message across to others. A Proven Text Corporate Financial Analysis with Microsoft Excel is the result of the author’s use of spreadsheets for teach- ing financial management over a four-year period. Classes have been conducted at both the graduate and undergraduate level. The text has been used for teaching in a classroom as well as for distance-learning on the Internet (via the CyberCampus system at Golden Gate University in San Francisco). Skills Pay the Bills Students have found that spreadsheets make learning easier and enhance their understanding of the complexities of financial management. The spreadsheet skills they have acquired have helped many of the author’s students gain employment and earn raises and promotions. That is the success story related by numerous students who have studied Corporate Financial Analysis with Microsoft Excel and applied its teachings. Understanding Spreadsheets are outstanding pedagogical tools for both teaching and learning. They are akin to the popu- lar Sudoku puzzles in having an arrangement of columns and rows. Like Sudoku puzzles, spreadsheets teach an understanding of the logical relationships between cell entries. Of course, a spreadsheet for a company’s financial statements, or its month-to-month cash budget, or the projected cash inflows and out- flows of expansions of corporate facilities is much larger and complex than a Sudoku grid. Students in the author’s classes have repeatedly stated that financial modeling with spreadsheets helps them understand much better the inner workings of corporations and the strategies and tactics of business management for operating in worldwide markets. The interactive feature of spreadsheets, with immediate feedback for the results of their decisions in creating and using models, has provided challenges that keep students actively engaged in the process of learning. After more than half a century in the business and educational fields, the author finds spreadsheets to be a most useful pedagogical tool. Student response confirms that belief.