Calculation of production costs in Excel

The production cost calculation of production is the determination of costs in terms of money per unit of goods, works or services. The calculation includes direct and indirect costs. Direct is the cost of materials, wages of workers, etc. Indirect costs: planned profit, transportation, etc.

We will not consider calculating articles in detail. We automate the process of calculating the planned production cost of production using Excel formulas. Our task is to create a table using Excel tools so that when you substitute data, the production cost of goods, works, and services is automatically considered.

The production cost calculation of goods in trade

It is better to learn how to calculate the production cost price from the sphere of trade. There are fewer costs. In fact - the purchase price, issued by the supplier; transportation expenses for the delivery of goods to the warehouse; duty and customs fees, if we import goods from abroad.

We take a certain group of goods. We calculate the production cost price for each of them. The last column - the planned production cost factor - will show the level of costs that the company will incur for the delivery of products.

We fill in the table:

Transportation costs, according to the logistics department, will be 5% of the purchase price.
The amount of the duty will vary by different groups of goods: for the goods 1 and 4 - 5% and for the goods 2 and 3 - 10%. To make it more convenient to set percentages, we sort the data by the column «Name of product».
For the calculation we use the formula: the purchase price + transport costs in monetary terms + duty in monetary terms.

The formula for calculating the planned ratio is the production cost price in monetary terms / purchase price.

The level of costs for the delivery of goods 1 and 4 will be 10%, 2 and 3 - 15%.

Formulas for calculation the planed production cost of the production in Excel

Each company calculates the planned production cost in its own way. After all, enterprises bear different costs depending on the type of activity. Any calculation must contain a decoding of the costs of materials and wages.

The calculation of the planned production cost price begins with the determination of the production cost of raw materials and materials used for the production of goods (which are directly involved in the technological process). The expenses of raw materials is included in the expenses of the standards approved by the enterprise minus technological losses. These data can be taken in the technological or production department.

We will reflect the norms of raw material consumption in the Excel table:

Here we managed to automate only one column which is the column with the expense taking into account the technological losses. The formula is = E3 + E3 * F3.

Note! For the «Technological losses,%» column, we set the percentage format. Only in this case the program will calculate correctly. The numbering of the lines begins above the header. If the data is messed up, you can restore them by numbers.

Knowing the norms, we can calculate the cost of materials (the calculation is for thousands of items):

In this table, you have to manually fill in only one column – «Price». All other columns refer to the data of the «Standards» sheet. In the column «Amount» the formula works: = D3 * E3.

The next article of direct costs is the wages of production workers. The basic salary and additional are taken into account. The principles of the salary is charged (piece-work, time-based, from output), you can find out in the accounting department.

In our example, the calculation of wages is carried out according to the norms of output: how much an employee of a certain qualification must make for a unit of working time.

Data for calculations are as follows:

The price is calculated by the formula: = C3 * D3.

Now we can calculate the basic salary of workers:

To fill the first two columns, not including the number in order, we linked the data of this table to the data of the previous one. The formula for calculating the bonus is = C3 * 30%. The basic salary is = C3 + D3.

Additional wages are all payments made by law, but not related to the production process (holidays, remuneration for long service, etc.).

Other data for calculating the production cost of production we added to the table immediately:

The column «Calculation of the indicator» indicates the place we are taking the data from. If we refer to other tables, then we use the resulting sums.

To estimate the calculation of the production cost of packages, conditional indicators of OS depreciation, percentages of additional wages and taxes, mandatory insurance premiums are taken.

The formula for calculating the expenses of a product with formulas: