Excel Budget Control YTD and FYForecast.
Companies in the USA 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.
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.
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.
Contact for more information
More information about Controlling Excel Tools
- Decisions Sale Price Lists Calculation Free Excel Template
- Working Hours Timesheet Free Excel Template
- Budget Control Excel YTD & FYForecast M15
- Excel Template Daily Sales Control
- Marketing Plan Free Excel Template
- Human Resources HR Budget Excel Template
- Management Budget Control Easy Theory Difficult Implementation
- E-Commerce KPIs Control Excel Template
- How to calculate costs in a Company
- What is Budgetary Control?
- Projects Kanban Control Excel Template
- Budget Control with SIMULATOR and Projections
- What is an Excel Action Plan template and how can it help you?
- Free Excel Template KPIs Dashborad
- Hotels Excel Cost Calculation
- 3 Methods Selling Price Calculation Free Excel Template
- Marketing Online Social Media Excel Template
- Project Management Excel Templates
- Pipeline CRM Excel spreadsheet
- Excel Spreadsheet for SWOT Analysis
- Working Plan Excel spreadsheet
- Planning Purchase Orders Excel report with ODOO data
Industrial company financial manager
Dani is helping us to use ODOO more efficiently, we are rapidly leveling up with the ERP. It is also preparing us very useful analysis and control reports and outstanding management tools. Always available by phone or email, willing to help and collaborate in everything that is proposed. Very professional and fast work. A key service for our company.
Controller ODOO ERP