2 Excel Templates

1) Bottom-up Budgeting Template

Bottom-up budgeting involves detailed input from individual departments, which ensures a more accurate and realistic budget.

Each department estimates its own costs and revenues, and these are then compiled to form the company-wide budget.

​Click here to get your Excel template​

The model can be adjusted very easily to any company. Keep you posted for the rest of templates.

2) MRR + ARR (Revenue forecasting) Template

What is included:

  • Key assumptions for MRR and ARR forecasting
  • Historical data a good forecasting base
  • New users forecast
  • User churn forecast
  • Total new users forecast
  • Upgrade effects in the forecast
  • Downgrade effects in the forecast
  • MRR forecast in the next 36 months
  • ARR forecast in the next 3 years

​​​Click here to get your Excel template​​​​​​​

This model, with small adjustments, can be used for many industries, but is more suitable for SaaS and subscription based businesses.

Here are 2 Infographics for Today:

1) Budgeting Checklist

​Get this infographic on a high-resolution PDF​​​​​​​​​​​

2) Strategic Finance Handbook

​Get this infographic on a high-resolution PDF​​​​​​​​​​​​​​​

Here’s today’s “How to” guide:​​​​

How to build a bottom-up Budgeting model (templates given above)

There are 3 pillars for budgeting by this method:

  • Templates to send to each department and make sure they understand the process
  • Key cost and revenue drivers
  • How to consolidate all data from each department into one comprehensive budgeting model.

Step-by-step guide

Let’s start with revenue.

We receive revenue projections from the Sales department.

To create an efficient projection, it’s best to segment revenue into categories.

Depending on the type of business, product assortment, sales structure, and other factors, we can create projections in several ways:

  • Projection by individual products or product categories.
  • Projection by customers.
  • Projection provided by each sales manager or sales developer for their own accounts.

For simplicity in demonstrating the essence of budgeting, I have not prepared a revenue budget based on quantities and prices in the above templates. However, you can do this if the scope and nature of your business require it.

All these segments should be grouped into business units, which we will use for management reporting. In this example, the business units are:

  • Chemicals
  • Coatings
  • Food
  • Pharmacy

After completing the revenue plan, we need to input assumptions regarding gross margins.

Of course, a historical analysis of these margins is very important, as well as understanding supply chain effects on gross margin.

This allows us to project gross profits.

Now, we can move on to expenses.

These are templates we prepare for various types of departments.

We are still on the Sales and Marketing department, but now on the expense side.

Each department’s template begins with an analysis and payroll budget.

There are two approaches here:

  1. Detailed – input all employees as well as rows for potential new hires, creating a budget for each employee’s salary.
  2. Aggregate – input planned headcount data and average salary. By multiplying headcount by average salary, we obtain the total payroll cost.

After payroll, the template also includes other expenses characteristic of this department.

For example: advertising, marketing expenses, sponsorships, market research, and others.

More importantly, you need to ensure that each line item relates to the corresponding position from the pre-defined income statement. In this way, by using Excel functions, we can easily consolidate data into financial reports, as you will see later.

Let’s continue with expenses by department.

The next department is the operations or production department. This is the function where value is created, whether in services or products.

It’s important to assess capacity accurately here because costs depend on capacity increases and utilization.

As you can see in the template, I didn’t go into too much detail regarding cost drivers. However, my suggestion is to feel free to further elaborate on this template—and not just this one, but others as well.

For example, you can start from cost-generating units or drivers. So, you can first project the quantity of units that drive a cost, then determine the price for each driver, which results in the total cost for a certain type of expense.

For example, rental cost.

We have 5 stores and a total of 1,000 square meters of retail space, and 300 square meters of office space. The plan is to open another store with 80 square meters and expand the office by 70 square meters. Since we can estimate the price per square meter, it’s easy to project the cost.

This type of planning (first units, then value) is easier to understand for the departmental people who build the template.

Here are the other departments:

  • Logistics and Supply Chain department
  • Finance & Accounting department
  • HR Department
  • IT Department Costs
  • General and Administration

This includes top management costs, so it’s essential to be careful and accurately estimate the benefits they receive, such as stock options, bonuses, and so on.

What is the next stage?

When we collect all this data, review it, and determine consistency, accuracy, and quality, we can proceed to create a consolidated budget in the form of financial statements.

Let’s start with the income statement.

So, we already have a well-known format. Now we use the Excel function SUMIF, which should sum all the lines from all templates into each position.