How to Create an Operating Budget in Excel (with Detailed Steps)

Get FREE Advanced Excel Exercises with Solutions!

Creating an operating budget in Excel may become time-consuming or daunting work. You don’t have to be a professional accountant or analyst to create an operating budget. In this article, I will show you how to create an operating budget in Excel with easy steps. Also, I will share the free template in this article, so you can download it and modify it for your use.


Watch Video – Create an Operating Budget in Excel


What Is an Operating Budget?

Every operation needs an operating budget and it includes all the incomes and expenses in a period that is planned by the company or the organization. It is important to create an operating budget for any project, program, or anything because it is an overview of the complete operations aiming for a target to achieve. And from it, the owner can modify the plan easily.  By creating an operating budget:

  • You can compare the forecast with the actual values easily.
  • You can monitor the expenses to avoid unnecessary purchases and the use of materials or labor.
  • It will improve efficiency by fixing the deadline and the expense by ensuring a target income.

Common Components of an Operating Budget

There are some main components of an operating budget, and without them, it will be incomplete. But as every business is unique and will add or remove any components from the operating budget. The main components of the operating budget are:

  • Incomes Data
  • Fixed & Variable Cost Data
  • Non-Cash Expenses like Depreciation or Amortization
  • Non-Operating Expenses such as interests, Taxes, etc

Create an Operating Budget in Excel: With Easy Steps

Before creating the operating budget for a company, it is needed to forecast the sales data to predict the income value. There are some market variables that affect the sales income and the production expenses. These are:

  • Trends in the industry
  • Launching of any new product of the company
  • Competitors effect
  • Seasonal trends in forecasting
  • World Economy

After evaluating these effects, each department will make their expense forecast for that period and the managerial level with the help of executives will forecast the sales volume and make the income forecast. After the creation of these, you will be able to make an operating budget.

When you have all the expense and income forecast data ready, you will have to create an operating budget to create a summary. For you, I am sharing a free template of Operating Budget and also sharing the steps with clear illustrations to make this template in Excel. So, you can easily modify it for your purpose.


Step 1: Make a Blank Template Layout

When you have the data ready, you have to make a layout of the operating budget template. This template contains three portions. 1. Expense 2. Income 3. Revenue. So, create 3 tables where each contains portions for months of the periods. Here, I have used 2 months. You can add or remove months as of your need. And for each month, there will be two columns for forecasted and actual values. You can also add one column to calculate the differences.

Expense Table:

List all the expenses here.

Expense Table: How to Create an Operating Budget in Excel

Income Table:

List all the products and sources of income here. You have already made a sales forecast so insert the values here.

Income Table: How to Create an Operating Budget in Excel

Revenue Table:

Here, you will get the value of revenue by calculating all the incomes and expenses.

Revenue Table: How to Create an Operating Budget in Excel


Step 2: Add Formulas for Automation

Now, you have to insert the formulas into cells that were needed to automate the operating budget.

  • At first, calculate the sum of expenses. You can use the SUM function to calculate the total forecasted expenses. Paste this formula into the cell C15
=SUM(C7:C14)

Add Formulas for Automation to Create an Operating Budget in Excel

  • Now, drag the Fill Handle button horizontally to the right cells to calculate the sum of actual expenses similarly for month 2 or use Excel Shortcuts Ctrl+C and Ctrl+P to copy and paste the formula.

drag the Fill Handle icon

  • Similarly, use the SUM function to calculate the total forecasted income and actual income both for month 1 and month 2.

Using SUM function

  • Now, you will have to calculate the revenue of the operating budget.
  • First, you will calculate the total forecasted expense and total actual expense for months 1 and 2. Similarly, calculate the forecasted income and the actual income. In the screenshot, I am showing them in the right-side cell using the FORMULATEXT function.
Revenue = Income – Expense

FORMULATEXT function to show formula of operating budget

Here,

E15 = Total Forecasted Expense of Month 1

F15 = Total Actual Expense of Month 1

G15 = Total Forecasted Expense of Month 2

H15 = Total Actual Expense of Month 2

K15 = Total Forecasted Expense of Month 1

L15 = Total Actual Expense of Month 1

M15 = Total Forecasted Expense of Month 2

N15 = Total Actual Expense of Month 2


Step 3: Insert Income and Expense Items with Forecast Values

After inserting the formula, you will input the forecasted expense and income data into the list. Now, you have the forecasted operating budget ready.

Insert Income and Expense Items with Forecast Values to Create an Operating Budget in Excel

Read more: How to Create a Project Budget in Excel


Step 4: Insert Actual Values During Operation

Now, when the project starts, you will input the actual values in the columns.

Insert Actual Values during Operation

  • And after the completion of the project, you will find the actual revenue from the project.

How to Create an Operating Budget in Excel


Step 5: Add Charts to Visualize the Operating Budget

Now, the central part of the operating budget is complete. But you can add some charts to visualize the data of expenses and income. So, you can compare the progress and priority.

Create Column Chart to Compare Actual vs Forecasted Expenses:

  • First, go to the top ribbon and select the Insert tab.
  • Then, click on the Bar Chart tool and select the 2D Clustered Column Chart option.

Add Charts to Visualise

  • Then, there will create a blank chart and then click on the chart.
  • You will see a new tab will create named “Chart Design”.
  • Then, go to the Chart Design tab > Select Data option.

Select Data from Chart Design Tab

  • Then, a window named Select Data Source will appear.
  • Select the column of cost, forecast and actual then press OK

Select Data source to Create Column Chart

  • As a result, a column chart will create showing a comparison of forecast and actual data for each expense item.

Create Column Chart to Compare Actual vs Forecasted Expenses:

  • Similarly, you can create a column chart for the income sources.

Create Column Chart to Compare Actual vs Forecasted Expenses:

Read More: How to Create a Construction Budget in Excel


Create Pie Chart to Compare Items Impact on Total:

You may want to create a Pie Chart to see the effect of each item on the total value. So, you can prioritize reducing the expenses and the incomes. To create Pie Chart follow the steps below:

  • Go to Insert tab > Pie chart and select the 1st one in 2D Pie.

Insert Pie Chart

  • Then select the cost and Actual column to compare the cost items’ effect on the total actual expenses.
  • Finally, press Ok.

Select Data Source to Create Pie Chart to Compare Items Impact on Total:

  • There, you will see a Pie chart showing the cost items as percentages so you can compare them easily.

Create Pie Chart to Compare Items Impact on Total:

  • In a similar way, you can create a pie chart to compare the income items.

Actual Income Pie Chart to Compare Items Impact on Total:


Notes

I have shared easy steps to create a basic operating budget in Excel. So, you can download the free template and modify it for use. You can convert this into other formats. I am sharing it here.

  • I have used two months in this template, so you have to add all months of the year to create an annual business budget template.
  • You can modify it to one single month by removing the columns of month 2 so it will become a monthly expense sheet. If you won’t want to forecast values then remove also the forecast columns.
  • Similarly, you can use this template for your business operations, family expenditure tracking, for your making your departmental budgets, and many more purposes.

Download Sample Workbook

You can download the Excel free template for an Operating Budget from the following button.


Conclusion

This article shows you how to create an operating budget in Excel with quick and easy steps. Also, I am sharing a free operating budget Excel template with you so you can download this and modify it for your purpose.  I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back To How to Create a Budget in Excel | Excel For Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo