Using Excel to calculate the cost of a step-by-step

The characteristics of using Excel to carry forward the method of step-by-step

The step-by-step of cost accounting in industrial enterprises is divided into step-by-step and parallel-carrying steps. The step-by-step closing process can be carried out either by integrated carry-over or by partial carry-over, both of which have advantages and disadvantages. If we want to make up for the insufficiency of the comprehensive carry-over, we need to do the cost reduction work, but this is a very troublesome matter. This article describes the way to solve this problem by step-by-Step cost accounting with Excel.

In the computer environment, each step of semi-finished product ledger also provides cost items and comprehensive indicators, of course, can consider simulation manual cost reduction work. But, in fact, this is completely unnecessary. Under the computer environment, the integrated carry-over and the partial carry-over of the step-by-step closing method can be carried out concurrently. In this way, there is no need to do the so-called "cost reduction" work, so that the problem of cost reduction is naturally resolved.

The key to the simultaneous carrying forward and the partial carry-over is to set up a partial index that provides both cost items and a semi-finished ledger for the integrated indicator at each step of the product production. When the cost of semi-finished goods is carried forward, the comprehensive index is taken in the semi-finished product ledger, and the partial carry-over cost is taken into account. The comprehensive carry-over and the partial carry-over carry on simultaneously, in the manual environment, this is almost equal to do two sets of cost accounting, will undoubtedly make the workload increase substantially, is inferior to carry on the cost reduction; and in the computer environment, this is an easy thing. Here's an example of Excel.

Examples of step-by-step cost accounting with Excel

It is assumed that the production of a product is divided into two steps, which are carried out by two workshops respectively. The first workshop production semi-finished products, the acceptance of semi-finished products warehouse. The second workshop is to be obtained from the semi-finished product base according to the required quantity, and the cost of semi-finished products is calculated by the weighted average unit cost per month. At the end of two workshops, the products are priced at the fixed cost. The cost calculation procedure is as follows.

First, select a worksheet in Excel named "Production cost detail Ledger a semi-finished product". On the table, design the production cost (semi-finished products) Detail account format, and according to the various production cost distribution table, semi-finished goods delivery and first workshop in the product quota cost information, register the first workshop a semi-finished production cost detailed account.

Total production cost of raw material wages and welfare costs

At the beginning of the month in the product

(fixed cost) 3,060 2,700 4,280 10,040

Monthly fee 5,080 3,010 6,600 14,690

Total 8,140 5,710 10,880 24,730

The finished turn production

Finished product cost 5,040 3,110 6,480 14,630

Month End in product

(fixed cost) 3,100 2,600 4,400 10,100

Among them, the "Finished production cost" column and each "total" number are automatically generated by setting up Excel formulas. The formula is set as figure one.

Other data, such as the raw material item data in the "cost this month" column, can also be generated automatically by linking to the raw material expense allocation table set up on the Excel worksheet. This article is limited to space, no longer introduced.