Site icon Professor Excel

Business Plan: How to Create Great Financial Plans in Excel

I guess, you are about to write a business plan and that is why you have come to this page. Very good – because in this article I am going to write down my experience with business plans and what I have learned creating them with Microsoft Excel. As I will point out again further down, I will only concentrate on the financial part of business plans. Specifically, how to set it up in Excel. Of course, you can also download an Excel template.

Parts of business plans

As you reached this page I suppose you already have a rough idea of what a business plan is. So, we will skip this part here.

A business plan is a formal written document containing the goals of a business, the methods for attaining those goals, and the time-frame for the achievement of the goals.”

https://en.wikipedia.org/wiki/Business_plan

But one comment concerning the scope of this article: The formal business plan has usually many different parts, in which you describe the business idea and product, the market, competition, legal construct and so on. But typically, investors are most interested in the financial part. They want to know first, what they can get out of it. Of course, the other parts are also very important, but the financial topics usually put everything described in the other sections into numbers.

I’m not going further into the details of all the other parts than then financial section here. Specifically, we will dive into the basics of the financial part and how to model it in Excel.

Please scroll down to download the business plan template. We are going to explore all the following advice with this template.

How to create a business plan in Excel

Advice 1: Be clear about the purpose and the recipient of the business plan

Before you start opening Excel, make sure that you are 100% clear of the purpose this business plan. Is the business plan just for you? Or do you create it for someone else, for example an investor or bank? Although the next steps might still be the same, the focus might be different. For example: Maybe you have a very good understanding of the major assumptions because you have been working in this field for some time. But for someone external you still need to validate them. Of course, in both cases the assumptions should be realistic and goals should be achievable. But maybe for your own peace of mind you would choose more pessimistic assumptions if the plan was only for you.

Advice 2: Go top-down in terms of line items

Possible structure of the line items in a business plan.

Now, let’s start in Excel. But how do we start?

My approach is to go top-down. I usually use a basic P&L (“Profit- and loss” calculation) structure to start with, having some placeholders for revenue and costs.

Specifically, I go through the following parts (also shown on the right-hand side).

Revenue

Let’s assume that you develop and sell Excel add-ins: 50 EUR per license – once-off. You would now start with assumptions of how many you can sell per month and the price. This is your first revenue item. At this point in time, I would leave it like this. We can later drill further down as much as we need (for example modeling discounts, the connection between marketing spending and number of units sold, price changes, etc.).

If we have multiple products, we calculate them in a similar manner.

Cost of goods sold

Cost of goods sold – or COGS – refers to the direct costs of producing the goods sold. Depending on the complexity you could also summarize cost of sales here or keep it separately.

Often, the COGS are directly linked to the number of units produced so you could refer to the numbers already calculated for the revenues.

In our example from above, we don’t have any direct costs for producing the Excel add-ins because we develop them ourselves and our salary will be regarded under “Salaries and Benefits”.

All other expenses

The structure of the expenses highly depends on your business. I usually start with these:

  • Salaries and Benefits
  • Rent and Overhead
  • Marketing and Advertising
  • Other expenses

Again, these items might look completely different for you. Example: if you travel a lot for your business, you might plan travel costs separately.

EBITDA

Subtracting costs from the revenue leads to the EBITDA (earnings before interest, taxes, depreciation, and amortization). This is one of the important financial performance indicators.

Amortization and depreciation

If you buy any assets for your business (for example machines, computers, even cars), you usually plan to use them over a certain period. When you first buy them, let’s say for 1,000 USD, you basically just exchange money for assets in the same amount. The problem: The assets will decrease in value the longer you use them. Within the cost items above, you don’t regard the acquisition value. So, how to regard them in your business plan?

You only regard the annual decrease of value. If you plan to use your 1,000 USD item for 5 years, you could (plainly speaking), each year regard 200 USD as depreciation.

Please note: If you later plan your cash, you have to make sure that you fully regard the initial sales price and not the depreciation.

The key difference between amortization and depreciation is that amortization is used for intangible assets, while depreciation is used for tangible assets.

https://www.fool.com/knowledge-center/whats-the-difference-between-amortization-deprecia.aspx

EBIT

Subtracting the amortization and depreciation from the EBITDA leads to the second key performance indicator, the EBIT (earnings before interest and tax).

Interest and taxes

Eventually, you have to prognose your interest costs (for example what you have to pay for bank loans) and your taxes, which is typically just a percentage of the EBT (the earning before taxes).

Advice 3: Think about the business drivers carefully

Good business plans are driver based.

Business drivers are the key inputs and activities that drive the operational and financial results of a business. Common examples of business drivers are salespeople, number of stores, website traffic, number and price of products sold, units of production, etc.

https://corporatefinanceinstitute.com/resources/knowledge/modeling/business-drivers/

Let me explain with an example: You want to plan the revenues. You have two different options:

Example for business plan drivers
  1. As before, you plan the number of units sold and the price per unit.

    This would be a driver based approach.

    You can see such example on the right side:
    • Revenue per month is split into number of units sold times price per unit.
    • Number of units sold is further split into number of salespersons and number of items sold per salesperson and month, and so on.
  2. Or you could just write a number and every following year you assume a growth in percentage (e.g. +2% per year).

Let’s finish this section with some final comments:

Advice 4: Choose the smallest period from the beginning in your business plan

So far, we have been focusing on the line items, for example costs, revenue, or drivers. Now, let’s talk about the time frame.

The question is: Should you plan on annual, monthly or any other basis? Or a mix?

I have seen many business plans doing it something like this:

Most business plans are not going beyond 5 years planning period.

My recommendation: Plan on monthly basis for the full period. There will be a point in time when you need to break it down into months. And it is always easier to sum up 12 months for annual values than to drill down from years to months.


Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!


Advice 5: Keep a unified, professional business plan structure

This advice should count for most Excel models: Try to keep the same structure throughout the whole Excel file.

Advice 6: Document business plan assumptions well

Document your assumptions well.

I can not say this often enough: Document your assumptions! Not only the values or variables, also your thoughts behind them. Why have you chosen this value? What is it based on? What is it used for?

Advice 7: Gross vs. net values

This question I am asked quite frequently: Should you use gross or net values? That means, include tax in revenues and costs?

Typically, you only work with net values, excluding VAT. For Germany with a tax rate of 19%, for example, if you invoice 119 EUR to a customer, you would only regard 100 EUR. Also, for costs, you would only regard net values.

Then, in your business plan, you start with revenue minus costs and eventually reach the EBT (earning before tax, please scroll up to see the P&L). From this, you calculate your company tax.

Advice 8: Think ahead

Some more things you should keep in mind when creating your business plan.

Download business plan template

So, after reading all this description and advice, it’s time to start. Probably many things I have written above sounds like common sense, right? But I can assure you: Doing it and regarding as much advice as possible is not necessarily simple.

Download the business plan template in Excel.

That’s why I have decided to create a template. I have pre-filled it with an imaginary example.

I know, there are countless Excel business plan templates around. So, why should you use this one?

Please feel free to take a look at it. If you like it, just use it. If not, please feel free to create your individual business plan – you now know how to do it!

Download link: Click here to start the download.

Image by koon boh Goh from Pixabay

Image by mohamed Hassan from Pixabay

Image by mohamed Hassan from Pixabay

Image by Memed_Nurrohmad from Pixabay

Exit mobile version