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

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.


Download Sample Workbook

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


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

Steps to Create an Operating Budget in Excel

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.

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.

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.

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)

How 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.

How to Create an Operating Budget in Excel

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

  • 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

How to Create an Operating Budget in Excel

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


Similar Readings


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.

How to Create an Operating Budget in Excel

Read more: How to Do Budgeting and Forecasting in Excel (2 Suitable Ways)


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 Visualise 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.

Create Column Chart to Compare Actual vs Forecasted Expenses:

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

Create Column Chart to Compare Actual vs Forecasted Expenses:

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

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

Create Column Chart to Compare Actual vs Forecasted Expenses:

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.

Create Pie Chart to Compare Items Impact on Total:

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

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.

Create Pie Chart to Compare Items Impact on Total:

Read more: How to Make a Budget Pie Chart in Excel (with Easy Steps)


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.

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. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo