Monthly office budget. Best Practices for Creating Budget Forms in Excel

The problem of lack of money is relevant for most modern families. Many people literally dream of getting out of debt and starting a new financial life. In times of crisis, the burden of low wages, loans and debts affects almost all families without exception. This is why people strive to control their expenses. The point of saving expenses is not that people are greedy, but to gain financial stability and look at your budget soberly and impartially.

The benefit of controlling financial flow is obvious - it is cost reduction. The more you save, the more confidence you have in the future. The money you save can be used to create a financial cushion that will allow you to feel comfortable for a while, for example, if you are unemployed.

The main enemy on the path of financial control is laziness. People first get excited about the idea of ​​controlling the family budget, but then quickly cool down and lose interest in their finances. To avoid this effect, you need to acquire a new habit - constantly control your expenses. The most difficult period is the first month. Then control becomes a habit, and you continue to act automatically. In addition, you will see the fruits of your “labor” immediately - your expenses will be amazingly reduced. You will personally see that some expenses were unnecessary and can be abandoned without harm to the family.

Poll: Is an Excel spreadsheet enough to control the family budget?

Accounting for family expenses and income in an Excel spreadsheet

If you are new to creating a family budget, then before using powerful and paid tools for home accounting, try maintaining your family budget in a simple Excel spreadsheet. The benefits of such a solution are obvious - you do not spend money on programs, and try your hand at controlling finances. On the other hand, if you bought the program, then this will stimulate you - since you spent money, then you need to keep records.

It is better to start drawing up a family budget in a simple table in which everything is clear to you. Over time, you can complicate and supplement it.

Read also:

Here we see three sections: income, expenses and report. In the expenses section we have introduced the above categories. Next to each category there is a cell containing the total expense for the month (the sum of all days on the right). In the “days of the month” area, daily expenses are entered. In fact, this is a complete report for the month on the expenses of your family budget. This table provides the following information: expenses for each day, for each week, for the month, as well as the total expenses for each category.

As for the formulas used in this table, they are very simple. For example, the total consumption for the “car” category is calculated using the formula =SUM(F14:AJ14). That is, this is the amount for all days on line number 14. The amount of expenses per day is calculated as follows: =SUM(F14:F25)– all numbers in column F from the 14th to the 25th line are summed up.

The “income” section is structured in a similar way. This table has categories of budget income and the amount that corresponds to it. In the “total” cell the sum of all categories ( =SUM(E5:E8)) in column E from the 5th to the 8th line. The “report” section is even simpler. The information from cells E9 and F28 is duplicated here. Balance (income minus expenses) is the difference between these cells.

Now let's make our expense table more complex. Let's introduce new columns “expense plan” and “deviation” (download the table of expenses and income). This is necessary for more accurate family budget planning. For example, you know that car costs are usually 5,000 rubles/month, and rent is 3,000 rubles/month. If we know the expenses in advance, then we can create a budget for a month or even a year.

Knowing your monthly expenses and income, you can plan large purchases. For example, family income is 70,000 rubles/month, and expenses are 50,000 rubles/month. This means that every month you can save 20,000 rubles. And in a year you will be the owner of a large sum - 240,000 rubles.

Thus, the “expense plan” and “deviation” columns are needed for long-term budget planning. If the value in the deviation column is negative (highlighted in red), then you have deviated from the plan. The deviation is calculated using the formula =F14-E14(that is, the difference between the plan and actual expenses for the category).

What if you deviate from the plan in some month? If the deviation is insignificant, then next month you should try to save on this category. For example, in our table in the category “clothing and cosmetics” there is a deviation of -3950 rubles. This means that next month it is advisable to spend 2050 rubles (6000 minus 3950) on this group of goods. Then, on average, over two months you will not have any deviation from the plan: (2050 + 9950) / 2 = 12000 / 2 = 6000.

Using our data from the expense table, we will build a cost report in the form of a chart.

We construct a report on family budget income in the same way.

The benefits of these reports are obvious. Firstly, we get a visual representation of the budget, and secondly, we can track the share of each category as a percentage. In our case, the most expensive items are “clothing and cosmetics” (19%), “food” (15%) and “credit” (15%).

Excel has ready-made templates that allow you to create the necessary tables in two clicks. If you go to the “File” menu and select “Create”, the program will offer you to create a finished project based on the existing templates. Our theme includes the following templates: “Typical Family Budget”, “Family Budget (Monthly)”, “Simple Expense Budget”, “Personal Budget”, “Semi-Monthly Home Budget”, “Monthly Student Budget”, “Personal Expenses Calculator” .

A selection of free Excel templates for budgeting

You can download ready-made Excel tables for free using these links:

The first two tables are discussed in this article. The third table is described in detail in the article about home accounting. The fourth selection is an archive containing standard templates from the Excel spreadsheet.

Try downloading and working with each table. After reviewing all the templates, you will surely find a table that is suitable specifically for your family budget.

Excel Spreadsheets vs. Home Accounting Software: What to Choose?

Each method of doing home accounting has its own advantages and disadvantages. If you have never done home accounting and have poor computer skills, then it is better to start accounting for your finances using a regular notebook. Enter all expenses and income into it in any form, and at the end of the month take a calculator and combine debits and credits.

If your level of knowledge allows you to use an Excel spreadsheet processor or a similar program, then feel free to download home budget spreadsheet templates and start accounting electronically.

When the functionality of the tables no longer suits you, you can use specialized programs. Start with the simplest software for personal accounting, and only then, when you get real experience, you can purchase a full-fledged program for a PC or smartphone. More detailed information about financial accounting programs can be found in the following articles:

The advantages of using Excel tables are obvious. This is a simple, straightforward and free solution. There is also the opportunity to gain additional skills in working with a table processor. The disadvantages include low performance, poor visibility, and limited functionality.

Specialized programs for managing a family budget have only one drawback - almost all normal software is paid. There is only one relevant question here - which program is the highest quality and cheapest? The advantages of the programs are: high performance, visual presentation of data, many reports, technical support from the developer, free updates.

If you want to try your hand at planning a family budget, but are not ready to pay money, then download for free and get down to business. If you already have experience in home accounting and want to use more advanced tools, we recommend installing a simple and inexpensive program called Housekeeper. Let's look at the basics of personal accounting using the Housekeeper.

Maintaining home accounting in the "Housekeeper" program

A detailed description of the program can be found on this page. The functionality of the "Housekeeper" is simple: there are two main sections: income and expenses.

The “Income” section is arranged in a similar way. User accounts are configured in the “Users” section. You can add any number of accounts in different currencies. For example, one account can be in rubles, the second in dollars, the third in Euros, etc. The principle of operation of the program is simple - when you add an expense transaction, the money is debited from the selected account, and when it is an income transaction, the money, on the contrary, is credited to the account.

To build a report, you need to select the report type in the “Reports” section, specify the time interval (if necessary) and click the “Build” button.

As you can see, everything is simple! The program will independently generate reports and point you to the most expensive expense items. Using reports and an expense table, you can manage your family budget more effectively.

Video on family budgeting in Excel

There are many videos on the Internet dedicated to family budget issues. The main thing is that you not only watch, read and listen, but also apply the acquired knowledge in practice. By controlling your budget, you reduce unnecessary expenses and increase savings.

The Ural Lotto company has developed a model in Excel, with the help of which the financial service can quickly draw up an Income and Expense Budget (I&C) and a Cash Flow Budget (CFB). An example in Excel can be downloaded from the link at the end of the article.

To develop a budgeting system, simplify the planning procedure and minimize the risk of errors in financial plans, a company needs a unified financial accounting model in Excel, in which only one table is allocated for data entry, and all others are generated automatically.

Let's imagine that the financial director, together with the economic service, has to develop a budgeting system from scratch. At first, it is difficult to understand how the budget process will be organized, what types of budgets and reports will be required, and in what form it will be necessary to obtain information on actual income and expenses from the accounting program. This is exactly what they thought at Ural Lotto LLC.

In the first months, we limited ourselves to the formation of separate budgets - sales, purchases, divisions. After some time, we managed to develop (sheets “detailed PL” and “detailed CF”, respectively) created using the Excel “Pivot Tables” tool. Using built-in filters, you can select items of interest, subitems of the first and second levels, and group separately income (receipts) and expenses (payments). To reflect the changes made to the worksheet, you will need to right-click and select “Update” from the menu that appears.

The “detailed PL” sheet allows you to create a budget of income and expenses both for the company as a whole and for a separate division. To get it, just select the items “BDR” and “To both budgets” in the “Budget Name” window. If you need a plan for a specific department, you will need to enter its name in the “CFD” window.

The “To both budgets” option is intended in case the numbers are the same for both budgets, that is, for example, when 10,000 rubles in January for some item goes to both the BDR and the BDDS. This is done to avoid entering the same numbers twice.

A detailed BDDS in Excel is created in a similar way: on the “detailed CF” sheet, you need to select “BDDS” and “Both budgets”, and then update the data. There is also filtering by CFD.

When is the indirect cash flow budgeting method better than the direct method?

This article will tell you which cash flow budgeting method is right for your company.

Useful documents

BDR and BDDS example in Excel

An example of an income and expense budget and a cash flow budget (CFB) in Excel for the entire company is shown on the sheets “PL Budget” and “CF Budget”. Unlike detailed plans, they are not formed directly from the worksheet, but after intermediate processing of information on auxiliary sheets (“Code of Articles” and “Code of Art.-Subst.”).

The first of the auxiliary tables is “Code of Articles.” – designed to consolidate budget data on the main items of expenditure and income (receipts and payments). It is configured in the same way as detailed budgets - using the “Budget Name” field (see Fig. 1).

Since it is used for both budgets, the BDR and BDDS can only be generated in turn by applying a filter in the “Budget Name” field. Simultaneous updating of both budgets in this version of the file is impossible. Of course, you can make two such auxiliary tables - for each budget, but using the file will become more difficult, because each time you would have to update a larger number of summary tables. And the file itself will become “heavier”.

Figure 1. Auxiliary table

The second auxiliary table (“Code of Article-Subst.”) consolidates data with the detail “Article” and “Subarticle of the first level”. The combined names of the latter are given in the “For Report” column in the worksheet. The work proceeds similarly to the previously described “Code of Art.” – you must select the name of the budget depending on which one is being formed.

Budgets are filled in with data from auxiliary tables using the VLOOKUP formula. For example, the calculation of “Revenue from the sale of the lottery for “6 out of 40”” for January (cell E10 on the “PL Budget” sheet) will be specified as VLOOKUP($A10;"Code of st.-subst."!$A:$T; 4;0), where A10 is the cell containing the name of the subarticle (“6 of 40”), “Code of art.-subst.”!$A:$T is the initial range in the second auxiliary table, where the search for the row same name, 4 – column number with data for January.

Download an example of BDR and BDDS in Excel

Benefits of Budgeting in Excel

A single budget in Excel has significantly simplified the planning process in the company. Budget adjustments have become easier. It is enough to enter a new value in one table, and this will be reflected in all the others. The likelihood of errors is reduced.

The “all in one” format made it possible to abandon the creation of separate budgets for sales, purchases, taxes, payroll, etc. The Ural Lotto company is relatively small: one central office (employees - 55 people), no branches. Therefore, it is much easier to fill out just one worksheet.

Table. Structure of a budget worksheet in Excel (work table sheet)

When planning, two budget documents are most often used - the Income and Expenditure Budget (IBB) and the Cash Flow Budget (CFFB). However, do not forget about the forecast balance. Almost all data for it is presented in the BDR and BDDS. Let's consider how to create a forecast balance based on the items of these budgets, transform indicators from the BDR and BDDS into the balance sheet.

FORMATION OF FORECAST BALANCE

To draw up a forecast balance, we need five budget documents:

1. Balance at the beginning of the planning period.
2. BDR plan.
3. Inventory purchasing plan.
4. Calculation of VAT payable.
5. BDDS plan.

We will show the connection between these documents schematically (see figure).

The primary document in planning is Budget of income and expenses(Table 1), which presents plans for revenue, cost of products sold and operating expenses in the forecast period.

Document " Calculation of VAT payable» is necessary if the company is on the classical taxation system, that is, it is a payer of value added tax. In this case, all income and expenses in the BDR and part of the balance sheet items (“Fixed Assets”, “Inventories”) according to the accounting rules are reflected without VAT, and in order to transform the data from the Budget of Income and Expenses into the BDR, we will need an additional column in the BDR with the VAT rate allocated therein.

If the company is on a simplified taxation system, all items in the Budget of Income and Expenditures and the Balance Sheet already contain VAT and the process of transferring data from budget to budget is simplified.

We will further show how not to lose VAT when transforming data from the cash flow budget into the cash flow budget, and then from the cash flow budget into the balance sheet, using the example of a company with a classic tax system.

Table 1. Planned Budget of income and expenses
	Article	Budget	VAT rate, %	Amount including VAT, rub.
	Revenues from sales	4 158 000
	Revenue with VAT 18%
	Revenue with VAT 10%
	Other income
	Cost of goods	3 150 000
	Cost of goods with VAT 18%
	Cost of goods with VAT 10%
	Gross income
	Operating expenses
	Labor costs
	Cover part
	Salary taxes
	Personnel costs
	Medical checkup
	Education
	Building maintenance costs
	Rental of premises
	Communal expenses
	Depreciation of fixed assets and intangible assets
	Taxes to the budget
	Property tax
	other expenses
	Commercial profit
	Other non-operating income
	Other non-operating expenses
	Interest on loans
	Bank services for current transactions
	Profit before tax
	Income tax
	Net income (loss

To plan in the BDDS articles 200.1 “Payment to suppliers” and 218.1 “VAT payable,” we will need a Budget for the movement of inventory (Table 2) and “Calculation of VAT payable” (Table 3).

Table 2. Inventory flow budget (excluding VAT), rub.
Nomenclature	Inventory at the beginning	Purchase of inventory	Sale of inventory (Article 200 BDR)	Closing inventory
Goods with VAT 18%
Goods with VAT 10%

In the Inventory Flow Budget, we plan the purchase of inventory (TK) based on the initial balances, the sales plan and the TK turnover rate for the period. You can keep this table broken down down to the item (if the revenue plan is also broken down by item), but to calculate the forecast balance, we just need to divide the entire item into groups according to VAT rates. In our example, the rates are 18% and 10%.

K. I. Pankova, head of PEO Holding Company "Domocenter"

The material is published partially. You can read it in full in the magazine

Budgeting is a universal tool in business process management. With the help of these tools, it becomes possible not only to optimize the use of the organization’s resources, but also to assess the economic activity of an enterprise that is only at the planning stage.

The key element here is the organization of an income and expenditure budget (abbreviated as BDR). On its basis, a forecast is made of the results of activities for the entire period for which the budget is formed.

If you are interested in automation of budgeting, implementation of treasury or accounting according to IFRS, check out our.

Before answering the question about the formation of financial and financial management, it is necessary to understand: the budget of income and expenses is the final part of the formation of the financial budget of the organization. The BDR is based on many indicators, including data on a set of budgets at all levels.

It is accepted that before drawing up the BDR, it is necessary to create a production budget, a budget related to sales, a budget that includes all production costs, a management budget, commerce, etc.

On a note.Thus, the budget of income and expenses is a certain feature that summarizes all these indicators.

The result of education B&R will calculate net profit at the end of the budget period.

Components of the BDR

The components of the budget of income and expenses are usually divided into two parts:

• profitable;
• consumable

Let's look at each in more detail.

Profitable

It includes the following:

• profit from the sale of main products, the outflow of which is planned in the budget phase;
• profit from other types of sales of any products;
• profit received from non-operating income. This also includes the amount of money received from loans that were provided to other organizations. Profits can also come from changes in currency exchange rates.

Expendable

This part consists of indicators:

• production costs. This data is taken from the costs associated with the production budget;
• expenses related to business and administration;
• expenses for other needs. This includes interest payments on the loan taken, etc.

On a note.If necessary, related to accounting needs, each individual line of income and expense can be detailed and new elements can be added.

OBD report form

The report model can be different and for the most part depends specifically on the specifics of the enterprise where it is compiled. But there is a general principle that must be observed - a real reflection of the reliable order and meaning of the calculation, which is planned as a result for the intended budget period.

The most popular type of report, which is also the simplest, is using Form 2 of the profit and loss report. Everything is drawn up according to the standard, but the basis is all the regulatory indicators of the organization.

On a note.The report, in which the budget of each level of the company will be formed, allows you to see almost all areas that negatively affect profits.

That is why a correctly drawn up budget of income and expenses, which will take into account all possible aspects of the organization’s activities, not only helps managers make the right decisions in a timely manner, but also changes some of the nuances in the company’s activities. For example, correct the sales plan to a more current one or activate additional resource reserves in a timely manner .

This is what form 2 looks like as an example:

Differences between BDR and BDDS

(cash flow budget), like the cash flow budget, is a financial budget. Moreover, they are also the most popular. Many people think that these concepts are similar, but this is far from the case, and you can notice a decent number of differences that can confirm this.

We know what BDR is. Now you need to find out what exactly a cash flow budget is.

BDDS is a document reflecting the cash flows that exist throughout the organization. That is, only those transactions that are expressed in monetary terms are included here.

Comparison

BDR differs from BDDS not only in the purposes for which they are formed, but also in the indicators that are included in their reporting.

• BDR - is developed to plan the profit that the organization is able to receive during the budget period. This includes cost and revenue data. In terms of the form of the report, the document is very similar to an accounting statement of profit and loss.
• BDDS performs a different function - a budget of this type is formed to distribute cash flow. They can be both incoming and outgoing. The document shows all the activities of the organization that were carried out in cash. Thus, all movements of company funds across various accounts are tracked. The document is similar to a cash flow statement.

It is in these points that the fundamental difference between BDR and BDDS lies.

If you create a report in Excel, it will look exactly like in the example. There is nothing complicated in compiling a BDR, if you look at it, of course.

Moreover, in the 1C system, each of the numbers can be deciphered right down to the primary document.

As you can see in the picture, many factors are taken into account. Such voluminous tables are typical mainly for large enterprises. If you have a small organization, then the number of columns may be reduced several times.

In conditions of growing competition and an unstable economic situation, an increasing number of companies are coming to the need to introduce budgeting. Budgeting in an enterprise is the process of planning, controlling and executing budgets in the process of financial management. In this article, we will try to figure out how to draw up an enterprise budget using an example.

Creating a budgeting system in a company or enterprise usually consists of several stages. At the first stage, the company needs to decide on goals, budgeting methodology, determine the financial structure (structure of financial responsibility centers - FRC), develop a budget model (composition, structure, types of budgets), approve the regulations and rules of the budget process. At the second stage, you can directly begin planning the enterprise budget. It is convenient to automate the preparation of enterprise budgets using a special software product.

The regulation on budgeting at an enterprise may contain the following sections:

• Strategic goals and objectives of the enterprise;
• Budget model;
• Financial structure of the company, etc.

Based on the Budgeting Regulations in the company, it is necessary to develop an Enterprise Budgeting Regulation, which may contain the following sections:

• The procedure for forming functional and master budgets, the structure of subordination;
• Assigning responsibilities and deadlines for submitting budgets and reporting;
• The procedure for approval and amendments;
• Budget control and analysis, etc.

There are several ways to implement a ready-made budget model. The most common and relatively cheap ways are:

• Budgeting in Excel

Company budget example Excel

Budgeting in Excel involves creating budget forms in Excel and linking these forms using formulas and macros. The forms of budgets, including the budget of income and expenses, the cash flow budget, can be different, with consolidated items or more detailed, divided into long periods (for example, an annual budget by quarter) or into shorter periods (for example, a monthly budget by week ) – depending on the needs of financial management in the company.

Below is a Budget of Income and Expenses (an example of preparation in Excel) and an example of a Cash Flow Budget.

Figure 1. Budget of income and expenses of an enterprise Excel sample.

Figure 2. Cash flow budget example in Excel.

Drawing up BDR and BDDS example in Excel

The process of compiling the BDR and BDDS, using an example in Excel, may look like this. Let's build budgeting in a company or enterprise using the example of a manufacturing company in Excel (details in the files below):

Figure 3. BDDS example in Excel (Cash flow budget example in Excel).

Figure 4. Budget budget example in Excel (Budget of income and expenses example of preparation in Excel).

This example is simplified as much as possible. But even from it it is clear that budgeting in Excel is a rather labor-intensive process, since it is necessary to collect all functional budgets and write formulas and macros to correctly display the final results. If you take a real enterprise, or even more so a holding structure, you can imagine how complicated the process of budgeting in Excel becomes.

An example of implementing Excel-based budgeting has many disadvantages: single-user mode, lack of ability to coordinate functional budgets, no differentiation of access to information, complexity of consolidation, etc. Thus, budgeting in Excel is not the optimal choice for a company.

Budgeting in programs on the 1C platform

Automation of budgeting and management accounting based on 1C, for example, in the WA: Financier system, makes the budgeting process in an enterprise more efficient compared to budgeting in Excel.

The budgeting subsystem “WA: Financier” includes the ability to create and control operational and master budgets.

The solution implements mechanisms with which users can independently configure the structure of budgets, their relationships, methods for obtaining actual data and data for calculations. The implemented mechanism for interaction with external accounting systems makes it possible to use external data both for calculating planned indicators or generating reports, and for reflecting actual data on budgeting registers.

This system allows you to effectively build the budgeting business process at all its stages:

• development of a budget model;
• coordination of budgets and their adjustments;
• reflection of actual data on budgeting items;
• control over budget execution;
• plan-fact analysis of indicators using developed reporting tools;
• formation of business management decisions.

Figure 5. Interface “WA: Financier: Budgeting”. Budgeting section.

WA: Financier “Budgeting” includes the following business processes:

• Modeling – development of a budget model;
• The main budget process is the registration of planned indicators by departments. Approval of budgets. Adjustment of plans and coordination of adjustments;
• Subsystem for interaction with data sources – setting up the receipt of data from external sources (as a special case, access to system data).
• System reports – a set of analytical reports.

Planned indicators are entered into the system using a flexible, customizable “Budget” document. The budget input form (the income and expense budget form, as well as the cash flow budget form) is as close as possible to the format in Excel, which ensures a comfortable transition for the user to work with the system.

Some budget items that depend on another budget item (for example, cash receipts from customers depend on the income item “Revenue”) can be planned using the dependent turnover mechanism, which is presented in the system in the form of documents “Registration of turnover dependencies by item.”

If necessary, it is possible to adjust the approved budget using special documents “Budget Adjustment” and track the changes made in reports with the view “Output budget adjustments separately”. It is possible to configure budget distribution and keep records of budget requests.

Using special documents “Accounting for actual data on budgets”, facts are obtained from external accounting systems, for example, 1C Accounting.

Various reports allow you to analyze planned and actual data, thus managing budgeting in the enterprise.

Thus, the introduction of budgeting in a company in programs on the 1C platform is the most optimal in terms of time, money and efficiency of further work.