Basic financial forecasting

You decided to start a company to sell out your new product. You know the details of the business, but you don’t want to go deep into the financial vocabulary. Here you can find a simple way to develop your initial pro-forma financials to keep handy for a possible investor meeting or you want a tool for thinking about the viability of your business.

You are thinking of putting $100K of start-up capital as equity on the table, and you will inject all the amount right into your business as working capital. You do not want to spend any portion of your money to fixed assets at least for now, and you are going to rent a fully furnished co-working space. This means, you are not going to think of depreciation and amortization of the equipments you will use. Additionally, you are not going ask for a loan from a bank, and planning to stay away from debt or any other liabilities.

Hint!, if you follow along the rest of this post, your final model will look like this one below!

Now you have a look and feel about what is the outcome of reading and applying the article. Let’s dive in!

You think you are going to sell at least $100K in the first year, and expect to grow your sales by 10% every year. The products you will sell, will cost you - Cost of Goods Sold - COGS - 20% of sales amount.

Besides, you expect the Operational Expenses like payroll, sales commissions, employee benefits, transportation and travel, rent and other expenses to incur 70% of sales amount. Since you don’t have historical data, you are using your educated guessing to approximate these point estimates.

You also think to keep company cash in the bank and receive 5% interest income over the year. The tax rate from your net income will be 30% and inevitable.

Since you want to grow your business, you will continue to put 90% of your sales into the Working Capital every year.

Now, we can summarize the assumptions above for our financial model.

Before continue reading, you may want to open a spreadsheet and type or copy the assumptions table above. Based on these assumptions, we can proceed to create our three-statement financial model.

Assumptions table will function as the model driver. Avoid entering any number into the rest of the model.

While entering the formulas into the spreadsheet you should not type any number, and just link your cell to the proper assumption item and lock the cell with F4.

Here is the Income Statement calculated according to assumptions.

As you can guess; Income Statement items are mainly derived from our Sales amount, as per the assumptions. Sales forecasts may be very detailed, with separate forecasts for each product, geography, or customer segment. Similarly, COGS and OPEX can be broken down into detailed line items which can be forecasted separately. For the purposes of this post, we keep everything in its simplest form.

You can go over the descriptions for the Income statement items below.

Once you have finished with the Income Statement, you can proceed to Balance Sheet items. Before typing the numbers lets get a brief overview of the balance sheet. This will help you to visualize which items balances with each other. Remember the fundamental equilibrium of accounting:

Total Assets = Liabilities + Equity

According to your assumptions, the initial Balance Sheet on day 1 will look like this. Your initial capital will sit on the Equity line, and your decision to put the entire amount into the business reflects on Working Capital. You also decided to rent a furnished office space, which helped us to omit fixed assets item in the balance sheet.

You may have seen various balance sheet layouts different then above. This is an over simplified version, that omits irrelevant items specific to our case. Remember we do not have fixed assets, as we rent the office, and we do not use bank loan. This level of granularity is sufficient for our purposes.

The first number you will link is the Equity (Line 12) at the Start column. Link the equity amount from assumptions table above.

Then link this equity amount to Working Capital (Line 9). Notice that we have only transposed the our starting Balance Sheet into another balance sheet with time dimension.

Let’s take a look at the calculation of Balance Sheet items below.

Before going deep into details of each item, CHECK if your balance sheet balances, that is to verify Line 10 equals to Line 13. If that is not ok, you might have an error in your formulas or links. Take some time to correct your spreadsheet and come back.

…

Now, we can continue to create Cash Flow Statement. Cash Flow Statements records the amount of cash entering into or leaving the company. Cash flow statements reveal the financial health of company.

Since we focus on the cash movements only, we will focus on changes relating to cash. We will start by adjusting Net Income in the statement of Cash Flow.
For example, we normally deduct Depreciation in the Income Statement calculations, but for cash flow statement we have to add it back as an adjustment to Net Income amount in the cash flow statement. No cash goes out of the company because of depreciation. In our case this is zero for all years, as seen in Line 15.

Likewise, increase in Fixed Capital grows the balance sheet but we deduct that amount for each year in the Cash Flow statement calculations. Any new investment made will lead a decrease in company cash, and we need to reflect it on the cash flow statement.

To calculate each period’s cash flow, we will begin with Net Income from the Income Statement, (simply link Line 7 to Line 14).

You can have a more detailed description for each line item in below table.

If you have finished creating spreadsheet version of your model, you can play with the initial assumptions table and observe changes.

If you are able to come this far, you can trust yourself that you can develop a basic three-statement financial model for your company.

Thank you for reading.