Soumik Dutta
The annual budget report is a significant element for any company. This report shows us the detailed income and expenses information of that organization. In this article, we will show you step-by-step procedures of how to prepare an annual budget for a company in Excel. If you are curious to know about it, download our template and follow us.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
What Is Annual Budget?
The annual budget of any company is a detailed report of that company’s expenditures for any financial year. It will help that organization to balance out the costs with their revenue for that year. Moreover, we can easily find every detailed issue month-wise regarding any project through the annual budget report. In addition, we can also get the most profitable projects, their peak revenue times, losing projects, and their most occurrence period from this report.
Step-by-Step Procedure to Prepare Annual Budget for a Company in Excel
In this article, we will show you the step-by-step procedure to prepare the annual budget for any company.
Step 1: Design Preliminary Summary Layout
In the first step, we are going to create the primary design layout of our summary report. First, we will design the Income section, and then we will make the Expense section.
- First of all, we are going to insert a shape where we input our summary title.
- For that, in the Insert tab, click on the drop-down arrow of the Illustration > Shapes option and choose your desired shape. In our case, we have selected the shape titled Flowchart: Multidocument.
- Write down the following title inside the shape Annual Budget-2022 and modify the shape format according to your desire.
- Now, below the shape, we will create the Income section.
- Entitle cell B5 as Income, and write down the name of all the months in the range of cells C5:N5.
- At last, entitle the cell O5 as Total.
- We have considered that our major income source is our projects. To separate them, we create a sub-section and titled cell B6 as Project Income.
- Then, create some slots for the running projects of your organization. We denote three rows for our sample projects.
- In the end, title cell B10 as Total to get the sum of all project income for a specific month.
- Besides the direct income, our institution may get money from other sources.
- Now, create another sub-section called Others Income in cell B11.
- Write down the following items in the range of cells B12:B15.
- Similarly, title cell B16 as Total to show the sum of any specific item of any particular month.
- Finally, denote cell B18 as Total Income to show the results of the total income.
- Thus, the preliminary design of our Income section is finished.
- Similarly, design the Expense section.
- For that, in the range of cells B22:B35 and B39:B42, note down some direct and indirect expense purposes, respectively.
- Additionally, after each sub-section keep a row to show the total result of that specific cause.
- At last, in cell B45, write down the title of that Total Expenses and keep this row to show the result of total costs.
- After that, denote cell B47 as Total Profit to show the profit in that row.
- Finally, we will insert our company logo.
- For that, select cell M2.
- In the Insert tab, click on the drop-down arrow of the Illustration > Pictures and choose the This Device option.
- As a result, a dialog box called Insert Picture will appear.
- Select your institution’s logo and click on Insert. To demonstrate the process, we are inserting our website logo titled Exceldemy.
- You will see our preliminary design of the summary report is completed.
Thus, we can say that we have completed the first task, to prepare an annual budget for a company in Excel.
Read more: How to Do Budgeting and Forecasting in Excel (2 Suitable Ways)
Step 2: Generate Annual Budget Report for Project
Now, we are going to prepare our detailed project budget. It is a detailed sheet of a specific income issue.
- First, create a new sheet in the Sheet Name Bar and entitle it to Project Budget.
- Now, in the Summary sheet, select the range of rows 1:5 and press ‘Ctrl+C’ to copy every item.
- Then, go to the Project Budget sheet and press ‘Ctrl+V’ to paste them.
- After that, modify the sheet title Annual Budget-2022 to Annual Project Budget.
- Besides it, change the title of cell B5.
- For that, write down the following formula in cell B5.
=Summary!B7
- Press Enter.
- Then, in the range of cells B6:B11, write down the following entities to input those specifications related to that project.
- To get the annual sum, we use the SUM function. For that, write down the following formula in cell O6.
=SUM(C6:N6)
- Again, press Enter.
- Double-click on the Fill Handle icon to copy the formula up to cell O11.
- Now, entitle cell B12 as Cost of Goods Sold (COGS), and for calculating its value, write down the following formula in cell C12.
=SUM(C6:C10)-C11
- Press Enter to get the result.
- Then, drag the Fill Handle icon to your right to copy the formula up to cell O12.
- After that, in the range of cells B13:B14, write the following titles to input their value in those rows.
- Finally, in cell B15, denote the row as Profit or Loss, and to estimate the value, write down the following formula in cell C15.
=(C13*C14)-C12
- Press Enter.
- Similarly, drag the Fill Handle icon to your right to copy the formula up to cell N15.
- At last, to get the annual profit of that project, write down the following formula in cell O15.
=SUM(C15:N15)
- Press Enter for the last time.
- You will get the detailed project budget list.
Finally, we can say that we have finished the second task, to prepare an annual budget for a company in Excel.
💬 Things You Should Know
If you have multiple projects, create separate sheets for individual projects. The rest of the procedure will be similar.
Similar Readings
- How to Make a Family Budget in Excel (2 Effective Ways)
- How to Make a Wedding Budget in Excel (2 Suitable Methods)
- How to Make a Household Budget in Excel (2 Smart Ways)
Step 3: Create Annual Salary List
In the following step, we will create a detailed salary sheet for our staff. This is a detailed sheet of a particular expense purpose.
- At first, create a new sheet in the Sheet Name Bar and entitle it to Salaries & Wages.
- Now, in the Summary sheet, select the range of rows 1:5 and press ‘Ctrl+C’ to copy every item.
- After that, go to the Salaries & Wages sheet and press ‘Ctrl+V’ to paste them.
- Then, modify the sheet title Annual Budget-2022 to Annual Salary List.
- Besides it, change the title of cell B5 from Income to Employee Name.
- Next, specify some rows for employees of your institutions.
- Now, to get the total salary of any employee, we will use the SUM function. Fo that, write down the following formula into cell O6.
=SUM(C6:N6)
- Press Enter.
- Double-click on the Fill Handle icon to copy the formula up to cell O8.
- Besides it, to get the total salary for any particular month, write down the following formula into cell C9.
=SUM(C6:C8)
- Again, press Enter.
- Then, drag the Fill Handle icon to your right to copy the formula up to cell O9.
- You will get ready the detailed salary sheet.
At last, we can say that we have accomplished the third task, to prepare an annual budget for a company in Excel.
💬 Things You Should Know
In this template, we just showed the detailed report of the salary and wedge of the Expense section. If you require, you can also add detailed reports of other cases like Utilities, Telephone Bills, Repairs & Maintenance, etc. You have to only follow this step every time.
Step 4: Generate Annual Budget Report for Company
Now, we will establish the relation in the Summary sheet with the other sheets.
- At the beginning of this step, to get the value of Profit or Loss of the first project, write down the following formula in cell C7.
='Project Budget'!C15
- Press Enter.
- After that, drag the Fill Handle icon to your right up to cell N7 to get values for every month.
- Now, to get the annual income of any project, we use the SUM function. In cell O7, write down the following formula to get the annual income of the first project.
=SUM(C7:N7)
- Press the Enter.
- Drag the Fill Handle icon to your right to copy the formula up to cell O9.
- Again, use the SUM function in cell C10 to get the total for that specific month.
=SUM(C7:C9)
- Press the Enter key.
- Similarly, drag the Fill Handle icon to your right to copy the formula up to cell O10.
- Now, use the same function to get the Totals in the Others Income section.
- After that, to get the Total Income, write down the following formula in cell C18.
=C10+C16
- Press Enter.
- Drag the Fill Handle icon to your right to copy the formula up to cell O18.
- Similarly, use the same formula to get the total of any particular month and for any specific item and Total Expense in the Expense section.
- You may remember that we have already created a detailed salary sheet. To get the total value, write down the following formula in cell C23.
='Salaries & Wages'!C9
- After that, press Enter.
- Then, drag the Fill Handle icon to your right to copy the formula up to cell N23 to get all the values.
- At last, to calculate the Total Profit, write down the following formula in cell C47.
=C18-C45
- Finally, press Enter.
- Drag the Fill Handle icon to your right to copy the formula up to cell O47 to get the value for every case.
- Our annual budget template is ready to use.
Thus, we can say that we have finished the final task, to prepare an annual budget for a company in Excel.
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to prepare an annual budget for a company in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!
