Tag: modelling

Financial modelling for start-ups. Part 5: Projected Financial Statements

  |   By  |  0 Comments

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!

 

Financial modelling for start-ups. Part 4: Hiring plan

  |   By  |  0 Comments

A hiring plan is an essential ingredient when it comes to building a successful business model, therefore, in this post I will show you how to construct one.

Staff-related costs are a huge component of the profit and loss and the hiring plan is something that will help you to calculate these costs precisely, allowing you to later change them accordingly in line with any developments in your forecasts.

Below is an example of how a hiring plan should look:

Below is a step-by-step guide to building your own:

1 – Make a list of the job roles with salaries. Salaries can be found on any local job search website. You might want to allow for salary increases once the company is launched, bearing in mind that there will be more responsibilities which should be awarded for.

2 – Each column to the right of the salaries serves as a month where you can input the number of people that you need to fulfil a particular position. They should all start with zero and grow over time.

3 – Identify the job roles which will require more people as the business grows, and list them in order of growth in sales volume. For our company these are the positions that involve working directly with the customers one-to-one, such as on-boarding, training, and sales managers. These are highlighted in grey in the picture above. Once these roles are identified, their number should be linked to the sales volume. To do that, you can create another table where you identify the number of customers to be covered by each role, as shown below:

This information should be included into your ‘costs assumption’ sheet and will be variable, changing over time. Once you have got this for these “customer-number-dependant” job positions you need to apply the following formula:

Number of training managers required = 1 / No of customers per 1 training manager X No of customers in that particular month / period

After this is done, you don’t need to worry about changing the number of sales agents required each time there is a change in sales pipeline or in the forecast number of customers.

4 – The last step is to multiply the number of employees from each job by their salaries and then link it back to your monthly budget in the staff related costs section (or maybe you have your own name for it)! This can be easily done using the Sumif function, which I’m sure you’re familiar with by now.

Until next time – happy modelling!

Financial Modelling for Start-ups. Part 3: Working Capital

  |   By  |  0 Comments

In this post I will look at a company’s working capital and how to calculate its components in order to build toward a financial model.

You may be wondering, however, what is working capital?

Working capital is the funds a company has for its day-to-day activities. It can also be described as the company’s current position, where  if we take all of its current assets, convert them to cash and pay off all of its liabilities– then whatever is left – positive or negative – describes the company’s current liquidity position. The firm should have enough cash left to support itself. If the figure is negative, it means that the business cannot sustain itself. On the other hand, if you end up with a large positive figure, it may indicate that the company doesn’t maintain its working capital sufficiently enough and is too cash heavy.

The formula for calculating working capital is very simple:

Working Capital = Current Assets – Current Liabilities.

However, this is only simple to calculate if you have a balance sheet in place and know where to get these figures from. If you are, like us, just starting out and in the process of preparing one, how can you derive your working capital figure?

In this case you may need to;

1) identify the components of the working capital based on a monthly budget, and…

2) calculate these components based on the duration of the working capital cycle which reflects the delay in time that cash takes to arrive in/out of your bank account as we all understand that cash doesn’t always flow simultaneously in and out once the transactions take place.

From the formula above, you might have guessed that the working capital consists of the following accounts (=components):

  • Accounts receivable – this is what is owed to the company for its services
  • Accounts payable – this is what the company owes for the purchases it makes
  • Accrued expenses – same as above, apart from these expenses will not yet be invoiced
  • Prepaid expenses – these are prepayments made for purchases in advance

Working capital also includes inventory, as well as other current liabilities and other current assets. For the purpose of this blog, I will not include these, as I want to a) keep this simple, and b) some of these are not relevant for a fintech start-up such as NOW Money.

In order to estimate the working capital duration cycle, I will use the simplified operating cycle method, which takes into consideration the time it takes for each business operation to convert an asset or liability into cash.

Firstly, you’ll need to look at each line of your monthly budget and assign each one a component from the list above.

Secondly, you’ll need to consider how long it will take to pay bills/receive the money. I suggest you use 1 month to start with for most of your AP/AR.

At the end of this exercise you should have a table that looks something like this:

With regard to the terms of payment, instead of using months, if you want to be more precise, you can use days.

Thirdly, by using the Excel Sumif formula with 2 conditions – accounts names (AP/AR/Other/etc) and the terms (1 month/2 months/1 year/etc), accumulate the working capital components on a separate sheet and calculate the amount for each one depending on the operating cycle. For example, 1 month (annual figure divided by 12 or if it’s a monthly budget – just use December figures), a year (if it’s one-off payment at the end of the year) – leave as it is, or even zero – if the payment should be made/received immediately – which is what happens with our revenues. If you used days before – divide the annual figure by 365 and multiply it by the number of days.

As you may have noticed, here we have LT assets as well, which are not part of the working capital, however we will need these later when we start preparing financial statements.

At the end of this exercise you will have calculated the components of the working capital ready to use in your future projected balance sheet and cash flow statements, which I will cover in my next blog!

 

Financial modelling for start-ups: preparing a budget in 5 easy steps

  |   By  |  3 Comments

If you are thinking of starting your own business or have already started one, sooner or later you will face questions such as:

  • What is my burn out rate?
  • Will I be able to afford to hire a sales director?
  • How many customers do we need to break-even?
  • How much does my company cost?

In order to answer these and many other questions, you need to have a financial model in place. More importantly, if you need to raise further funds to grow your company, a financial model is something which will prove invaluable.

As Winston Churchill once put it:

Plans are of little importance, but planning is essential.”

The same applies to a financial model. It doesn’t have to be precisely correct, however it does need to show that you, as a founder, know exactly how your business operates and what you are trying to achieve.

There are different templates available on the web for financial modelling, however, in my opinion, using someone else’s template is like wearing someone else’s suit – it just doesn’t feel right. If you want to feel comfortable you need to have one tailor-made just for you.

A good financial model has the following components:

  • Budget (monthly or quarterly)
  • Assumptions for revenue and cost
  • Projected financial statements (balance sheet, income statement, cash flow and statement of equity)
  • Headcount or hiring plan
  • Working capital assumptions
  • Key metrics
  • Sensitivity analysis

If you don’t have a financial background, the five easy steps listed below should be enough to get you going and prepare the first part of the financial model, the budget, which is a great starting point for drafting financial statements and other key financial and operating metrics.

The five steps

1 – Start gathering actual data

When starting a business, you may feel that keeping track of your expenses is an arduous and boring task. However, I would highly recommend that you remember, (no matter how boring it sounds) to separate your business expenses from your personal ones! Get a separate credit card, separate bank account – whatever works best for you – but make sure you keep everything separate, it’s highly important! After this is done, start collecting all of your business expenses’ receipts and invoices and inputting them into a simple Excel table which might look like this:

Excel data gather 1 HR

It is very crucial to add a date column, where you input the dates stated on your receipts and invoices, which ideally highlight when the product was purchased or service was rendered.

2 – Simplify the presentation of your expenses by type

After the data is gathered, you need to add another column and classify all expenses into several categories. For a start-up company these are the main expenses that most likely will be present:

  • Administrative expenses – office rent (probably in a shared area), stationery and subscription to admin software such as Dropbox, Microsoft, etc.
  • Staff related expenses – salaries – this can also fall into the administrative expenses category, but due to its significance it is better to separate them
  • Regulatory expenses – licensing, other legal fees, and notarial fees are significant in the first year, therefore also suggest to present them separately
  • Selling and Marketing expenses – google ads, attending marketing events and PR campaigns, etc.
  • IT costs – if you are a digital start-up, IT costs would constitute a major part of your profit and loss statement, therefore it is wise to present them separately as well.

After this exercise is done, your table might look like this:

Simplify data 2

3 – Accumulate your actuals by expense types and by periods (whether months or quarters)

This step simply means presenting your actuals in an aggregated format based on the expense types and periods. By using the Excel formula Sumif, your actual transactions will be accumulated and presented by expense types:

Accumulate 3

In this table, columns are months, meaning that this budget is prepared on a monthly basis. To do that, after the dates are inputted in step 2, by using Excel formula Month, they can be distributed to different months which they relate to.

4 – Start projecting your expenses and revenues for future periods

After you have finished inputting your data, it’s time for projections, which, in my opinion, is the most exciting part of financial modelling! If you have already incurred expenses, then it should be fairly easy to predict how much something will cost you later on. However, if there is a new expense – then you need to do some research on how much it will cost your business. And if you have no idea whatsoever on where to go for an answer – then you need to start making assumptions.

5- Accumulate assumptions

Create a separate sheet for assumptions where you will accumulate all data related to future expense and revenue drivers. One of the advantages to keeping them on a separate sheet, is that after you link them in your budget table, you can easily change your projected data by changing those assumptions. Another advantage is that it makes your calculations transparent for someone who wants to review them.

Assumptions can include the following:

  • Growth rate or your customers’ number or projected customers’ number in absolute amounts
  • Number of customers per sales agent
  • Office rent per year
  • Technology cost per user, etc.

After you have performed these 5 steps above, you will have a budget which will be a very important source of information for preparing projected financial statements, key metrics and valuation for your company.

Stay tuned to the blog for the rest of my “five easy steps” series, which will help you create the entire financial statement.