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.
Prepare Budget for a Company in Excel: 2 Easy and Effective Ways
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.
➤ Prepare 3 tables for Income, Expense, and Summary with your desired color formatting.
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.
➤ 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.
In the last table on the right side, we have formed the Summary table with 3 rows containing Expected Balance, Actual Balance, and Difference.
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.
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.
➤ Enter the actual and expected incomes from different sources in the Actual and Expected column.
➤ Apply the following formula in cell E5 and drag down the Fill Handle tool.
=C5-D5Here, 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)
In this way, we will get the differences for all of the sources of income.
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.
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.
➤ Press ENTER and drag the Fill Handle tool to the right side.
In this way, you will get the Total Actual Income, Total Expected Income, and Total Difference.
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.
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.
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.
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).
After selecting the Production department from the list it will appear in cell B12.
Similarly, select various departments for the remnant cells like the first cell.
Read More: How to Create a Business Budget in Excel
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.
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.
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).
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).
After selecting the Office Rent option from the list it will appear in cell C16.
Similarly, we have selected different sources from the lists of corresponding cells and then we have got the following figure.
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.
In this way, we have retrieved the actual expenses for our desired sources in the Actual column.
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.
➤ Apply the following formula in cell F12 and drag down the Fill Handle tool.
=E12-D12Here, 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).
Then, we have got the differences in the Difference column.
➤ Select cell D19 where we want the total result and then go to the Home Tab >> Editing Group >> AutoSum Option.
➤ Press ENTER and drag the Fill Handle tool to the right side.
In this way, we have calculated all of the total expenses.
Step-06: Budget Summary Calculation
Now, we will calculate the remnant balance after expending on the needed sources from our income.
➤ Type the following formula in cell J4.
=D8-E19Here, D8 is the Expected Income and E19 is the Expected Expense, their difference will give us our Expected Balance.
➤ Apply the following formula in cell J5.
=C8-D19Here, C8 is the Actual Income and D19 is the Actual Expense, their difference will give us our Actual Balance.
➤ To have the differences between these balances apply the following formula in cell J6.
=J5-J4Here, J5 is the Actual Balance and J4 is the Expected Balance, their difference will give the Difference.
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.
➤ 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).
After giving the chart title as Income, we have got the following Income chart.
Similarly, by selecting the values of the expenses you can also have the Expense chart like the following figure.
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.
➤ On the left portion select the New option and on the right pane select the Budgets option.
After that, you will have different types of budgets on the right side.
➤ Among the options, we have selected the option Monthly Company Budget.
After that, a wizard will pop up where you have to select the option Create.
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.
In the Income sheet, you will have detailed incomes from different sources where you can change the numbers and sources as well.
The Personnel Expenses sheet will have different sources of personnel expenses which you can change as per your wish.
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.
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.
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.
Read More: How to Create an Operating Budget in Excel
Download Workbook
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.
