Beginners Guide to Financial Modelling on Microsoft Excel

Most people probably have no idea of how extensive MS Excel is. From easy tips and tricks to formulae, MS Excel also has financial modelling capabilities. A financial model is an inbuilt feature in spreadsheet that allows businesses to use Excel to forecast the performance of a business now and in the future.

The goal of financial modelling is to bring together various aspects e.g. accounting, financial and business metrics to create an abstract representation in Excel. For any organization, financial modelling is a part of daily routine. Excel is also the most available and easy to customize tool.

Characteristics of a financial model

A financial model is not just a basic excel sheet. It is more complex than that, as it has some characteristics that are not found on the basic Excel sheet.

First, a good financial model should be dynamic. Any changes done to the figures in the model should affect the outcome of the results.

Secondly, the inputs used in the model are interdependent and when a user changes any of the input, the entire model is affected.

Lastly, the models indicate what is likely to happen (forecast) and hence always look into the future and not backward.

In a nutshell, financial, model in Excel are a more complex spreadsheet that contain unique structure, variables and outputs.

Other than decision-making and financial analysis, a financial model is also used to raise capital, business and asset acquisition, growing a business organically, asset valuation as well as budget forecasting.

Strategies for Excellent financial Modelling on MS Excel

In as much as financial modelling is a complicated process, it is important to have a general idea of how to go about it and the steps to follow when building a model.

1. Write down your design ideas on paper

Have a strategy on how you want to build a financial model. Think about your business or organization, your main sources of revenue and expenditure. What costs are dependent on sales, the initial investment, main sheets for the model etc. On a blank piece of paper, design your ideas to structure and model your model in Excel.

2. Define your goal

What do you want your financial model to do?  This should be a top consideration as no model can perfectly fit your goals and hence the need to create a model from scratch.

3. Create your worksheet

We have many MS Excel models or templates that are made for various projects and you can use any of them. However, it would be a great idea to create your own so that you can interact with the features that you want to incorporate to your model.

You can borrow some of the features on these models but because your company and goals are unique, creating your model from scratch would be ideal.

Start by opening a blank Excel sheet and save it using a name of your choice. You can add a number or “version 1.0” because it will help organize the files in your folder as well as clearly show the progress in creating the model. Make sure to save all these versions in one folder to avoid losing them.

Create a template sheet that will be the reference for all the sheets that will be used in the model. Define the timelines, the purpose of each column, use color codes and letters. When you need another sheet, just copy the template and populate it differently.

4. Define your timeline

When would you like to start your model? Do you want to include historical data or forecast data only?  Are you looking to have a monthly, quarterly or annual model? All these will depend on the goal that you have settled for. For example, the immediate next financial year can show monthly level while the subsequent years can show annual level.

[Also Read: Considerations for Dashboard Creation in Microsoft Excel]

5. Create a control sheet

A control sheet will serve many functions and you can include all the generic information that you need in your model. For example, what colors will be used for what purpose, number of months per year, range, names, dates etc.

Defining the range is something that will be updated on a constant basis but you can create the lists that you will need to use as well as the correspondent ranges. Depending on your financial model, ranges can refer to the start date and end date, graph information, credit and financial ratios. A typical list that you can create is the Yes or No list.

6. Create a Stress Check

One of the easiest ways to stress check a model is by testing the formula logic built into the calculations of the model. A simple  test will reveal if the resulting values make any sense. A more extensive test is filling the formula down or to the right in the adjacent cells and confirm if the change flows properly. If they do not, it means that a reference within the formula has been overlooked and must be adjusted. Stress testing always reveals these errors.

7. Design the output sheet

By the time you decide to create a financial model, it means that you already know the problem that your model should solve. It is also important to have an idea what the answers might look like.

Outputs refer to summaries, charts and reports that will appear or represent the information keyed in. For example, if you are making a decision about looking for a financier for a project, the output maybe the subsequent cash flow and the expected returns.

By thinking and planning about the output of the model from the onset, it will encourage you to be more focused and ensure that the calculations will give the desired report.

When designing a financial model, it is important to use different colors to differentiate between various inputs and outputs. Separate your assumptions from the rest of the model and use clear and bolding titles for your sections.

Avoid linking your model to other Excel sheets to minimize the errors that arise. Lastly, avoid using circular references unless necessary. Circular references mean having a formula in a cell and this formula uses the same cell for calculation.

Tags :
finance,Financial Modelling,Microsoft Excel
Share This :