Financial Modelling using Dynamic Arrays E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 581

Veröffentlichungsjahr: 2024

Beschreibung

Dive into the transformative power of Excel's dynamic arrays in financial modelling. Learn to optimize formulas with LET, create reusable LAMBDA functions, and craft sophisticated models. The book provides a comprehensive introduction to Excel’s dynamic arrays, comparing legacy methodologies with modern capabilities while integrating practical tips and best practices.
Through real-world examples and step-by-step tutorials, you’ll uncover the full potential of functions like SORT, FILTER, SEQUENCE, and LAMBDA. Discover how dynamic arrays reduce errors, boost efficiency, and enable innovative approaches to financial modelling. The book also highlights advanced features like eta lambdas and helper functions, offering a deep dive into the cutting-edge tools now available in Excel 365.
Whether you’re building complex financial models or just looking to refine your techniques, this guide equips you with the knowledge to transform your processes. Excel enthusiasts and professionals alike will appreciate the clarity and depth this book provides, helping you elevate your modelling game to a whole new level.

Leseprobe

Financial Modelling with Dynamic Arrays

Dr. Liam Bastick

Copyright © 2024 SumProduct Pty Limited.

Published in 2024 by SumProduct Pty Limited. Ground Floor, 470 St Kilda Road, Melbourne, Vic 3004, Australia.

All rights reserved.

Author: Dr. Liam Bastick

Editors: Kathryn Newitt and Oscar Hagan

Indexer: Cheryl Lenser

Compositor: Bronkella Publishing

Cover Design: Shannon Travise

Distributed by Independent Publishers Group, Chicago, IL

ISBN 978-1-61547-087-7 Print, 978-1-61547-173-7 Digital

Library of Congress Control Number: 2024939407

Version 20240704a

No parts 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, without either the prior written permission of the publisher, or authorisation through payment of the appropriate photocopy fee to the Copyright Clearance Center. Requests for permission should be addressed to the publisher, SumProduct Pty Limited, Ground Floor, 470 St Kilda Road, Melbourne, Vic 3004, Australia, tel: [phone], email: [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 public services. If professional advice or other expert assistance is required, the services of a competent professional person should be sought. SumProduct (www.sumproduct.com) is such an organisation that undertakes both tailored training and consulting services. Neither the author nor the publisher is liable for any actions prompted or caused by the information presented in this book. Any views expressed or implied herein are those of the author and do not represent the views of the organisations he works for.

Microsoft and Excel are registered trademarks of the Microsoft Corporation.

For Brenda & Patrick, together again.

About the Author

Dr. Liam Bastick FCA FCMA CGMA MVP

Starting off as a university lecturer, Liam has over 30 years’ experience in financial model development / auditing, and liaised with Microsoft when they were first considering dynamic arrays, both their behaviour and prospective features / functions.

He has considerable experience in many different sectors (e.g. banking, energy, media, mining, oil and gas, private equity, retail, transport and utilities) and has worked in many countries (including Australia, Belgium, Denmark, France, Germany, Hong Kong, Indonesia, Malaysia, Netherlands, New Zealand, Philippines, Singapore, Switzerland, United Kingdom, United States and Vietnam). He has worked with internationally recognised clients, constructing and reviewing strategic, operational, planning and valuation models for many high profile assignments.

With over 2,000 articles written for the accounting profession, he is a regular contributor to the American Institute of Certified Public Accountants (AICPA), Chartered Accountants Australia and New Zealand (CAANZ), Certified Practising Accountants Australia (CPAA), the Chartered Institute of Management Accountants (CIMA), the Institute of Chartered Accountants in England and Wales (ICAEW), Microsoft’s Excel Blog and various LinkedIn specialist discussion groups.

Liam is a Fellow of the Institute of Chartered Accountants (FCA), a Fellow of the Institute of Chartered Management Accountants (FCMA), a Chartered Global Management Accountant (CGMA), and is also a professional mathematician, specialising in probability and number theory.

A frequent public speaker, Liam attends Excel and Power BI conferences around the globe and has been a central organiser for the Excel Summit South, Unlock Excel and Excel Virtually Global. He has also authored and edited several books including the sister volumes Introduction to Financial Modelling, Continuing Financial Modelling, Financial Modelling in Power BI and Financial Modelling for Project Finance, as well as the Power BI MVP Book and Excel Insights.

Since 2012, he has been recognised by Crimewatch and Microsoft, the latter as a Most Valuable Professional (MVP) in Excel, one of c.130 such awardees worldwide (as at the time of writing). In 2021, Liam was the recipient of the inaugural Lifetime Achievement Award for Financial Modelling by the Financial Modeling Institute (the inconsistent spelling of “Modelling” is as intended!).

He still follows Derby County, the England cricket team and trails of breadcrumbs.

Preface

Wow, Book 5! At this rate, I might get syndicated. I will probably be 173 by then.

This book revisits the topics of the first book in a new light. It looks to create and think about financial modelling purely using dynamic arrays. Of course, there are tips and tricks from the past books, but this one is all about array modelling – and it does require a different mindset.

I continue to be lucky enough to be appointed a Most Valuable Professional (MVP) by Microsoft for services to Excel – one of over one hundred “experts” as at the time of writing. I have used this fabulous network to create simpler solutions to some of the problems I was faced with in developing this book. In fact, a lot of concepts are similar here to Financial Modelling in Power BI, which helped a lot.

I’d like to thank those that helped contribute to this book. There are quite a few: Oscar Hagan and Talia Cao helped devise cunning alternatives to some of my original formulae used to build the model, and the ever-vigilant Kathryn Newitt edited it technically with the help of Oscar, as they endeavoured to spot all my clangers. I am pretty confident there are now no errors whatsojggjgrjlwg.

I’d also like to thank Bill Jelen for continuing to publish these books, and most importantly my immediate family, Nancy and Layla, who continue to support me, especially when I tend to do so much writing during the Christmas break. As always, I allow my daughter, Layla Bastick, to have the usual final word:

“If you’re getting an advance, will you buy me a guitar please? Love you daddy xxx”

Liam Bastick, April 2024

Editor’s Notes

When Liam said that creating a financial model with dynamic arrays would be less of a drag, I was hoping for a much shorter book! Of course, he meant that the clicking and dragging across that is required for “CRaFT”ily consistent standard excel formulae is redundant when using dynamic arrays.

What is needed, is a thorough explanation of what dynamic arrays are, and how they open up a world of possibilities for financial modellers. Which is why this is the longest book so far, needing not just one, but two editors this time around.

In order to appreciate some of the more complex formulae needed to model financial statements with dynamic arrays, I encourage you to build the model as you read. If you take the time to get to grips with the concepts in this book, you will soon have some clever new tricks to add to your repertoire, and some very old jokes!

Kathryn Newitt

www.sumproduct.com

Helping put together the case study for this book and the book itself was an exciting process (though I might be the only person in the world that thinks so), once I got past how intimidating some of the formulae are.

I’ve found that this process has helped me really start thinking with dynamic arrays and opened my eyes up to the possibilities they create in Excel. Hopefully this book will leave you feeling the same way.

As Kathryn has already said, for the best experience do consider building the model as you read through the book. I’ve found no better way to wrap my head around the more complex formulae derived by Liam and Talia than to build them up step by step within the model. If you can make it through Liam’s terrible jokes (we tried to remove them, he won’t let us!) then you really will learn a lot about what’s now possible in Excel using dynamic arrays.

Oscar Hagan

www.sumproduct.com

Downloadable Resources

Throughout the book, we have included relevant images to help follow the process. However to really get to grips with the build, and check your own results, nothing beats being able to read through the actual model. We have also provided useful supporting Excel examples for the concepts discussed in this book. All of the milestones of the case study as well as a selection of other examples can be accessed by using the link:

https://www.sumproduct.com/fm-with-da-book-resources

Contents

CHAPTER 0: INTRODUCTION

CHAPTER 0.1: FILE LOCATION

CHAPTER 1: KEY EXCEL FUNCTIONS

CHAPTER 1.1: SUM

CHAPTER 1.2: IF

CHAPTER 1.3: IFERROR

CHAPTER 1.4: SUMIF

CHAPTER 1.5: SUMIFS

CHAPTER 1.6: SUMPRODUCT

CHAPTER 1.7: VLOOKUP / HLOOKUP

CHAPTER 1.8: LOOKUP

CHAPTER 1.9: INDEX and MATCH

INDEX

MATCH

INDEX MATCH

CHAPTER 1.10: XLOOKUP and XMATCH

XLOOKUP

Comparisons with LOOKUP

Useful Features of XLOOKUP

Partial and Exact Matching

XMATCH

CHAPTER 1.11: CHOOSE

CHAPTER 1.12: OFFSET

Aside: Volatile Functions

CHAPTER 1.13: MOD

Common Example with MOD and OFFSET

CHAPTER 1.14: EOMONTH and EDATE

CHAPTER 1.15: MAX, MIN, LARGE and SMALL

CHAPTER 2: KEY EXCEL FUNCTIONALITIES

CHAPTER 2.1: ABSOLUTE REFERENCING

Method 1: Text Little Time

Method 2: OFFSET from the Outset

CHAPTER 2.2: NUMBER FORMATTING

Example 1: Comprehensive

Example 2: Hiding the Contents of a Cell

Example 3: Formatting Based on Conditions

Use with Caution

CHAPTER 2.3: STYLES

Macros

CHAPTER 2.4: CONDITIONAL FORMATTING

CHAPTER 2.5: RANGE NAMES

Space: the Final Frontier?

Deleting Range Names

Relative Referencing

Other Types of Names

CHAPTER 2.6: DATA VALIDATION

Other Types of Data Validation

List

Custom

Data Validation is Reactive Not Proactive

Best Excel Tip Ever...?

CHAPTER 2.7: DATA TABLES

1-D Data Tables

2-D Data Tables

Important Considerations

Data Table on Other Sheets

CHAPTER 2.8: GOAL SEEK and SOLVER

NPV and IRR

Introducing Solver

Using Solver with a Reference

VBA Approach

CHAPTER 2.9: HYPERLINKS

CHAPTER 3: OTHER EXCEL POINTERS

Quick Analysis

New Workbook Defaults

Formulas Options

Save Options

Saving Files

Advanced Options

Quick Access Toolbar

CHAPTER 4: INTRODUCING ARRAYS

Array Functions

The Future

CHAPTER 5: DYNAMIC ARRAYS

Spilling the Beans

#SPILL! Errors

Returning to Dynamic Arrays

Implicit Intersection Implications

SINGLE Function / @ Operator

Dynamic Arrays vs. Legacy Array Formulae

SORT Function

SORTBY Function

FILTER Function

Interlude: the #CALC! Error

UNIQUE Function

SEQUENCE Function

TRANSPOSE Function

Back to the SEQUENCE Function

RANDARRAY Function

Death of Data Tables and PivotTables?

Dynamic Charts

Calculation Order Concern

CHAPTER 6: LET and LAMBDA

Introducing LET

LAMBDA Function

LAMBDA function components

Naming a LAMBDA

Calling LAMBDA

LAMBDA Has No Aversion to Recursion

Taking it Further: Pascal’s Triangle

Care with IFS and SWITCH

In Summary

CHAPTER 7: LAMBDA HELPER FUNCTIONS

BYCOL and BYROW

CHOOSECOLS and CHOOSEROWS

DROP

EXPAND

HSTACK and VSTACK

MAKEARRAY

MAP

REDUCE

SCAN

TAKE

TOCOL and TOROW

WRAPCOLS and WRAPROWS

CHAPTER 8: GROUPBY, PIVOTBY, PERCENT OF and ETA LAMBDAS

eta Lambdas

GROUPBY

PIVOTBY

PERCENT OF

Still in beta

CHAPTER 9: BEST PRACTICE METHODOLOGY

Consistency

Robustness

Flexibility

Transparency

CHAPTER 10: LAYOUT TIPS

CELL

Step 1: FINDing the Beginning and the End

Step 2: LEFT a bit, RIGHT a bit, Aim for the MID Section

Step 3: Error Trapping

Sheet Title Revision

Back to Layout Tips

Splits

Hyperlink Home Cell

Summary

CHAPTER 11: TIME SERIES ANALYSIS

CHAPTER 12: ERROR CHECKS

Variables vs. Constants

CHAPTER 13: MODEL TEMPLATE EXAMPLE

Difference Between a Template and a Reusable File

CHAPTER 14: FINANCIAL STATEMENT THEORY

Income Statement

Balance Sheet

Cash Flow Statement

Linking Financial Statements

Appropriate Order of the Financial Statements

CHAPTER 15: CONTROL ACCOUNTS

Building a Financial Model

CHAPTER 16: REPEATABLE CALCULATIONS

Labels Calculation

Main Formula

Next Steps

CHAPTER 17: EXAMPLE MODEL BUILD

CHAPTER 17.1: INITIAL STRUCTURE

CHAPTER 17.2: ADDING SHEETS

CHAPTER 17.3: CREATING THE FINANCIAL STATEMENT WORKSHEETS

Building a Financial Model

CHAPTER 17.4: PREPARING THE FINANCIAL STATEMENT WORKSHEETS

CHAPTER 17.5: LINKING THE FINANCIAL STATEMENT WORKSHEETS

Building a Financial Model

CHAPTER 17.6: ERROR CHECKS

Building a Financial Model

CHAPTER 17.7: REVENUE

Building a Financial Model

The Four Methods of Entering Inputs into a Model

Working Capital Adjustments

Revenue Calculations

CHAPTER 17.8: COGS

Reminder: The Four Methods of Entering Inputs into a Model

Back to the Case Study

CHAPTER 17.9: INVENTORY

Purchases and Related

Inventory

Completing the Control Accounts

CHAPTER 17.10: OPERATING EXPENDITURE

And Again: The Four Methods of Entering Inputs into a Model

CHAPTER 17.11: CAPITAL EXPENDITURE

Theory

Reverse Depreciation Rates Method

Back to the Case Study

The MAP Function Revisited

Back to the Case Study Again

CHAPTER 17.12: DEBT

The 3 R’s of Debt Modelling

Capitalised vs. Rolled Up

Return of Finance

Return on Finance

CHAPTER 17.13: TAXATION

Liam’s Law of Tax

Back to the Case Study: Tax Expense

Tax Depreciation

Accounting Depreciation

Depreciation Timing Difference

Tax Payable and Paid, Part 1

Tax Losses Memorandum

Tax Payable and Paid, Part 2

Tax Control Account

CHAPTER 17.14: EQUITY

Building a Financial Model

Equity Inputs

Return of Equity

Return on Equity

Maximum Dividend Payable

Return of Return on Equity

CHAPTER 17.15: SINGLE ENTRY ACCOUNTING

Building a Financial Model

CHAPTER 17.16: OPENING BALANCE SHEET REVISITED

Building a Financial Model

CHAPTER 17.17: INDIRECT CASH FLOW EXTRACT

CHAPTER 17.18: CASE STUDY WRAP-UP

Building a Financial Model

CHAPTER 18: RATIO ANALYSIS

Profitability Ratios

Liquidity Ratios

Asset Management Ratios

Debt (Gearing) Ratios

Equity Ratios

Market Value Ratios

Example

CHAPTER 20: AND FINALLY…

Index

RAISING ARRAYS FOR MODELLING

CHAPTER 0: INTRODUCTION

I said I wanted a raise for writing this book and I guess the publishing company misheard me. Instead, I sit here and write a book on arrays. Good job I am not a cosmologist writing about Uranus.

I sit here pondering over this latest book’s introduction reading an email from one “verified reader” who is annoyed that Financial Modelling in Power BI did not explain how to create the calculations in Tableau and wanting to know why it did not discuss programming languages in detail. Therefore, I realise I need to be very clear here about the following.

This book continues the series on financial modelling, but this time looks to take on Excel’s latest weaponry. Since I wrote Introduction to Financial Modelling, Microsoft’s spreadsheeting behemoth has become Turing-complete. Whilst it always had array formulae, it now has dynamic arrays, a LET function, a LAMBDA function, lambda helper functions and eta lambdas. And few seem to know what all this means and how it can make building financial models more versatile.

So, you are presented with a choice, dear reader. You can continue to keep building financial models using all the traditional, “legacy” tools of Excel (I wouldn’t hold your breath waiting for artificial intelligence or chatbots to do it for you) or you can dip your proverbial toe in the new dynamic array waters.

The water is reasonably warm and not too murky. Some calculations have to become more complex with good reason, but the benefits are immense. With practice, experience and increasing expertise, you can build models that will grow with your projects and businesses, just by changing an input rather than rebuilding sections.

Some have told me that I am not the first to build a model using dynamic arrays. I am aware others have tried and built models that use dynamic array formulae. I think the example model here is different though: it uses dynamic array formulae for pretty much every calculation. I am not aware of any other model out there mirroring this. We are going to build a model that uses dynamic array formulae throughout / completely.

When some calculations get too hard, I note my peers have reverted to traditional formulae. Instead, I have spent countless hours with a wet towel over my head in a darkened room refusing to give in. This model has dynamic array formulae for inventory, depreciation and tax losses – can you honestly say you’ve seen these calculations elsewhere?

As always, I have my usual dilemma. Not everyone reading this book is going to be aware of the other books in this series, let alone have read them. Do I repeat myself or do I assume everyone has read everything else in the series? It’s obvious: I need to make each book stand alone. If you are a loyal reader of the series, you will see there is necessary repetition, but if you’re a loyal reader, you keep returning, you understand my reasoning and you have accepted this. I have to make this book self-contained. Don’t let it dissuade you from considering the sibling books: they address different topics.

The plan is therefore as follows:

Key Excel functions: to kick off, I have put together a refresher on key traditional functions previously used in financial modelling, many of which will still be needed. Don’t be put off by the attendees. If you are reading the guest list and noting appearances from IF, SUM and VLOOKUP, and you’re thinking, “this book is very basic”, I can assure you there are some scary surprises in store as I show typical mistakes made every day with these functions and other popular ones too. Don’t become a casualty.Key Excel functionalities: There’s other attributes that we need to exploit. In this section, I will discuss key functionalities such as absolute referencing, number formatting, conditional formatting, Data Tables, data validation, range names, hyperlinks and the like. Other Excel pointers: There are other points / settings to note that make for an easier life when modelling. I detail them in this section.Remembering arrays: now we start to veer off into the wide blue yonder. If we are going to utilise dynamic arrays, it might be an idea to understand why they are so radical and what Excel used to have before – and why the calculation engine had to change.Introducing dynamic arrays: this section introduces dynamic array modelling and the key functions Microsoft introduced to exploit the new world. I explain the concepts in detail and provide comprehensive examples of how the spreadsheet world is moving on.LET, LAMBDA et al: the brave new world doesn’t stop there, with new functions to help you create your own modelling vision. LET invites complexity but reduces memory requirements, whilst LAMBDA quite literally allows you to build anything. It needs help though with lambda helper functions and the latest eta lambdas, but hey, Excel has become “Turing-complete” – if only I knew what that meant! Well, be prepared to find out here.“Best Practice” methodology: Now that the new tools have been introduced, I need to provide a reminder of “Best Practice”. I discuss a conceptual framework and I propose something very simple – something our company calls CRaFT.Layout tips: No matter whether you use traditional functions or the new breed, everyone can benefit from useful tips and insights on how best to layout a spreadsheet – and a financial model.Time series analysis: If we are going to build a model, we will have to work with dates – and that’s perhaps not quite as straightforward as you might think. In this section, I will explain how dates ought to be constructed and why – including periodicity issues – and how to ensure they work in the realm of dynamic arrays.Checks: Talking of issues, checks remain part of any modelling ecosystem. Unfortunately, they are often added as an afterthought. They shouldn’t be. In this section, I will explain why they should be at the forefront of your model development, explaining the three types you need to consider.Base template: Whilst all models are different, they do share common attributes. It’s this foundation which I can translate into a base template to use at the outset of developing a financial model, be it for legacy modelling or financial modelling with dynamic arrays.Financial statement theory: Here, I consider just what is an Income Statement, a Balance Sheet and a Cash Flow Statement. Yes, you may know what they are – but I want to do it from the perspective of understanding the purpose of each statement so that it guides you in determining the order of building a financial model. Control accounts: Debits and credits may be the accountants’ way of keeping the mystery alive in finance, but the whole transactional nature of accounting and finance is often understood better by the majority of end users if we adopt control accounts. I explain what they are here, why they are useful, and most importantly, if you choose to use control accounts, Balance Sheet errors and similar mistakes will become a thing of the past.Example of a model build using dynamic arrays: It might be a small paragraph here, but it’s a big part of this book as we reinvent financial models using completely new formulae drawn from the world of LET, LAMBDA and dynamic arrays.

As always, this is intended to be a practical book. There are many supporting Excel examples to review and use, grouped by chapter / section, to visualise the important concepts discussed here. Don’t be a wallflower and just “review” the examples. Recreate them for yourself. The mistakes you make will prove invaluable in learning key elements of this exciting new topic.

Let’s go!

CHAPTER 0.1: FILE LOCATION

There is little point in me creating useful supporting Excel examples for the concepts discussed in this book without being very specific about where you can find them! All of the milestones of the case study as well as a selection of other examples can be accessed by using the link:

https://www.sumproduct.com/dynamic-arrays-book-resources