Prelims

Snapshot

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.

Key Research

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).

Organizational Strategy

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.

Financial Planning

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.

Supply Chain Management

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.

Impact of Research

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.

What's Next?

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

Unique Aspects of this Book

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).

Organization of this Book

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.