Book Image

Hands-On Financial Modeling with Microsoft Excel 2019

By : Shmuel Oluwa
Book Image

Hands-On Financial Modeling with Microsoft Excel 2019

By: Shmuel Oluwa

Overview of this book

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 explores terminologies of financial modeling with the help of Excel. This book will 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 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 and covers best practices and practical use cases. By the end of this book, you will have examined the data from various use cases, and have the skills you need to build financial models to extract the information required to make informed business decisions.
Table of Contents (15 chapters)
Free Chapter
1
Section 1: Financial Modeling - Overview
4
Section 2: The Use of Excel - Features and Functions for Financial Modeling
7
Section 3: Building an Integrated Financial Model

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:

  1. 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.
  2. 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.