Inventories are the stocks of goods that a business holds for sale or for use in production. There are three types of inventories: raw materials, work in progress (WIP), and finished goods. Raw materials are the inputs that are used to produce goods. Work in progress are the goods that are partially completed and still in the production process. Finished goods are the goods that are ready for sale or delivery.
Changes in inventories are the differences between the opening and closing balances of inventories in a given period. Changes in inventories reflect the net effect of production and sales activities on the inventory levels. Changes in inventories are important for measuring the gross domestic product (GDP) of a country, as they indicate the contribution of the manufacturing sector to the economy. Changes in inventories are also important for managing the cash flow and profitability of a business, as they affect the cost of goods sold (COGS) and the working capital.
In this article, we will explain how to calculate changes in inventories of finished goods and work in progress in excel formula. We will also provide a comprehensive explanation of the basic theory and the procedures involved. We will use a scenario to give a detailed example with real numbers and use an excel table to explain the calculation. We will also discuss other approaches if available.
The basic theory behind changes in inventories of finished goods and work in progress is based on the following accounting equation:
Changes in Inventories = Production - Sales
This equation states that the changes in inventories are equal to the difference between the production and sales of goods in a given period. Production is the amount of goods that are produced by a business. Sales are the amount of goods that are sold or delivered by a business.
To calculate changes in inventories of finished goods and work in progress, we need to know the following information:
- The opening and closing balances of finished goods and work in progress inventories
- The production and sales of finished goods and work in progress in the period
The opening and closing balances of inventories can be obtained from the balance sheet of a business. The production and sales of inventories can be obtained from the income statement or the production and sales reports of a business.
The formula for calculating changes in inventories of finished goods and work in progress is as follows:
Changes in Finished Goods = (Closing Finished Goods - Opening Finished Goods) + (Sales of Finished Goods - Production of Finished Goods)
Changes in Work in Progress = (Closing Work in Progress - Opening Work in Progress) + (Production of Finished Goods - Production of Work in Progress)
These formulas are derived from the accounting equation by rearranging the terms and substituting the production and sales of finished goods and work in progress.
The changes in finished goods reflect the net effect of production and sales of finished goods on the finished goods inventory. The changes in work in progress reflect the net effect of production of finished goods and work in progress on the work in progress inventory.
Procedures
The procedures for calculating changes in inventories of finished goods and work in progress in excel formula are as follows:
- Step 1: Enter the opening and closing balances of finished goods and work in progress inventories in cells A2 and B2, respectively.
- Step 2: Enter the production and sales of finished goods and work in progress in cells C2 and D2, respectively.
- Step 3: Enter the formula for changes in finished goods in cell E2:
=(B2-A2)+(D2-C2) - Step 4: Enter the formula for changes in work in progress in cell F2:
=(B2-A2)+(C2-D2) - Step 5: Format the cells as currency or number with two decimal places.
Example
To illustrate the calculation of changes in inventories of finished goods and work in progress in excel formula, we will use the following scenario:
- A business has an opening balance of $100,000 for finished goods inventory and $50,000 for work in progress inventory at the beginning of the year.
- The business produces $500,000 worth of finished goods and $400,000 worth of work in progress during the year.
- The business sells $450,000 worth of finished goods and $350,000 worth of work in progress during the year.
- The business has a closing balance of $120,000 for finished goods inventory and $60,000 for work in progress inventory at the end of the year.
Using the excel formula, we can calculate the changes in inventories of finished goods and work in progress as follows:
| Inventory | Opening Balance | Closing Balance | Production | Sales | Changes |
|---|---|---|---|---|---|
| Finished Goods | $100,000 | $120,000 | $500,000 | $450,000 | $30,000 |
| Work in Progress | $50,000 | $60,000 | $400,000 | $350,000 | $10,000 |
The changes in finished goods inventory are calculated as: =(B2-A2)+(D2-C2) = ($120,000 - $100,000) + ($450,000 - $500,000) = $30,000
The changes in work in progress inventory are calculated as: =(B2-A2)+(C2-D2) = ($60,000 - $50,000) + ($400,000 - $350,000) = $10,000
The results show that the business has increased its finished goods inventory by $30,000 and its work in progress inventory by $10,000 during the year. This means that the business has produced more goods than it sold, which indicates a positive contribution to the GDP and a negative impact on the cash flow and profitability.
Other Approaches
There are other approaches for calculating changes in inventories of finished goods and work in progress, such as using the cost of goods manufactured (COGM) and the cost of goods sold (COGS) formulas. The COGM is the total cost of producing finished goods in a given period. The COGS is the total cost of selling finished goods in a given period. The formulas for COGM and COGS are as follows:
COGM = Opening Work in Progress + Production Costs - Closing Work in Progress
COGS = Opening Finished Goods + COGM - Closing Finished Goods
These formulas are based on the assumption that the production costs are equal to the value of the goods produced. Using these formulas, we can calculate the changes in inventories of finished goods and work in progress as follows:
Changes in Finished Goods = COGS - Sales of Finished Goods
Changes in Work in Progress = COGM - Production of Finished Goods
These formulas are equivalent to the formulas we used earlier, as we can prove by substituting the COGM and COGS formulas into them. However, these formulas may require more information and calculations than the formulas we used earlier, as they involve the production costs and the opening and closing balances of both finished goods and work in progress inventories. Therefore, the formulas we used earlier may be simpler and more convenient for calculating changes in inventories of finished goods and work in progress in excel formula.
