Examples of product cost calculations, calculations in Excel. Finance in Excel Excel costing and write-off of materials

Inventory accounting in Excel is an excellent solution for any trading company or manufacturing organization for which it is important to keep track of the quantity of materials, raw materials used and finished products.

Who can spreadsheets help?

Large companies purchase ready-made solutions in electronic format for these purposes. However, they are quite expensive, and some complex programs require hiring a qualified employee with a high salary to work in the warehouse. This is not possible for start-ups or small companies. Fortunately, there is a way out, and you can use Excel spreadsheets. This electronic tool, which is second in popularity only to the office program Word, has functionality that is quite sufficient for solving warehouse accounting problems.

A few important rules

Those who are interested in the question of how to keep inventory records should seriously consider creating their own computer program from the very beginning. In this case, you should adhere to the following rules from the very beginning:

• All directories must initially be created as accurately and thoroughly as possible. In particular, you cannot limit yourself to simply indicating the names of goods and you should also indicate articles, codes, expiration dates (for certain types), etc.
• Opening balances are usually entered into tables in monetary terms.
• You should follow the chronology and enter data on the receipt of certain goods at the warehouse earlier than on shipment to the buyer.
• Before filling out Excel tables, it is necessary to conduct an inventory.
• You should foresee what additional information may be needed and enter it too, so that in the future you do not have to clarify the data for each product.

Before you start developing a spreadsheet to ensure the smooth functioning of your warehouse, you should consider its specifics. General recommendations in this case are as follows:

• It is necessary to compile directories: “Buyers”, “Suppliers” and “Points of registration of goods” (small companies do not require them).
• If the list of products is relatively constant, then it is recommended to create their nomenclature in the form of a database on a separate sheet of the table. In the future, expenses, income and reports must be filled out with references to it. The sheet in the Excel table with the heading “Nomenclature” should contain the name of the product, product codes, product groups, units of measurement, etc.
• The report is generated using the Pivot Table tool.
• Receipts to the warehouse should be recorded on the “Receipt” sheet.
• It is required to create “Consumption” and “Remaining” sheets to track the current status.

We create directories

To develop a program to keep inventory records in Excel, create a file with any name. For example, it might sound like “Warehouse.” Then we fill out the directories. They should look something like this:

To prevent headings from running away, they need to be fixed. To do this, on the “View” tab in Excel, you need to click on the “Freeze Areas” button.

The “Buyers” table looks exactly the same.

Freeware can provide a convenient and partially automated service if you create an auxiliary directory of goods release points in it. True, it will only be required if the company has several retail outlets (warehouses). As for organizations that have one point of issue, there is no point in creating such a directory for them.

Our own “Warehouse” program: creating a “Receipt” sheet

First of all, we need to create a table for the item. Its headings should look like “Product Name”, “Variety”, “Unit of Measurement”, “Characteristics”, “Comment”.

• Select the range of this table.
• In the "Name" field located directly above the cell called "A", enter the word "Table1".
• Do the same with the corresponding range on the “Suppliers” sheet. In this case, indicate “Table2”.
• Receipt and expense transactions are recorded on two separate sheets. They will help you maintain inventory records in Excel.

For “Parish” the table should look like the figure below.

Automation of accounting

Warehouse accounting in Excel can be made more convenient if the user can choose from a ready-made list the supplier, product name and point of accounting.

Wherein:

• the unit of measurement and supplier code should be displayed in the table automatically, without operator participation;
• invoice number, date, price and quantity are entered manually;
• the Warehouse program (Excel) calculates the cost automatically, thanks to mathematical formulas.

To do this, you need to format all directories in the form of a table and create a drop-down list for the “Name” column. For this:

• select the column (except for the header);
• find the “Data” tab;
• click on the “Data Check” icon;
• in the “Data type” field, look for “List”;
• in the “Source” field we indicate the function “=INDIRECT("item!$A$4:$A$8")".
• Check the boxes next to “Ignore empty cells” and “List of acceptable values.”

If everything is done correctly, then when filling out the 1st column you can simply select from the list. At the same time, in the column “Unit. change." the corresponding value will appear.

Autocomplete for the “Code” and “Vendor” columns, as well as a drop-down list, are created in the same way.

To fill out the “Cost” column, use the multiplication formula. It should look like “= price * quantity”.

You also need to create a drop-down list called “Accounting points”, which will indicate where the received goods were sent. This is done in exactly the same way as in previous cases.

"Turnover sheet"

Now that you have almost created a convenient tool that allows your company to maintain inventory records in Excel for free, all that remains is to teach our program to display the report correctly.

To do this, we start working with the corresponding table and set zeros at the beginning of the time period, since we are just going to keep warehouse records. If it was carried out earlier, then the balances should be displayed in this column. In this case, units of measurement and names of goods must be taken from the nomenclature.

To facilitate warehouse accounting, the free program must fill in the “Shipments” and “Receipts” columns using the SUMIFS function.

We calculate the remaining goods in the warehouse using mathematical operators.

This is how we ended up with the “Warehouse” program. Over time, you can make adjustments to it yourself to make accounting for goods (your products) as convenient as possible.

Any organization acquires materials for the company’s activities not for their own sake. And the purchased valuables will not lie dead weight in the warehouse for the director to admire. They are intended for use in production, sales or administrative purposes. Therefore, purchased materials are subsequently consumed in production.

However, in the warehouse the storekeeper or warehouse manager is responsible for them, and the materials are taken into account on account 10. When the materials leave the warehouse, the situation will change: the account and the person in charge will change. In this article we will analyze the write-off of materials with step-by-step instructions for this procedure for you.

1. Accounting entries for writing off materials

2. Registration of write-off of materials

3. Write-off of materials - step-by-step instructions if not everything is consumed

4. Standards for writing off materials for production

5. Example of a write-off act

6. Methods for writing off materials for production

7. Option No. 1 – average cost

8. Option No. 2 – FIFO method

9. Option No. 3 – at the cost of each unit

So, let's go in order. If you don't have time to read a long article, watch the short video below, from which you will learn all the most important things about the topic of the article.

(if the video is not clear, there is a gear at the bottom of the video, click it and select 720p Quality)

We will look at write-offs of materials in more detail than in the video later in the article.

1. Accounting entries for writing off materials

So, let's start by determining where the purchased materials can be sent. It should be noted that materials are truly ubiquitous and there are ways to, as they say, “plug a hole” in any problem area of ​​the organization:

• - serve as the basis for the production of products
• - be an auxiliary consumable material in the production process
• — perform the function of packaging finished products
• - used for the needs of the administration in the management process
• — assist in the liquidation of decommissioned fixed assets
• - used for the construction of new fixed assets, etc.

And the accounting entries for writing off materials depend on what materials are released from the warehouse for:

Debit 20"Primary production" - Credit 10– raw materials were released for production

Debit 23"Auxiliary production" - Credit 10– materials were sent to the repair shop

Debit 25"General production expenses" - Credit 10– rags and gloves were provided to the cleaning lady servicing the workshop

Debit 26"General running costs" - Credit 10– paper for office equipment was issued to the accountant

Debit 44"Sales expenses" – Credit 10– containers for packaging finished products were issued

Debit 91-2"Other expenses" - Credit 10– materials were released for the liquidation of fixed assets

It is also possible for a situation where it is discovered that the materials listed in the accounts are actually missing. Those. there is a shortage. For such a case, there is also an accounting entry:

Debit 94“Shortages and losses from damage to valuables” – Credit 10– missing materials written off

2. Registration of write-off of materials

Any business transaction is accompanied by the preparation of a primary accounting document, and write-off of materials is no exception. The step-by-step instructions in the next paragraph contain the study of the primary documents that accompany the write-off process.

Currently, any commercial organization has the right to independently determine the set of documents that will be used to formalize the write-off of materials, so the registration of write-off of materials may vary from organization to organization.

The main thing is that the documents used are approved as part of the accounting policy and contain all the mandatory details provided for in Article 9 of Law No. 402-FZ “On Accounting”.

Standard forms that can be used when writing off materials (approved by Resolution of the State Statistics Committee of October 30, 1997 No. 71a):

• — demand-invoice (Form No. M-11) is applied if the organization has no limits on receiving materials
• — limit-fence card (Form No. M-8) is applied if the organization has established limits on the write-off of materials
• — invoice for the issue of materials to the side (Form No. M-15) is applied to another separate division of the organization.

The organization can modify these forms - remove unnecessary details and add details that the organization needs.

The invoice requirement is suitable for accounting for the movement of material assets within an organization, between financially responsible persons or structural divisions.

The invoice in two copies is drawn up by the financially responsible person of the structural unit handing over material assets. One copy serves as the basis for the handing over unit to write off valuables, and the second copy serves as the basis for the receiving unit for the receipt of valuables.

3. Write-off of materials step-by-step instructions if not everything is consumed

Usually, when preparing these documents, it is assumed that the released materials were immediately used for their intended purpose, which means they are accompanied by the postings that we discussed above - for credit 10 of the account and debit 20, 25, 26, etc.

But this does not always happen, especially in large production. Materials transferred to the work site or workshop may not be immediately used in production. In fact, they simply “move” from one storage location to another. In addition, when dispensing materials, it is not always known what type of product they are intended for.

Therefore, those materials that are released from the warehouse but not consumed should not be taken into account as expenses of the current month, neither in accounting nor in tax accounting for income tax. What to do in this case, how to write off materials, step by step instructions below.

In such situations, the release of materials from the warehouse to the production department should be reflected as an internal movement, using a separate subaccount to account 10, for example, “Materials in the workshop.” And at the end of the month, another document is drawn up - a materials consumption act, where the direction of materials consumption will already be visible. And at this moment the materials will be written off.

Such tracking of material consumption will allow you to achieve greater reliability in accounting and correctly calculate income tax.

Please note that this applies not only to materials that go into production, but also to any property, including stationery used for administrative needs. Materials should not be issued “in reserve”. They must be used immediately. Therefore, a one-time operation to write off 10 calculators for an accounting department of 2 people, during an audit, will certainly raise questions as to what purpose they were required in such quantities.

4. Example of a write-off act

1. - or you issue and immediately write off only what is actually consumed (in this case, the requirement of an invoice is quite sufficient)
2. - or you draw up an act for writing off materials (transmitting a demand invoice, and then gradually writing off acts for writing off).

If you use write-off acts, do not forget to also approve their form as part of the accounting policy.

The act usually indicates the name, and, if necessary, the item number, quantity, accounting price and amount for each item, number (code) and (or) name of the order (product, product) for the manufacture of which they were used, or number (code) and (or) the name of the costs, the quantity and amount according to consumption standards, the quantity and amount of consumption in excess of the standards and their reasons.

An example of what such an act might look like is in the picture below. I repeat, this is just an example; the type of act will very much depend on the specifics of the enterprise. Here, as a basis, I took the form of the act that is used in budgetary institutions.

5. Standards for writing off materials for production

Accounting legislation does not establish standards in accordance with which materials should be written off for production. But paragraph 92 of the Methodological Guidelines for the accounting of MPZ (Order of the Ministry of Finance dated December 28, 2001 No. 119n) states that materials are released into production in accordance with established standards and the volume of the production program. Those. the amount of materials written off should not be uncontrolled and the standards for writing off materials into production should be approved.

In addition, for tax accounting it would be useful to remember Article 252 of the Tax Code: expenses are economically justified and documented.

The organization sets its own standards for materials consumption (limits). . They can be fixed in estimates, technological maps and other similar internal documents. Documents of this kind are not developed by the accounting department, but by the unit that controls the technological process (technologists), and then they are approved by the manager.

Materials are written off for production in accordance with approved standards. You can write off materials in excess of the norm, but in each such case you need to explain the reason for the excess write-off. For example, correction of defects or technological losses.

The release of materials in excess of the limit is carried out only with the permission of the manager or his authorized persons. On the primary accounting document - the demand invoice, the act - there must be a note about the excess write-off and its reasons. Otherwise, the write-off is illegal and leads to a distortion of the cost and accounting and tax reporting.

On the topic of expenses in the form of technological losses, you can read: Resolution of the Federal Antimonopoly Service of the North Caucasus District dated 02/04/2011. No. A63-3976/2010, letters from the Ministry of Finance of Russia dated July 5, 2013. No. 03-03-05/26008, dated January 31, 2011. No. 03-03-06/1/39, dated 10/01/2009 No. 03-03-06/1/634.

6. Methods for writing off materials for production

So, now we know what documents we need to write off materials, and we also know the accounts to which they are debited. From the documents we know how much materials were written off. Now all that’s left to do is determine the cost of their write-off. How can we determine how much the materials sold cost, and what amount will be the write-off entry? Let's look at a simple example, based on which we will study the methods of writing off materials for production.

Example

Sladkoezhka LLC produces chocolate candies. Cardboard boxes are purchased for their packaging. Let 100 such boxes be purchased at a price of 10 rubles. a piece. A packer comes to the warehouse to pick up boxes and asks the storekeeper to give him 70 boxes.

So far we have no question about how much each box costs. The packer receives 60 boxes for 10 rubles, for a total of 600 rubles.

Even if 80 boxes were purchased, but the price is already 12 rubles. a piece. The same boxes. Of course, the storekeeper does not keep the old and new boxes separately, they are all stored together. The packer came again and wants more boxes - 70 pieces. The question is: at what price will the boxes sold for the second time be valued? It is not written on each box exactly how much it cost - 10 or 12 rubles.

Different answers can be given to this question, depending on which method of writing off materials for production is approved in the accounting policy of Sladkoezhka LLC.

7. Option No. 1 – average cost

After the packer left the warehouse with the boxes for the first time, there were 40 boxes left for 10 rubles each. – this will be, as they say, the first game. Another 80 boxes were purchased for 12 rubles. - This is already the second batch.

Let's count the results: we now have 120 boxes for a total amount of: 40 * 10 + 80 * 12 = 1360 rubles. Let’s calculate how much a box costs on average:

1360 rub. / 120 boxes = 11.33 rub.

Therefore, when the packer comes for the second time for boxes, we will give him 70 boxes for 11.33 rubles, i.e.

70*11.33=793.10 rub.

And we will have 50 boxes left in the warehouse worth 566.90 rubles.

This method is called average cost (we found the average cost of one box). As new batches of boxes continue to arrive, we will again calculate the average and issue boxes again, but at a new average price.

8. Option No. 2 – FIFO method

So, by the time of the packer’s second visit, we have 2 batches in our warehouse:

No. 1 - 40 boxes for 10 rubles. – according to the time of acquisition, this is the first batch – the “older” one

No. 2 – 80 boxes for 12 rubles. - according to the time of acquisition, this is the second batch - more “new”

We assume that we will issue the packager:

40 boxes from the “old” one - the first batch purchased at the price of 10 rubles. – total for 40*10=400 rub.

30 boxes from the “new” one - the second batch in time to purchase at a price of 12 rubles. – total for 30*12=360 rub.

In total, we will issue in the amount of 400 + 360 = 760 rubles.

There will be 50 boxes left in the warehouse at 12 rubles, for a total of 600 rubles.

This method is called FIFO - first in, first out. Those. First, we sort of release material from an older batch, and then from a new one.

9. Option No. 3 – at the cost of each unit

At the cost of a unit of inventory, i.e. Each unit of materials has its own cost. This method is not applicable for ordinary cardboard boxes. Cardboard boxes are no different from each other.

But materials and goods used by the organization in a special manner (jewelry, precious stones, etc.), or inventories that cannot normally replace each other, can be valued at the cost of each unit of such inventories. Those. If all our boxes were different, we would put a different tag on each one, then each of them would have its own cost.

Here are the most important questions on the topic of writing off materials: step-by-step instructions are now before your eyes. For those who keep records in the 1C: Accounting program, watch a video tutorial on writing off materials in this program.

What problematic issues do you have regarding the write-off of materials? Ask them in the comments!

Write-off of materials step-by-step instructions for accounting

In the course project, depending on the nature of the products produced by the facility being designed, its total or production cost, or intra-shop costs for this processing stage, are calculated. If the manufactured product is commercial, then its full cost is calculated; for semi-finished products intended for further processing at this enterprise, their production cost is determined. When processing a semi-finished product produced by another department of the same workshop, the cost of which cannot be determined, intra-shop costs for this processing are calculated, which include all items of the workshop cost, except for the costs of the processed semi-finished product.

To calculate the cost of production or processing costs, a project estimate is drawn up in Table 13, in which the costs for each item are consistently determined for the annual output and per costing unit.

Table 13. Project calculation of the cost of polyethylene tubular film made of polyethylene.

The projected annual output is 2900 tons.

The calculation unit is 1 ton.

The calculation of electricity costs has some features. In the course project, it is necessary to determine the annual consumption of electricity for technological and propulsion (power) purposes, its consumption per costing unit of production and calculate the cost of a unit of electricity using a two-rate tariff, at which it is taken into account in the cost of the designed product.

To determine the annual electricity consumption it is necessary to establish:

• -list and quantity of electrical equipment used in the workshop;
• - nominal nameplate power of a unit of each type of electrical equipment;
• - demand coefficient, which is the product of coefficients taking into account the simultaneous loading of electrical equipment and the average use of its maximum power (0.8);
• - coefficient of increase in declared (paid) power due to electricity losses in electrical equipment and cable networks (1.1).

The annual energy consumption is calculated in Table 14.

Then the specific energy consumption per unit of production is calculated.

If the declared power of electrical equipment (N declared) is 50 kW, then the cost of electricity is calculated according to a two-part tariff as follows.

First, the payment for all electricity (3 el) received per year from the power system is determined, and the enterprise’s costs for maintaining its own energy facilities are taken into account:

Z el = (N application C 1 +W) K e.x

where N declared is the total declared power of electrical equipment, kW;

W - annual electricity consumption, kWh;

Ts 1 - basic fee for each kW of declared power, rub.

K e.h. - coefficient taking into account the costs of maintaining the enterprise’s energy facilities (K e.x = 1.1).

Then the cost of 1 kWh of electricity used by the designed facility (C el) is calculated:

C el = Z el / W

Table 14. Calculation of annual electricity consumption.

In the article “Wages of basic production workers”

includes the basic and additional wages of these workers, calculated in the corresponding section of the course project.

Contributions for social needs are calculated as a percentage of the wages of main production workers (30%).

The article “General production expenses” consists of 2 parts: “Costs for the maintenance and operation of equipment” and “General shop expenses”. To determine the annual cost for each of these parts, special estimates are drawn up with the same names.

Estimate of “Costs for the maintenance and operation of equipment” - Table 15. To determine the annual value of “General shop expenses”, an estimate is drawn up in Table 16.

Table 15. Estimated overhead costs.

In the case of economic activities of enterprises, the calculation of product costs becomes a significant factor. There is a direct dependence of this indicator on the volume of products produced and their quality. In addition, other factors are also important:

1. Time spent by employees at work.
2. Quantity and quality of materials used.
3. Use of equipment.
4. Raw materials.

Cost indicator– basic when it comes to determining the price for a particular product. Therefore, let’s talk in more detail about how to calculate it, how product costs are calculated.

What is included in the cost

Cost: basic concept

When working with cost, they rely on the current costs of the enterprise that arose due to the organization of production processes and the sale of products.

There are two types of cost for companies:

• According to the plans.
• In fact.

The cost of the planned type depends on the level of production cost that is expected. Measurements are carried out over certain time intervals. In this case, the following indicators are assumed to be used:

1. Consumption standards for materials.
2. Raw materials.
3. Labor costs.
4. Equipment.

Actual cost is an indicator related to current production indicators. Separately determine the level of costs required to create a unit of production. An Excel sample with calculations can be downloaded from us.

Costing is used to calculate exact numbers. In this case, special articles are used that determine the type of costs. The types of such articles also depend on several factors:

• Manufactured products along with their characteristic features.
• Production processes, their specifics.
• The sector of the economy for which the enterprise operates. The example is easy to download on our website.

About the types

In relation to production practice, the concepts of full cost, as well as its production variety, have become widely used. The last group involves the use of such expense items as:

1. General expenses for production and housekeeping.
2. Remuneration for workers for their work. This includes standard wages.
3. Purchase of fuel, raw materials and similar consumables.
4. Use of certain materials.

When it comes to the full cost, costs associated not only with the production itself are taken into account, but also, for example, commercial ones. We are talking about the process of selling products, advertising, storage, creating suitable packaging, paying salespeople, and similar things. An example calculation in Excel will help you figure it out.

Depending on the volume of goods produced, the costs that determine the cost may change. Therefore, the costs themselves can be constant or variable. General production and general economic in this case represent a conditionally permanent variety. But technological costs and labor costs cannot be considered as such. After all, this characteristic can increase, depending on the results of the enterprise.

Cost of various products

On the role of calculations in enterprise management

Accounting for production costs is a prerequisite for organizing the costing itself. It is assumed that all operations in the enterprise must have documentary evidence. It is important to analyze and summarize information based on various characteristics. Only a detailed analysis of production allows us to further organize costing with accurate results.

There is a close relationship between production accounting and costing. For example, information collected during production accounting becomes the basis that helps calculate the cost of one unit of goods. Accuracy has varying degrees, depending on the goals of the enterprise in terms of costing.

The costing processes themselves have three stages:

1. The first is to calculate the cost of products produced in general.
2. The next stage is calculating indicators for individual types of goods.
3. Finally, the latter includes indicators of one unit of a good, service or work.

The costing process is often highly complex. First come the primary costs, and then move on to auxiliary indicators. It is important to take into account the so-called mutual favors, which further complicates the process.

When it is necessary to manage production, calculation becomes necessary, even only based on objective factors.

Valuation of finished goods inventory has previously been the main goal for creating such systems.

What to consider when working

More balanced solutions are now being used. The information used makes it possible not only to solve traditional problems, but also to predict economic consequences. For example, when you need to analyze the following indicators:

1. At what quality level is the staff working?
2. Is it worth upgrading your current equipment?
3. The need to optimize the range of products.
4. What price would be optimal?
5. Should I continue producing products or do something else?

Thanks to such procedures, it becomes easier for the enterprise to evaluate the adopted plans. In addition, the data is used for further activity planning.

About the principles and objects of calculation

The following principles become the main ones for calculation, regardless of the characteristics of a particular enterprise:

• Scientific justification for any costs. In some industries this is important for developing appropriate guidelines.
• Accurate identification of objects and units of measurement. In many cases, accounting objects for different areas of activity do not coincide with each other.

The choice of a specific unit of measurement depends on the characteristics of production.

In addition, it is important to choose a specific method for allocating indirect costs. Otherwise, the unit cost of production cannot be determined correctly. The company chooses the appropriate method independently. The research results are recorded in the accounting policy. The main thing is not to change them for at least one financial year.

Costs must be clearly delineated by period. The main principle is the so-called accrual principle. After all, transactions are often processed at the time of transfer, and may not be linked to cash flows. That’s why you have to understand what accurate calculation is.

Costing methods

About methods in calculation

It is important to understand which method is used to reflect and document all production costs. There are various options for action that can be taken in this direction.

Pro-process

The most relevant solution for the extractive industries. Energy companies also often use this method. It is also suitable for processing industries, which are characterized by the use of simple technologies.

The method is suitable for enterprises with the following characteristics:

• In the production of mass-produced products.
• There are restrictions on the nomenclature.
• Measurement and calculation are carried out with a standard unit of measurement.
• Complete absence of unfinished processes in production, or a minimum number of them.

For products, the cost price is calculated in three stages:

1. Determination of cost for all goods produced by production. Then we divide the costs by the number of products. We get the result associated specifically with the unit of production.
2. We divide the amounts of commercial and management activities by the volume of products created over a certain time period.
3. All that remains is to add up the numbers obtained in the two previous stages.

Transverse method

An excellent method for mass or industrial production. Production has several stages through which products pass. The relevance of the method increases in enterprises where various types of chemical processing are used. Each completed process leads to the creation of objects in the form of products, when the cost of production is calculated.

The essence of the cross-type method is described as follows. Current accounting is used to reflect direct costs, but is broken down based on redistributions rather than types of goods. The situation when different goods are received during the same redistribution is no exception. Redistributions become objects for carrying out operations.

Processing is part of production technologies when a semi-finished product is obtained in finished form. It can be sold externally or processed further. Passing through all stages of processing allows us to obtain finished products. A current option for industrial complexes.

Table in Excel

Custom

The method works best for unique items, each of which is made to order. A single type of production is the main characteristic of enterprises for which the custom method of costing will be relevant. This has implications for those wondering how to do the calculations.

The following characteristics distinguish unit type production:

• Wide range of manufactured goods. Repetitions are largely absent. Individual products are created when certain orders are placed.
• Each workplace has its own specialization in terms of technology. It is impossible to assign work processes and details to a specific place on an ongoing basis.
• Use of tools with multipurpose equipment.
• The presence of a serious share in manual assembly and finishing operations.
• A staff consisting of a large number of generalists whose qualifications are at a high level.

And in this method, individual costing items are highlighted, which allow you to achieve the most accurate result.

The results of any calculation need to be adjusted from time to time, because their accuracy is almost always questioned.

is an Excel workbook designed to calculate the planned cost of manufactured products using the direct costing method. Additionally, data on marginal profit and profitability for the specified sales volume for the period are calculated. To generate special reports, the file requires macros to be connected.

Introduction

Calculating the cost of manufactured products based on direct costs (direct costing) is one of the most labor-intensive tasks in the process of creating a financial plan. In cases where an enterprise produces complex products that require several stages of assembly, the calculation becomes significantly more complicated due to the need to take into account the levels of occurrence of components with each other. This process is necessarily encountered at enterprises that use the concepts of “semi-finished product”, “semi-product” or “reprocessing”. The description of step-by-step production is specification products. Specifications in some types of production are also called recipes(chemical, food industry), or use the English term BOM (bill of materials). The components of the specifications in the program are called resources, in practice this may be nomenclature, positions or various English terms (for example, SKU). Consumption in the specification is usually indicated per unit of product, but in practice there is also consumption per thousand units. Unlike actual specifications, planned specifications usually use the average (weighted average) price of purchase and balance; LIFO/FIFO methods are not used for planned indicators.

Complex software packages and accounting systems may include a module for calculating planned specifications, with the so-called “explosion” algorithm. If you handle data sets correctly, this entire complex process can be described in Excel at the formula level. Spreadsheets themselves will calculate the tree and nesting levels, and then calculate the data in the correct sequence. The program Costing is built on this principle of operation of formulas. At the same time, data processing is performed even faster than in complex software systems (due to recalculation based on changes), and the volume of information is quite sufficient for small and medium-sized enterprises. In the program, additional automation (macros) is used only to display specifications in a visual form; all basic calculations are made using standard formulas.

Changes in version 2.6

• The appearance of workbooks has been updated in accordance with the design of the latest versions of Microsoft Office.
• Drop-down list for searching directory items by the first letters of a word. Activated by double click.
• Highlighting in red the journal elements not found in the directories.

Features and Limitations

Excel spreadsheets have inherent limitations on the amount of information they can process. The program is not intended to replace large accounting systems for hundreds of thousands of items of complex specifications. But in most SMBs, the volume and complexity of data can be processed in Excel.

The program was tested in different operating modes.

IN automatic Excel calculation mode We recommend the following parameters when working on high-performance computer:

• Number of resources (nomenclature) - up to 2000
• Number of specification lines (occurrences of components) - up to 5,000
• Number of nesting levels of specifications - up to 4

IN manual Excel calculation mode the limitations are mainly related only to the capabilities of the computer. For normal operation of reports, we recommend the following restrictions:

• Number of resources (nomenclature) - up to 5000
• Number of specification lines (occurrences of components) - up to 30,000
• Number of nesting levels of specifications - up to 5

By default, reports do not support more than five levels of BOM nesting. To remove this limitation, you can insert columns into the report sheet by correctly copying the formulas.

The program works correctly with a special type of resource returnable waste when indicating the assigned cost and negative expense in the specification.

This version only supports one specification of any resource type. You can modify the program to store several specifications of the same product (semi-finished product) with the ability to select the specification number used for planning. You can also simply store different versions of specifications in different Excel files.