September 19, 2017
Uncategorized No Comments

Advanced financial modeling in excel

Financial modeling in Excel has become the standard when it comes to preparing financial plans and cash flow forecasts. As such financial models are used to justify financial decision-making. To convince investors to contribute funds, this is much easier than when having a solid advanced financial model at hand.

So what do you need to do if you like to present a more advanced financial model in Excel?

You can consider the following:

Separating Input from Output

Your model should clearly separate inputs from outputs. Professional financial modelers therefore use different colors for assumptions and calculations. Focusing on the blue input cells below, allows a skilled financial modeler to quickly understand the structure of the model and play around with the assumption.

Input output


Adding Financial Ratios

Putting up cash flows on an excel worksheet is one thing, making sense of the data is another thing. For this reason, an advanced financial model on Excel needs to also show the development of a variety of important financial ratios. There are financial ratios of relevance to banks, the management or also the business owners. Liquidity financial ratios tell you if the company has sufficient cash to serve its short-term obligations. Bank financial ratios which focus on debt leverage should assess, whether the company has any unused debt capacity and how much more debt a company could raise – if needed. Efficiency and return ratios tell you how efficient the invested assets are used in relation to the financial returns.

Financial ratios

Adding Scenarios

Financial models provide always s a very subjective point of view towards a business case since they depend on their assumptions. What can be very useful is to think in scenarios, e.g; a downside, base, and upside case. Therefore, e.g. a real estate investment model can use three different set of rental price assumptions to figure out the resulting range of rental incomes and capitalized building values. Financial modelers then simply add a switch in Excel which allows them to use the assumptions specified for the selected scenario.

Sensitivity Tables

Sensitivity tables are tables with two different input parameters on the x and y axis and a range of upper and lower values each. Below, sensitivity tables, based on the example of a Gold Mining financial model are shown on the right. Key input assumptions such as operating costs per ton, or the gold price are varied and the sensitivity table outputs the desired value (e.g., the Project IRR in this case). The financial model now has to be linked in such a way that each column header is inputted into the financial model, and the result is then updated on the sensitivity table. Therefore the excel model needs to be enhanced with the inputs on the left, but also the model itself needs then to link to these inputs and take the changes into account.


Sensitivity Table in Excel

Sensitivity Table in Excel

Sensitivity tables are hard to read. Therefore, one can create a Tornado Diagram (top left) which uses the minimum and maximum result of the sensitivity table to illustrate the effects of changing the input parameters.

These are just some first points which can be looked at when presenting more advanced spreadsheets with financial modeling in Excel.