Financial Modeling for Decision Making

Using MS-Excel in Accounting and Finance

Ron Messer

• Publisher: Emerald Publishing Limited
• Year: 2020
• ISBN:
  9781789734157
• Topic: Business
• Subtopic: Financial Accounting
• Index: Business

Most books on Microsoft Excel for accounting and finance serve primarily academic research purposes: they are relatively unstructured and dominated by discussions of rather esoteric and infrequently used applications. None addresses the growing need for an introduction to crucial data analytic skills (i.e., analysis of 'big data') and to the recent innovations by Microsoft Excel in this regard, including Power Query (data cleaning and management), Power Pivot (advanced pivot tables using databases) and Power BI (creating executive KPI dashboards).
In Financial Modeling for Decision Making: Using MS-Excel in Accounting and Finance, Ron Messer provides just such a practical, advanced-level guide to this essential program. Crucially, he focuses on using Excel in situations encountered by accounting and finance students and professionals, and he structures these in terms of the past, present and future in order to reflect a typical operating cycle, which includes initial planning, exercising control, and receiving feedback. Throughout this book, Messer discusses real-life applications of this important analytical tool, which has become the industry standard for spreadsheet software.
For its clear structure and emphasis on practical usage, Financial Modeling for Decision Making is essential reading for accounting and finance professionals, as well as accounting and finance students in post-secondary institutions.

Introduction

Chapter 1

Better Learning Decisions

So, what is this book about?

This book is about financial modeling. It deals with the types of business decisions made by accounting and finance students and professionals. The book’s purpose is to teach advanced Excel techniques. All employers value these skills in their staff, and it is expected that the material covered will enhance students' employment prospects and the career advancement of professionals.

Works of this genre are typically part of a management science curriculum and consequently are focused on some topics that Accounting and Finance practitioners do not consider relevant in their day-to-day routine—such as Monte Carlo simulations, using real options and integer programming. Different from many books that explain how Excel is used for decision-making, this text focuses specifically on Accounting and Finance department applications. Each of the decision contexts explored is based on the personal experience of the author, whose employment history includes the following areas of financial management:

• Budget development and management
• Product and service costing and pricing
• Cash management
• Financial reporting
• External and internal auditing
• Evaluating information systems risk and controls
• Enterprise risk management
• Personal and corporate taxation
• Corporate finance
• Capital budgeting
• Financial systems development and implementation
• Developing financial policies and procedures

By using this book, students and professionals will develop an expert-level proficiency with Excel and apply these skills to problems in cost accounting, capital budgeting and linear programming. Statistical techniques, such as single- and multivariable regression analysis and forecasting, will also be discussed. Students will learn to work with pivot tables applied to data mining and analysis situations. They will also develop pro forma financial statements and build a discounted cash flow model for purposes of valuing a company. In addition, Excel will be used to create a budget, perform variance analysis, and develop key performance indicators (KPIs), along with executive dashboards. Also addressed will be several common amortization tables used for decision-making.

How to Learn (and Teach) Financial Modeling

Learning Financial Modeling

A common lament heard from those taking Excel training courses is that they thought that they had learned some important techniques but, because they did not put them into practice, the knowledge acquired was soon forgotten. It is not surprising therefore that many people learning how to use the software repeat the same—or similar courses—year after year, which is both expensive and inefficient. This occurs because Excel courses usually focus on either (1) functionality or (2) application, but seldom both. Functional courses address the technical aspects of the software, such as how to create pivot tables. Application courses address the use of Excel for specific problems; for example, preparing a capital budgeting model. Uniquely, this text combines both of these aspects, integrating functionality and application; it explains Excel functions as well as their application to common business problems.

The book is purposefully structured to address common weaknesses in how MS-Excel is taught—to this end it emphasizes being useful to those operating a business. When I worked as a financial systems analyst, one of the most important things I learned was that software should never drive an organization’s operations; business operations are primary and must always dictate computerized functionality. This means that the starting point for learning how to use Excel should not be the application itself, but rather the decisions made within the business—such as whether to invest in capital assets. It is for this reason that the text first addresses common business decisions, then explains the underlying management theory used in making these decisions and finally proceeds to show how Excel can be employed to decide on a course of action. By taking this approach it turns the traditional “how to teach Excel” approach on its head and hopefully puts end users (and not techies) in charge of the learning process.¹

For each decision-making context (future, present, and past), a topic is explored in terms of its primary subject area and the types of decisions made. Real-life business situations are described, which are then explored in terms of the underlying theory and financial management techniques used. To facilitate decision-making, the requisite Excel functionality is demonstrated through a detailed exercise.

The chapters follow a consistent format, with examples of real-life Excel applications and include these parts:

• Snapshot: A chapter overview for each decision context, outlining the (1) topic, (2) subject area, (3) decisions made, (4) author’s experience, and (5) relevant Excel functionality for building the financial model.
• Background theory: As this book addresses both Excel functionality and its application to decision-making, an overview of the relevant accounting and finance theory will be provided—for example, a discussion of discounted cash flows and using the weighted average cost of capital (WACC) when making capital budgeting decisions.
• Financial management techniques: The section will address the formulas used in decision–making—for example, the calculation of the break-even point in unit sales is: Fixed costs ÷ unit contribution margin.
• Using Excel functionality: The key Excel functions, including formulas, for each decision-making context are discussed.
• Demonstration Exercise: A detailed, guided exercise on using Excel to develop a financial model for decision-making is shown.
• Afterthought: The author provides some—hopefully, thought-provoking—commentary on the chapter topic as food for thought.

Teaching Financial Modeling

Teaching computer applications is very different from the instruction that takes place during a standard lecture session. It is challenging because a happy medium needs to be achieved between speaking and hands-on practice. As a heuristic, I use a 50/50 ratio—i.e., I spend about half of the class tim...