How to Create a Department Budget in Excel (with Detailed Steps)

Get FREE Advanced Excel Exercises with Solutions!

Looking for a way to create a department budget in an Excel workbook? Don’t worry at all. You have landed in the right place. just take a cup of tea and read the article. Here, I will show you how to create a department budget in Excel.


Download Practice Workbook

You can download the practice book from the link below.


Step by Step Guide to Create a Department Budget in Excel

Let’s say., we are dealing with a department dealing with boys wear (i.e. tops, bottoms, outerwear). We want to create a budget for this department in Excel.

In this section, you will find some practical steps to create a department budget in Excel. I will discuss them step-by-step with proper illustrations. Let’s check them now!


🖊️ Step 1: Determining Budget for Class and Sub-Class

Whatever department you are dealing with, this would include some class and sub-class under it. A specific amount of budget should be determined for the classes (or sub-classes).

  • First of all, create a data table in Excel like this. The data table would contain some classes and sub-classes. Assign factors for classes (i.e. Tops, Bottoms, Outerwear) for the department you are dealing with.

Factors to create Department budget in Excel

  • Then, under each type of class, there will exist some sub-classes. Assign these sub-classes under each category of classes.

Sub Classes in excel to Create a Department Budget

  • Now, determine a specific amount of budget (i.e. $50000) for the corresponding department. This budget will be distributed over each category of classes and sub-classes.

Department Budget in Excel calculator

Read More: How to Do Budgeting and Forecasting in Excel (2 Suitable Ways)


🖊️ Step 2: Setting Percentage of Budget Under Each Class

As you have selected classes under your department and set a budget for your whole department, now a specific portion of the budget will be set under each class in such a way that summing the distributed budget will result in the total budget

  • In our budget sheet, we have assigned three classes. We have set the percentages of the total budget for the classes like below:
  • Tops: 70%
  • Bottoms: 26%
  • Outerwear: 4%

Percentage of Classes in Excel to Create Department Budget

  • It is noticeable that we have used the SUM function to show the percentages assigned to the classes are 100% in total.

Read More: How to Make a Personal Monthly Budget in Excel (with 6 Quick Steps)


🖊️ Step 3: OTB Planning for the Budget

Basically, OTB (Open to Buy) is an inventory management strategy a business uses for a specific amount of time. It is the amount needed to purchase a specific product to achieve the set target of the projected budget. Let’s determine OTB for the classes of the corresponding department

  • Here, apply the following formula to determine the budget considering the percentage of the class Tops.

=$D$16*E8

Here,

  • D16 = Department Total Budget
  • E8 = Percentage of Budget for the class Tops

  • Then, copy this formula to the other classes to get the distributed budget for them.

  • After getting the budget for each main class, the percentage of OTB for the sub-classes should be set. Now, your business model should decide what percentage of the main class would be distributed to the sub-classes. And one thing is noticeable here: summing the percentages of the sub-classes under any category of the class must be 100%.

Now, it’s time to calculate the OTB for each of the sub-classes.

  • Here, type the following formula to determine the OTB for the sub-class: T-shirts under the class: Tops.

=$D$8*F5

Here,

  • D8 = Total Class Budget for Tops
  • E8 = Percentage of Budget for the Sub-class T-shirts

Creating Department Budget in Excel

  • Then, drag the Fill Handle tool to Autofill the formula for the other sub-classes of the class: Tops.

Department Budget in Excel

  • Hence, you will get the OTB for the sub-classes of the class: Tops.

Budget for Subclass t Create Budget in Excel

  • Repeat this process for all of the classes and the sub-classes.

Department Budget in Excel

Read More: How to Create a Personal Budget in Excel (With Easy Steps)


Similar Readings


🖊️ Step 4: Determining Budget for the Occurring Time Period

Let’s say, our business model has a plan to deal with two consecutive months (i.e. October and November) and what percentages of which classes and sub-classes will be covered are predetermined.

Based on this data, we will now determine what amount of budget for which classes and sub-classes would be spent in which month.

  • The dataset specifies that for the Sub-class T-shirt, 42% of the budget will be spent in October and the rest 58% will be spent in November. In order to calculate the amount spent on October for Tshirt, apply the formula below.

=$D5*D20

Here,

  • D5 = OTB of T-Shirt
  • D20 = Percentage of Budget to be spent on October for T-shirt

  • Now, press ENTER to get the budget for T-shirt on October,

  • Then, find out the budget for the other sub-classes and months of the class; Tops in the same way.
  • Now, apply the formula below to calculate the budget spent on the sub-class: T-shirt for the whole time period (October and November).

=SUM(G5:H5)

Here,

  • G5 = OTB of T-Shirt on October
  • H5OTB of T-Shirt on November

  • After that, drag the formula down for the other sub-classes of this class.

Read More: How to Prepare Budget for a Company in Excel (2 Suitable Examples)


🖊️ Step 5: Calculate Class-wise Budget and Total Budget

  • Now, apply the following formula to get the total amount spent in October for the class: Tops.

=SUM(G5:G7)

Here,

  • G5 = OTB of T-Shirt on October
  • G7OTB of Shirts on October

Calculating Total Budget in Excel

  • Then, drag the formula to the right-end to get the result for the month: November and also the total budget on the whole time.

Calculating Department Budget in Excel

  • Following the similar steps, find out the result for other classes and sub-classes.

  • Finally, determine the total budget spent on October by applying the following formula.

  • Then, drag the formula to the right end to get the budget for October and also the total budget for the whole period of time.

How to Create a department Budget in Excel

🔗Note: The total budget obtained here must match with the predetermined departmental budget.

Read More: How to Calculate Remaining Budget Using Formula in Excel


Conclusion

In this article, I have tried to show you some methods to create a department budget in Excel. I hope this article has shed some light on your way to this. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy. Have a great day!


Related Articles

Rafiul Hasan
Rafiul Hasan

Hey there! I am Md. Rafiul Hasan. Currently, I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our work time and made it easy for us to perform quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo