Prelims

Financial Modeling for Decision Making

Financial Modeling for Decision Making: Using MS-Excel in Accounting and Finance

Ron Messer

Kwantlen Polytechnic University, Canada

United Kingdom – North America – Japan – India – Malaysia – China

Emerald Publishing Limited

Howard House, Wagon Lane, Bingley BD16 1WA, UK

First edition 2020

Copyright © 2020 Emerald Publishing Limited.

Reprints and permissions service

Contact: permissions@emeraldinsight.com

No part of this book may be reproduced, stored in a retrieval system, transmitted in any form or by any means electronic, mechanical, photocopying, recording or otherwise without either the prior written permission of the publisher or a licence permitting restricted copying issued in the UK by The Copyright Licensing Agency and in the USA by The Copyright Clearance Center. Any opinions expressed in the chapters are those of the authors. Whilst Emerald makes every effort to ensure the quality and accuracy of its content, Emerald makes no representation implied or otherwise, as to the chapters' suitability and application and disclaims any warranties, express or implied, to their use.

British Library Cataloguing in Publication Data

A catalogue record for this book is available from the British Library

ISBN: 978-1-78973-414-0 (Print)

ISBN: 978-1-78973-413-3 (Online)

ISBN: 978-1-78973-415-7 (Epub)

I dedicate this book to the memory of my late brother, Martin Oscar Messer, who left this world much too soon.

Ron Messer is a faculty member of the School of Business at Kwantlen Polytechnic University whose research interests focus broadly on management accounting and decision making. A graduate of six universities, Ron holds undergraduate and graduate degrees in both public and business administration. He is a CPA, a Chartered Accountant, and a Certified Management Accountant with 25 years of working experience in accounting, finance, and information systems. His essays have appeared in publications in Canada, the United States, and the United Kingdom.

Ron Messer's primary areas of interest include organizational strategy, financial planning, and supply chain management (with an emphasis on logistics). To this end, he has published in peer-reviewed academic journals (14 essays), industry-specific journals (six articles), chapters in authoritative texts (four chapters in four books), and business case studies (two teaching cases).

Based on his strategy research, Ron developed a method for charging airport landing and terminal fees that optimizes the social welfare for both the traveling public and private sector operators. In conjunction with this work, Ron also proposed a pricing framework for private sector airports. With respect to the public sector, he suggested a renewed focus on organizational vision, whereby the government adopts private sector values in the delivery of its services.

In the area of financial planning, Ron proposed a budgeting method that eliminates many of the problems encountered when companies prepare their annual financial plan. He also created a process for identifying budget “cheaters,” which allows for the creation of more realistic business plans and avoids the inefficient allocation of corporate capital.

In an essay written almost two decades ago (“Airports, Air Cargo and the Internet”), Ron outlines the possible effects of e-commerce on airports as logistics hubs in the supply chain. In this paper, Ron predicts that Internet-enabled business models will lead to increased air cargo and possible on-airport manufacturing facilities. Recent evidence surrounding the development at Indian airports (with the production of off-patent pharmaceuticals), the United Parcel Services aviation group (servicing laptop computers), and the many “fulfillment” centers under construction at, or near, airports (Amazon) provides evidence to support this hypothesis.

Ron Messer's essays on supply chain management and logistics describe the impact of e-commerce on aviation globally. In the area of organizational strategy, Ron has proposed a way in which university business programs can distinguish themselves and become world-class institutions.

Overall, Ron's research is helping to develop strategies so that governments can deliver public services more effectively, including airports operating in deregulated environments. His work is influencing the way private and public institutions operate by proposing efficient methods that are globally relevant.

Ron recently published a business case that was used as part of a competition for management accounting students across the United States, along with teaching notes. He has also completed a paper that discusses enhanced teaching methods for management accounting. His most ambitious project to date is examining decision making in speculative markets to understand the underlying mechanisms that govern participant behaviors. This involves analyzing large data sets and developing several heuristic models.

Check out Ron online at:

LinkedIn: https://www.linkedin.com/in/ron-messer-464878b6/?ppe=1

and

Google Scholar: https://scholar.google.ca/citations?user=K95HjQMAAAAJ&hl=en

This book was written with two objectives in mind:

1. To be useful for making business decisions.

2. To show how MS-Excel can be applied to making these decisions.

It is the type of publication that I wish I had when I was learning how to use this popular spreadsheet package. For the most part, the training I received was disjointed, lacked focus, and did not establish a context for using MS-Excel; for example, how to develop a financial model to decide whether to pursue a business opportunity. For this reason, the starting point for each chapter is a business decision that needs to be made. Each decision is supported by the relevant accounting/finance theory, and then (and only then) is Excel introduced as a tool for addressing a management problem. This process reverses the method commonly used for teaching Excel, where the functionality is demonstrated and then applied to a disparate set of case facts – an approach that incorrectly attempts to find a problem for a solution.

This book provides accounting students in post-secondary institutions with an advanced level understanding of how to use MS-Excel. It reflects real-life applications of this important analytical tool, which has become the industry standard for spreadsheet software. The text focuses on using MS-Excel in situations encountered by accounting and finance students and professionals; these are contextualized in terms of the past, present, and future and reflect a typical operating cycle, which includes initial planning, followed by exercising control, and is completed when feedback is received.

The book also addresses the growing need for data analytic skills (i.e., “big data”) and the recent innovations by MS-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). However, while data analytics is important in financial management, it must be remembered that it forms only part of the larger picture that is captured by financial modeling.

The Excel ‘shell’ files that are used in conjunction with the financial modeling exercises shown in this book are available through the Emerald Publishing website. The completed solutions for the financial models can also be found there.

The Excel files are located at URL: : https://bit.ly/finmod2020

All companies use some type of spreadsheet application in the day-to-day operation of their Accounting and Finance departments. This can be as a stand-alone tool (e.g., mortgage calculator; amortization schedule), or – particularly for smaller companies – a significant part of their financial systems (e.g., payroll application; inventory subledger). For this reason, acquiring competencies in MS-Excel will be extremely valuable for students when they are applying for jobs and will help them tremendously during their employment interviews. Robert Half, the finance and accounting recruiting specialists, have noted in their most recent employer survey that advanced level knowledge of MS-Excel if one of the most sought-after skills by prospective hiring managers.

So, why do we need another textbook on using MS-Excel for business? Unlike previous publications that have focused primarily on management science applications, this text is specifically geared to business decisions that require financial modeling techniques used in Accounting and Finance departments. The applications discussed and models presented are based on the working experience of the author over his more than 25 years of employment in various areas of financial management. In addition, the text structures the practical usage of MS-Excel for decision making in the context of time: past, present, and future. In contrast, many books on this subject are relatively unstructured and dominated by esoteric and infrequently used applications of MS-Excel, primarily for academic audiences.

This text is ideally suited to a one-semester, senior undergraduate course of 14 weeks and addresses the competencies required by professional accounting associations, such as those offering the CPA and CMA designations. It covers the use of MS-Excel in:

• Break-even analysis, for new venture decisions

• Time series forecasting, for sales demand decisions

• Capital budgeting, for major investment decisions

• Regression analysis, for predictive analytics decisions

• Linear programming, for product mix and scheduling decisions

• Corporate valuations, for equity financing decisions

• Data analytics, for customer buying decisions

• KPIs using dashboards, for strategic decisions

• Budget development, for performance management decisions

• Amortization tables, for debt and asset management decisions

Each chapter can be covered in one weekly computer lab, with chapters one and two combined in the first session. This allows for 10 weeks of instruction, two mid-term tests, and one final exam. Chapter 12 integrates materials from the prior chapters and can be used as a stand-alone capstone project for students to complete.

This book is different from similar publications in several respects.

Focus on Accounting and Finance: Most texts that cover using MS-Excel for business address management science applications and do not deal specifically with accounting and finance situations. This book will be valuable to both accounting students as well as financial management professionals for making business decisions.

Practical applications: The content of this book will be immediately relevant to students and practitioners who are, or will be, dealing with similar situations in their professional work. For example, using capital budgeting models to make billion-dollar investment decisions, or developing corporate valuation models to price shares for an IPO.

Real-life examples: The Excel functionality used in decision making is based on the author's more than 25 years of financial management experience. The text will address common business decisions made by financial management professionals.

Decision-making emphasis: Employers want their newly hired employees to assist them in guiding the company. The focus of the book is on making sound business decisions that provide value for the enterprise.

Employer demand: Based on feedback received from our university's alumni and those who hire our graduates, companies increasingly want their employees to have advanced level MS-Excel skills. This book addresses that need. Feedback from our degree holders tells us that the organizations that employ them are impressed with their knowledge of MS-Excel.

The book addresses the day-to-day work done by Accounting and Finance staff in a company. This is illustrated in the table below, which shows how everyday business decisions relate to the accounting and finance concepts addressed in the topical coverage in the text (Table 1).

The book is also different from competing publications because it addresses real-life business situations that the author has encountered during his more than 25 years of work experience. This is summarized in the table below, along with references to the related MS-Excel functionality that is used in the demonstration exercises in the book (Table 2).

The book is organized around three dimensions relating to the decisions made by accounting and finance students and professionals, including: (1) time frame, (2) accounting cycle, and (3) financial analytics.

1. The decision context based on time frame includes the future, present, and past. Note that the normal chronological sequence has been reversed in this book to better align with the accounting cycle.

2. The accounting cycle includes distinct phases for planning (future orientation), control (thinking about the present), and feedback (using what has been learned from the past).

3. Decisions based on the future allow businesses to make plans – using predictive analytics. Decisions made in the present emphasize control, since they guide day-to-day actions – using descriptive analytics. Decisions using past information are reflective and give feedback about the effectiveness of plans in relation to actual results – using diagnostic analytics.

This schema is shown below and reflected in the chapter sequence of the table of contents. The dimension columns are organized according to the time frame in which the decisions are made. They include the applicable part of the accounting cycle and the related financial analytic techniques. The financial models discussed in the book are also listed, based on the applicable time frame and stage of the accounting cycle to which they relate (Table 3).

This book is designed for advanced Excel applications and therefore students require an intermediate level knowledge of the software, focused primarily on the functional aspects of using Excel. This will include coverage of the following topics.¹

Working with Excel Tables, Pivot Tables, and Pivot Charts:

• Explore a structured range of data

• Freezing rows and columns

• Creating an Excel table

• Plan and create an Excel table

• Rename and format an Excel table

• Add, edit, and delete records in an Excel table

• Sort/Filter data

• Insert a Total row to summarize an Excel table

• Split a worksheet into two panes

• Insert subtotals into a range of data

• Use the Outline buttons to show and hide details

• Create and modify a Pivot Table

• Apply Pivot Tables styles and formatting

• Filter and sort a Pivot Table

• Insert a slicer to filter a Pivot Table

• Group Pivot Table items (Home Group/Group Field)

• Create a Pivot Chart

Managing Multiple Worksheets and Workbooks:

• Create a worksheet group

• Format and edit multiple worksheets at once

• Create cell references to other worksheets

• Consolidate information from multiple worksheets using 3-D references

• Create and print a worksheet group

• Create a link to data in another workbook

• Create and print a worksheet group

• Create a link to data in another workbook

• How to edit links

• Create and use an Excel workspace

• Insert a hyperlink in a cell

Developing an Excel Application:

• Create, edit, and delete defined names for cells and ranges

• Paste a list of defined names as documentation

• Use defined names in formulas

• Add defined names to existing formulas

• Create valid rules for data entry

• Protect the contents of worksheets and workbooks

• Add, edit, and delete comments

• Macros (create, save)

Working with Advanced Functions:

• Working with Logical functions (IF, AND, and OR)

• Working with comparison operators such as <, <=, = <>, >, or >= to compare two values

• Inserting calculated columns in an Excel Table

• Using structured references in formulas (Fully qualified and Unqualified)

• Nest the IF function

• Using the VLOOKUP and HLOOKUP functions (to find approximate and exact match)

• Use the IFERROR function

• Use conditional formatting to highlight values

• COUNTIF, SUMIF, AVERAGEIF

Exploring Financial Tools and Functions:

• Work with financial functions such as FV, PV, RATE, NPER, and PMT

• Interpolate and extrapolate a series of values (Home Editing Fill Series)

• Determine a payback period

• Calculate a net present value (NPV) and an internal rate of return (IRR)

Performing What-If Analysis (Data, Data Tools, What-If Analysis):

• Perform what-if analysis with Goal Seek, Data Table, Scenario Manager, and Solver.

• Use Goal Seek to calculate a solution (Goal Seek)

• Create a one-variable data table (Data Table)

• Create a two-variable data table (Data Table)

• Create and apply different Excel scenarios (Scenario Manager)

• Generate a scenario summary report

• Generate a scenario PivotTable report

• Run Solver to calculate optimal solutions (Data Table)

• Create and apply constraints to a Solver model

• Save and load a Solver model

Connecting to External Data:

• Import data from a text file (Data, Get External Data, From Text)

• Working with connections and external data ranges (Data, Connections group)

• Define a trusted location

Collaborating on a Shared Workbook:

• Integrating Excel with Other Office Applications

• Understanding copying and pasting, linking, and embedding objects into Word document

• Linking Excel and Word Files

• Updating a linked object

• Embedding an object

• Modifying an Embedded Object

With these topics as a foundation, the text incorporates more advanced functionality into decision making for accounting and finance students and professionals.