ExcelInsider Team
One of the most important things to do for financial planning in a company is to prepare a budget. By contacting all the departments of a company, the financial manager prepares how much money should be allocated for expenses and how much revenue can possibly be earned. In this article, we have prepared a step-by-step guide for you so that you can prepare a budget for a company in Excel. We will use the previous year’s budget to estimate our goals and create a new budget to support the company’s financial objectives.
➤ Use the following formula to forecast revenues:
=FORECAST.ETS(E4,$B$6:$D$6,$B$4:$D$4)
➤ Replace E4 with the month you are forecasting for, $B$6:$D$6 with the values the forecast should be derived from, and $B$4:$D$4 with the dates of the previous values.
➤ If the expenses are supposed to increase, multiply the previous expenses by the estimated increased rate using the following formula:
=B16*B16+10%
➤ Replace B16 with the expenses that occurred in the previous period.
➤ Calculate tax by multiplying the profit by the tax percentage.
➤ Add everything up using the SUM function.
=SUM(E22:E24)
➤ Replace E22:E24 with your data range.
There are a lot of steps involved in budget preparation, and you need a proper guide to understand all of that. In this article, we have all of the steps explained in detail for your convenience. Make sure to follow the steps carefully to prepare a budget for your company at ease.
Steps for Preparing a Budget for a Company in Excel
In this tutorial, we have the revenues and expenses of the XYZ company for the first three months of the year 2025. We have to prepare a budget for the next quarter. Let’s begin:
Step 1: Prepare the Table
We already have the values for January to March. We are going to expand the table to prepare a budget for April to June.
➤ Select B4 to D4 cells, and autofill E4 to G4 cells.
➤ Select B5 to D25 cell, and click on Format Painter of the Clipboard section in the Home tab.
➤Select E5 to G25 cell to copy the formatting.
Step 2: Estimate the Revenue
Before determining the expenses, it is important to estimate the revenue. We can forecast the revenue from the data of the first three months. Here is how you do it:
➤In E6 cell, write the following formula:
=FORECAST.ETS(E4,$B$6:$D$6,$B$4:$D$4)
➤Autofill F6 and G6.
➤ Write the following formula in the E7 cell and autofill F7 and G7, as there are no other revenues.
=E6
Step 3: Calculate Gross Profit
Now that we have estimated the revenue, we can calculate the gross profit. Follow the procedure below:
➤ The Net Variable Costs of Revenue are the same for all months; we can autofill these for the new quarter.
➤ Write the formula from below to the E11 cell to calculate gross profit, and autofill the other two months.
=E7+E9
Step 4: Estimate the Expenses
There will be some changes in the expenses category as they tend to increase for various reasons. Here is how to estimate them:
➤ The salaries will remain the same; we can autofill those.
➤ We are assuming that the location expenses will increase by 10%. Use the following formula to calculate that and autofill the rest of the months.
=B15*110%
➤ The marketing costs will be increased by 15%. Use the following formula to calculate that, and don’t forget to autofill.
➤ The Administration and Other Costs will remain the same; we can copy and paste those for the new months.
➤ Select B17 to D18 cells and press Ctrl + C .
➤ Select E17 to G18 cells and press Enter to complete copying.
➤ Insert the following formula to calculate profit before depreciation and interest. Autofill the other months after you enter the formula:
=E11+SUM(E14:E18)
➤ Financial expenses remain the same, so the same values from previous months can be autofilled here.
➤The profit before taxes can be calculated using the following formula. Autofill other cells like you did for the rest of the sheet.
=E19+E21
Step 5: Calculate the Taxes and Complete the Budget
Now that all of the revenues and expenses are calculated, we can move on to the taxes and complete the budget.
➤ For this budget, we are assuming a flat 15% tax. It might change for your company, so bear that in mind.
➤ Write the following formula in the E24 cell and autofill the rest to calculate the tax:
=-E22*15%
➤ Finally, calculate the profit/loss using the following formula:
=E22+E24
Frequently Asked Questions
Is Excel good for making a budget?
Considering most companies use Excel to prepare budgets, it can be said that it is good for making a budget. However, Excel uses floating-point values to store everything at a low level, so for bigger calculations, a few pennies can differ from actual values.
How to set a budget for beginners?
For creating a personal budget, there are a lot of templates provided by Microsoft itself that you can use to create a budget. Use your expenses from the previous months to estimate the budget values, and create a draft to practice.
What is the 50 30 20 rule in Excel?
Assuming you are talking about the personal budget, the 50/30/20 rule states that you should spend 50% of your income on your needs, 30% on your wants, and 20% on your savings. These are generic financial advices, not connected to Excel.
What is a fixed expense?
Fixed expenses are costs that remain constant regardless of production. For example, in a factory, the rent of the factory would have to be paid whether you produce something in that factory or not.
How to start a budget spreadsheet?
If it’s your first time creating a budget, consider using a template so that you don’t get lost. Categorize your incomes and expenses, and estimate the values if you don’t have a real number. For an organization, you might want to create a graph or chart to present the budget better.
Wrapping Up
In this article, we have prepared a quarterly budget for a company. We hope that you have learned how to prepare a budget for a company in Excel from this tutorial. Should you have any questions, drop them in the comment section. We will see you in the next article.
