Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Prepare Annual Budget for a Company in Excel

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.

Design Preliminary Summary Layout to Prepare Annual Budget for a Company

  • 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.

Design Preliminary Summary Layout to Prepare Annual Budget for a Company

  • In the end, title cell B10 as Total to get the sum of all project income for a specific month.

Design Preliminary Summary Layout to Prepare Annual Budget for a Company

  • Besides the direct income, our institution may get money from other sources.
  • Now, create another sub-section called Others Income in cell B11.

Design Preliminary Summary Layout to Prepare Annual Budget for a Company

  • Write down the following items in the range of cells B12:B15.

Design Preliminary Summary Layout to Prepare Annual Budget for a Company

  • Similarly, title cell B16 as Total to show the sum of any specific item of any particular month.

Design Preliminary Summary Layout to Prepare Annual Budget for a Company

  • 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.

Design Preliminary Summary Layout to Prepare Annual Budget for a Company

  • 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.

Design Preliminary Summary Layout to Prepare Annual Budget for a Company

  • 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.

Design Preliminary Summary Layout to Prepare Annual Budget for a Company

  • 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.

Design Preliminary Summary Layout to Prepare Annual Budget for a Company

  • 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.

Design Preliminary Summary Layout to Prepare Annual Budget for a Company

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.

Generate Detailed Project Budget to Prepare Annual Budget for a Company

  • 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.

Generate Detailed Project Budget to Prepare Annual Budget for a Company

  • 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.

Generate Detailed Project Budget to Prepare Annual Budget for a Company

  • 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.

Generate Detailed Project Budget to Prepare Annual Budget for a Company

  • 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.

Generate Detailed Project Budget to Prepare Annual Budget for a Company

  • Similarly, drag the Fill Handle icon to your right to copy the formula up to cell N15.

Generate Detailed Project Budget to Prepare Annual Budget for a Company

  • 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.

Generate Detailed Project Budget to Prepare Annual Budget for a Company

  • You will get the detailed project budget list.

Generate Detailed Project Budget to Prepare Annual Budget for a Company

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


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.

Create Detail Salary Sheet to Prepare Annual Budget for a Company

  • 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.

Create Detail Salary Sheet to Prepare Annual Budget for a Company

  • 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.

Create Detail Salary Sheet to Prepare Annual Budget for a Company

  • 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.

Create Detail Salary Sheet to Prepare Annual Budget for a Company

  • 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.

Create Detail Salary Sheet to Prepare Annual Budget for a Company

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.

Finalize Summary Report of the Annual Budget for a Company

  • 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.

Finalize Summary Report of the Annual Budget for a Company

  • 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.

Finalize Summary Report of the Annual Budget for a Company

  • 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.

Finalize Summary Report of the Annual Budget for a Company

  • After that, to get the Total Income, write down the following formula in cell C18.

=C10+C16

  • Press Enter.

Finalize Summary Report of the Annual Budget for a Company

  • 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.

Finalize Summary Report of the Annual Budget for a Company

  • 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.

Finalize Summary Report of the Annual Budget for a Company

  • 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.

Finalize Summary Report of the Annual Budget for a Company

  • 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.

Finalize Summary Report of the Annual Budget for a Company

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!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo