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

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.

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

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

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%

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

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.

Here,

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

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

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

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

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

Similar Readings

• How to Make a Budget Constraint Graph on Excel
• Prepare a Vacation Budget in Excel (with Easy Steps)
• How to Make Food and Beverage Budget in Excel
• Make a Family Budget in Excel (2 Effective Ways)
• How to Make a Household Budget in Excel (2 Smart Ways)

🖊️ 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.

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

Here,

• G5 = OTB of T-Shirt on October
• H5 =  OTB 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.

Here,

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

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

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

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

• How to Create a Business Budget in Excel (With Easy Steps)
• Make a Budget Pie Chart in Excel (with Easy Steps)
• How to Prepare Annual Budget for a Company in Excel
• Create an Operating Budget in Excel (with Detailed Steps)
• How to Make a Wedding Budget in Excel (2 Suitable Methods)
• How to Make a Budget in Excel for College Students (with Quick Steps)