How to Calculate Remaining Budget Using Formula in Excel

In this article, we will learn how to calculate the remaining budget using formula in excel. Nowadays, the budgets of almost every company are made using excel worksheets. Hence it is essential to know how we can construct a budget using an excel worksheet and calculate the remaining budget using a formula. So let’s get started.

Calculate Remaining Budget Using Formula in Excel: Step-by-Step Procedures

To illustrate how we can calculate the remaining budget using a formula, first, we have to make a sample budget of a business company in an excel worksheet. Then we can use a formula to calculate the remaining budgets. Follow the steps below to construct a budget worksheet.

Step 01: Creating a Heading of the Budget Worksheet

Initially, we have to give a budget heading with attractive formatting. First, open a blank excel sheet, take some cells, Merge, and Center Here I have taken cells B2 to E2. Then give a proper heading.

Now change the font size to 14 and make it bold. Give the merged cell an eye-catching cell colour.

Then, we need to make headings for the Description of items of the Budget, and its corresponding Estimation, Actual Expenses, and Remaining Budget. We also need to format the cells to make them aesthetic.

As we can see in the picture, we will have 4 columns in our budget worksheet. Now it is just a matter of inputting data of various items of the budget.

Step 02: Inputting Rent/Lease Budget Data

We will first input the Estimated Budget and Actual Expenses of Rent or Lease related items.
In cell B5, write Building Rent/Lease; in the same row, write the Estimated Budget and Actual Expenses in cells C5 and D5, (See the figure below)

On E5, insert the following formula to calculate the Remaining Budget.

=C5-D5

Similarly, write the data about Equipment Rent/Lease and calculate the remaining budget by subtracting Actual Expenses from the Estimated Budget. We will apply the same formula to calculate the remaining budget for each item.

Step 03: Inserting Product/ Material Budget Data

Now, we will enter the budget data related to Product/Material. We first write Product/Material on cell B7 and make it bold. Below B7, write about Coffe and Other Data

Step 04: Inputting Marketing Expenses Budget Data

Now enter the Marketing Expenses (Boucher, Website, Business Cards, Advertising, and Others) and calculate the remaining budget using the same formula.

Step 05: Entering Administrative Expenses Budget Data

Then, we will input the data about Administrative Expenses (Fax/Copies, Postage, Office Supplies, and Others)

Step 06: Inputting Sales Expenses Budget Data

Here, we will input data about sales expenses (Mileage, Parking, and Others)

Step 07: Entering Operating Expenses Budget Data

This is the last type of item we need to enter into the budget worksheet. The Operating Expenses include Utilities, Insurance, Others, Salaries, and Miscellaneous.

Step 08: Calculating Total Budget Remaining

Now that we have input everything, we will calculate the total Estimated Budget using the following formula.

=SUM(C5:C6,C8:C9,C11:C15,C17:C20,C22:C24,C26:C30)

The Total Actual Expenses can be calculated using the following formula

=SUM(D5:D6,D8:D9,D11:D15,D17:D20,D22:D24,D26:D30)

Finally, the Total Budget Remaining is calculated by subtracting the Total Actual Expenses from the Total Estimated Budget.

=C31-D31

Things to Remember

Each company will have its own type of expenses. So we may have to categorize the budgetary items differently. But the formula structure will be the same.
The Total budget remaining can also be calculated by summing all the budget remaining for each item.

=SUM(E5:E30)

It will yield the same result.

Conclusion

That is the end of this article. Hopefully, you have a clear idea on how we can calculate the remaining budget using formula in excel. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.

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

Get FREE Advanced Excel Exercises with Solutions!