Budget organization excel sample. How to build a "light" cash flow budget
Ural Lotto has developed a model in Excel, with which the financial service will be able to quickly draw up an Income and Expense Budget (BDR) and a Cash Flow Budget (BDS). An example in Excel can be downloaded from the link at the end of the article.
To develop a budgeting system, simplify the planning process and minimize the risk of errors in the financial plans of the company, you need a unified model of BDR in Excel, in which only one table is reserved for data entry, and all the others are generated automatically.
Imagine that the CFO, 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, in what form it will be necessary to receive information on actual income and expenses from the accounting program. This is exactly what was argued at Ural Lotto LLC.
In the first months they were limited to the formation of separate budgets - sales, purchases, units. After some time, it was possible to develop (the sheets “detailed PL” and “detailed CF”, respectively) were created using the Excel tool “Pivot Tables”. Using the built-in filters, you can select articles of interest, sub-articles of the first and second levels, group income (income) and expenses (payments) separately. To reflect the changes made to the worksheet in them, you 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 unit. To get it, just select the items “BDR” and “To both budgets” in the window “Budget Name”. If you need a plan for a specific unit, you will need to enter its name in the "CFD" window.
The option “To both budgets” is intended in case the numbers are the same for both budgets, that is, for example, when 10,000 rubles in January for any item goes to both the BDR and the BDS. This is done so as not to drive the same numbers twice.
In Excel, a detailed BDDS is created in the same way: on the "detailed CF" sheet, select "BDDS" and "To Both Budgets", and then update the data. There is also filtering by the Central Federal District.
When an indirect cash flow budget planning method is better than direct
This article will tell you which cash flow budgeting method is right for your company.
Useful documents
BDR and BDSDS example in Excel
An example of the budget of income and expenses and the cash flow budget (BDS) in Excel for the whole company is shown on the sheets “PL Budget” and “CF Budget”. Unlike detailed plans, they are not formed directly from the worksheet, but after the intermediate processing of information on supporting sheets (“Code of stations” and “Code of stations-subst.”).
The first of the auxiliary tables is the “Code of Art.” - It is intended to consolidate budget data on the main items of expenses and income (receipts and payments). It can be configured in the same way as detailed budgets - using the “Budget name” field (see Fig. 1).
Since it is used for both budgets, BDD and BDDS can only be formed in turn, using the 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 it will become more difficult to use the file, because each time you would have to update a larger number of pivot tables. And the file itself will become "heavier."
Figure 1. Auxiliary table
The second auxiliary table (“Code of Articles of Sub-Sub.”) Consolidates the data with details of “Article” and “Sub-article of the first level”. The combined names of the latter are listed in the “For Report” column of the worksheet. The work takes place 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 with data from auxiliary tables using the VLOOKUP formula. For example, the calculation of “Revenues from the sale of the lottery according to“ 6 of 40 ”” for January (cell E10 on the “PL Budget” sheet) will be set as VLOOKUP ($ A10; "Set of subst."! $ A: $ T; 4; 0), where A10 is the cell containing the name of the sub-article (“6 of 40”), “Code of subst.”! $ A: $ T - the initial range in the second auxiliary table, where the row will be searched with by the same name, 4 is the column number with data for January.
Download example of BDR and BDS in Excel
Benefits of Budgeting in Excel
The unified budget in Excel has greatly 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 allowed to abandon the creation of separate budgets for sales, purchases, taxes, payroll, etc. The Ural Lotto company is relatively small: one central office (55 employees), no branches. Therefore, it is much easier to fill out just one worksheet.
Table. The structure of the budget worksheet in Excel (sheet "Work table")
Not a single enterprise can exist without planning and control of payments: every day, the head of the company needs to make a decision on how to distribute funds and prioritize payments. It can help him Cash flow budget(BDDS) - a document that contains all the received applications for payment and information about available cash in the company. The article presents the forms of weekly road safety planning, discusses the mechanisms for forecasting revenue from sales in the wholesale and retail sectors, gives recommendations on creating budget forms that are sent to spending unit managers.
Budget control
Essential component of the control system - control of treasury budget execution, that is, control over the receipt and expenditure of funds that are planned in the BDS. Operational control of the cash flow budget, as a rule, carries out budget controller. Guided by the approved cash limits, he determines the budget line items to finance overplanned expenses. The financial controller evaluates each incoming application for settlements and ascertains whether it does not exceed the limit for the corresponding budget item.
Limit exceeded in the budget period, it is possible only by special order of an authorized official (financial or general director). When it comes to the redistribution of expenses between various budget items, these powers are assigned to financial controllers.
Monthly cash flow plan
Cash flow planning for the current month should begin with a general cash flow budget plan, an example of which is presented in table. 1.
In general, the BDDS consists of the following blocks:
- Plan of cash balances at the beginning of the month.
- The plan for the receipt of funds from the main activity (revenue from sales, bonuses from suppliers, income from sublease of premises, etc.).
- Operating expenses plan, which consists of two parts:
- payment plan for suppliers for the goods;
- payment plan for other operating expenses.
- Flow plan for financial activities: balance between loans receivable and repayment minus interest on loans payable.
- Investment activity flow plan: the difference between the income from the sale of fixed assets and payments for the acquisition and repair of fixed assets.
As a result, we obtain the planned net flow for the period under review and derive a forecast of cash balance at the end of the period.
Table 1. Budget cash flows, RUB |
||||||
Article |
Counterparty |
Debt on the 1st day |
Charges / |
Budget |
Arrears on the 31st |
|
Balance at the beginning of period |
||||||
In the current account |
||||||
Sales revenue |
||||||
Retail sales |
||||||
Wholesale |
||||||
Other supply |
||||||
Operating expenses |
||||||
Payment to suppliers |
||||||
Payment to suppliers |
LLC Alpha |
|||||
Payment to suppliers |
Omega LLC |
|||||
Operating costs |
||||||
Payroll |
||||||
Okladny part |
Employees |
|||||
Employees |
||||||
Staff costs |
||||||
Taxi LLC |
||||||
LLC "Express" |
||||||
Medical checkup |
Clinic № 1 |
|||||
Building Maintenance Costs |
||||||
Rental of premises |
LLC Terem |
|||||
Rental of premises |
LLC Teremok |
|||||
Communal expenses |
Gorvodokanal |
|||||
Communal expenses |
HeatElectroStation |
|||||
Private security company "Dobrynya" |
||||||
Taxes to the budget |
||||||
VAT payable |
||||||
Income tax |
||||||
Property tax |
||||||
Payroll taxes |
||||||
Total operating cash flow |
||||||
Flow from financial activities |
||||||
Attracting loans |
||||||
Loan repayment |
||||||
Payment of interest on loans |
||||||
Flow from investing activities |
||||||
OS revenue |
||||||
OS purchase |
||||||
OS repair |
IP Ivanov P.A. |
|||||
Net flow from activities |
||||||
Balance at the end of the period |
If as a result of planning at the end of the period negative cash balances, the budget is adjusted by reducing the payment plan. Therefore, to understand the situation, it is better to immediately add to the BDDS information on current debt to suppliers, planned costs for the coming month and forecast debt at the end of the month, taking into account the budgeted amounts of payments.
In our example, the net monthly flow is forecasted negative (–47.7 thousand rubles), but due to the initial balances of 65 thousand rubles. we able to fulfill the stated budget for a given month. At the same time, we are increasing receivables from our customers from 185 thousand rubles. up to 290 thousand rubles and reduce accounts payable to suppliers of goods from 450 thousand rubles. up to 300 thousand rubles. On the whole, the picture is optimistic for the month.
However, it is worth paying attention that this month a loan of 500 thousand rubles is planned: we are ending the loan term at Bank No. 1, we expect to get a loan for the same amount at Bank No. 2. And if we get a loan at Bank No. 2, we we can a little later than the deadline for repayment at Bank No. 1, then within a month we need to accumulate in the accounts 500 thousand. rub. (approximately half of our monthly revenue). That is, for almost half a month we will not be able to spend large amounts on operating expenses: all payments on them will begin only after receiving a loan from Bank No. 2.
Of course, there are mandatory payments that cannot be postponed for the second half of the month (payment of rent, utility bills, payment of wages according to the schedule). Therefore to us need a daily or weekly cash flow plan, which in the future we must strictly adhere to, so as not to spoil our credit history at Bank No. 1.
We will draw up a weekly cash flow plan for the next month, where we plan the receipt of revenue and mandatory expenses, after which we will display the amounts that we can allocate for other payments.
Weekly revenue plan
A plan for revenue from retail and wholesale is formed according to different principles. Income from wholesale customers is easy to predict through a deferred payment. To do this, use the staff report " Gross profit", Which is located in the" Sales "block of the" Reports "tab on the Excel toolbar (Fig. 1).
Set up the Gross Profit report for our requirements:
- Go to the report settings, click the checkmark " Advanced setup».
- On the tab “ Are common»:
- we set the sales period for which we expect the receipt of funds from customers (usually it is equal to the maximum deferment provided to our customers);
- in the block " Parameters"Click the checkboxes" Display general results "and" Display detailed records ";
- in the block " Indicators"We leave only" the cost of sale, rubles. " and “with VAT”, uncheck the remaining indicators (Fig. 2).
- On the tab “ Groupings»Remove all groups that are provided by default report (Fig. 3).
- On the tab “ Selections»Set the selection: we are only interested in sales of the wholesale division (Fig. 4).
- On the tab “ Additional fields”Displays the fields“ Buyer ”and“ By dates ”, for all fields in the column“ Placement ”we set the type to“ In separate columns ”, in the column“ Position ”-“ Instead of grouping ”(Fig. 5).
- Click on the button " To form"And we get the report, which is presented in table. 2.
Table 2. Gross profit report based on the presented settings |
||
Buyer |
By days |
Cost of sale, rub. |
LLC "Horizon" |
||
LLC Domovoi |
||
IP Borisov A. G. |
||
IP Osintsev A.N. |
||
IP Osipov A. Yu. |
||
IP Pinyuga I.G. |
||
IP Poluektov D.A. |
||
IP Lovtsova N.V. |
||
IP Khomenko A.V. |
We copy the received report into Excel and add the data we need: add a deferral of payments and calculate the payment term as the sum of two columns: Sale date + Deferral of payment (Table 3).
Table 3. Calculation of the payment date from the date of sale and the granted deferred payment |
||||
Buyer |
Date of sale |
Cost of sale, rub. |
Deferred payment, days |
payment date |
LLC "Horizon" |
||||
LLC Domovoi |
||||
IP Borisov A. G. |
||||
IP Osintsev A.N. |
||||
IP Osipov A. Yu. |
||||
IP Pinyuga I.G. |
||||
IP Poluektov D.A. |
||||
IP Lovtsova N.V. |
||||
IP Khomenko A.V. |
Now we will group the payment dates by week using the pivot table:
- We select the table. 3 together with the header and tab " Insert"Click on the icon" Summary table”(Fig. 6 (a)).
- In the dialog that opens, specify where you want to place the pivot table: on a new sheet or on an existing one (you must specify the cell into which you want to insert the pivot table). To create a new pivot table, it is better to first place it on a new sheet, bring it to a form convenient for us, and then transfer it to the sheet where we will work with it in the future (Fig. 6 (b)).
In the appeared window “ PivotTable Field List»Set its form (Fig. 7):
- in the “Line Names” block by dragging the field “Payment Date”;
- in the “Values” block by dragging the “Cost of sale, rubles.”
- We get the report presented in table. 4.
Table 4. The initial view of the pivot table |
|
payment date |
Payment, rub. |
The overall result |
- It can be seen that the table shows the dates of payments for the previous month. We remove them using the pivot table filter. We get up to any cell with a date and call the context menu with the right button, select "Filter"\u003e "Filter by date" in it, set the filter "After"\u003e "01.07.2016" (Fig. 8).
- Now the table contains only sales with a due date in July. Call the context menu again and select " Group". In the dialog box that appears, set the range: from 04/04/2016 to 07/31/2016 with the step "Days", the number of days is 7 (Fig. 9).
- We got weekly wholesale cash flow forecast (tab. 5).
Table 5. The final view of the pivot table |
|
payment date |
Payment, rub. |
04.07.2016–10.07.2016 |
|
11.07.2016–17.07.2016 |
|
18.07.2016–24.07.2016 |
|
25.07.2016–31.07.2016 |
|
The overall result |
Now do retail cash flow forecast. There are two important points to consider when planning cash flow:
- retail sales have a pronounced seasonality on the days of the week: buyers often visit stores on weekends (they account for the peak of sales);
- we can use the proceeds from retail sales for payments on the current account only after it has been collected at the bank, which is carried out on business days with a delay of one to two days. That is, the sales proceeds on Monday go to the current account on Tuesday-Wednesday (depending on the collection conditions), the proceeds on Friday-Sunday will be credited to the current account on Monday or Tuesday. Thus, we will be able to use the proceeds for July 29–31 only in August. But on July 1, we will receive the collection of proceeds for June 30.
Make up retail sales day planon the basis of which we form cash collection plan. You can split the monthly plan by the days of the week in the proportion of the last month or the same month of the last year, which is more desirable, since in this case we can also take into account the monthly seasonality of sales.
When using data for the last year, you need to do a comparison not by dates, but by days of the week. So, 01.07.2016 falls on Friday, in 2015 the first Friday of July was July 3. Therefore, to derive the proportions of seasonality, we need to take sales from July 3 to 08/02/2015. That is, to get the date of last year, similar to the day of the week of this year, you need to take 364 days (exactly 52 weeks).
Table 6 presents a breakdown of the sales plan by day and collection plan by day of the week and grouped by week. As a result, we see the following: since the last days of July fall on the weekend, the cash flow plan differs from the sales plan by 75 thousand. rub. Other income in our budget is sublease income, which must be paid before the 10th day of each month according to the lease. Therefore, we put these receipts in the second week.
Table 6. The plan for revenue from retail sales to the current account, rubles.
Day of the week |
Last year date |
Last year's revenue |
Current year date |
Current year revenue |
Collection |
Total per week |
---|---|---|---|---|---|---|
Sunday |
||||||
Monday |
||||||
Sunday |
||||||
Monday |
||||||
Sunday |
||||||
Monday |
||||||
Sunday |
||||||
Monday |
||||||
Sunday |
||||||
Total |
1 000 000 |
Payment schedule
We have formed a cash flow plan for the weeks. Now put it in the BDS obligatory payments (in table. 7 are highlighted in color):
- payment of wages: the last month’s salary must be paid before the 10th, the premium is paid until the 15th, the advance for the current month is paid until the 25th. We set 50% of the salary payable for the second week, 100% of the premium for the fourth and 50% of the salary for the last week of the month;
- rent payment: according to the agreements, the deadline for rent payment for the current month is the 10th day. We pay for the second week;
- communal payments must be completed before the 25th, put them on payment on the 25th, that is, for the last week;
- security according to the agreement concluded with the private security company, it is paid until the 20th day, we put on payment for the fourth week;
- payroll taxes you need to pay before the 15th day, which means that we will need money for them in the third week;
- personal income tax paid at the same time as the payment of wages, so we distribute it weekly in the same proportion as the payment of salaries, bonuses;
- for other taxes payment term from the 25th to the 31st day (last week of July);
- repayment of loans and interest payments - until the 22nd day ( attraction of loans - after the 25th).
All other payments in the coming month are immediately attributed to the last week (when we can replenish current assets with a new loan, which is scheduled for July 25).
As a result, we see that we can spend only on the payment of goods in the first three weeks 120 thousand. rub., the remaining amount of debt can be closed to suppliers in the last two weeks of July.
If the opinion of suppliers is important to us, we need to notify them in advance of the situation. You can provide them with a clear payment schedule for this month so that they can also plan their financial capabilities for the coming month.
Table 7. Weekly payment planning, rub. |
||||||||
Article |
Counterparty |
Payment term |
Monthly Budget |
|||||
Balance at the beginning of period |
||||||||
In the current account |
||||||||
Sales revenue |
1 105 000 |
|||||||
Retail sales |
||||||||
Wholesale |
||||||||
Other supply |
Until the 10th |
|||||||
Operating expenses |
1 117 700 |
|||||||
Payment to suppliers |
||||||||
Payment to suppliers |
LLC Alpha |
|||||||
Payment to suppliers |
Omega LLC |
|||||||
Operating costs |
||||||||
Payroll |
||||||||
Okladny part |
Employees |
Salary - until the 10th day; advance payment - until the 25th day |
||||||
Employees |
Until the 15th |
|||||||
Staff costs |
||||||||
Taxi LLC |
||||||||
LLC "Express" |
||||||||
Medical checkup |
Clinic № 1 |
|||||||
Building Maintenance Costs |
||||||||
Rental of premises |
LLC Terem |
Until the 10th |
||||||
Rental of premises |
LLC Teremok |
Until the 10th |
||||||
Communal expenses |
Gorvodokanal |
Until the 25th |
||||||
Communal expenses |
HeatElectroStation |
Until the 25th |
||||||
Private security company "Dobrynya" |
Until the 20th |
|||||||
Taxes to the budget |
||||||||
VAT payable |
Until the 25th |
|||||||
Income tax |
Until the 28th |
|||||||
Property tax |
Until the 30th |
|||||||
With salary |
||||||||
Payroll taxes |
Until the 15th |
|||||||
Total operating cash flow |
–12 700 |
–15 000 |
–63 993 |
–225 860 |
||||
Flow from financial activities |
–25 000 |
–250 000 |
–25 000 |
|||||
Attracting loans |
After the 25th |
|||||||
Loan repayment |
Until the 22nd |
|||||||
Payment of interest on loans |
Until the 22nd |
|||||||
Flow from investing activities |
–10 000 |
–10 000 |
||||||
OS revenue |
||||||||
OS purchase |
||||||||
OS repair |
IP Ivanov P.A. |
|||||||
Net flow from activities |
–47 700 |
–15 000 |
–55 879 |
–88 993 |
||||
Balance at the end of the period |
Creating budget forms for the budget controller
Now consider different ways to get a monthly BDSDS plan. If the company is small and there are few contractors, then the economist is able to independently plan upcoming payments for a month. It is enough to collect current debt to suppliers and contractors on accounts 60, 76 and analyze the monthly charges for all counterparties.
In our example, there are only two suppliers of goods and nine contractors and service providers (see Table 7), most of them monthly issue the same invoices (rent, security, utility bills and taxi services). It is clear that forecasting payments on them is quite easy. Complexity can only arise in tax planning. So, you need to seek help from the chief accountant, since he is responsible for timely payment of taxes.
For large enterprises, it is difficult for one economist to plan the budget for all expense items correctly, therefore in such companies usually all expense items are assigned to responsible employees, the so-called budget managers. They are the ones who plan payments, and then submit applications for payment of bills to the financial service. To make it easier for you to collect a common budget based on the budgets submitted by the managers, it’s better to develop single budget formatwhich they must fill.
Table 8 presents the budget form of the manager of the block of costs for the maintenance of the building, from which it is easy to transfer data to the general form of BDS. If there are many articles in the BDDS, it is better to enter the article code. Then using functions SUMMES () You can automatically transfer data from the controller’s budgets to the general budget.
Table 8. The budget form of the manager of cost items |
||||||
Article / Counterparty |
Deadline for payment (if any) |
Debt on the 1st day, rub. |
Costs for the current month, rub. |
Budget for payment, rub. |
Debt on the 31st day, rubles. |
|
Rental of premises |
||||||
LLC Terem |
Until the 10th |
|||||
LLC Teremok |
Until the 10th |
|||||
Communal expenses |
||||||
Gorvodokanal |
Until the 25th |
|||||
HeatElectroStation |
Until the 25th |
|||||
Security |
||||||
Private security company "Dobrynya" |
Until the 20th |
|||||
There are a few things to consider regarding form design:
- the controller should not change the number and sequence of columns (otherwise, formulas configured to its budget will not work correctly). If he wants to make further clarifications on the article, let him do it to the right of the approved form;
- the controller can add lines to the report if he has increased the number of counterparties for any cost item. At the same time, adding new rows should not lead to the need to change the resulting rows;
- all cells with calculation formulas must be protected from editing (to avoid accidentally overwriting or changing the formula to an incorrect one);
- the total values \u200b\u200bfor the manager’s budget must be verified with the data that fell into the consolidated road safety system in order to exclude the possibility of information distortion.
Let's see how to implement these requirements using Excel features.
- Cell Protection
To protect cells from editing, click the " Protect sheet"On the tab" Peer review". Note that by default, Excel protects all cells in the sheet from changes, and we need to ensure that stewards do not spoil the resulting rows. Therefore, protection should be removed from the cells in which managers are allowed to make changes. You can remove protection from a cell in the " Cell format"On the tab" Protection"(Fig. 10).
The controller is allowed to change the number of lines (add and delete), so when setting sheet protection, do not forget to check the "insert lines" and "delete lines" checkboxes (Fig. 11). Be sure to set a password to remove protection. Otherwise, employees who know how to work with sheet protection will easily circumvent this limitation.
- Consideration in the SUM () formula of new lines that the controller can add.
The main rule when developing a budget of arbitrary length: always use sUM function (). Applying this rule does not always guarantee that all data will fall into the resulting rows. Figure 12 shows an example when the budget controller added a new line to the end of the “Lease of premises” block (quite logical from his point of view), but it did not appear in the final formula.
Way out of this situation: between all cost blocks, insert a line and include it in the summation formula (for recognition, be sure to highlight the line with some color). For the user, this line will become a kind of separator between cost groups, and he will always add new lines just before it (Fig. 13).
The next stage of financial management is the collection of applications for payment and the maintenance of a daily payment calendar.
Instead of a conclusion
Proper development of budgets for the controller of cost items will partially automate the collection of planned road safety data for a month, which will speed up the process of preparing it and reduce the influence of the human factor when consolidating data from different sources.
If the planned budget for the month is reduced to a surplus, this does not mean that there will be no budget deficit in the middle of the month (the situation is most likely in the month of repayment of a large loan amount). Therefore, it is extremely important to make not only a monthly, but also a weekly forecast of cash flows, in order to know in advance about possible failures in the budget and correctly plan payments in order to avoid such problems.
When planning, most often they use two budget documents - the Budget for income and expenses (BDR) and the Budget for cash flows (BDS). However, do not forget about the forecast balance. Almost all the data for it is presented in the BDR and BDS. Consider how to form a forecast balance based on the articles of these budgets, transform indicators from the BDR and BDS into the balance sheet.
FORMING FORECAST BALANCE
To make a forecast balance, we need five budget documents:
- Balance at the beginning of the planning period.
- BDR plan.
- Inventory procurement plan.
- Calculation of VAT payable.
- Road Safety Plan.
The relationship between these documents will be shown schematically (see figure).
The primary document in planning is Budget income and expenses (table. 1), which presents plans for revenue, cost of goods sold and operating costs 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 BDS, we 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 Income and Expense Budget and balance sheet already contain VAT and the process of transferring data from the budget to the budget is simplified.
How not to lose VAT when transforming data from the BDR to the Cash Flow Budget, and then from the BDS to the Balance, we will show further on the example of a company with a classical tax system.
Table 1. The planned budget of income and expenses |
||||
Article |
Budget |
VAT rate, % |
Amount with 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 |
||||
Okladny part |
||||
Payroll taxes |
||||
Staff costs |
||||
Medical checkup |
||||
Training |
||||
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 |
||||
Loan interest |
||||
Banking services for current operations |
||||
Profit before tax |
||||
Income tax |
||||
Net income (loss |
For planning in BDDS articles 200.1 “Payment to suppliers” and 218.1 “VAT payable”, we need a Budget for the movement of inventory (table 2) and “Calculation of VAT payable” (table 3).
Table 2. The budget for the movement of inventory (excluding VAT), rub. |
||||
Nomenclature |
Inventory at the beginning |
Inventory Purchasing |
Sale of inventories (article 200 of the BDR) |
Inventory at the end |
Goods with VAT 18% |
||||
Goods with VAT 10% |
||||
In the Inventory Movement Budget, we plan to purchase inventory (TK) based on the opening balances, sales plan, and TK turnover ratio for the period. You can maintain this table up to the item (if the revenue plan is also divided by item), but to calculate the forecast balance, it is enough for us to divide the entire item into groups at VAT rates. In our example, the rates are 18% and 10%.
K. I. Pankova, Head of PEO HC "Domocenter"
The material is partially published. You can read it in full in the magazine.
The budget for the next year is formed taking into account the functioning of the enterprise: sales, purchase, production, storage, accounting, etc. Budget planning is a long and complicated process, because it covers most of the functioning environment of organizations.
For a good example, consider a distribution company and draw up a simple enterprise budget for it with an example in Excel (an example of a budget can be downloaded from the link under the article). In the budget, you can plan the cost of bonus discounts for customers. It allows you to simulate various loyalty programs while controlling costs.
Data for budgeting income and expenses
Our company serves about 80 customers. The assortment of goods is about 120 items in the price list. She makes a margin on goods 15% of their cost and thus sets the selling price. Such a low margin is economically justified by intense competition and is justified by large turnover (as with many other distribution companies).
A bonus reward system is offered for customers. The percentage of discounts on purchases for large customers and resellers.
The conditions and size of the interest rate of the bonus system is determined by two parameters:
- Quantitative boundary. The quantity of a particular product purchased that gives the customer the opportunity to receive a certain discount.
- Percentage discount. The size of the discount is a percentage, which is calculated from the amount that the client acquired when overcoming the quantitative border (bar). The size of the discount depends on the size of the quantitative border. The more goods purchased, the greater the discount.
In the annual budget bonuses relate to the section "sales planning", so they affect an important indicator of the company - margin (profit indicator as a percentage of total income). Therefore, an important task is the ability to set several options for bonuses with different boundaries at the levels of implementation and the corresponding% bonuses. Margin needs to be kept within certain limits (for example, at least 7% or 8%, the same is the profit of the company). And customers will be able to choose several options for bonus discounts.
Our budget model with bonuses will be quite simple, but effective. But first, we’ll draw up a report on the movement of funds for a particular client to determine whether it is possible to give him discounts. Pay attention to formulas that refer to another sheet before calculating the percentage discount in Excel.
Budgeting an enterprise in Excel taking into account loyalty
The budget project in Excel consists of two sheets:
- Sales - contains the history of the movement of funds over the past year for a particular client.
- Results - it contains the conditions for accruing bonuses and a simple account of the results of the distributor's activities, which determines the forecast of customer attractiveness indicators for the company.
Cash flow for customers
The structure of the table “Sales for 2015 by customer:” on the “sales” sheet:
![](https://i1.wp.com/exceltable.com/otchety/images/otchety2-2.png)
Enterprise Budget Model
On the second sheet, we set the boundaries for achieving bonuses, the corresponding percentage of discounts.
The following table is the basic form of the budget of income and expenses in Excel with the general financial indicators of the company for the annual period.
The structure of the table “Bonus system conditions” on the “results” sheet:
![](https://i0.wp.com/exceltable.com/otchety/images/otchety2-5.png)
- Bonus bar border 1. A place for setting the level of the boundary bar by quantity.
- Bonus% 1. A place to set discounts when crossing the first border. How is the discount calculated for the first border? It is clearly visible on the "sale" sheet. Using the function \u003d IF (Quantity\u003e border 1 of the bonus bar [quantity]; Sales * percent 1 bonus discount; 0).
- Bonus bar border 2. A higher border compared to the previous border, which makes it possible to get a big discount.
- Bonus% 2 is a discount for the second border. It is calculated using the function \u003d IF (Quantity\u003e border 2 of the bonus bar [quantity]; Sales * percent 2 bonus discounts; 0).
The structure of the table "General report on the turnover of the company" on the sheet "results":
![](https://i0.wp.com/exceltable.com/otchety/images/otchety2-6.png)
Ready-made enterprise budget template in Excel
And so we have a ready-made enterprise budget model in Excel, which is dynamic. If the boundary bar of bonuses is at the level of 200, and the bonus discount is 3%. This means that last year the client purchased goods in the amount of 200pcs. And at the end of the year, he will receive a 3% discount on this bonus. And if a customer has purchased 400pcs of a certain product, it means that he has overcome the second boundary bar of bonuses and is already receiving a 6% discount.
Under such conditions, the “Margin 2” indicator will change, that is, the distributor’s net profit!
The task of the head of the distribution company to choose the most optimal levels of boundary bars to provide customers with discounts. You need to choose so that the indicator "Margin 2" was at least in the aisles of 7% -8%.
Download the enterprise budget bonus (sample in Excel).In order not to search for the best solution by typing, and not to make mistakes, we recommend that you read the following article. It describes how to make a simple and effective tool in Excel: Excel data table and number matrix. Using the “data table”, you can automatically visualize the most optimal conditions for the client and distributor.
We "re sorry, the browser you are using is limiting the functionality of this website. To get the best experience, we recommend that you switch to an up-to-date fully supported web browser. If you feel that you have received this message in error, please contact us.
Whether you are managing your company's finances or your home finances, budgeting is an important first step. Having a budget is necessary to keep track of ongoing expenses, determine in which areas you can reduce costs, and make decisions about what goals to spend money on.
While creating a budget can seem like a complicated process, using a budget template can help make the process a little less intimidating. How to choose a template suitable for your purposes from a huge number of existing ones? We looked at the best templates in Excel and talked about them in this article so that you can choose the ones that are best for you. In addition, we offer a detailed description of how to use the personal monthly budget template in Excel and in Smartsheet.
How to choose the right budget template
A budget template can be very complex or very simple: it all depends on your goals. This can be creating a budget for a project at work, tracking home expenses or planning an upcoming important event, such as a wedding, or all of the above together. In any case, it’s important to choose the right template for managing your budget. We offer you a description of the various types of templates and tell you in which cases they should be used.
Training Club Budget
Typically, training clubs conduct fundraising events or receive sponsorship to meet their annual goals. Having a training club budget is important for managing the club’s operations and strategies, as well as creating a goal archive for each year. This training club budget template will help you quickly track and manage club income and expenses, as well as compare your overall budget and current balance.
Download training club budget
Business budget
Regardless of the size of your business, having a business budget is key to your company's growth. A business budget will help you make strategic decisions about potential growth areas, cost savings, and the overall health of your company. This business budget template is great for service providers and companies producing and selling goods.
Download business budget template
Student budget VNU
Any novice student needs to create his own student budget as early as possible. Although the amount of money needed to study at VNU can be impressive, the template of a student at VNU will help determine how much money is needed for current expenses, how much to save, and how to put it into practice. In this student template, you will find a table for income and expenses data on a quarterly basis, and another table for a preliminary assessment of monthly school expenses.
Download student budget template of VNU
Department Budget
The department’s budget is useful for determining the department’s potential expenses for the coming fiscal year. This department budget template will help you compare percentage changes in budget indicators for each year.
Download department budget template
Retirement Budget Template
When you plan to retire, you must create an action plan as early as possible. Do you know how much your daily expenses will be? What sources of income will you have? Using the early retirement template, you can better determine how much you definitely need to save for retirement. This template includes two pages: one page for evaluating your retirement income and expenses per week, two weeks, a month, a quarter and a year, and the second one for viewing the preliminary budget taking into account inflation.
Download retirement budget template
Monthly Family Budget Template
Families who are serious about their financial future will appreciate this family budget planning template. Whether money is being saved to buy a car or a home for the whole family or to pay for higher education for children, the family budget template will help create your family annual budget and help achieve your goals. This family budget template includes all articles of family income and expenses by month, as well as a summary of all current data for each category.
Download the family budget template
Detailed personal budget template for the month
The detailed personal budget template for a month is similar to a zero-based budget template, since it is based on the same budget management method - each ruble of income is used to cover expenses, resulting in a zero balance. However, the difference between a detailed personal budget is that it can be used to maintain a budget for every two weeks, rather than for a month. Thus, the possibility of going beyond the budget for the billing period and beginning of the next period with a negative balance is reduced. In the detailed personal budget template, you can specify all your income and expenses on the first page and track transactions on the following pages.
Holiday Shopping Budget
Holidays are not the easiest time. To make sure that you have not forgotten to buy a gift for your beloved aunt, fill in the holiday shopping budget in advance. Use this holiday shopping budget template to make a list of the gifts you want to purchase, mark who you want to give them, how much they cost, how they will be packaged, and whether you intend to deliver them in person or send by mail. In this template you will find a convenient dashboard that provides the ability to quickly view the remaining cash in your holiday shopping budget.
Download holiday shopping budget template
Home Repair Budget
If you plan to build a new house or just update something in an already built one, the budget for the construction work in the house will come in handy for planning the necessary expenses, the desired improvements and emergency repairs. Make sure your construction or repair work is on time and within budget, using the Home Repair Template. Track materials and work for each individual element, compare them with the budget, and also be aware of the current budget balance.
Download the home construction budget template
Home spending budget
This template is similar to the family budget planning template - it can also be used to track income and expenses for your entire family. The difference lies in the fact that in this template a separate page is allocated for each month, while in the family budget template one page fits a whole year. This home spending template provides a more detailed view of the data for each month.
Download home spending budget template
Budget management tool
Using the budget management tool template, you can track expenses and manage your budget in one place. The template includes an annual budget, a budget for one month, and a transaction history log. This template is a complete budget management solution. You can record all transactions, as well as track spending for the whole year or for every month.
Download cash management template
Personal budget
A personal budget will help you track and manage your income, expenses and savings so that you can move towards achieving your financial goals. It doesn’t matter whether you are building your first budget from scratch or updating an existing one, using a personal budget template will help you quickly get a visual overview of your finances. In this personal budget template, you can indicate your income, planned savings and expenses on one page, and view the dashboard to get general information about the state of the budget on the second page.
Download personal budget template
Project's budget
A key factor in managing successful projects is creating an accurate project budget and tracking it. This can be a daunting task, as project scope and schedules are constantly changing. Using this template will help you control the budget of your project. You can track materials, work and fixed costs for each project task, as well as monitor the difference between the actual and budgeted amounts.
Download the draft budget template
Simple budget
If you are just creating your very first budget, a simple budget template will come in handy. This template makes it possible to list all expenses and income and view the totals for each category, and the dashboard will help you visually present a part of the income that goes to pay expenses and the remaining free income.
Download a simple budget template
Wedding Budget
Planning your wedding day can take a lot of time, effort and money. Creating a budget before planning your wedding will allow you to set the initial amount of money that you can spend. Using the budget template for the wedding celebration, you can not only determine how much money you need to save, but also identify unplanned expenses. This template contains a means of preliminary estimation of the amount you want to spend for each category. Then, as you plan the event, you will be able to add the amount of actually spent funds to track the divergence of the budget.
Download wedding budget template
Weekly budget
A weekly budget planner will be useful to keep track of your income and expenses for a week or two weeks. This template is based on the family budget template and includes additional columns for each week. With it, you will get a detailed idea of \u200b\u200byour budget.
Download weekly budget template
Zero base budget table
A zero-base budget template is a budget for one month, where the difference between your monthly income and expenses should be zero. The basis of this method of budgeting is the idea that every ruble of your income goes to some section of your budget, and you always know where your money goes. This template includes two sections: one contains a list of all sources of your income, and the other contains all your expenses. Once both sections are filled out, you can see if the difference is really zero and edit the budget accordingly.
Download a zero base budget template
The importance of a personal budget
Having a personal budget is important not only for your financial well-being and peace of mind, but also for achieving your short-term and long-term goals. Managing a budget using a personal budget template will help you stay on track.
To get started with your personal budget, follow these steps:
- Define your goals. Create a list of your short and long term financial goals. Decide which goals are more priority for you, how you plan to achieve them, and also determine the time frame for achieving them. Achieving short-term goals should take no more than a year and include elements such as, for example, paying off credit card debts. Achieving long-term goals can take several years and include goals such as creating savings for your children's higher education or retirement savings.
- Track your spending. To make an accurate assessment of how much money you should devote to cover certain expenses of your personal budget, you will need to know how much you spend on each category. Review your bank statements for the last 3-4 months to find out what are the main items of your expenses. Of course, you can always change the budget amount for each category of spending, but the approach we offer will provide you with basic indicators from which you can push.
- Customize your budget. Use the personal budget template to get started, and then personalize it based on your requirements. In addition, please note that the costs will vary from month to month, so do not forget to periodically check your budget and update it as circumstances change.
Getting started with the personal budget template in Excel
Once you have created a list of your goals and started tracking expenses, create your current budget using the personal budget template.
Open the personal budget template, add information about your income, planned savings and the amount of expenses for each month. This template consists of two pages: dashboard and budget.
On the budget page you will find three sections: income, savings and expenses. The income category includes the following sources of income:
- Wage
- Interest income
- Dividends
- Refunds
- Business
- Pension
- Other
In the next section, you can indicate your planned savings. This section may include short-term or long-term goals that you defined earlier. The section contains the following categories, which you can change:
- Contingency fund
- Transfer to a savings account
- Pension
- Investments
- Education
- Other
The last section of the personal budget page contains expenses. Here you will find various categories and related subcategories. The main categories of expenses include:
- House (household expenses)
- Transport
- Daily expenses
- Entertainment
- Health
- Vacation
As soon as you enter values \u200b\u200bfor each category of income, savings and expenses, you will see that the total amount for each month is automatically calculated and displayed at the bottom of each column. In addition, the totals are calculated at the end of each line and represent the current data for each element of the budget, category and section.
On another page you will find a dashboard for your budget. The dashboard will help you get a visual representation of the state and health of your budget, and it will also be automatically updated when changes are made to the budget table. The dashboard included in the personal budget template consists of 4 parts:
- A brief summary of possible sources of savings. This short summary will help you calculate your potential savings for each month after you reach your current savings goals. The amount of potential savings is calculated by subtracting the total savings and total expenses from the total income.
- The diagram of the ratio of income and expenses. This chart allows you to quickly visualize the difference between your total income and expenses for each month, which will help you assess the health of your budget.
- Pie chart of the relationship between income, expenses and savings. This chart shows how much of your budget is spent on income, expenses, and savings.
Using a personal budget template in Smartsheet
Smartsheet is a robust spreadsheet tool for collaboration and collaboration. A pre-formatted personal budget template simplifies budget creation, budget health checks, and reporting improvements. In this template, you can specify your budget for the month, and then track the actual spending. Using the available formulas, you can work with annual total expenses, annual planned expenses and their difference. This data will be automatically calculated as the table data changes. Smartsheet’s powerful collaboration features let you attach attachments, set reminders, and give your budget access to those interested.
Here's how to use the personal budget template in Smartsheet:
1. Select a personal budget template
- Go to the site website and log in to your account (or use the free 30-day trial version).
- Go to the "Home" tab, click "Create" and select the "View Templates" option.
- Enter the word "Budget" in the "Search for templates" field and click on the icon in the form of a magnifying glass.
- A list of templates is displayed. For our example, we will use the "Family Planning Monthly" template. Click on the blue "Use Template" button in the upper right corner.
- Name your template, select where to save it, and click OK.
2. Enter data for your budget
A pre-formatted template opens containing the content for the sample, as well as ready-made sections, categories and subcategories. In Smartsheet, you can easily add or remove rows, depending on your budget.
Just right-click on a row and select "Paste Above" or "Paste Below" to add a row, or "Delete" to delete a row.
- Update the section and subsection names in the main column according to your data.
* Please note that the "Savings and Savings" section in this template is included in the "Expenses" section. You can transfer this section to a more suitable place for you by selecting the necessary lines, right-clicking on them and selecting "Cut". Then, right-click on the row where you want to insert the selected rows, and click "Insert."
- Enter your income, savings, and expenses for the relevant categories of your budget in the Monthly Budget column. Please note that the hierarchy has already been pre-configured for you, and the formulas will automatically calculate the totals for the categories based on the data specified in the subsections.
- On the left side of each line you can attach files directly to budget items (ideal for attaching bank statements, tax documents, etc.).
- Add important details in the Comments column, for example, account information or links to specific accounts.
3. Update actual monthly budget data
- Enter the actual amount in rubles for each of the budget items during the corresponding month. You can set up receiving reminders by opening the “Alerts” tab at the bottom of the page and selecting “New Reminder”. Additional information .
- You can provide access to your budget to interested parties. This will help not only keep others informed about the state of the budget, but also increase reporting on your part. To grant access, click the Sharing tab at the bottom of the page. Add the email addresses of the users you want to share, add a message and click on the blue "Share this table" button in the lower right corner of the dialog box.
Find the best personal budget template
A budget is your financial basis. It clearly describes your income, expenses and planned savings. A suitable budget template will help you start moving towards your financial goals and complete control over your money. Using a pre-formatted template, you can reuse the same budget basis for the next year, which will greatly simplify the budget process and create a budget journal.
Learn how to easily use your personal budget template. Try the free 30-day version of Smartsheet.