How to Efficiently Prepare Your Company Budget in 4 Simple Steps

Preparing your company budget does not have to be tedious if you follow these 4 simple steps using Excel Power Tools

1. Send out an empty template to the business units
2. Save the completed templates in a folder
3. Consolidate the completed template using Power Query
4. Create the Power Pivot report.

1. Send out an empty template to the business units

Before you send out the template to the business unit, you need to understand that accountants and other business units are wired differently. When it comes to budgeting, accountants think of the profit and loss statement and would prepare the template based on accounting codes. Other business units budget based on the way they operate. Other business units do not know how to fit their budgets into the template accountants prepared. The difference between the two can be summed up by the 2 terms, financial budgeting and activity-based budgeting

Financial Budgeting vs Activity-Based Budgeting

Financial budgeting is the preparation of future business revenue and cost based on the statutory structure of the financial statement.

Activity-based budgeting is the preparation of the future business revenue and cost based on the activities undertaken by the respective business units.

For example, the sales units will prepare the budget based on the way they operate. If the sales units are focused on managing direct customers, distributors, resellers, etc, they are likely to budget based on these categories. They are likely to plan their sales expenses around these activities instead of breaking them down into telephone expenses, entertainment expenses, utilities, etc.

The marketing unit is likely to plan their marketing budget based on the events they are planning to participate in, product launches, marketing campaigns. It is unlikely to be based on generic descriptions like marketing, advertising & promotions, meetings & exhibitions.

The logistic unit is unlikely to budget warehousing and shipping costs only. It is likely interested to know the goods that are coming in and may be involved in budgeting the shipping and warehousing component of Cost of Good Sold. The sales unit is probably aware of the cost of manufacturing but not the other costs.

Human Resources unit is unlikely to budget based on staff salary only. They are likely to budget for new hires, replacements, training, insurance and employee benefits. With the changes in activities, HR is likely interested to know how they should budget for each activity.

The business units are unlikely to be interested in the accounting codes and budget according to the template given by the accounting department. They are also unlikely to be trained or interested to know the money they have budgeted for is fixed assets or monthly expenses. All they know is that they need the money to carry out their activities.

When Finance sends out the budgeting template, they are unlikely to get a very enthusiastic response from the business units. The business units do not know how to fit the numbers into the template and are likely to do so too. They are not trained in this area. If they are, they wouldn’t be in their respective roles. Instead, they would have become accountants.

To have a successful and effective budgeting exercise, the Finance unit needs to prepare the templates with more columns to accommodate the needs of the business units. In this way, they are likely to get a better response and also have a better understanding of the budget numbers prepared by the business units.

2. Save the completed template in a folder

There is no need to link the files together. All you have to do in this step is to save the completed template in the folder. Just make sure that the columns are arranged in the same way. There is a function in Excel called Power Query which can help you consolidate the numbers together, not once but many times. Your budget will always be up to date when you use Power Query to consolidate the budget numbers

3. Consolidate the completed template using Power Query

Power Query has a function to get data from a folder. By using this function, the completed templates can be aligned to one another. No linking of files is needed. Power Query can also hide unwanted rows and columns during consolidation. When the business units gave you the template, just make sure that the columns are not changed.

4. Create the Power Pivot report

When Power Query has consolidated the numbers, which usually take no more than one minute, just direct Power Query to load the numbers into Power Pivot. This relatively new function can help you calculate the variance between the budgeted numbers and actual. You don’t have to do any copy & paste at all.

5. Data Visualization

That’s the in thing now. It is no longer just numbers. This is an extra step to help you score points and make your budgeting process as efficient as possible. By presenting the numbers using visuals, management is likely to get a clearer picture of the entire budget and shorten the deliberation process.

Would you like to know how we do it step by step?

Drop us a message in our contact us page. We have the right solution and course for you to make sure that you have an easy time prepare the budget for next year.