Money doesn’t give you happiness. Yet, it is much better to be unhappy in your new BMW than on your bicycle 😉
In consulting you will have to from time to time create or supervise the creation of financial models. There are the bread and butter of every due diligence, restructuring or even business development projects. Sometimes you will have to create a simplified version of a financial model that I call a business model in Excel. Nevertheless, on many occasions you will have to deliver a full blow financial model that is generating financial statements such as profit and loss, balance sheet and cash flow. It is not the most thrilling thing to do and you most likely will not have wet dreams about playing with financial models, yet it is a thing that you have to master to be a true badass consultant.
Today, I will show you how to do it fast and efficiently to have more time for procrastination.
As an introduction have a look at the picture below that shows on the left the separate sheets in Excel that you will have to create in order to generate 3 financial statements (profit and loss, balance sheet and cash flow)
I will explain step by step how you can generate those 3 financial statements. It is always better to work with example, so I will use the case of a firm that is producing hand-made products from clay: ceramic dishes, chimneys, tubes and pots. On the basis of the data from 2014 and 2015 we want to model their business model till 2022.
Profit and Loss Account.
- Sales / Revenues. The first thing you will want to do is to model the profit and loss of the firm. There is no profit without sales, so the first thing to do is to model in a separate sheet the sales / revenues of the firm. Modeling of this part is pretty easy. You have to estimate how many products you will sell and at what price. There are plenty of ways in which you can do it. In our example we will do it on the product group level. We have 4 groups of products: ceramic dishes, chimneys, tubes and pots. For every group of products we will define the following drivers of the sales / revenues: # of products sold at the beginning of our analyses, average price per product at the beginning of our analysis, growth in quantity and growth in price. Below the end result:
- Materials and Energy. Once you are done with the sales you can get down to estimating the costs. Every cost position we will estimate in a separate sheet. The first thing we will start with is the materials and energy you need to use to produce the products. When it comes to materials and energy we can divide them into fixed and variable. Fixed are set at certain level and they will not change. Variables will depend on the size of the sales. You can model it by assuming certain cost per product or you can model it as a set percentage of sales. We will use the latter approach. Below the end results.
- Payroll and Social Security costs. One of the biggest cost position is always the salaries and wages. We model it in the sheet Payroll. Again we use very simple approach. We look at the number of people we have to have and the average costs per employee (including social security, pension plans etc.). In our example we model them in 3 separate groups: Head Office (high paid employees), Production Plant (lower paid employees) and the Board of Directors (the overpaid ones). We first model the direct salaries / wages by looking at the number of people, the average salary at the beginning of the analysis and assumed per annum growth of salaries. On top of that we calculate the social security costs as a percentage of the payroll costs. Below the end results:
- External Services. This cost position we treat in a similar way as materials and energy. We have some fixed part that doesn’t depend on the sales (i.e. rent of the office space) and a variable part that we calculate as a set percentage of the sales. In some cases you can model it as a set cost per unit of production. Below the end-result
- Depreciation. So far it was a piece of cake. You didn’t have to break a sweat. We were doing simple calculations to model each and every cost position. Depreciation is much more complicated as you have to calculate for every assets 3 things: gross book value (how much I paid for it when it was acquired), depreciation (what part of the gross value I can allocate to costs), net book value (gross value – sum of all depreciation). On top of that, you have to do it for every year separately to track assets. Since a picture is worth a thousand words below a movie that will explain you slowly but surely how to do it in practice:
- Taxes and Payments. In cost positions we have some taxes and payment that we have to pay to local government. Those taxes don’t depend on profit and are just cost position. They usually depend on the size of the factory, space, number of employees. Here we have assumed that those taxes are fixed and only change according to the inflation rate.
- Other costs. As always other costs may appear. Those you can model either as fixed or as a percentage of all costs or sales. I have chosen to model it as a percentage of sales. I applied the same approach also to other operating costs and other operating revenues.
After we have managed to calculate all cost positions and sales we can combine it into one beautiful sheet Profit & Loss statement. Below is what you will get:
Now it’s time to create the balance sheet. So far we have only been able to estimate the net book value of fixed assets that we can use to generate the Fixed Assets part of the Balance Sheet. We still have to estimate the working capital, debt, equity. This will help us generate the liabilities and current assets. Below the scheme showing how roughly we will proceed:
Let’s see in details how we will estimate each and every piece to assemble the balance sheet:
- Working Capital. This part is a little bit more difficult as you have to take into account a lot of components. Below an overview of all elements that you have to generate to properly estimate the working capital. In Working capital sheet you will have to estimate how much money will be eaten away by receivables and inventory and to what extent you can rely on financing yourself using liabilities (mainly from suppliers). In most cases you use as a driver the sales or cost level and specific position in days of sales or days of production. As I said in some cases a movie is the best means of explanation, therefore, below a movie that will show you how to estimate each and every component of working capital:
- Debt. Another thing you have to estimate before you start building the balance sheet is the debt, bank loans that you have taken. In our case there are 2 loans: an old one and a new one. Since we want to show separately the short-term and long-term debts you have to divide those 2 loans into 2 parts: short-term (what will have to be paid this year) and long-term (what will have to be paid next years). The payment of the loans will depend on the schedule agreed with the banks. We want to pay the old loan now and the new one that we will take this year we want to pay back in 3 years. In this way in 2022 we will be debt free 🙂 Below how it will look like in practice in Excel sheet.
- Equity. The last part that we have to model to assemble the balance sheet is the changes to equity. In our case we assume that here will be no new paid-in capital from shareholder. We also assume no changes to reserve and revaluation capital. There will be only changes in supplementary capital due to withholding some of the profits. We will pay out 1/3 of profits from previous year as a dividend in 2017 and after 2017 2/3 of the profits. Below you can see the end results.
- Cash. The only position missing from our calculation is the Cash. This is done on purpose. At the end we want the Assets to be equal to Liabilities (basic rule of bookkeeping). That’s why in our model the cash will be modeled as a difference between Liabilities and the sum of Assets expect for the Cash. Below a graph that show what we want to achieve. As you can see we get all the necessary data for liabilities from the sheet: Debt, SC (Equity), P&L (profit and loss statement) and Working Cap. Assets are generated mainly using Working Capital sheet and the Fixed Assets Summary (FA Summary) from which we get the net book value of the assets. The cash is the difference between Liabilities and the sum of assets expect for the Cash.
At the end you will get a nice looking balance sheet. The last part of the model is to generate using the balance sheet the cash flow statement. You want the cash flow position we will get from this method to be equal to the cash position generated in the balance sheet. If there is a discrepancy it means that there was some mistake in our model. Below the general scheme that we will use to generate the cash flow. We will start with the net profit and we will use deprecation (from Fixed Asset Summary sheet), difference between interest paid and earned (from Profit and Loss sheet) and changes in inventories, receivables, liabilities and others (from Balance Sheet) to get to the cash flow from operating activities. This will tell us how much cash was generated from the ongoing business activities. Next stage is to see what will be left after you pay for investments (expansion and maintenance capex), therefore you deduct from the operational cash flow the cash flow from Investments. Investments made will decrease the cash flow and disinvestment will increase it. Now we have to pay the interest and the loans. That’s why as a next step you deduct the financial cash flow. Taking a new loan or getting interest on your cash will increase the cash flow whereas paying loans and paying interest will decrease it. After this we finally get to the so called Free Cash Flow to Equity. We can finally share our profits with shareholders. What is left will increase our cash flow position. Below the results for our case:
We have finally reached the end of building the financial model. By now you should have plenty of detailed sheets showing in details the sales generation, cost positions and their drivers, capex by groups, debt, equity and working capital creation along with the underlying assumptions. From this we got the 3 financial statements: profit and loss, balance sheet, cash flow. Below roughly how the data flows between different sheets in Excel.
From here we can go many ways:
- Valuation. We can use the model to estimate the value of the business using DCF or the multiplier method. That’s why during due diligence projects such model is a must
- Estimation of savings. We can use the model to estimate the impact of potential improvement that we can implement. For example you can cut down some of its costs, reduce the number of people needed, renegotiate the loans, contracts with service providers etc. The model helps you estimate what could be the impact of your actions both on profit as well as on cash.
- Budgeting and cash flow management. Models like this can be also use to manage the cash flow of the firm or to create the budget for the next year. Such model with also be a must if you do a turnaround projects where the cash position, liquidity is even more important the profitability. In consulting you may be forced to prepare such a model by the client who want to re-negotiate with the banks the loans or by banks that want to check whether the business they are asked to finance will not collapse.
That’s in short. Below you will find the case we have discussed in the form of a presentation. For more check my on-line course Financial Modeling for Business Analysts and Management Consultants
I recommend checking the following courses where you can find all the financial models in Excel and step by step guides how to create them:
- Financial Modeling for Business Analysts and Management Consultants
- Financial Modeling for Startups
- Online Business Models in Excel – Practical Guide
- Offline Business Models in Excel – Practical Guide