Continuing Financial Modelling E-Book

MrExcel's Holy Macro! Books

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

Seitenzahl: 454

Veröffentlichungsjahr: 2024

Beschreibung

This book provides a thorough exploration of advanced financial modeling techniques, designed to elevate the skills of finance professionals. Starting with a recap of essential concepts, it progresses into dynamic 'What-If?' analysis, advanced forecasting methods, and inventory modeling. The focus is on practical applications, ensuring readers can implement the techniques immediately.
Topics such as capital expenditure, debt calculations, and valuation are covered in detail, including DCF and MIRR analysis. The book emphasizes accuracy and efficiency in financial models, offering insights into refining forecasts and linking complex models. With a focus on sensitivity analysis and scenario planning, readers gain tools to handle real-world financial challenges.
The final chapters delve into advanced Excel functions like XLOOKUP, dynamic arrays, and scenario-building tools. Best practices for maintaining model accuracy, reducing file sizes, and creating professional models are thoroughly discussed. This guide equips readers with the expertise to manage complex financial modeling tasks confidently.

Leseprobe

Continuing Financial Modelling

Dr. Liam Bastick

Copyright © 2020 SumProduct Pty Limited.

Published in 2020 by SumProduct Pty Ltd, Suite 803, Level 8, 276 Pitt Street, Sydney NSW 2000 Australia. Simultaneously published in the USA by Holy Macro! Books, PO Box PO Box 541731, Merritt Island FL 32954.

All rights reserved.

Author: Dr. Liam Bastick

Indexer: Nellie Jay

Compositor: Joseph Kirubakaran

Cover Design: Shannon Travise

First Printing: January 2021. Updated with corrections June 2022.

Distributed by Independent Publishers Group, Chicago, IL

ISBN 978-1-61547-068-6 Print, 978-1-61547-154-6 Digital

Library of Congress Control Number: 2020999999

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 Ltd, Suite 803, Level 8, 276 Pitt Street, Sydney NSW 2000 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.

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, valuations, mergers and acquisitions, project finance, public private partnerships, strategy, training and consultancy. Indeed, he has been appointed as an independent expert for the courts of Victoria and New South Wales, in Australia.

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 many internationally recognised clients, constructing and reviewing strategic, operational, planning and valuation models for many high profile International Public Offerings (IPOs), Leveraged Buy-Outs (LBOs) and strategic assignments.

With over 1,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), Finance 3.0, 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 and Excel Virtually Global. He has also authored and edited several books including the sister volume Introduction to Financial Modelling, 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 66 such awardees worldwide (as at the time of writing).

He still follows Derby County and the England cricket team.

Preface

It’s just like waiting for a bus: you wait forever for one and then several come at once. Well, it took me nigh on 30 years to put keyboard to virtual paper for the first tome, Introduction to Financial Modelling, and then I wrote three more in less than two years!

This book is not so much the sequel to my first sortie into the world of financial modelling, as the continuing reconnaissance mission. If I don’t say so myself, the first book contained a lot of useful tips, tricks and ideas about how to build a financial model, but there was so much that was left unsaid.

This book is a companion volume to what I wanted to be a simple base. It attempts to address some of the gaps, such as modelling inventory, considering what-if? analysis and extending the model to develop a valuation. But there’s much more I wanted to address, and I have tried my best to include most of those topics within these pages.

I have been lucky enough to remain a Most Valuable Professional (MVP) by Microsoft for services to Excel – one of 66 so-recognised “experts” as at the time of writing. Now I know an “ex” is a has-been and a “spurt” is a drip under pressure, but I hope you’ll see me as a farmer – someone out(-)standing in their field! It takes thousands of mistakes to get good at something. I hope you take all the tips out of this volume and avoid the aforementioned errors.

I’d like to thank those that helped contribute to this book. There are quite a few: Tim Heng for technically editing it, putting up with the usual raft of bad jokes and arguing points ad nauseum, Jonathan Liau, Hanh Tran and Greg Liu for firming up the examples, Bill Jelen for assisting getting it out into the wild, and most importantly my immediate family, Nancy and little Layla, who continue to support me. I let my daughter, Layla Bastick, have it the first time around, so I thought it best to let her have the final word once more (she always does anyway):

“Surprisingly, he’s made another book! I haven’t actually read the first one yet because there was too much writing and information. However, I applaud my daddy for being able to write it. I love you daddy.”

Liam Bastick, May 2020

www.sumproduct.com

Editor’s notes

I’m not quite sure how I got roped into editing yet another of Liam’s books. It’s almost as if the jokes in the first weren’t funny enough, so he’s felt the need to double down this time. Or that the content in the first book wasn’t complex enough, so he’s tripled the challenge here.

Of course, that’s not an entirely fair argument. We deliberately went into the first book trying to keep it simple and hands-on practical. The goal was never to make it comprehensive and solve every modelling problem under the sun. We knew there was always going to be a second and third book in the series, which would take particular concepts and ideas and drill into them in far more detail.

Even now, this book isn’t going to cover every unique circumstance. There will be ideas here that you might think should be calculated differently. There are certainly sections here that I would do differently. But it’s important to note that nothing in modelling will ever be transferred perfectly from one person’s experience to another’s, and the examples in this book are here to give you inspiration and a solid basis for adapting the concept to your own particular problems.

So if you find that you see something in the book that doesn’t quite work for your circumstances, feel free to get in touch and we can have a discussion about it and help you work it out (for a fee, of course). If you find that you disagree with something in the book and feel that you simply must write in to vent and complain, then at the risk of stealing one of Liam’s lines that I edited out, please let us know at [email protected] – we can promise you that no one will read your email.

Enjoy your reading, and we’ll meet again in a book’s time!

Tim Heng, Microsoft Excel MVP

www.sumproduct.com

Downloadable Resources

A picture may show a thousand words, but it’s incredibly hard to convey exactly how a formula or a set of calculations work without seeing the overall context of the model and being able to drill into the formula itself. With that in mind, we have set up a page on our website for further reference, where we will (for as long as our website and the internet as we know it still exists) provide files containing examples that we have used throughout this book, as well as any supplementary materials that we think may be useful.

Head to our website at https://www.sumproduct.com/book-2-resources to download any of the files referred to in our book, as well as any other materials that we think might be useful for you.

Contents

About the Author

Preface

Editor’s notes

Downloadable Resources

Chapter 0: Not an Introduction, But a Continuation

Chapter 1: Recap

Chapter 1.1: Best Practice Concept

Chapter 1.2: Time Series Analysis

CHAPTER 1.3: Financial Statement Theory

CHAPTER 1.4: Control Accounts

Chapter 2: What-If? Analysis

Chapter 2.1: Conditional Formulae

CHAPTER 2.2: OFFSET

CHAPTER 2.3: Scenario Analysis

CHAPTER 2.4: Data Tables

CHAPTER 2.5: INDEX and MATCH

CHAPTER 2.6: Using Tornado Charts for Sensitivity Analysis

CHAPTER 2.7: Simulations Analysis

CHAPTER 2.8: Variance Analysis

CHAPTER 2.9: Breakeven Analysis

Chapter 3: Forecasting Considerations

CHAPTER 3.1: Seasonal / Cyclical Forecasting

CHAPTER 3.2: Revising Forecasts

CHAPTER 3.3: Pro-Rating Forecasts Over Time

CHAPTER 3.4: Changing Periodicities

CHAPTER 3.5: Modelling Historical, Actual and Forecast Data

CHAPTER 3.6: Rolling Budgets and Charts

CHAPTER 3.7: Forecasting Maximum Cash Required

Chapter 4: Modelling Inventory

Chapter 5: Capital Expenditure

Chapter 6: Debt

CHAPTER 6.1: Debt Sculpting

CHAPTER 6.2: Calculating Interest Rates Correctly

CHAPTER 6.3: Useful Repayment Functions and Formulae

Chapter 7: Valuation Considerations

CHAPTER 7.1: Deriving the Correct Cash Flow for DCF

CHAPTER 7.2: Considering Discount Factors

CHAPTER 7.3: Common DCF Modelling Errors

CHAPTER 7.4: Using the Dividend Discount Model

CHAPTER 7.5: Irrelevant IRR

CHAPTER 7.6: Modified Internal Rate of Return (MIRR)

CHAPTER 7.7: Smoothing Capital Expenditure

CHAPTER 7.8: Calculating Economic Lives

Chapter 8: Linking Models

Chapter 9: Life’s Too Short

CHAPTER 9.1: Section Numbering

CHAPTER 9.2: US vs. European Dates

CHAPTER 9.3: Sheet Referencing

CHAPTER 9.4: Reducing File Size

CHAPTER 9.5: Taking it to the Limit

CHAPTER 9.6: Order of Operations

CHAPTER 9.7: Keeping Styles Under Control

CHAPTER 9.8: Wearing Protection

Chapter 10: Look To The Future

CHAPTER 10.1: Dynamic Arrays

CHAPTER 10.2: XLOOKUP and XMATCH

CHAPTER 10.3: LET it Be

CHAPTER 10.4: New Data Types

CHAPTER 10.5: STOCKHISTORY

Resources

Index

Chapter 0: Not an Introduction, But a Continuation

They say I always have to undertake things an even number of times: the first time to do it and the second time to apologise. Well, just like that embarrassing condition you can only tell your doctor about, I’m back.

This sister volume to Introduction to Financial Modelling starts where that book ended. The first tome addressed my frustration that there’s never been a really practical book that helps novices and the experienced alike to build better financial models. It began with the key things you needed to know and use in Excel, discussed what “Best Practice” really is and then explained a bullet-proof method so that you could build three-way integrated relationships (sounds kinky I know) between your financial statements, namely the Income Statement, the Balance Sheet and the Cash Flow Statement. Heck, it was all about getting your financial models to work and that blessed Balance Sheet to balance first time, every time.

But that’s where that book finished. That’s not the end of the story. If you did read that book, hopefully, you now feel like a more competent modeller and understand why you should model in a certain order. However, to keep the page count down it deliberately ended with providing both a process and a justification for building a model in a particular way.

This book addresses some of the complications deliberately excluded from that riveting read. There were various reasons for this (one already mentioned), but perhaps the main factor was that these issues do not occur in every model. But they do happen. That’s what this book focuses on: the common problems in building a model and how best to solve them.

Since these issues are unrelated and occur irregularly, this book differs in structure from its predecessor. That book took a premiss, described it and justified it, cradle to grave. It needed to be read linearly – that’s not the case here. Most chapters are standalone and may act more as a reference guide to tackle the common issues that occur. Therefore, dear reader, feel free to dip in and out of this book as you see fit. There is no over-arching, massive case study this time – just the usual Excel examples, proliferated throughout.

The plan is therefore as follows:

Recap: It may have been a while since you read the first book, it may be you did not read it all (cheapskate). I recognise we need to start somewhere with a common ground and this chapter recapitulates the salient points from the introductory book.What-if? analysis: Since the first book came out, if there’s one question I have been asked above all else, it’s “where’s the sensitivity and scenario analysis?”. That subject matter didn’t sit right with me in the first book, with the primary objective to show you how to build three-way integrated financial statements – but it follows on. Therefore, I’ve made it Chapter 2.Forecasting: At the other end of the spectrum, the first book could have had a preamble too on how to get your inputs in the first place. Chapter 3 takes a look at ways to model your forecasts and how to update forecasts with actuals as they eventuate.Modelling inventory: I was in two minds about including this in the first book. Inventory is often an important part of a financial model, but was deliberately excluded last time out. There was a good reason for this: unlike other areas, modelling inventory was a little more complex and requires more than one control account. When writing the first book, I felt that might have detracted from the emphasis I was placing on control account modelling. I put this omission right in Chapter 4.Capital expenditure: Another key aspect of any financial modelling, this book looks at key modelling issues associated with non-current asset expenditure. I discussed depreciation in some detail in the first book, but here I turn my attention to errors made in modelling opening Net Book Value depreciation, as well as smoothing capital expenditure and confirming economic lives. The first topic is included in Chapter 5, but the remaining subjects are tacked later in Chapter 7, where I consider a myriad of different valuation considerations.Debt: I have over 30 years’ experience in debt (very few professionally). It’s another important area to consider in the financial modelling arena, and Chapter 6 looks at both getting the interest right and shows you how to incorporate debt sculpting without a macro, as well as some other useful formulae and functions.Valuations: I have spent many years building, teaching and reviewing valuation models and have seen many errors made. Chapter 7 looks at some of the common mistakes made – and how you can avoid them. It’s best to use first principles – and keep it very, very simple.Linking models: There comes a time in every modeller’s professional life where you realise you can’t have everything in one Excel workbook. But are you disciplined enough to structure model relationships appropriately? Chapter 8 provides some simple tips on how to make life easier in a multi-model environment.Miscellaneous: This ad hoc Chapter 9 looks at common issues encountered whilst modelling. Perhaps more of a reference section than other parts of the book, this chapter highlights limits and calculation orders, whilst also providing useful tricks and tips for file protection, keeping file size lower and ensuring the number of workbook styles does not spiral out of control.The future: Excel is moving on; are you? The final chapter addresses three key topics which are going to revolutionise the way we both use Excel and model in the very near future. This chapter looks at dynamic arrays, the new functions XLOOKUP and XMATCH (goodbye VLOOKUPandINDEX MATCH!) and rich data types. Know what’s coming (some of which has arrived) now.

Remember, above all, this book is primarily a practical book. Make use of the extensive Excel models, grouped by chapter / section, to visualise the important concepts discussed here. Get that laptop out (it’s not just for private browsing). There are examples aplenty and the best way to understand is to do. Enjoy!

Liam, New Year’s Eve, 2019.