Home Data Hands-On Financial Modeling with Excel for Microsoft 365 - Second Edition

Hands-On Financial Modeling with Excel for Microsoft 365 - Second Edition

By Shmuel Oluwa
books-svg-icon Book
Subscription FREE
eBook + Subscription €11.99
eBook €24.99
Print + eBook €30.99
READ FOR FREE Free Trial for 7 days. €11.99 p/m after trial. Cancel Anytime! BUY NOW BUY NOW BUY NOW
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
READ FOR FREE Free Trial for 7 days. €11.99 p/m after trial. Cancel Anytime! BUY NOW BUY NOW BUY NOW
Subscription FREE
eBook + Subscription €11.99
eBook €24.99
Print + eBook €30.99
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
  1. Free Chapter
    Chapter 1: An Introduction to Financial Modeling and Excel
About this book
Financial modeling is a core skill required by anyone who wants to build a career in finance. Hands-On Financial Modeling with Excel for Microsoft 365 explores financial modeling terminologies with the help of Excel. Starting with the key concepts of Excel, such as formulas and functions, this updated second edition will help you to learn all about referencing frameworks and other advanced components for building financial models. As you proceed, you'll explore the advantages of Power Query, learn how to prepare a 3-statement model, inspect your financial projects, build assumptions, and analyze historical data to develop data-driven models and functional growth drivers. Next, you'll learn how to deal with iterations and provide graphical representations of ratios, before covering best practices for effective model testing. Later, you'll discover how to build a model to extract a statement of comprehensive income and financial position, and understand capital budgeting with the help of end-to-end case studies. By the end of this financial modeling Excel book, you'll have examined data from various use cases and have developed the skills you need to build financial models to extract the information required to make informed business decisions.
Publication date:
June 2022
Publisher
Packt
Pages
346
ISBN
9781803231143

 

Chapter 1: An 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.

To provide some clarity, in this chapter, we will learn some popular definitions and 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.

By the end of the chapter, you should be able to hold your own in any discussion about basic financial modeling.

We will cover the following topics:

  • The main ingredients of a financial model
  • Understanding mathematical models
  • Definitions of financial models
  • Types of financial models
  • Limitations of Excel as a tool 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 alternative scenarios as described in the following subsections.

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 the equipment in-house. There may also be similar equipment already in place. Considerations will thus be whether to make, 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:
    1. The cost of the investment: Isolate all costs specific to the investment, for example, construction, additional manpower, added running costs, adverse effects on existing business, marketing costs, and so on.
    2. The benefit gained from the investment: We could gain additional sales. There will be a boost in other sales as a result of the new investment, along with other quantifiable benefits. Regarding the return on investment (ROI), a positive ROI would indicate that the investment is a good one.

Financing

Financing decisions primarily revolve around whether to obtain finance from personal funds or external sources:

  • Individual: 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 deposit so that the bank would lend you the difference. The considerations would be as follows:
    • Interest rates: The higher the interest rate, the less you would seek to finance externally.
    • Tenor of the 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 establish the least amount you will require from the bank, no matter what interest rate they are offering.
    • Amount of monthly repayments: How much you will be required to pay monthly to repay the loan.
  • Company: A company would need to decide whether to seek finance from internal sources (approach shareholders for additional equity) or external sources (obtain bank funding). We can see the considerations in the following list:
    • Cost of finance: The cost of finance can be easily obtained with the interest and related charges. These finance charges will have to be paid whether or not the company is making a 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 fall due. This exposes the company to all the consequences of defaulting, including security risk and embarrassment among other things.
    • The desired debt-to-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:

  • The expectations of the shareholders: Shareholders provide cheap funds and are generally patient. However, shareholders 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 funds are considered cheap because payment of dividends is not mandatory but at the discretion of the directors.
  • The need to retain surplus for future growth: It is the duty of the directors to temper the urge to succumb to pressure to declare as many dividends 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 investors.

You should now have a better understanding of financial decision making. Let's now look at mathematical models that are created to facilitate financial decision making.

 

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 cheapest option, the option that carries an acceptable level of risk, or the most environmentally friendly option, but is usually a mixture of all these features.

Inevitably, there is 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 that serve as 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. This model is referred to as a financial model.

         
About the Author
  • Shmuel Oluwa

    Shmuel Oluwa is a financial executive and seasoned instructor, of over 25 years, in a number of finance related fields, with a passion for imparting knowledge. He has developed considerable skill in the use of Microsoft Excel and has organised training courses in Business Excel, Financial Modeling with Excel, Forensics and Fraud Detection with Excel, Excel as an Investigative Tool, Accounting for Non-Accountants, Credit Analysis with Excel amongst 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 3 languages. English, Yoruba and Hebrew.

    Browse publications by this author
Hands-On Financial Modeling with Excel for Microsoft 365 - Second Edition
Unlock this book and the full library FREE for 7 days
Start now