Using Excel for Business Analysis E-Book

Danielle Stein Fairhurst

• Herausgeber: John Wiley & Sons
• Kategorie: Fachliteratur
• Serie: Wiley Finance Editions
• Sprache: Englisch

Seitenzahl: 468

Veröffentlichungsjahr: 2012

Beschreibung

A clear, concise, and easy-to-use guide to financial modelling suitable for practitioners at every level Using a fundamental approach to financial modelling that's accessible to both new and experienced professionals, Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals + Website offers practical guidance for anyone looking to build financial models for business proposals, to evaluate opportunities, or to craft financial reports. Comprehensive in nature, the book covers the principles and best practices of financial modelling, including the Excel tools, formulas, and functions to master, and the techniques and strategies necessary to eliminate errors. As well as explaining the essentials of financial modelling, Using Excel for Business Analysis is packed with exercises and case studies to help you practice and test your comprehension, and includes additional resources online. * Provides comprehensive coverage of the principles and best practices of financial modeling, including planning, how to structure a model, layout, the anatomy of a good model, rebuilding an inherited model, and much more * Demonstrates the technical Excel tools and techniques needed to build a good model successfully * Outlines the skills you need to learn in order to be a good financial modeller, such as technical, design, and business and industry knowledge * Illustrates successful best practice modeling techniques such as linking, formula consistency, formatting, and labeling * Describes strategies for reducing errors and how to build error checks and other methods to ensure accurate and robust models A practical guide for professionals, including those who do not come from a financial background, Using Excel for Business Analysis is a fundamentals-rich approach to financial modeling.

Leseprobe

Contents

Preface

Chapter 1: What is Financial Modelling?

What’s the Difference between a Spreadsheet and a Financial Model?

Types and Purposes of Financial Models

Tool Selection

What Skills Do you Need to Be a Good Financial Modeller?

The Ideal Financial Modeller

Summary

Chapter 2: Building a Model

Model Design

The Golden Rules for Model Design

Design Issues

The Workbook Anatomy of a Model

Project Planning Your Model

Model Layout Flow Charting

Steps to Building a Model

Information Requests

Version-Control Documentation

Summary

Chapter 3: Best Practice Principles of Modelling

Document Your Assumptions

Linking, Not Hard Coding

Only Enter Data Once

Avoid Bad Habits

Use Consistent Formulas

Format and Label Clearly

Methods and Tools of Assumptions Documentation

Linked Dynamic Text Assumptions Documentation

What Makes a Good Model?

Summary

Chapter 4: Financial Modelling Techniques

The Problem with Excel

Error Avoidance Strategies

How Long Should a Formula Be?

Linking to External Files

Building Error Checks

Avoid Error Displays in Formulas

Circular References

Summary

Chapter 5: Using Excel in Financial Modelling

Formulas and Functions in Excel

Excel Versions

Handy Excel Shortcuts

Basic Excel Functions

Logical Functions

Nesting: Combining Simple Functions to Create Complex Formulas

Cell Referencing Best Practices

Named Ranges

Summary

Chapter 6: Functions for Financial Modelling

Aggregation Functions

LOOKUP Formulas

Other Useful Functions

Working with Dates

Financial Project Evaluation Functions

Loan Calculations

Summary

Chapter 7: Tools for Model Display

Basic Formatting

Custom Formatting

Conditional Formatting

Sparklines

Bulletproofing Your Model

Customising the Display Settings

Form Controls

Summary

Chapter 8: Tools for Financial Modelling

Hiding Sections of a Model

Grouping

Array Formulas

Goal Seeking

Pivot Tables

Macros

User-Defined Functions (UDFs)

Summary

Chapter 9: Common Uses of Tools in Financial Modelling

Escalation Methods for Modelling

Understanding Nominal and Effective (Real) Rates

Calculating Cumulative Totals

How to Calculate a Payback Period

Weighted Average Cost of Capital (WACC)

Building a Tiering Table

Modelling Depreciation Methods

Break-Even Analysis

Summary

Chapter 10: Model Review

Rebuilding an Inherited Model

Auditing a Financial Model

Appendix 10.1: QA Log

Summary

Chapter 11: Stress-Testing, Scenarios, and Sensitivity Analysis in Financial Modelling

What’s the Difference between Scenario, Sensitivity, and What-If Analysis?

Overview of Scenario Analysis Tools and Methods

Advanced Conditional Formatting

Comparing Scenario Methods

Summary

Chapter 12: Presenting Model Output

Preparing an Oral Presentation for Model Results

Preparing a Graphic or Written Presentation for Model Results

Chart Types

Working with Charts

Handy Charting Hints

Dynamic Range Names

Charting with Two Different Axes and Chart Types

Bubble Charts

Waterfall Charts

Summary

About the Author

About the Website

Index

Founded in 1807, John Wiley & Sons is the oldest independent publishing company in the United States. With offices in North America, Europe, Australia, and Asia, Wiley is globally committed to developing and marketing print and electronic products and services for our customers’ professional and personal knowledge and understanding.

The Wiley Finance series contains books written specifically for finance and investment professionals, as well as sophisticated individual investors and their financial advisors. Book topics range from portfolio management to e-commerce, risk management, financial engineering, valuation, and financial instrument analysis, as well as much more.

For a list of available titles, please visit our website at www.WileyFinance.com.

Copyright © 2012 John Wiley & Sons Singapore Pte. Ltd.

Published in 2012 by John Wiley & Sons Singapore Pte. Ltd. 1 Fusionopolis Walk, #07-01, Solaris South Tower, Singapore 138628

All rights reserved.

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as expressly permitted by law, without either the prior written permission of the Publisher, or authorization through payment of the appropriate photocopy fee to the Copyright Clearance Center. Requests for permission should be addressed to the Publisher, John Wiley & Sons (Asia) Pte. Ltd., 1 Fusionopolis Walk, #07-01, Solaris South Tower, Singapore 138628, tel: [phone], fax: [phone], e-mail: [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 the Publisher is not engaged in rendering professional services. If professional advice or other expert assistance is required, the services of a competent professional person should be sought. Neither the author nor the publisher is liable for any actions prompted or caused by the information presented in this book. Any views expressed herein are those of the author and do not represent the views of the organizations he works for.

Microsoft and Excel are registered trademarks of Microsoft Corporation.

Other Wiley Editorial Offices

John Wiley & Sons, 111 River Street, Hoboken, NJ 07030, USA

John Wiley & Sons, The Atrium, Southern Gate, Chichester, West Sussex, P019 8SQ, United Kingdom

John Wiley & Sons (Canada) Ltd., 5353 Dundas Street West, Suite 400, Toronto, Ontario, M9B 6HB, Canada

John Wiley & Sons Australia Ltd., 42 McDougall Street, Milton, Queensland 4064, Australia

Wiley-VCH, Boschstrasse 12, D-69469 Weinheim, Germany

978-1-118-13284-5 (Paper)

978-1-118-13285-2 (ePDF)

978-1-118-13286-9 (Mobi)

978-1-118-13287-6 (ePub)

For Mike, of course.

Preface

This book was written from my course materials compiled over many years of training in analytical courses in Australia and globally—most frequently courses such as Financial Modelling in Excel, Data Analysis & Reporting in Excel, and Budgeting & Forecasting in Excel, both as face-to-face workshops and online courses. The common theme is the use of Microsoft Excel, and I’ve refined the content to suit the hundreds of participants and their questions over the years. This content has been honed and refined by the many participants on these courses, who are my intended readers. This book is aimed at you, the many people who seek financial analysis training (either by attending a seminar or self-paced by reading this book) because you are seeking to improve your skills to perform better in your current role, or get a new and better job.

When I started financial modelling in the early nineties, it was not called financial modelling—it was just “Using Excel for Business Analysis,” and this is what I’ve called this book. It was only just after the new millennium that the term financial modelling gained popularity in its own right and became a required skill often listed on analytical job descriptions. This book spends quite a bit of time in Chapter 1 defining the meaning of a financial model as it’s often thought to be something that is far more complicated than it actually is. Many analysts I’ve met are building financial models already without realising it, but they do themselves a disservice by not calling their models, “models”!

However, those who are already building financial models are not necessarily following good modelling practice as they do so. Chapter 3 is dedicated to the principles of best modelling practice, which will save you a lot of time, effort, and anguish in the long run. Many of the principles of best practice are for the purpose of reducing the possibility of error in your model, and there is a whole section on strategies for reducing error in Chapter 4.

The majority of Excel users are self-taught, and therefore many users will often know highly advanced Excel tools, yet fail to understand how to use them in the context of building a financial model. This book is very detailed, so feel free to skip sections you already know. Because of the comprehensive nature of the book, much of the detailed but less commonly used content, such as instructions for the older Excel 2003 users, has been moved to the companion website at www.wiley.com/go/steinfairhurst. References to the content on the website, and many cross-references to other sections of the book, can be found throughout the manuscript.

BOOK OVERVIEW

This book has 12 chapters, but these can be grouped into three parts. Whilst they do follow on from each other with the most basic concepts at the beginning, feel free to jump directly to any of the parts. The first section—Chapters 1 to 3—addresses the least technical topics about financial modelling in general, such as tool selection, model design, and best practice.

The second section—Chapters 4 to 8—is extremely practical and hands-on. Here I have outlined all of the tools, techniques, and functions in Excel that are commonly used in financial modelling. Of course it does not cover everything Excel can do, but it covers the “must-know” tools.

The third section—Chapters 9 to 12—is the most important in my view. This covers the use of Excel in financial modelling and analysis. This is really where the book differs from other “how-two” Excel books. Chapter 9 covers some commonly used techniques in modelling, such as escalation, tiering tables, and depreciation—how to actually use Excel tools for something useful! Chapter 11 covers the several different methods of performing scenarios and sensitivity analysis (basically the whole point of financial modelling to my mind!). Lastly, Chapter 12 covers the often-neglected task of presenting model output. Many modellers spend days or weeks on the calculations and functionality, but fail to spend just a few minutes or hours on charts, formatting, and layout at the end of the process, even though this is what the user will see, interact with, and eventually use to judge the usefulness of the model.

ACKNOWLEDGEMENTS

This book would not have been written had it not been for the many people who have attended my training sessions, participated in online courses, and contributed to the forums. Your continual feedback and enthusiasm for the subject inspired me to write this book and it was through you that I realised how much a book like this was needed.

The continued support of my family made this project possible. In particular, Mike my husband for his unconditional commitment and to whom this book is dedicated, my children who give me such joy, as well as my remarkable parents and siblings who have always inspired and encouraged me without question. I would like to give a special thanks to my ever-patient assistant Susan Wilkin for her dedication and diligence throughout the project, Kurt Alexander for his steadfast enthusiasm, and to Joe Porteus for keeping me on the right track.

I hope you find the book both useful and enjoyable. Happy modelling!

CHAPTER 1

What Is Financial Modelling?

There are all sorts of complicated definitions of financial modelling, and in my experience there is quite a bit of confusion around what a financial model is exactly. A few years ago, we put together a Plum Solutions survey about the attitudes, trends, and uses of financial modelling, asked respondents “What do you think a financial model is?” Participants were asked to put down the first thing that came to mind, without any research or too much thinking about it. I found the responses interesting, amusing, and sometimes rather disturbing.

Some answers were overly complicated and highly technical:

“Representation of behaviour/real-world observations through mathematical approach designed to anticipate range of outcomes.”

“A set of structured calculations, written in a spreadsheet, used to analyse the operational and financial characteristics of a business and/or its activities.”

“Tool(s) used to set and manage a suite of variable assumptions in order to predict the financial outcomes of an opportunity.”

“A construct that encodes business rules, assumptions, and calculations enabling information, analysis, and insight to be drawn out and supported by quantitative facts.”

“A system of spreadsheets and formulas to achieve the level of record keeping and reporting required to be informed, up-to-date, and able to track finances accurately and plan for the future.”

Some philosophical:

“A numerical story.”

Some incorrect:

“Forecasting wealth by putting money away now/investing.”

“It is all about putting data into a nice format.”

“It is just a mega huge spreadsheet with fancy formulas that are streamlined to make your life easier.”

Some ridiculous:

“Something to do with money and fashion?”

Some honest:

“I really have no idea.”

And some downright profound:

“A complex spreadsheet.”

Whilst there are many other (often very complicated and long-winded) definitions available from different sources, but I actually prefer the last, very broad, but accurate description: “a complex spreadsheet.” Whilst it does need some definition, a financial model can pretty much be whatever you need it to be.

As long as a spreadsheet has inputs and outputs, and is dynamic and flexible—I’m happy to call it a financial model! Pretty much the whole point of financial modelling is that you change the inputs and the outputs. This is the major premise behind scenario and sensitivity analysis—this is what Excel, with its algebraic logic, was made for! Most of the time, a model will contain financial information and serve the purpose of making a financial decision, but not always. Quite often it will contain a full set of financial statements: profit and loss, cash flow, and balance sheet; but not always.

According to the more staid or traditional definitions of financial modelling, the following items would all most certainly be classified as financial models:

A business case that determines whether or not to go ahead with a project.

A five-year forecast showing profit and loss, cash flow, and balance sheet.

Pricing calculations to determine how much to bid for a new tender.

Investment analysis for a joint venture.

But what about other pieces of analysis that we perform as part of our roles? Can these also be called financial models? What if something does not contain financial information at all? Consider if you were to produce a spreadsheet for the following purposes: