Cost of Goods Manufactured Schedule: Preparing in Excel

Cost of Goods Manufactured Schedule: Preparing in Excel
Page content

Building the Spreadsheet

Determining the cost of manufactured goods is a key financial component to managing a successful manufacturing business. Without a clear understanding of the cost of manufacturing a product, you cannot set a price point for the product that produces enough profit to make the manufacturing worthwhile. The most efficient method of preparing a cost of goods manufactured schedule is using spreadsheet software, such as Microsoft Excel, because you can build in the calculations and reuse the spreadsheet each month.

Materials Cost

The first item to take into account when preparing a cost of goods manufactured schedule is the cost of raw materials used to manufacture the products. Label the first section of your schedule “Materials Cost,” then create a line for each of the following items below the subhead: beginning raw materials inventory, raw materials purchases, obsolete/retired/returned raw materials, ending raw materials inventory, and total raw materials used.

The beginning raw materials inventory is the ending raw materials inventory balance from the previous month of operations. Enter all purchases of raw materials to reflect the increase in inventory balance. Deduct raw material returned to vendors or retired due to obsolescence or damage. Finally, deduct the raw material ending balance to determine the total cost of raw material used.

Direct Labor Cost

Next, create a line for direct labor cost. Direct labor costs are wages and salaries paid to employees who physically create the product. Direct labor does not include administrative positions or other personnel who work in the factory but do not work directly with raw material in product creation.

Overhead Costs

Overhead costs are all costs attributed to the manufacturing of a product that cannot be directly tied to the manufacturing process. Overhead costs vary based on the business, but usually include indirect materials, indirect labor, property tax, factory liability and property insurance, utilities, and depreciation on the factory itself and factory equipment. Indirect materials can include items such as safety glasses and disposable tools. Indirect labor includes wages and salaries paid to employees that work in the factory but do not direct create the products, such as supervisors, security guards and janitorial staff.

Many manufacturing companies have the factory separately metered for utilities in order to distinguish between the utilities cost for running the factory versus the utilities cost of maintaining the administrative office. Unfortunately, most real estate tax bills do not distinguish a cost between different areas of the same property, but managers determine an appropriate allocation of the real estate tax between the factory and the administrative office. If your state collects personal property tax, you can separate the tax based on the value of factory machinery versus administrative office equipment.

Create a new section for overhead costs and list each type of overhead cost and the total amount incurred for the month. Add a total overhead costs line at the end of the section, then another total line below that for total manufacturing costs.

Cost of Goods Manufactured

The last step in preparing a cost of goods manufactured schedule is using the total manufacturing costs to determine the cost of goods manufactured. The last section of your spreadsheet starts with your beginning balance of work in process inventory, adds the total manufacturing costs to that amount and then reduces the amount by the ending balance in work in process inventory. That calculation returns your cost of goods manufactured for the month. You can now use this financial data to prepare your cost of goods sold schedule.

References and Image Credits

Reference: “Principles of Accounting”; A. Douglas Hillman, Richard F. Kochanek, Corine T. Norgaard; 1991

Image by Kaye Morris