Financial modelling and analysis

Financial modelling 

Many of us have heard of the word “modeling” especially in the context of data modeling and financial modeling. What does the word model mean? In simple terms, a model represents a scenario of the real application done in a confined manner. For example, if a group of engineers were to build a bridge, they would sit together and build a “model” of it first to see if it would fit in the actual place. This model will help them understand many factors such as wind, tension, stress, maximum load, etc. Most of the models are done in labs or a confined area. In the financial world, the financial modeling refers to models that are used to dict an outcome of financial performance. For example, investing in a property and expecting it to appreciate over a number of years (assuming no natural disaster happens during this period which may then lower the price of property). This is a sample financial model.    

• It is important to remember that when reading a pictorial representation of any model, it should be read from left to right. Generally, time periods are on the X-axis moving from current (left) to future (right). 

When designing a model, many often wonder how long one should design the model for i.e. short term or long term. This would really depend on the given scenario. For example, if you are looking at a company’s cash flow, then you would ideally look at the next 12 months. However, if your project involves growing trees for timber , then you may need to stretch your model to something like 30 years (the aver-age duration required for trees to grow to its peak).  

Note that when you use a model to predict the company’s cash flow for 5 years, the chances of it being accurate or near accurate is unlikely as no one can predict this! 

There are just too many external factors that can affect these projections such as  the local and global marketing conditions, market trends, acceptance level of product/ser-vices, life cycle of product/services, competition from overseas, etc. 

When you plan out your model, it could be based on months, quarters, or years. 

You should decide what is best. For example, do not make a model with a monthly prediction for 10 years. It would be better to use yearly predictions instead. 

Generally speaking, do not make a weekly model for more than a year; monthly model beyond 3 years; and quarterly model beyond 5 years. 

On a personal basis, I have always used a monthly model for most of my predictions (generally one year and more).  

Analyzing a simple financial model 

Now that you have been briefed on what a financial model is, let us have a look at a simple financial model. The screen capture shows the income (or revenue) predicted for the first 5 years of operations, its related expenses and profit (in value and percentage) .

Let us analyze the above financial model. The income row indicates that income increases gradually over the 5 years.  However, the expense also increases over the years. The company maintains a reasonable profit throughout the 5 years. The last row indicates that profit (in percentage) is actually decreasing over the 5 years. 

The formula for the above is as follows (accomplished by pressing the Ctrl + ~ keys. 

Pressing the same keys once more will remove the formulas from being displayed). If you were heading the above company, what would you do to increase the profit margins further? 

Before proceeding any further, you need to understand more financial statements and how to read and interpret them before you can come to any understanding some " cost cutting measures '' to improve profitability.

Basic financial statements 

There are three basic financial statements. These are: 

Balance sheet 

Income statement (or sometimes known as profit and loss statement)

Cash flow statement 

To help understand your company’s financial statement better, it would help if you 

had some understanding of the above three to create a proper financial model. 

Balance sheet 

The balance sheet indicates the financial position of a company at a specific moment in time. It tells what you "own" and what you "owe" on a specific date.  It 

should be noted that the balance sheet of a company changes from day to day. 

A balance sheet is usually prepared at least once a year to show to all the share-holders and to the public (if it is a public listed company).  

As companies operate on a 12 month fiscal year period, they usually prepare the financial statements at the end of this fiscal year. The financial statements are prepared by accountants and require to be audited (for a private limited and public company).  

The balance sheet is prepared at the end of the fiscal year and sometimes can take months to prepare (quite often outdated when it is ready!). 

A balance sheet has three basic components: 

Assets (anything and everything physical the company owns) 

Liabilities (amount the company owes to others such as banks or other financial establishments) 

Equity (amount the company owes to the investors based on their capital injected) 

You can relate all the three basic components as follows: 

Assets = Equity + Liability

500,000 = 100,000 + 400,000

The formula above helps anyone understand how the three components are related. 

For example, if you decide to purchase a property worth $500,000 (Asset), you have decided to pay a down payment (or initial investment) of $100,000 (Equity). 

The balance of payment to purchase the property will come in the form of a loan from the bank, for example. In the above, the balance of $400,000 will come from the bank as a loan. This is the known as the liability as you owe this amount to the

bank (including the interest and other fees that the bank will impose on you). 

Note that in any balance sheet, the Total assets (commonly displayed on the left side) must always equal to the Owner’s equity plus the Liabilities (typically shown on the right side of the equation).

Alphanerum announces new interesting course for financial modelling and analysis using Excel sheet .

Financial modeling and analysis for engineers ,non finance exerts,is very necessary knowledge for lead engineers and project managers to understand it and estimate the finance of the project and discuss any items with the client and executive managers.

The items of the course is :

Part 1

Introduction to financial modelling

Analyzing a simple financial modeling

Basic financial statements

Balance sheet

Limitations to Balance sheet

Income statement

Limitations of the Income statement

Some terminology

How to tell if the Balance sheet work

Depreciation

Off Balance sheet financing

PART 2 : UNDERSTANDING LOGICAL FUNCTIONS

Logical functions

IF function

Nested IF function

AND function

OR function

NOT function

Further logical function examples

PART 3 :UNDERSTANDING FINANCIAL FUNCTIONS

Financial functions

FV function

Time value of money

PV function

PMT function  

NPER function

NPV function

IRR function

RATE function

IPMT function

PPMT function

SLN function

SYD function

VDB function

MIRR function

To recap

PART 4 : CREATING FORM BUTTONS

Form buttons

Using list to create options

PART 5 : INVENTORY BALANCE EXERCISE

Creating an inventory system

PART 6 : CALCULATING LOAN REPAYMENT

Calculating monthly loan repayments

PART 7 :HANDLING BOTTLENECK MANAGEMENT

Handling limiting factors

PART 8 :CALCULATING CUSTOMER PROFITABILITY..

Customer profitability

PART 9 : COMMONLY USED TERMINOLOGY.

Commonly used terminology

PART 10 : Principle rules of financial modelling

If you are interested ,please email me via [email protected] and cc. [email protected]

Special discount to early booking before 15.11.2019