How to create a budget in Excel (plus budgeting tips)
By Indeed Editorial Team
Published 6 April 2022
The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.
Businesses of all sizes require budgets. They include the set amount of money each department has based on its turnover. If you run a business or a department, you may use Microsoft Excel to create your budgets. In this article, we discuss what a budget is, how to create a budget in Excel and some tips for budgeting within a business or department.
What is a budget in the workplace?
A budget is the amount of money that you have for business expenses. You could consider overall business expenses or expenses for a particular department. For example, if you run a small business, you might set aside an amount of your turnover for all business expenses. You could further split this into different areas, such as marketing, staff, equipment and website costs. It's a good idea to write your budget down and keep it organised, so you can visualise how much you're spending and how much money remains. You can do this by creating a budget in Excel.
How to create a budget in Excel using templates
Templates are perhaps the easiest way to create a budget in Excel. If you want a general budget and can experiment with different settings to see which works best for your company, you could opt for a template. Follow these steps to create a budget in Excel using a pre-designed template:
1. Navigate to the Excel budget window
To locate this window, click on 'File' then 'New' and search for 'Budget'. A popup window appears with a few different options for budgeting templates. You could try pie charts or bar graphs. There are also a few tables that might work best for workplace budgets.
2. Enter the information and see what changes
With a template, the equations are already set up so you can enter your own information and see how it affects the spreadsheet. You might calculate how much you have left of your budget and see whether some departments have too much or too little. Various templates are suitable for different situations, but others are quite specific. You may wish to experiment with a few before finding the best one for your business.
How to create a custom budget in Excel
If you want to fully control your budget spreadsheet and include some bespoke parts, you could create a custom budget in Excel. Once created, you can re-use this for other departments and subsequent years. Here's how to create a custom budget spreadsheet in Excel:
1. Open a new spreadsheet
To start your new custom budget, open a new spreadsheet. You can do this by clicking 'File', 'New', then 'Blank Workbook'. Then, begin the formatting. Select the first two rows and the columns you need. Navigate to the 'Home' menu and press 'Merge & Centre'. You can write the title of your budget here, for example, 'August budget'.
2. Add details of payments
First, you could add details of any payments you've received. For example, if you work for a consultancy firm and have five clients, you could write their names in the first column. If their names don't fit in the provided space, you can merge two cells. Next to the list of clients, create a column for the payment date, then write the amount in the next column. At the end of this column, note your total expenditure. You can do this by typing the equation '=SUM(E4:E9)', changing the cell numbers to reflect the cells that have payments.
3. Create an expenses budget
Once you have a payment budget, you can create an expenses budget. You can either do this in the same spreadsheet or on a new worksheet. Follow step one and make your table. Include different expenses, for example 'marketing', 'software' and 'staff' in the left column. In the next column, write your projected expenditures, which all add up to create your total projected business expense. Write your business expense at the bottom of the right column.
4. Edit and reflect
When you've finished the month, you can edit your budget to reflect what you actually spent. You can also include a cell underneath your projected business expense to tell you how close you are to the budget. In this cell, write the equation ‘=SUM(E10-E9-E8-E7-E6-E5-E4)'. You can edit the formula to reflect which of your cells contain information. The first number (E10 in this example) is your projected total expense. This indicates how much money you have remaining or how much you have gone over budget.
5. Insert a graph
You could also insert a graph to help visualise your expenses. To do this, add a column to the right of your spreadsheet and call it 'Percentage'. Then, add the formula '=SUM(expense/total expenses)'. For example, if you are working with your marketing budget at first, your formula might be '=SUM(E4/E10)'. If you have spent £200 on marketing and your total budget is £1000, this is 20% of your income. You can then highlight the column and press the % button at the top to convert these numbers to percentages.
To create the graph, highlight the percentage and the expenses category columns. Navigate to 'Insert' at the top and select your graph. Pie charts and bar graphs can both be effective ways to display your information in a clear and accessible manner. You could even create a graph for your projected expenses and your actual expenses to see how they compare.
Tips for managing your budget
Once you have created your budget in Excel, you might want to read some tips about managing it. Here's some helpful information:
Use your budget to improve your business
Budgets can help you ensure that you have enough money to pay bills and staff, enabling you to cut down on things that aren't a priority. You can also use yours to grow your business. For example, if you have £500 remaining each month, you could invest in marketing. Or, if you have enough each month to pay a full-time staff member, you could hire somebody. Without a budget, you might not have a clear idea of how much money you have remaining each month; writing it down can help you understand your situation and make savvy business decisions.
Make an emergency fund
Most business owners experience unexpected costs during their working life. To mitigate this, you may wish to add an emergency fund to your list of expenses each month. You could put a certain amount, perhaps £100 or £200, in a separate bank account and save it for any emergencies or unexpected situations. Having the assurance of money saved up could increase your confidence in spending in other areas and ensure you do not have financial trouble in the future.
Review the budget with other employees
It's a good idea to go through your budget with another senior staff member. They may know different areas and suggest ways to cut down on certain expenses. They might also recommend increasing the budget in particular areas. You could also share the budget with employees in different departments. For example, you could show human resources how much you have allotted for staff wages and ask for feedback. This could encourage various departments to work productively and maximise their allocated income.
Don't forget taxes
When working out your budget for different departments, you consider your total turnover minus any taxes. The money that remains after turnover and expenses is your profit. When working out how much money you have to spend, don't forget that you may also have taxes to pay. Calculate how much these could be and factor them in.
When should I create a budget?
If you own a business, it's a great idea to create a budget from the first day of operation. Your budget may initially be low, but managing your money is a good habit to form. Depending on your business, you might create a budget every month or once per year. If you create multiple budgets for different departments, it may be beneficial to have them running for the same amount of time.
Many businesses do their budgeting at the beginning of the tax year. This is April 6th each year. This enables businesses to consider how much of their turnover they have left after tax and work out annual profits from there. You might calculate an annual budget, split this up into months and calculate the actual expenditure at the end of each month. You can then work out the total expenditure for the entire year by April 5th, the subsequent year.
Please note that none of the companies mentioned in this article are affiliated with Indeed.
Explore more articles
- Programme vs project: definitions, differences and tips
- How to be a socially responsible corporation (with benefits)
- What is subscription management? (And why is it important?)
- 8 top finance management software (plus key features)
- 8 crucial project roles and their main responsibilities
- 11 techniques for dealing with adversity in the workplace
- 5 DAM software packages (with definition and benefits)
- 6 machine learning tools (and what they're used for)
- How to calculate the contribution margin ratio in a business
- What are the most common drop shipping benefits?
- 10 tips for onboarding customers effectively (with steps)
- 14 different types of digital content (with definitions)