Book Image

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

By : Shmuel Oluwa
Book Image

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

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 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.
Table of Contents (19 chapters)
1
Part 1 – Financial Modeling Overview
4
Part 2 – The Use of Excel Features and Functions for Financial Modeling
8
Part 3 – Building an Integrated 3-Statement Financial Model with Valuation by DCF
15
Part 4 – Case Study

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.