Cost of production with the example of calculation in Excel

The cost – it is a monetary reflection expenses current on production and implementation of goods.

For the manufacturing sector this indication - is the basis for the formation of price. The calculation base – it`s the calculation of distribution costs. And to simplify this process you may with helping Excel.



Calculate Product Costs for a Manufacturer in Excel

It has been noted already that the list of costs itemized each company will have its own. But in their frame you can substitute any dates, where appropriate, you can change the formulas and receive the finished calculation.

For example costing and calculating the selling price we examine the dates from the following table:

following table.

The calculation scheme costing:

  1. The recyclable waste we calculate from the cost of raw materials and stuff (we take the specified percentage).
  2. For determining of the additional wage we need to take inti account the following dates: if the basic salary is more than 200 that additional one is equaled 10% from the base; less than 200 – 15%.
  3. The accruals for salaries – 30% from the sum of basic and additional wages (an additional 10% which has introduced from 2015 on the annual income which more than 600 is not taken into account therein).
  4. The costs of maintaining equipment – 5% from basic salary.
  5. The general expenses – 9% from the average basic wage.
  6. The overhead costs – 18% (25% BW + 75% AW). There are: BW – the basic wage, AW – additional wage.
  7. The production cost = sum of expenses on maintenance of the equipment, raw materials and stuff, fuel and energy, accessories, BW and AW, accruals for salary, overhead and general expenses after deduction of recyclable waste.
  8. Non-production costs (expenses) – 3% from the production cost.
  9. The total cost = the production cost + the manufacturing costs.
  10. The profit of the manufacturer we count in percentage from the total cost.
  11. The wholesale price = the total cost + the profit of the manufacturer.
  12. VAT we count from wholesale prices.
  13. The selling wholesale price = the wholesale price of the manufacturer + the indirect taxes (VAT in the example).

Based on the scheme, we will introduce dates and formulas for calculation in an Excel spreadsheet.

Excel spreadsheet.

The explanations to calculation of certain accrued articles:

  1. The recyclable waste: =B2*12.54% (the percent was taken from the first table).
  2. The additional wages: =IF(B6<200,B6*0.15,B6*0.1). The function =IF() allows take into account to the different conditions of charging.
  3. The accruals for salaries: =(B6+B7)*30%. If you follow the letter of the law and take an additional 10% with annual salary more than 600, we need to use the same function.
  4. The maintenance of equipment is: =B6*5%.
  5. The overhead costs: =18%*(B6*25%+B7*75%).
  6. The general expenses: =9%*B6.
  7. The production cost: =(B2+B3+B5+B6+B7+B8+B9+B10+B11)-B4.
  8. The production costs: =3%*B12.
  9. The total cost: =B12+B13.
  10. The profit of the manufacturer: =B14*3.45%.
  11. The wholesale price of the manufacturer: =B14+B15.
  12. The formula for calculating VAT: =B16*20%.
  13. For calculating of wholesale price: =B16+B17.

On the same principle calculation is carried out product cost of goods B and C.

You can do so that Excel takes the benchmark data for calculation right away from the relevant tables. For example, raw materials and stuff you may take from the production report. The salary you may take from the statement. Of course, if all information is maintained in Microsoft Excel.



The costs itemized

The calculation is made per unit produced in one stage of production (meter, kilogram, a thousand pieces). The list of costs itemized reflects the anticipated production specifics.

The most characteristic for domestic enterprises are the following:

  1. Raw materials and stuff.
  2. The components (for industrial machinery: fuel and energy).
  3. The basic and additional salary of production workers.
  4. The accruals for salaries.
  5. The overhead costs after deduction recyclable waste.
  6. The general expenses.
  7. The commercial expenses.

Distinguish between the production cost (items 1-6) and total (the sum of production and commercial costs). The business expenses are associated with the implementation of the goods (advertising, packaging, storing, etc.).

Directly on the cost include to direct costs (items 1-3). The indirect costs are expressed in ratios or percent’s, because they are related with the production of the whole product or certain types of products.

The example of calculating the cost of production calculation you can download links:

The specific of the enterprise «dictates» the list of direct and indirect costs. In the shipbuilding, for example, almost all costs are relating to direct costs. In the chemical industry they are relating to indirect costs.


en ru