Agriculture is one of the most complex industries in terms of accounting and reporting, according to my mind, primarily for reporting. 

Since the duration of the manufacturing cycle at the enterprise is almost a year (depending on the crops grown), it is possible to form a financial result only once a year (and therefore to draw up a profit & loss statement).
Agricultural enterprise planning begins with determining what and in which fields will be grown (“Areas” sheet). That means that a table is drawing up with a breakdown by crops and an indication of the planting acreage. This table can also be compiled in the context of subdivisions and different types of the same crops cultivars. Since, depending on the variety and place of seeding, the culture technique can be different.

Table № 1 “Crops”

On the separate sheet “General indicators” we put out particular data for the calculation, which will be used on different calculation sheets. There is no point in describing these tables because of their simplicity.

After the crops and planting acreage are determined, one can proceed directly to drawing up an operation flow chart. The operation flow charts are placed on sheets with the name of the culture to which this chart belongs.

Table № 2 “The operation flow chart”

The operation flow chart consists of three tables. The first two small ones are at the top of the sheet.
The first table is a header with general information on the operation flow chart. Line by line:

1. Year of cultivation. The planned activity season is indicated.
2. Field №. Works on which fields are reflected in this chart.
3. Forecrop. Which type of crop was grown in these areas last season.
4. Sowing area, ha. The total sowing area according to this map.
5. Planned yield, c/ha. The planned yield is indicated in centners per hectare.
6. Planned gross output,t. Computational cell. The planned harvest in tons is calculated using the formula: (Line 4 * Line 5) / 10.
7. Prime cost, UAH. Computational cell. It is determined as the sum of totals for the columns in the main table: columns №9 + №11 + №15 + №22 + №24.
8. Revenue, UAH. Computational cell. The planned revenue from the sales of the grown crop is calculated using this chart. According to the formula: Line 6 * Selling price.
   The second table is header, which shows the totals according to the operation flow chart:
9. Profit according to the operation flow chart.
10. Return on investment.
11. The prime cost of 1 tonne of manufactured products.
    Third table. The main table of the operation flow chart. I will describe in more detail the table above for each column:

The entire technological process for growing a particular crop is entered into the above table.
It should be noted that not all table cells in every line have to be filled in. Only the necessary data for business transactions is filled in.
Pay attention to the column №21 “Price.” This column contains the VLOOKUP formula, which selects from the “Price” table the price of the particular product indicated in the column №17. Doubtless, there is an opportunity to set prices manually and the table will calculate everything correctly. However, if there are a lot of operation flow charts, then you can be mistaken indicating the prices of the same product in different charts or forget to change the price of a product in some operation flow chart because of the change of price on the market.
The table for entering prices for agricultural materials is extremely simple:

Table №3 “Prices”

In the columns is entered the following data:

When the operation flow charts are finished for all grown crops, then appears a new challenge:
There is a necessity to consolidate this data into one table.
To do this, you will have to build a primary consolidation table, where all expenses will be collected by crop type. This table has two functions:

1. Based on it, it will be possible to build a consolidation table by type of expenditures.
2. In future exactly in this table it will be possible quickly to find data in which crop a particular product or fertilize is used.
   The table looks quite simple, but large, because on one sheet you collect all the operation flow charts for your company/subdivision. You will have to make efforts, writing formulas, that is because individual blocks of the table will refer to different tables and it will not work if you just copy them from one block to another. Formulas should be changed manually.

Table №4 “Crop costs”

Pay attention that:

1. That is not the entire table “Crop costs” but only a fragment of it, since the table is completely large. However, all of its blocks look the same, where each culture has a particular block.
2. All lines, except for “Salary,” “Outsourced services,” “POL,” “Irrigation,” are formed using the SUMIFS formula (as described below).
3. The lines “Salary,” “Outsourced services,” “POL,” “Irrigation,” are formed using the SUMIFS formula. Since in these lines, one criterion is enough for summing up, namely for the period of incurring costs, the corresponding columns of operation flow charts are used.

Let’s take a look at the content of each column.

Once again, I want to remind you that it is necessary to enter all operation flow charts separately in this table. There will be a separate block for each chart.
In addition to that, I recommend entering a complete list of the products and fertilizers used in each block (for each technological map), regardless of their usage in a specific operation flow.
After you have compiled a set of costs in the context of crops, the next step is to compile a total set of costs. In this table, the data is provided not for crops, but for the enterprise as a whole in the context of products, fertilizers, and types of expenditures.
Sheet “Total set of costs”
In fact, these are two tables. The total set of costs is formed on the basis of the previous table “Crop costs,” fixed costs are also written in this section (it means that costs are not directly related to the technological process).
Let’s take a look at these tables.

Table №5 Total set of costs by operation flow charts

Let’s take a look the economic meaning of each column:

Table №6 “Fixed costs”

Costs that are not connected with the operation flow chart are calculated in this table.

The column data is the same as in the above table, but here columns 1, 9-21 are used.

There are columns 2-4 in the table, but they are not filled in. These columns are needed in the table because it is on the same sheet with the pivot table for technological expenses.

Columns 5-8 are empty because the fixed costs are for crops grown in the season of their origin. Thus, in the planned season it will be taken into account the fixed costs of the planned year, although the costs for operation flow charts appear in the previous season.

After pivoting these two tables, you will get the budget for the planned season. The total sum of costs is reflected in the line “Total costs.”

Pay attention, the monthly costs need to be considered in the formulas for the distribution of costs according to the operation flow chart. The column №2 “Month” is introduced in the chart for this purpose.
Then, after cost budget calculation, we determine the actual need for agricultural materials (plant protection agents, fertilizers, etc.). It is necessary to create a table “Warehouse” to do this.

Table №7 “Warehouse”

Economic content of columns:

Pay attention, in the tables is used conditional formatting: when the quantity of material in the warehouse is greater than the demand for it for the season. The corresponding table cell in column 5 is colored in red.
After the calculation of the actual need for the purchase of agricultural materials, we can plan the cash flow.
Sheet “Cash flow”
The sheet “Cash flow” contains several tables that help to calculate the cash flow for each month.

Table № 8
This table calculates the procurement time of agricultural materials based on the time of demand.

Economic meaning of the above table columns:

In the 7th column, the formula is put (an example of a formula from the first table cell of the column is given): IF((‘Total set of costs’!$U2-CF!$F5)>=SUM(‘Total set of costs’!$F2:’Total set of costs’!F2);0;((SUM(‘Total set of costs’!$F2:’Total set of costs’!F2))-(‘Total set of costs’!$U2-CF!$F5)))

In the columns 8-21 is used the formula (again, example of the formula is from the first table cell in the column): IF(SUM(SUM($G5:G5);’Total set of costs’!$U2-CF!$F5)>=’Total set of costs’!$U2;0;IF((‘Total set of costs’!$U2-CF!$F5)>=SUM(‘Total set of costs’!$F2:’Total set of costs’!G2);0;((SUM(‘Total set of costs’!$F2:’Total set of costs’!G2))-(SUM($G5:G5;’Total set of costs’!$U2-CF!$F5)))))

I understand that the formulas look heavy and it is rather difficult to understand them without an example in the table. I give their examples to understand at least in general what formulas are used in the tables on the CF (cash flow) page.

The above formulas are used in those table lines where the purchase data of agricultural materials is calculated

In the last four lines of the table, where according to the operation flow chart data on the material consumption on wages, POL, products, Irrigation, and outsourced services are calculated, the formula is much simpler:

SUMIF(‘Total set of costs’!$A$2:$A$74;CF!$A73;’Total set of costs’!F$2:F$74)

This formula is taken from the line “Payment” and the column “October 17.”

If you are going to buy agricultural materials not as needed, but, for example, in advance or postpone the payment period for after the crop harvesting, then it will be necessary to transfer the amounts of necessary payments manually right on top of the formulas by entering data in particular table cells and deleting data from other cells. Try to remember to check that the total cost per month (from column 22) is equal to the total cost of purchasing agricultural material (from column 6).

Below is a table of cash flow for fixed costs, where fixed costs are indicated on a monthly basis.

Table №9 “Cash flow for fixed costs”

In other words, that is a complete copy of the fixed costs table from the “Total set of costs” sheet. Note, that fixed costs could vary on the time of occurrence upon payment, but to simplify the calculation, we accept that they arise and are paid in the same period. The resulting defect is not large.
The next line is “Total cash flow.” Here we summarize the results of the above two tables.
Next is a table where the income from the sale of grown crops is planned.

Table № 10 “Cash flow from sales”

The line “Monetary funds need” is shown in green. Data in this line is determined by the formula: from the table cell of the “Total revenue” line subtract the corresponding table cell of the “Total cash flow” line.
Next table is “Cash flow from financing activities.“

Table № 11 “Cash flow from financial activities”

Data on investments and withdrawals of business owners, as well as procurement and repayment of loans from banks, are indicated. The total for the table is calculated as the difference between the lines “Investments” and “Withdrawals,” “Procurement of a loan” and “Repayment of a loan” in the line “Total investments/withdrawals”.
Now It remains to make the last step: to calculate the occurrence of cash shortage in the process of fulfilling of the planned.

The next formulas are mentioned in the last two lines to do this:
The line “Cash balance at the beginning of the period”: the balance of funds at the beginning of the planning period is entered manually in the first line cell (or from the sheet “General indicators”), then there is a link to the line “Cash balance at the end of the period” from the previous column. Thus, the cash balance at the end of the period will always be equal to the cash balance at the beginning of the next period.
The line “Cash balance at the end of the period”. In each table cell of the line we enter the following formula:
“Cash balance at the beginning of the period” – “Total cash flow” + “Total revenue” + “Total investments/withdrawals.”
For the above formula, table cells by the columns of the corresponding periods are summed. That is all about the “CF” sheet.

Sheet “Finances”

Tables №12, 13, 14

Tables №12 and 13 are tables for calculating interest on the use of credit lines. The tables manually indicate only: credit line limit, annual interest, credit line debt at the beginning of the period (indicated in an orange table cell). Other data in these tables are calculated automatically. Data on procurement and repayment of loans are taken from table №11 “Cash flow from financial activities” on the sheet “CF.” The sums of the calculated interest are included in the fixed costs of the enterprise on the sheet “Total set of costs.”

Table №14 is the calculation of added value tax (the tax rate is 20%). The sum of VAT liabilities arising on the sale of products and the amount of VAT credit arising as a result of purchases are calculated here.

The resulting excess of liabilities over loans gets into the fixed costs on the sheet “Total set of costs.”

You should understand the amount of the loan for VAT is calculated approximately, since it is impossible accurately to plan how much of the incurred expenses will be incurred by the VAT payers. Doubtless, there are payments for which their recipients are VAT payers (we know that for sure), but there are cost items for which only part of the payments will maintain a VAT credit.

Sheet “Profit & loss account”

The final sheet, where the financial result of the company’s activities in the planned season is formed.

The report is divided into two parts: revenue and expenditure.

The revenue part consists of tables for each crop that is grown in the planned season. Each crop table contains data:

1. The amount of the grown crop (the data is taken from the “CF” sheet from table №10 “Cash flow from sales,” Column 3).
2. Average selling price. (the data is taken from the sheet “CF” from table №10 “Cash flow from sales” Column 2).
3. Sum. Calculated value. Quantity * Average price.
4. Primed cost of sold products. The data is taken from the corresponding operational chart flow (Table №2). The first table of the “header” clause 7.
5. Gross profit. Calculated value. Clause 3 minus clause 4 of this table.
6. Gross-profit rate. Calculated value.The ratio of clause 3 to clause 4 of this table.

The total of the income part is formed in the line “Total gross profit for the enterprise.” It is determined as the sum of the lines of gross profit for each crop.

The expenditure part consists of a line item listing of fixed costs. This data is from table №9 “Cash flow for fixed costs.”

The total of the expenditure side is formed in the line “Total of operating expenses” and determined as the sum of all fixed expenses.
The sum total of the report is formed in the line “Net profit for the enterprise” and determined as the difference between the totals of the income and expenditure side.
By the means of the described in this article calculations, you will be able to combine all the operational flow charts for the grown crops, data on the sowing area in the context of crops, data on the procurement time of the necessary products and fertilizers, cash shortage, and the possible ways of its elimination. In addition to that, you will see a guaranteed financial result with the planned crop rotation and used sowing technology.