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
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:
- 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.
- 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:
- Choose drivers that are measurable. You will most probably later on compare the drivers to reality and therefore make sure that they are not impossible to measure.
- Figure out, which driver has most impact. You should focus on those first. Driver with no or very limited impact can be skipped initially.
- Are drivers depending on each other? If yes, it should be modeled accordingly.
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:
- Plan on monthly basis for the first 24 to 36 months.
- Switch to annual planning for the years 3/4 to 5.
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.
- Structure of worksheets: Make sure that most worksheets are set up with the same structure. For example, start with a headline in cell B2, years starting in column H, content in row 10.
- Layout / format of cells: Make sure you use a consistent formatting. For example, Excel provides cell styles – use them.
For more recommendations about professional formatting, please refer to this article. - Universal settings and assumptions should be consolidated on one sheet (for example tax rates, start date, company name).
Advice 6: Document business plan 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.
- Business plans are “living documents”. Keep in mind that at some point in the future you have to update it or extend it.
- Validate your assumptions: After some time, you will come back to your plan having real life figures. Now, it’s time to compare and – if necessary – adjust the plan.
- Think about subsequent analysis: The initial business plan in shape of a P&L is just the beginning. Further analysis will be based on it, for example
- a valuation (“Discounted Cash Flow model”),
- liquidity planning,
- bank loan simulations,
- financial dashboards,
- budget planning,
- maybe even the first real official P&L (at least when it comes to the line items of your business plan)
- and much more…
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.
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?
- This template is very flexible: I have always included place holders so that you can add much more items if needed.
- In terms of the time frame, I have created monthly columns for up to ten years. Typically, you need less. Then just hide the extra columns.
- Also, I have created a consistent structure throughout the model.
- No fancy Excel functions and formulas, mainly just plain links.
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
