Budget Control Excel YTD & FYForecast M15

Budget Control Excel YTD & FYForecast M15.

Goals to achieve in Budget Control:

Configuration of the Budgetary and Analytical Control System version M15 adapted to the needs of the company.

Monthly update of the information and the structure of the tool.

Control of information stability so that there are no errors.

Support and advice to the company in relation to the analysis of the information and how to take advantage of it to obtain the best performance.

The tool will be adapted to analyze the information of the different Business Units that the company requires.

Report #1: Analytic P&L YOY (Year-Over-Year)

Report #2: Budget Control Year-to-Date (YTD). Compare the results as of the accounting closing date of the month with the established objectives.

Report #3: Budget Control Full Year Forecast (FYFcst) Compare the results by combining the data as of the closing date of the current accounting month with the forecasts for the months remaining until the end of the year with the annual budget.

Analysis and control charts for the 3 reports.

Action plan by manager / department.

Other reports: Year-to-Date Budget Control by Department and Full Year Forecast Budget Control by Department.

Advantages of implementing a Budgetary Control System with Projections in your company:

Maximize PROFITS
Improve internal processes to be more competitive
Anticipate decisions to achieve desired results
Encourage the entire organization to continuously improvement and not be left behind
Delegate responsibilities and Evaluate the performance to Directors

Download Budget Control Excel M15

Many companies have assumed a management model that allows the anticipation of results through projections.

This is a great advantage since, if the forecasts are not good, the company has a lot of room to take corrective measures that allow the achievement of the established objectives.

This type of model is applicable to companies with a significant volume of revenues and expenses, it can be applied to small businesses or freelancers, but this type of business does not have the degree of complexity necessary to obtain a good performance from this management model.

We are going to give a very simple example to understand the advantages that the Budget Control Excel Year-to-Date & Full-Year Forecast 2kM15 model can bring to the company.

Suppose that in 2021 the company had revenues of 10.0K and expenses of 7.5K. The profit is 2.5K for 2021. In 2020, it had revenues of 9.0K and expenses of 7.0K, so the profit was 2.0K.

This example can be review in a simple way in the following table, which includes the deviations.

Deviations are the variations of one value with respect to another, in this case the current year with respect to the previous year.

As can be seen, revenues have improved, but expenses have been worse, even though the profit has been better in 2021 than in 2020.

However, the company had set a goal for 2021 according to its strategic plan of 9.5K revenues and 6.5K expenses to obtain a 3.0K profit, despite the fact that it has achieved a good result of 2.5K in 2021 The objective proposed by the management has not been reached.

As can be review in the following table, the revenues have exceeded the objectives, but not the expenses, which have been much higher than those established in the budget, that is, the company has spent more than it had to spend.

This can be due to many factors, so it is important to first identify what factors have affected the increase in expenses. If there has been adequate control or have been external factors beyond the control of the company.

For 2022 the company establishes a new objective based on the results of 2021, the new objective are revenues of 11.0K and expenses of 7.0K to obtain a profit of 4.0K, achieving this objective is essential for the company for strategic and financial reasons.

However, having closed the results for the month of February 2022 and with the projections from March to December 2022, the company has a revenue forecast of 10.2K and expenses of 7.3K, so if everything goes according to plan, the company will make a profit of 2.9K, with a negative deviation of 1.1K from the target, as review in the following table.

Is this a negative situation? Of course NOT, in any case it is an advantage or an opportunity, since these projections are made in February for the period from March to December 2022, which means that the company has 10 months to take the necessary measures to achieve or exceed the target.

These measures, which a priori can be obvious and simple, such as increasing revenues or reducing expenses, in organizational complex companies can be a challenge.

It is not easy to reduce expenses without affecting revenues, which means to do more with less or at least with the same resources.

And this is where the thinking heads of companies come in, those who propose solutions that achieve better results with an optimization of resources.

In large companies they use methodologies such as lean manufacturing, 6-sigma, or any methodology that boost continuous improvement.

These companies need continuously improvement to maintain or increase their competitiveness and not be swallowed by competition.

How many times have you heard that companies that don’t move forward end up dying?

In many cases it is an unwritten rule that is usually accomplished.

Leadership or survival depends on continuous improvement.

Therefore, to improve it is necessary for the company to be able to control the results and not let the results control it.

For this reason, American companies that have a very strong philosophy of continuous improvement require a budget control system capable of managing projections that allows them to make decisions or corrective measures before the year ends.

And this is what a management tool such as the Budget Control Excel 2KM15 USA version facilitates.

On my page I have published many versions of this management model that I have implemented in some companies and year after year it has evolved, including more functions and automations.

This version implemented in 2021 and with a view to 2022 and subsequent years is one of the most complete versions implemented and updated in all companies I provide this type of service.

I will try to explain the most important functions for Budget Control Excel 2KM15 USA version.

Language selection, currently Spanish or English, although you can substitute the translations of the language for the one your company needs.

It can also be configured so that the financial year begins any month of the year (for example, it begins in July instead of January)

Importing and checking accounting data easier, although it is a task that I perform every month to the companies that provide the service, it is worth mentioning it.

Settings area. Here you define the Analytical Accounts to be used, the structure of the report model that will be common to all reports and the terms to be translated that will be applied according to the selected language.

Mapping Analytical Accounts. The objective of this configuration is to relate ledger accounts with their corresponding analytical accounts.

We can use a matrix distribution system, registering percentages of accounting accounts to different analytical accounts, or use a 1 to 1 mapping, that is, registering a single analytical account to an accounting account.

Regardless of the mapping used, this configuration is the key to translate the accounting information into the analytical information that we will use in the reports.

Distribution of business units. With this configuration we can create different analytical reports by business units, in addition to the consolidated report that unifies each business unit. This type of configuration is used when a company has a single accounting for different very well differentiated lines of business (for example, a company that manufactures Wine and Cheese at the same time) 2 different businesses using the same accounting.

If each business unit corresponded to a company that belongs to a group and each company had its own accounting, then each company would have its own budget control system and the distribution by business units would not apply.

The distribution by business units is done by assigning percentages through 2 methods.

Method 1) Assigning percentages of ledger accounts to each business unit.

For example, the sales accounting account could be assigned 75% to Wines and 25% to Cheese. Possibly there are accounting accounts that are used to relate directly 100% into a business unit, therefore this assignment is simple, however, the usual thing is that there are common accounts to both business units whose distribution percentage can be complex to assign. For example, administrative expenses common to both business units, it could be done in a simple way by assigning 50% to each unit, unless the percentage that each unit amount can be clearly identified.

Method 2) Assigning analytical account percentages to each business unit.

The operation is similar to that of accounting accounts, but instead of making a distribution by accounts, it is simplified through analytical accounts.

Budget Configuration (Objective or situation desired by the company)

In this version we can use 3 methods to configure the budget.

Each method is adapted according to the needs of each company.

The simplest method is the «Analytical Budget» since it will be prepared from analytical accounts, to elaborate it we can use information from the previous year applying adjustments for the following year or the current year until the closed accounting month, including the projections of the months pending to close.

Maybe a more advanced method is required, then we can make it with a zero base, this is more complex since it consists of preparing the budget from scratch, practically without any reference (this way of preparing the budget is not usually applied in companies where I implement this system)

Another simple method of preparation is the «Accounting Budget», it is prepared from accounting accounts, normally using the information from the previous year or the current year up to the closed accounting month, including the projections of the months pending to close.

And the most complex method is the «Budget by Items» here we go to a more detailed level using concepts such as identified suppliers, each concept must be assigned an accounting account. Some suppliers can be grouped under a single concept (for example office supplies). Here it is important to go into detail in those concepts that add significant value to the company. In some companies I have implemented this type of budgeting and it works very well. But you need to be aware that you must dedicate more time to work.

Forecasts Configuration. You can use the same methods that can be configured when preparing the budget.

Unlike the budget, the projections may vary each month as interpreted by the Manager, the Financial Director, the Controller or the Head of Administration or Accounting or depending on the decisions to be taken or the corrective measures to be applied.

When we start working in January or the first month of the financial year, the configured projections will correspond exactly.

But once the accounting month of January or the first month of the financial year is closed and it is compared with the budget, we will realize that we will have to adjust the projections.

Projections will always have to be justified, to do this we can support an action plan or a roadmap that validates them.

Let’s take a look the REPORTS

3 essential reports to achieve the strategic objectives of the financial perspective

Report 1. YEAR OVER YEAR Comparison Year-to-Date

Analysis of the evolution of each analytical account and evaluation of the performance of the current year vs the previous year

Report 2. YTD Budget VS Actual YTD.

How we are at the accounting closing date regarding the budget.

Report 3. FY Budget VS Full-Year Forecast.

How we are going to be at the end of the year with respect to the objective based on the actual data of the months already closed in accounting and the projections that we can control and manage until the end of the year.

This report is the one that contributes the most value to the system since it allows planning, anticipation, taking measures and decisions, delegating action plans … In order to achieve the objectives and establish a methodology for continuous improvement.

Download a DEMO of the 2kM15 version Budget Control.

Download Budget Control Ytd FyFcst M15

Example of Budgetary Control Reports

J.A.T
This way of keeping a budget control changes everything. We had never tried it because we did not think it would be useful. But with Dani’s methodology we now see the potential and we think it will help us in very many ways.

R.M
The Budgetary Control technique and tool is helping us to better manage expenses and that allows us to make better decisions. A great contribution to our management control.

Premium Management Tool

Included in Management Control Service or ODOO & CONTROLLING

More information about Controlling Tools