Projected Financial Statements (otherwise known as pro-forma financial statements) refers to a set of financial statements which are an important part of a business model. No, not important, ESSENTIAL. It’s actually the apotheosis of the whole business model and is the first thing that’s turned to when someone looks at it. Whatever has been done before, is only in preparation for creating these statements.
The pro-forma should contain the following three statements:
- Profit and Loss
- Balance Sheet
- Cash Flow
- (You can also include Statement of Change in equity if you are willing to, but investors usually only want to see the first three)
In this post, I am going to look at the simplest way to produce the pro-forma Balance Sheet and Profit and Loss statement. In the next blog, I will look at the Cash Flow Statement and Statement of Changes in Equity.
First thing’s first, let’s talk about the Statement of Profit and Loss. It’s the easiest one to do, which you’ll remember if you’ve followed my blog from the beginning. If not, to make a Profit and Loss statement in Excel, you should have a detailed (ideally, monthly) budget, where all your expense items are attributed to certain expense types, as they are going to appear in your final Profit and Loss statement. Then simply, by using the Sumif function, you summarise all your budgeted figures on an annual basis. The process looks like this:
Once you have your Profit and Loss statement, you can use it as a starting point for creating your Balance Sheet.
From the Profit and Loss statement you need the Net Profit figure, which you will use for your Retained Earnings in the Balance Sheet (within the Equity section). For the first year the Retained Earnings will be equal to the Net Profit figure. Every subsequent year’s Retained Earnings is the Retained Earnings from the previous year, plus Net Profit for the current year.
Then you need to work out the components of your working capital, which I covered in Financial Modelling for Start-ups. Part 3: Working Capital.
Next, I suggest you calculate your Cash line. In order to do that, you need to make sure your budget has a line for cash (or bank – doesn’t really matter how you name it) at the bottom. This is where you summarise the income, (less expenses, plus investment) you are planning to receive and any loans. Ideally you should track it on a monthly basis against your actual cash in bank. Once you have that sorted, use the amount at the end of the year for your Balance Sheet Cash account. Then it needs to be adjusted for the accounts receivable and payable by decreasing it by the amount of receivables and decreasing it by the amount of your payables.
If you have investment coming in, include it in your Capital within Equity, and corresponding amount, to the Cash line. If you have a loan, add a line for Long or Short-term loans in Liabilities and the corresponding amount goes to Cash.
Capital line should include all capital put up as of the Balance Sheet date and the same amount should either be in Receivables from the owners or Cash (included in your budget in the Cash line as well).
That’s it for this blog, in the next one we will cover the other two financial statements to make your business model look even more beautiful!
