29,99 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Explore the aspects of financial modeling with the help of clear and easy-to-follow instructions and a variety of Excel features, functions, and productivity tips
Key Features
- A non data professionals guide to exploring Excel's financial functions and pivot tables
- Learn to prepare various models for income and cash flow statements, and balance sheets
- Learn to perform valuations and identify growth drivers with real-world case studies
Book Description
Financial modeling is a core skill required by anyone who wants to build a career in finance. Hands-On Financial Modeling with Microsoft Excel 2019 examines various definitions and relates them to the key features of financial modeling with the help of Excel.
This book will help you understand financial modeling concepts using Excel, and provides you with an overview of the steps you should follow to build an integrated financial model. You will explore the design principles, functions, and techniques of building models in a practical manner. Starting with the key concepts of Excel, such as formulas and functions, you will learn about referencing frameworks and other advanced components of Excel for building financial models. Later chapters will help you understand your financial projects, build assumptions, and analyze historical data to develop data-driven models and functional growth drivers. The book takes an intuitive approach to model testing, along with best practices and practical use cases.
By the end of this book, you will have examined the data from various use cases, and you will have the skills you need to build financial models to extract the information required to make informed business decisions.
What you will learn
- Identify the growth drivers derived from processing historical data in Excel
- Use discounted cash flow (DCF) for efficient investment analysis
- Build a financial model by projecting balance sheets, profit, and loss
- Apply a Monte Carlo simulation to derive key assumptions for your financial model
- Prepare detailed asset and debt schedule models in Excel
- Discover the latest and advanced features of Excel 2019
- Calculate profitability ratios using various profit parameters
Who this book is for
This book is for data professionals, analysts, traders, business owners, and students, who want to implement and develop a high in-demand skill of financial modeling in their finance, analysis, trading, and valuation work. This book will also help individuals that have and don't have any experience in data and stats, to get started with building financial models. The book assumes working knowledge with Excel.
Shmuel Oluwa is a financial executive and seasoned instructor with over 25 years' experience in a number of finance-related fields, with a passion for imparting knowledge. He has developed considerable skills in the use of Microsoft Excel and has organized training courses in Business Excel, Financial Modeling with Excel, Forensics and Fraud Detection with Excel, Excel as an Investigative Tool, and Accounting for Non-Accountants, among others. He has given classes in Nigeria, Angola, Kenya, and Tanzania, but his online community of students covers several continents. Shmuel divides his time between London and Lagos with his pharmacist wife. He is fluent in three languages: English, Yoruba, and Hebrew.Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 226
Veröffentlichungsjahr: 2019
Ähnliche
Hands-On Financial Modeling with Microsoft Excel 2019
Copyright © 2019 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
Commissioning Editor: Pavan RamchandaniAcquisition Editor:Yogesh DeokarContent Development Editor:Nathanya DiasSenior Editor: Martin WhittemoreTechnical Editor: Joseph SunilCopy Editor: Safis EditingProject Coordinator:Kirti PisatProofreader: Safis EditingIndexer:Priyanka DhadkeProduction Designer:Jyoti Chauhan
First published: July 2019
Production reference: 1080719
Published by Packt Publishing Ltd. Livery Place 35 Livery Street Birmingham B3 2PB, UK.
ISBN 978-1-78953-462-7
www.packtpub.com
Packt.com
Subscribe to our online digital library for full access to over 7,000 books and videos, as well as industry leading tools to help you plan your personal development and advance your career. For more information, please visit our website.
Why subscribe?
Spend less time learning and more time coding with practical eBooks and Videos from over 4,000 industry professionals
Improve your learning with Skill Plans built especially for you
Get a free eBook or video every month
Fully searchable for easy access to vital information
Copy and paste, print, and bookmark content
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.packt.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.packt.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks.
Contributors
About the author
Shmuel Oluwa is a financial executive and seasoned instructor with over 25 years' experience in a number of finance-related fields, with a passion for imparting knowledge. He has developed considerable skills in the use of Microsoft Excel and has organized training courses in Business Excel, Financial Modeling with Excel, Forensics and Fraud Detection with Excel, Excel as an Investigative Tool, and Accounting for Non-Accountants, among others. He has given classes in Nigeria, Angola, Kenya, and Tanzania, but his online community of students covers several continents.
Shmuel divides his time between London and Lagos with his pharmacist wife. He is fluent in three languages: English, Yoruba, and Hebrew.
I would like to dedicate this book to my late parents, Yirael and Levi Oluwa, and to my beloved late sister, Hodel Oluwa. Only the memories can soften the loss.
About the reviewers
Bernard Obeng Boateng is a data analyst and a financial modeler with over 10 years' working experience in banking, insurance, and business development. He has a BSc degree in administration from the University of Ghana Business School, and is certified in business analytics from the world's leading business school, Wharton. Bernard is the principal consultant of BEST LTD, a firm that provides training and financial solutions to individuals and corporate institutions in Ghana.
As a financial modeler, he was part of a team that created an agriculture insurance risk model for the governments of Ghana and Rwanda. He has trained over 500 hundred corporate workers in Ghana and has an online training video series called Excel Hacks for Productivity.
Tony De Jonker, Excel Microsoft MVP is the principal of De Jonker Consultancy and AlwaysExcel, The Netherlands and specializes in Financial Modeling, Analysis, Reporting and Training for clients worldwide. He is the founder and presenter of the annual Excel events, such as Excel Experience Day, Excel Expert Class and Amsterdam Excel BI Summit. Tony offers a range of Excel Business related training courses in Dutch, English or German based on more than 34 years of spreadsheet modeling and more than 41 years of Finance and Accounting experience. and has written more than 150 articles on using Excel in Business for the Dutch Controller’s Magazine.
Packt is searching for authors like you
If you're interested in becoming an author for Packt, please visit authors.packtpub.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.
Table of Contents
Title Page
Copyright and Credits
Hands-On Financial Modeling with Microsoft Excel 2019
Dedication
About Packt
Why subscribe?
Contributors
About the author
About the reviewers
Packt is searching for authors like you
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files 
Download the color images
Conventions used
Get in touch
Reviews
Section 1: Financial Modeling - Overview
Introduction to Financial Modeling and Excel
The main ingredients of a financial model
Investment
Financing
Dividends
Understanding mathematical models
Definitions of financial models
Types of financial models
The 3 statement model
The discounted cash flow model
The comparative companies model
The merger and acquisition model
The leveraged buyout model
Loan repayment schedule
The budget model
Alternative tools for financial modeling
Advantages of Excel
Excel – the ideal tool
Summary
Steps for Building a Financial Model
Discussions with management
Gauging management expectations
Knowing your client's business
Department heads
Building assumptions
Building a template for your model
Historical financial data
Projecting the balance sheet and profit and loss account
Additional schedules and projections
Cash flow statement
Preparing ratio analysis
Valuation
Summary
Section 2: The Use of Excel - Features and Functions for Financial Modeling
Formulas and Functions - Completing Modeling Tasks with a Single Formula
Understanding functions and formulas
Working with lookup functions
The VLOOKUP function
The INDEX function
The MATCH function
The CHOOSE function
Implementing the CHOOSE function
Utility functions
The IF function
The MAX and MIN functions
Implementing the functions
Pivot tables and charts
Implementing pivot tables
Pitfalls to avoid
Protect sheets
Summary
Applying the Referencing Framework in Excel
Introduction to the framework
Relative referencing
Absolute referencing
Mixed referencing
Implementing the referencing framework
Summary
Section 3: Building an Integrated Financial Model
Understanding Project and Building Assumptions
Understanding the nature and purpose of a project
Conducting interviews
Historical data
Building assumptions
General assumptions
Profit and loss and balance sheet assumptions
Profit and loss account growth drivers
Year-on-year growth
Compound annual growth rate
Balance sheet growth drivers
Days of inventory
Debtor days
Creditor days
Summary
Asset and Debt Schedules
Understanding the BASE and corkscrew concepts
Asset schedule
The straight line method
The reducing balance method
Approaches to modeling assets
The detailed approach
Asset and depreciation schedule
The simple approach
Debt schedule
The complex approach
The simple approach
Creating a loan amortization schedule
Creating the template
Creating the formulas
Using the schedule
Summary
Cash Flow Statement
Introduction to the cash flow statement
Items not involving the movement of cash
Net change in working capital
Cash flow from investment activities
Cash flow from financing activities
Balancing the balance sheet
Troubleshooting
Circular references
Creating a quick cash flow statement
Summary
Valuation
Absolute valuation
Free cash flow
Time value of money
Weighted average cost of capital
Terminal value
Calculating the present value
Relative valuation – comparative company analysis
Trading comparatives 
Precedent transaction comparative
Summary 
Ratio Analysis
Understanding the meaning and benefits of ratio analysis
Learning about the various kinds of ratios
Liquidity ratios
Efficiency ratios
Return on average assets 
Return on average capital employed
Return on average equity
Debt-management ratios
Interpreting ratios
Understanding the limitations of ratio analysis
Using ratios to find financially stable companies
Summary
Model Testing for Reasonableness and Accuracy
Incorporating built-in tests and procedures
Troubleshooting
Understanding sensitivity analysis
Using direct and indirect methods
The direct method
The indirect method
Understanding scenario analysis
Creating a simple Monte Carlo simulation model
Summary
Another Book You May Enjoy
Leave a review - let other readers know what you think
Preface
Financial modeling is a core skill required by anyone who wants to build a career in finance. Hands-On Financial Modeling with Microsoft Excel 2019 examines various definitions and relates them to the key features of financial modeling with the help of Excel.
This book will help you understand financial modeling concepts using Excel, and provides you with an overview of the steps you should follow to build an integrated financial model. You will explore the design principles, functions, and techniques of building models in a practical manner. Starting with the key concepts of Excel, such as formulas and functions, you will learn about referencing frameworks and other advanced components of Excel for building financial models. Later chapters will help you understand your financial projects, build assumptions, and analyze historical data to develop data-driven models and functional growth drivers. The book takes an intuitive approach to model testing, along with best practices and practical use cases.
By the end of this book, you will have examined the data from various use cases, and you will have the skills you need to build financial models to extract the information required to make informed business decisions.
Who this book is for
This book is for data professionals, analysts, and traders, as well as business owners and students, who want to implement the skill of financial modeling in their analysis, trading, and valuation work and develop a highly in-demand skill in finance. The book assumes a working knowledge of Excel.
What this book covers
Chapter 1, Introduction to Financial Modeling and Excel, shows you the basic ingredients of a financial model and what are my favorite definitions of a financial model. You will also learn about the different tools for financial modeling that currently exist in the industry, as well as those features of Excel that make it the ideal tool to use in order to handle the various needs of a financial model.
Chapter 2, Steps for Building a Financial Model, helps you to devise a systematic plan to observe that will allow you or any other user to follow the flow from the beginning to the end of your model. It will also facilitate the building of your model and provide a useful roadmap for troubleshooting any errors or discrepancies that may arise.
Chapter 3, Formulas and Functions – Completing Modeling Tasks with a Single Formula, teaches you the difference between formulas and functions. You will learn the functions that make Excel ideal for modeling. You will also learn how to combine functions and where to get help with constructing your formulas where necessary.
Chapter 4, Applying the Referencing Framework in Excel, shows you what makes Excel come alive. The referencing framework is what makes Excel dynamic and enables the creation of integrated financial models. A sound knowledge of referencing in Excel can significantly speed up your work and is priceless for reducing the amount of boring repetition. You will learn in an uncomplicated manner how to use relative, absolute, and mixed referencing.
Chapter 5, Understanding Project and Building Assumptions, shows the measure of the importance of this topic, because about 75% of your modeling time should be spent on getting to know and understanding the project. As mentioned a number of times, there are different types of model. Which model you use will depend on the nature and purpose of your project, as well as your target audience. When building your assumptions, you will need to interview all those in a position to give informed and accurate growth projections for the various aspects of the entity's operations.
Chapter 6, Asset and Debt Schedules, shows us how to prepare an asset schedule to incorporate additions and disposals and current depreciation charges. You will also prepare a debt schedule to reflect projected additional finance and debt repayments as well as interest charges.
Chapter 7, Cash Flow Statement, covers the cash flow statement and explains how it is used in financial modeling. We will learn how to generate this as efficiently as possible.
Chapter 8, Ratio Analysis, teaches you how to compute performance indicators to give an idea of the projected financial health of the company. You can then compare this with historical ratios and determine whether this is consistent with your projections. The ratios will be divided into the following categories: liquidity, profitability, returns, and gearing.
Chapter 9, Valuation, shows us the various types of valuation methods and their advantages and affinity to different models. We will learn about the most accurate method, which is the discounted cash flow method.
Chapter 10, Model Testing for Reasonableness and Accuracy, provides a way to consider a range of alternatives to some of our key assumptions. Since we have been careful to format our input cells differently, we can quickly identify those inputs that have a significant impact on our final result, change them, and see the effect this has on our valuation. Finally, we look at the presentation of our results with the help of charts.
To get the most out of this book
A basic knowledge of statistics and Excel will be useful, along with a keen interest in financial modeling.
Download the example code files
You can download the example code files for this book from your account at www.packt.com. If you purchased this book elsewhere, you can visit www.packt.com/support and register to have the files emailed directly to you.
You can download the code files by following these steps:
Log in or register at
www.packt.com
.
Select the
SUPPORT
tab.
Click on
Code Downloads & Errata
.
Enter the name of the book in the
Search
box and follow the onscreen instructions.
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
WinRAR/7-Zip for Windows
Zipeg/iZip/UnRarX for Mac
7-Zip/PeaZip for Linux
The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/Hands-On-Financial-Modeling-with-Microsoft-Excel-2019. In case there's an update to the code, it will be updated on the existing GitHub repository.
We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!
Download the color images
We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here: https://static.packt-cdn.com/downloads/9781789534627_ColorImages.pdf.
Conventions used
There are a number of text conventions used throughout this book.
CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: "By typing =D4 in cell F5, the contents of cell D4, Happy day, have been duplicated in cell F5."
A block of code is set as follows:
=PMT($C$2/$C$4,$C$3*$C$4,$C$5)
Bold: Indicates a new term, an important word, or words that you see on screen. For example, words in menus or dialog boxes appear in the text like this. Here is an example: "The rows are labeled 1, 2, 3, and so on, up to 1,048,576, and the columns are labeled A, B, C, and so on, up to XFD."
Get in touch
Feedback from our readers is always welcome.
General feedback: If you have questions about any aspect of this book, mention the book title in the subject of your message and email us at [email protected].
Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packt.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.
Piracy: If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.
If you are interested in becoming an author: If there is a topic that you have expertise in, and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.
Reviews
Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!
For more information about Packt, please visit packt.com.
Section 1: Financial Modeling - Overview
In this section, you will understand the meaning of financial modeling with Excel, including an overview of the broad steps to follow in building an integrated financial model.
This section comprises the following chapters:
Chapter 1
,
Introduction to Financial Modeling and Excel
Chapter 2
, Steps for Building a Financial Model
Introduction to Financial Modeling and Excel
If you asked five professionals the meaning of financial modeling, you would probably get five different answers. The truth is that they would all be correct in their own context. This is inevitable since the boundaries for the use of financial modeling continue to be stretched almost daily, and new users want to define the discipline from their own perspective. In this chapter, you will learn the basic ingredients of a financial model and what my favorite definitions are. You will also learn about the different tools for financial modeling that currently exist in the industry, as well as those features of Excel that make it the ideal tool to use in order to handle the various needs of a financial model.
In this chapter, we will cover the following topics:
The main ingredients of a financial model
Understanding mathematical models
Definitions of financial models
Types of financial models
Alternative tools for financial modeling
Excel—the ideal tool
The main ingredients of a financial model
First of all, there needs to be a situation or problem that requires you to make a financial decision. Your decision will depend on the outcome of two or more options. Let's look at the various aspects of a financial model:
Financial decisions: Financial decisions can be divided into three main types:
Investment
Financing
Distributions or dividends
Investment
We will now look at some reasons for investment decisions:
Purchasing new equipment
: You may already have the capacity and know how to make or build in-house. There may also be similar equipment already in place. Considerations will thus be whether to make or buy, sell, keep, or trade-in the existing equipment.
Business expansion decisions
: This could mean taking on new products, opening up a new branch or expanding an existing branch. The considerations would be to compare the following:
The cost of the investment
: Isolates all costs specific to the investment, for example, construction, additional manpower, added running costs, adverse effect on existing business, marketing costs, and so on.
The benefit gained from the investment
: We can gain additional sales. There will be a boost in other sales as a result of the new investment, along with other quantifiable benefits. To get the
return on investment
(
ROI
), a positive ROI would indicate that the investment is a good one.
Financing decisions primarily revolve around whether to obtain finance from personal funds or from external sources.
For example, if you decided to get a loan to purchase a car, you would need to decide how much you wanted to put down as your contribution, so that the bank would make up the difference. The considerations would be as follows:
Interest rates
: The higher the interest rate, the lower the amount you would seek to finance externally
Tenor of loan
: The longer the tenor the lower the monthly repayments, but the longer you remain indebted to the bank
How much you can afford to contribute
: This will put a platform on the least amount you will require from the bank, no matter what interest rate they are offering
Number of monthly repayments
: How much you will be required to pay monthly as a result of the foregoing inputs
Financing
A company would need to decide whether to seek finance from internal sources (approach shareholders for additional equity) or external sources (obtain bank facility). We can see the considerations in the following list:
Cost of finance
: The cost of bank finance can be easily obtained as the interest and related charges. These finance charges will have to be paid whether or not the company is making profit. Equity finance is cheaper since the company does not have to pay dividends every year, also the amount paid is at the discretion of the directors.
Availability of finance
:
It's generally difficult to squeeze more money out of shareholders, unless perhaps there has been a run of good results and decent dividends. So, the company may have no other choice than external finance.
The risk inherent in the source
:
With external finance there is always the risk that the company may find itself unable to meet the repayments as they are due.
The desired debt or equity ratio
:
The management of a company will want to maintain a debt to equity ratio that is commensurate with their risk appetite. Risk takers will be comfortable with a ratio of more than 1:1, while risk averse management would prefer a ratio of 1:1 or less.
Dividends
Distributions or dividend decisions are made when there are surplus funds. The decision would be whether to distribute all the surplus, part of the surplus, or none at all. We can see the considerations in the following list:
Expectation of the shareholders
: Shareholders provide the cheap finance options and are generally patient. However, they want to be assured that their investment is worthwhile. This is generally manifested by profits, growth, and in particular, dividends, which have an immediate effect on their finances.
The need to retain surplus for future growth
:
It is the duty of the directors to temper the urge to satisfy the pressure to declare as much dividend as possible, with the necessity to retain at least part of the surplus for future growth and contingencies.
The desire to maintain a good dividend policy
:
A good dividend policy is necessary to retain the confidence of existing shareholders and to attract potential future investors.
Understanding mathematical models
In the scheme of things, the best or optimum solution is usually measured in monetary terms. This could be the option that generates the highest returns, the least cost option, the option that carries an acceptable level of risk, and the most environmentally friendly option, but is usually a mixture of all these features. Inevitably, there is an inherent uncertainty in the situation, which makes it necessary to make assumptions based on past results. The most appropriate way to capture all the variables inherent in the situation or problem is to create a mathematical model. The model will establish relationships between the variables and assumptions, which serve as an input to the model. This model will include a series of calculations to evaluate the input information and to clarify and present the various alternatives and their consequences. It is this model that is referred to as a financial model.
