How to Prepare Budget for a Company in Excel (2 Suitable Examples)

If you are trying to prepare a budget for a company in Excel, then you will find this article useful. So, let’s start with our main article to know the detailed procedures of making the budget for a company easily.

Download Workbook


2 Ways to Prepare Budget for a Company in Excel

A budget of a company represents a detailed listing of all of the actual and estimated incomes and allocated expenses of a company with the profits or losses of this company. In this article, we will try to explain the two ways of creating this budget for a company both manually and with the help of an Excel template.
We have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.


Method-1: Prepare a Budget for a Company Manually in Excel

If you want to make your budget template on your own then you can follow this method. After going through the following steps you will be able to do the basic calculations and formatting needed to decorate your budget template according to your wish.


Step-01: Creating Basic Outlines to Prepare Budget for a Company

After opening a blank workbook, we created a worksheet Budget where we will prepare our main budget template. Firstly, the header is created declaring the purpose of this template which is Budget for “X” Company.

how to prepare budget for a company in excel

➤ Prepare 3 tables for Income, Expense, and Summary with your desired color formatting.

making outline

Now, we will put the entities of income in the first table on the left side.
➤ We have used Total Income as the header of the first table and then used 4 columns for 4 portions – Source, Actual, Expected, and Difference. As the Source of income, we have used Revenue, Incentive, and Extra Income.

making outline

➤ Below the Total Income table, we have inputted the details of the expenses in the Total Expense table. There are 5 columns – Department, Source, Actual, Expected, and Difference in the Total Expense table.

how to prepare budget for a company in excel

In the last table on the right side, we have formed the Summary table with 3 rows containing Expected Balance, Actual Balance, and Difference.

making outline

Finally, in the Expense sheet, we have listed all of the expenses of different departments of this company, which we will further use for the template.

making outline

Read More: How to Prepare Annual Budget for a Company in Excel


Step-02: Calculation of Incomes

Here, we will calculate the total income, and also we will determine the differences between the actual income and our predicted income.

how to prepare budget for a company in excel

➤ Enter the actual and expected incomes from different sources in the Actual and Expected column.

calculation of incomes

➤ Apply the following formula in cell E5 and drag down the Fill Handle tool.

=C5-D5

Here, C5 is the Actual Income and D5 is the Expected Income (for calculating the difference in income we have to subtract actual from expected value)

how to prepare budget for a company in excel

In this way, we will get the differences for all of the sources of income.

calculation of incomes

Now, we will use the AutoSum option to calculate the total income values automatically and so if we add or delete any values then the total value will be updated automatically.
➤ Select cell C8 where we want the result and then go to the Home Tab >> Editing Group >> AutoSum Option.

calculation of incomes

Then, you will have the following formula in this cell.

=SUM(C5:C7)

The SUM function will calculate the sum of the range C5:C7.

how to prepare budget for a company in excel

➤ Press ENTER and drag the Fill Handle tool to the right side.

calculation of incomes

In this way, you will get the Total Actual Income, Total Expected Income, and Total Difference.

calculation of incomes

Read More: How to Make a Budget in Excel (2 Easy Methods)


Step-03: Creating Dropdown List for Different Departments of Expenses

In this step, we will calculate the total expenses and the differences between the actual and expected values.

how to prepare budget for a company in excel

Firstly, to create a dropdown list for the cells of the Department column we will use the Data Validation option.
➤ Select all of the cells of the Department column and then go to the Data Tab >> Data Tools Group >> Data Validation Dropdown >> Data Validation Option.

dropdown list

Then, the Data Validation wizard will open up.
➤ Select the List option in the Allow box and then in the Source box type the name of the departments separated by commas (here we have entered Production, Sales, Miscellaneous in the Source box).
➤ Press OK.

how to prepare budget for a company in excel

After that, the dropdown symbol will appear in the cells of the Department column and by clicking this symbol you will get various options like the following figure.
➤ Select any department from the list (here we have selected Production for the first cell).

dropdown list

After selecting the Production department from the list it will appear in cell B12.

dropdown list

Similarly, select various departments for the remnant cells like the first cell.

dropdown list

Read More: How to Create a Business Budget in Excel (With Easy Steps)


Step-04: Creating Dropdown List for Different Sources of Expenses

To create a dropdown list for the cells of the Source column, select all of the cells of the Source column and then go to the Data Tab >> Data Tools Group >> Data Validation Dropdown >> Data Validation Option.

how to prepare budget for a company in excel

Then, the Data Validation wizard will open up.
➤ Select the List option in the Allow box and then in the Source box type the following formula

=IF($B12=Expense!$E$4,Expense!$B$4:$B$6,IF($B12=Expense!$E$7,Expense!$B$7:$B$9,Expense!$B$10:$B$15))

Here, we have used the IF function 2 times for creating the Nested Loop effect.

  • The first IF function will check if the value of cell $B12 matches with the value in cell $E$4 of the Expense sheet, and if it matches then it will return the values in the range $B$4:$B$6 of the Expense sheet otherwise it will go to the next loop.
  • The second IF function will check if the value of cell $B12 matches with the value in cell $E$7 of the Expense sheet, and if it matches then it will return the values in the range $B$7:$B$9 of the Expense sheet otherwise it will return the values in the range $B$10:$B$15 of the Expense

➤ Press OK.

dropdown list

After that, the dropdown symbol will appear in the cells of the Source column and by clicking this symbol you will get various options like the following figure depending on the value of the corresponding cell of the Department column.
➤ Select any department from the list (here we have selected Raw Materials for the first cell).

how to prepare budget for a company in excel

If we click on the dropdown symbol of cell C16 whose corresponding cell of the Department column has the entity Miscellaneous, then we will see the source of expenses of this department in the list.
➤ Choose an option from the list (here, we have chosen the Office Rent option).

dropdown list

After selecting the Office Rent option from the list it will appear in cell C16.

dropdown list

Similarly, we have selected different sources from the lists of corresponding cells and then we have got the following figure.

how to prepare budget for a company in excel


Similar Readings


Step-05: Calculation of Expenses to Prepare a Budget for a Company

For having the Actual expenses from the Expense sheet corresponding to the sources of the Source column we will use the VLOOKUP function here.

➤ Type the following formula in cell D12 and drag down the Fill Handle tool.

=VLOOKUP($C12,Expense!$B$4:$D$15,2,FALSE)

Here, $C12 is the lookup value in the Source column, Expense!$B$4:$D$15 is the lookup range in the Expense sheet, 2 is the column number of this range, and FALSE is for an exact match.

how to prepare budget for a company in excel

In this way, we have retrieved the actual expenses for our desired sources in the Actual column.

calculation of expenses

Similarly, use the following formula to get the Expected Expenses from the Expense sheet.

=VLOOKUP($C12,Expense!$B$4:$D$15,3,FALSE)

Here, $C12 is the lookup value in the Source column, Expense!$B$4:$D$15 is the lookup range in the Expense sheet, 3 is the column number of this range, and FALSE is for an exact match.

calculation of expenses

➤ Apply the following formula in cell F12 and drag down the Fill Handle tool.

=E12-D12

Here, E12 is the Expected Expense and D12 is the Actual Expense (for calculating the difference in expense we have to subtract the expected value from the actual value).

how to prepare budget for a company in excel

Then, we have got the differences in the Difference column.

calculation of expenses

➤ Select cell D19 where we want the total result and then go to the Home Tab >> Editing Group >> AutoSum Option.

calculation of expenses

➤ Press ENTER and drag the Fill Handle tool to the right side.

how to prepare budget for a company in excel

In this way, we have calculated all of the total expenses.

calculation of expenses


Step-06: Budget Summary Calculation

Now, we will calculate the remnant balance after expending on the needed sources from our income.

how to prepare budget for a company in excel

➤ Type the following formula in cell J4.

=D8-E19

Here, D8 is the Expected Income and E19 is the Expected Expense, their difference will give us our Expected Balance.

budget summary calculation

➤ Apply the following formula in cell J5.

=C8-D19

Here, C8 is the Actual Income and D19 is the Actual Expense, their difference will give us our Actual Balance.

budget summary calculation

➤ To have the differences between these balances apply the following formula in cell J6.

=J5-J4

Here, J5 is the Actual Balance and J4 is the Expected Balance, their difference will give the Difference.

how to prepare budget for a company in excel


Step-07: Plotting Income and Expense Values

If you want to plot the values of the incomes and expenses, then you can follow this step.

how to prepare budget for a company in excel
➤ For the incomes, select the actual and expected income values with their sources and then go to the Insert Tab >> Charts Group >> Insert Column or Bar Chart >> select your desired column chart (here, we have selected the first option of the 2-D Column chart).

plotting chart

After giving the chart title as Income, we have got the following Income chart.

how to prepare budget for a company in excel

Similarly, by selecting the values of the expenses you can also have the Expense chart like the following figure.

plotting chart


Method-2: Prepare a Budget for a Company Using Excel Template

If you want to have a ready-made template for budget calculation, then you can use Budget template built-in Excel.
➤ Go to the File tab after opening a blank workbook.

how to prepare budget for a company in excel

➤ On the left portion select the New option and on the right pane select the Budgets option.

Excel template for budget

After that, you will have different types of budgets on the right side.
➤ Among the options, we have selected the option Monthly Company Budget.

how to prepare budget for a company in excel

After that, a wizard will pop up where you have to select the option Create.

Excel template for budget

After that, a new workbook will be created where you will have the following 4 sheets – Monthly Budget Summary, Income, Personnel Expenses, Operating Expenses, etc.
In the first sheet, we will have the summary budget with their graphical representation, here we can enter our company name and date in the indicated regions.

how to prepare budget for a company in excel

In the Income sheet, you will have detailed incomes from different sources where you can change the numbers and sources as well.

Excel template for budget

The Personnel Expenses sheet will have different sources of personnel expenses which you can change as per your wish.

Excel template for budget

In the Operating Expenses sheet, there are different values of expenses for various operating expenses and you can change the sources also besides changing their values.

how to prepare budget for a company in excel

We have demonstrated the result of changing some estimated and actual incomes in the Income sheet. We have also entered our company name (“X” Company) in the left corner.

Excel template for budget

Because of moderating the values in the Income sheet, we will see the change in the Monthly Budget Summary sheet also. The total balance has been changed with the shape of graphs. Besides, we have entered the company name and today’s date (05/07/2022) in the portion for Date on the right corner. Ultimately, the date will take the following format.

Excel template for budget

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


Conclusion

In this article, we tried to cover the steps to prepare a budget for a company in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo