Welcome to this article aimed at helping you with 11 corrected inventory management exercises from the Operational Management subject of the BTS MCO.
If you would like to first review the course on the same theme, Inventory Management, I invite you to read my article Inventory Management: The 7 Key Points to Master and also the article Supply Management: The 3 essential principles.
In this section:
- Application: BioLife Grocery Store
- Application: Eclat d'Or Jewelry
- Application: BioFruits Company
- Application: Euras
- Application: Sweet Cookies
- Application: Chez Paul Supermarket
- Application: The Bakery “Les Délices”
- Application: S-Hyper Supermarket
- Application: Superhoney
- Application: “Le Bon Deal” store
- Application: Meringuerie
Application: BioLife Grocery Store
States :
BioLife Company is a grocery store that specializes in selling organic products. The company's management is concerned about its inventory management and wants to do a detailed analysis.
There are two flagship products that attract the most attention from management because of their divergent stock turnover. The first, an organic orange juice, has a lead time of 10 days and the daily demand is 50 units. The second, a pack of organic rice, has a lead time of 5 days and the daily consumption is 10 units. The safety stock information is 40 units for organic orange juice and 20 units for organic rice. However, BioLife management believes that the safety stock can be optimized.
Finally, management would like to know the different costs associated with managing these stocks.
Work to do :
1. Calculate the alert stock for organic orange juice and organic rice.
2. Propose a new safety stock for organic orange juice and organic rice based on the data provided by management.
3. What impact will this new proposal have on the alert stock?
4. Estimate the stockout costs if demand exceeds forecast for these two products.
5. Propose inventory management optimization strategies for the company.
Proposed correction:
1. The alert stock is calculated using the formula: Daily demand x Lead time + Safety stock. So, for organic orange juice, the alert stock is 50 x 10 + 40 = 540 units. For organic rice, it is 10 x 5 + 20 = 70 units.
2. The new safety stock values can be based on demand volatility. Since management believes that the safety stock can be optimized, we will reduce the safety stock by 10% for organic orange juice and 15% for organic rice. Therefore, the new safety stock is 40 – 4 = 36 units for orange juice and 20 – 3 = 17 units for organic rice.
3. With these new safety stock values, the alert stock will be 50 x 10 + 36 = 536 units for organic orange juice and 10 x 5 + 17 = 67 units for organic rice.
4. Stockout costs depend on many factors such as the duration of the stockout, the cost of placing an emergency order, and the costs associated with loss of customers and brand image. Accurate estimation of these costs requires additional data.
5. To optimize inventory management, the company can consider using a continuous replenishment system, implementing automatic inventory monitoring, good supplier relationship management to reduce lead times, and finally good demand forecasting using analytical tools to minimize stockouts.
Summary of Formulas Used:
| Concept | Formulas |
|---|---|
| Alert stock | Daily demand x Supply lead time + Safety stock |
| New Safety Stock | Safety stock – optimization percentage |
Application: Eclat d'Or Jewelry
States :
The Eclat d'Or jewelry store has an initial stock of 5000 pieces of jewelry with a unit value of €100. During the year, the jewelry store made 10 purchases of jewelry with a unit value of €000 and sold 80 pieces of jewelry to the public.
Work to do :
1) What is the final stock of jewelry for this year?
2) Calculate the inventory turnover rate.
3) How many times was the stock of jewelry renewed during this year?
4) What is the value of the ending stock of jewelry for this year?
5) What are the advantages and disadvantages of a high inventory turnover rate?
Proposed correction:
1) To calculate the ending inventory, we use the formula “Ending Inventory = Beginning Inventory + Purchases of the year – Sales of the year”. So the ending inventory is 5000 + 10 – 000 = 12 pieces of jewelry.
2) To calculate the inventory turnover rate, we use the formula “Inventory turnover rate = Sales of the year / ((Beginning inventory + Ending inventory) / 2)”. So the inventory turnover rate is 12 / ((000 + 5000) / 3000) = 2 times.
3) The inventory turnover rate indicates that the jewelry inventory was sold and replenished approximately 2,67 times during this year.
4) The value of the ending inventory can be found by multiplying the number of jewelry in the ending inventory by the unit value of the purchase. Therefore, the value of the ending inventory is 3000 x €80 = €240.
5) A high inventory turnover rate is generally good because it indicates that goods are being sold quickly, which can mean high demand and good sales. However, an inventory turnover rate that is too high can also indicate that the jewelry store does not have enough inventory to meet demand, which could lead to missed sales.
Summary of Formulas Used:
| Packages | Explanations |
|---|---|
| Initial Stock | Total number of jewelry items in stock at the beginning of the year |
| Purchases of the year | Total number of jewelry purchased during the year |
| Sales of the year | Total number of jewelry sold during the year |
| Final Stock = Initial Stock + Purchases for the year – Sales for the year | Total number of jewelry items remaining in stock at the end of the year |
| Inventory turnover rate = Sales for the year / ((Opening inventory + Ending inventory) / 2) | This formula indicates how many times the stock was sold and renewed during the year |
Application: BioFruits Company
States :
The company BioFruits specializes in the sale of organic fruits. It must regularly manage its stock to avoid losses caused by the waste of unsold fruits. The company currently holds a stock of 3000 kg of organic apples. The following data was collected:
– Initial stock of organic apples: 2000 kg
– Purchases of organic apples over the year: 15000 kg
– PV excluding tax of one kg of organic apples: €2
– HT PA of one kg of organic apples: €1,30
Work to do :
1. What is the consumption of organic apples this year?
2. Calculate the margin rate per kg of organic apples.
3. Calculate the markup rate per kg of organic apples.
4. Calculate the company's overall margin for the year.
5. How much money would the company have saved if it had managed to sell all the remaining inventory?
Proposed correction:
1. The consumption of the year can be calculated using the formula: Initial stock + Purchases – Final stock. That is: 2000 kg + 15000 kg – 3000 kg = 16000 kg. The company therefore consumed 16000 kg of organic apples during the year.
2. The margin rate is calculated using the formula: ((PV HT – PA HT) ÷ PA HT) x 100). That is: ((€2 – €1,30) ÷ €1,30) x 100 = 53,84%. The company's margin rate for the kg of organic apples is 53,84%.
3. The mark-up rate is calculated using the formula: ((PV HT – PA HT) ÷ PV HT) x 100. That is: ((€2 – €1,30) ÷ €2) x 100 = 35%. The company's mark-up rate for the kg of organic apples is 35%.
4. The overall margin is calculated by multiplying the unit margin by the quantity consumed. Unit margin = PV HT – PA HT = €2 – €1,30 = €0,70/kg. Overall margin = Unit margin x consumption = €0,70 x 16000 kg = €11200. The company's overall margin for the year is therefore €11200.
5. If the company had sold all the remaining stock, it would have made an additional margin of 3000 kg x unit margin. That is: 3000 kg x €0,70/kg = €2100. The company would therefore have saved €2100.
Summary of Formulas Used:
| Formulas | Explanation |
|---|---|
| Consumption = Initial stock + Purchases – Final stock | This formula is used to calculate the consumption for the year. |
| Margin rate = ((PV excluding tax – PA excluding tax) ÷ PA excluding tax) x 100 | This formula is used to calculate the margin rate on the product. |
| Brand rate = ((PV excluding tax – PA excluding tax) ÷ PV excluding tax) x 100 | This formula is used to calculate the markup rate on the product. |
| Overall margin = Unit margin x Quantity consumed | This formula is used to calculate the company's overall margin for the year. |
| Unit margin = PV excluding tax – PA excluding tax | This formula is used to calculate the margin for each unit of product. |
Application: Euras
States :
The building materials company Euras, specializing in construction materials, has registered the following information for a specific product:
– Initial stock: 1 units
– Purchases over the year: 3 units
– Sales over the year: 2 units
– Final stock: ?
Work to do :
1. Calculate the company's ending inventory.
2. Calculate the cost of goods sold (COGS) assuming each unit costs €50.
3. What would be the CMV if the unit costs €60?
4. What inventory management method would be most beneficial for the business?
5. What is the impact of poor inventory management on the business?
Proposed correction:
1. Ending inventory equals beginning inventory plus purchases for the year minus sales for the year.
So, Final Stock = Initial Stock + Purchases for the year – Sales for the year
Final Stock = 1 units + 500 units – 3 units = 500 units
2. Cost of goods sold equals the number of units sold multiplied by the unit cost.
So, CMV = units sold x unit cost
CMV = 2 units x €800 = €50
3. If each unit costs €60, the COGS would be 2 units x €800 = €60.
4. To maximize profitability and avoid excess or insufficient inventory, the company should consider “just-in-time” inventory management. This method minimizes storage costs, reduces the risk of merchandise deterioration and improves operational efficiency.
5. Poor inventory management can have a significant negative impact on the business. It can lead to unnecessary storage costs, lack of liquidity due to excessive investment in inventory, loss of sales if inventory is not available when customers need it, and decreased operational efficiency.
Summary of Formulas Used:
- Final Stock = Initial Stock + Purchases for the year – Sales for the year
- CMV = units sold x unit cost
Application: Sweet Cookies
States :
Sweet Cookies is a company specializing in the manufacture and sale of artisanal cookies. They are particularly meticulous about the quality of their production, which requires optimal management of their stock of raw materials and ingredients, especially since some of these products are perishable.
As of January 1, Sweet Cookies has a stock of 200 kg of flour worth €600, 100 kg of chocolate valued at €500 and 75 kg of butter for a price of €300.
During the month of January, the company purchased an additional 80 kg of flour for €240, 50 kg of chocolate for €275 and 30 kg of butter for €120.
The January biscuit sales required the consumption of 180 kg of flour, 90 kg of chocolate and 55 kg of butter.
Work to do :
1. Calculate the value of the ending inventory for each raw material at the end of the month.
2. How could Sweet Cookies improve its inventory management?
3. Calculate the inventory turnover rate for each raw material.
4. Using the inventory turnover rate, analyze Sweet Cookies' level of inventory management.
5. Propose action recommendations for Sweet Cookies inventory management based on the analysis of inventory turnover rate.
Proposed correction:
1. The value of the final stock for each raw material at the end of the month is calculated using the following formula: Initial Stock + Purchases – Consumption.
– Flour: 200 kg + 80 kg – 180 kg = 100 kg. In terms of value: €600 + €240 – (180 kg * (€600 ÷ 200 kg)) = €300
– Chocolate: 100 kg + 50 kg – 90 kg = 60 kg. In terms of value: €500 + €275 – (90 kg * (€500 ÷ 100 kg)) = €375
– Butter: 75 kg + 30 kg – 55 kg = 50 kg. In terms of value: €300 + €120 – (55 kg * (€300 ÷ 75 kg)) = €180
2. Sweet Cookies could improve its inventory management by trying to reduce its inventory level to reduce its capital immobilization and storage costs, while ensuring that it maintains a sufficient level to cover its production needs and avoid stockouts.
3. The inventory turnover rate is calculated using the following formula: Consumption ÷ (Initial Stock + Final Stock) / 2.
– Flour: 180 kg ÷ ((200 kg + 100 kg) / 2) = 1,2
– Chocolate: 90 kg ÷ ((100 kg + 60 kg) / 2) = 1,125
– Butter: 55 kg ÷ ((75 kg + 50 kg) / 2) = 0,9167
4. The analysis of the inventory turnover rate shows that flour is the most used raw material in the production of biscuits, followed by chocolate and finally butter. This means that stocks of flour and chocolate are renewed more quickly than butter.
5. Sweet Cookies could consider slightly reducing its stocks of butter, which are renewed less quickly than other raw materials, to free up tied up capital. However, this must be done while ensuring that there is no risk of stock shortages. The company could also seek to optimize its purchases of flour and chocolate, which are the most consumed raw materials.
Summary of Formulas Used:
| Spas | Formulas |
|---|---|
| Final stock value | Initial Stock + Purchases – Consumption |
| Inventory turnover rate | Consumption ÷ (Initial Stock + Final Stock) / 2 |
Application: Chez Paul Supermarket
States :
Chez Paul is a supermarket looking to improve its inventory management. You are called in as a consultant to help them analyze their current situation and offer suggestions for improvement. Here is some information you have collected:
– Initial stock (January 1): €75
– Purchases made during the year: €375
– Final stock (December 31): €85
– Sales excluding tax made during the year: €500
Work to do :
1. Calculate the usage of goods for the year.
2. Determine the utilization rate of goods for the year.
3. Calculate the average inventory for the year.
4. Determine the inventory turnover rate for the year.
5. Calculate the number of storage days for the year.
Proposed correction:
1. The usage of goods is calculated by subtracting the ending stock from purchases, after adding the beginning stock. We therefore obtain:
Initial stock + Purchases – Final stock = €75 + €000 – €375 = €000
2. The utilization rate of goods is calculated by dividing the Cost excluding VAT by the Sales excluding VAT. We therefore obtain:
Cost excluding tax / Sales excluding tax = €365 ÷ €000 = 500 or 000%.
3. The average inventory is calculated by adding the opening inventory and the closing inventory, then dividing the total by two. We therefore obtain:
(Initial Stock + Final Stock) ÷ 2 = (€75 + €000) ÷ 85 = €000
4. The inventory turnover rate is calculated by dividing the average inventory by the Cost excluding VAT. We therefore obtain:
Average inventory / Cost excluding tax = €80 ÷ €000 = 365 or 000%.
5. The number of days of storage is calculated by dividing 360 by the inventory turnover rate. We therefore obtain:
360 ÷ Inventory turnover rate = 360 ÷ 0,219 = 1 days, or approximately 643 years.
Summary of Formulas Used:
| Formulas | Description |
| Initial stock + Purchases – Final stock | Use of goods (Purchase Costs Excluding Tax: Cost excluding tax) |
| Cost excluding VAT / Sales excluding VAT | determination of the rate of utilization of goods |
| (Initial Stock + Final Stock) / 2 | Average stock |
| Average stock / Cost excluding VAT | Inventory turnover rate |
| 360 / Inventory turnover rate | Number of days of storage |
Application: The Bakery “Les Délices”
States :
Claire works at Boulangerie “Les Délices”, a local bakery known for its fresh and tasty pastries. Claire is responsible for managing the flour inventory, a key ingredient in their products.
She receives information about the current stock of flour:
1. Initial stock as of January 1, 2021: 300 kg
2. Purchase of flour in 2021: 2 kg
3. Total production in 2021: 1 kg
Work to do :
1. What was the final stock of flour in 2021?
2. What is the average monthly flour consumption in 2021?
3. What is the inventory turnover rate for the year 2021?
4. If Claire orders flour once every 2 months, how many kg should she order each time?
5. If the bakery wants to maintain a safety stock of 150 kg, how many kg should Claire have in stock before placing a new order?
Proposed correction:
1. Final stock 2021 = Initial stock + Purchase – Production = 300 kg + 2000 kg – 1850 kg = 450 kg of flour.
2. Average monthly consumption = total production for the year ÷ number of months in the year = 1850 kg ÷ 12 months = 154,17 kg of flour per month.
3. Inventory turnover rate = total production for the year ÷ average inventory = 1 kg ÷ ((850 kg + 300 kg) ÷ 450) = 2 times per year.
4. The amount of flour that Claire must order every two months is 154,17 kg x 2 months = 308,33 kg of flour.
5. Claire must have 308,33 kg + 150 kg = 458,33 kg of flour in stock before placing a new order.
Summary of Formulas Used:
| Ending stock | = Initial stock + Purchase – Production |
| Average monthly consumption | = total production of the year ÷ number of months in the year |
| Inventory turnover rate | = total production of the year ÷ average stock |
| Bi-monthly order | = average monthly consumption x 2 |
| Stock before new order | = Bi-monthly order + safety stock |
Application: S-Hyper Supermarket
States :
S-Hyper supermarket has recently started selling a new product, an organic chocolate bar, and you are tasked with analyzing its inventory management. Here is the information available for the first week of sales:
– At the beginning of the week, the supermarket had 500 chocolate bars in stock.
– Total sales for the week are 350 chocolate bars.
– The unit purchase cost of the chocolate bar is €0,70.
– The unit selling price to the consumer is €1,50.
– The applicable VAT rate is 5,5%.
Work to do :
1. What is the final stock of chocolate bars at the end of the week?
2. Calculate the value of the ending stock in €.
3. What is the margin rate on each chocolate bar sold?
4. What is the markup rate on each chocolate bar sold?
5. What is the overall margin made on the sale of chocolate bars during the week?
Proposed correction:
1. The ending stock of chocolate bars at the end of the week is 500 (starting stock) – 350 (chocolate bars sold) = 150 chocolate bars.
2. The value of the ending stock is 150 chocolate bars x €0,70 (unit purchase cost) = €105.
3. The margin rate is ((€1,50 – €0,70) ÷ €0,70) x 100 = 114,29%.
4. The markup rate is ((€1,50 – €0,70) ÷ €1,50) x 100 = 53,33%.
5. The overall margin made on the sale of chocolate bars during the week is (€1,50 – €0,70) x 350 = €280.
Summary of Formulas Used:
– Final stock = Initial stock – Quantity sold
– Final stock value = Final stock x Unit purchase cost
– Margin rate = ((Unit selling price – Unit purchase cost) ÷ Unit purchase cost) x 100
– Markup rate = ((Unit selling price – Unit purchasing cost) ÷ Unit selling price) x 100
– Overall margin = (Unit selling price – Unit purchasing cost) x Quantity sold
Application: Superhoney
States :
Supermiel is a company that manufactures and sells honey. It manages its honey inventory in order to optimize production and sales. You have been hired as an inventory manager to help Supermiel better manage its resources.
1. On January 1, the company had 150 jars of honey in inventory. During the month, it produced 1000 jars of honey and sold 800. You are tasked with calculating the company's ending inventory for the month of January.
2. For the month of February, the company plans to produce 1200 jars of honey and sell 1100. You need to calculate the company's inventory level on March 1.
3. In order to avoid a stock shortage, the company wants to maintain a safety stock of 100 jars of honey at the end of March. If it plans to sell 1300 jars of honey, how many should it produce in March assuming that the stock on March 1 is the one calculated in question 2?
4. The unit cost of purchasing a jar of honey varies and Supermiel predicts an average purchase cost of €3 for the month of April. If the company sells each jar of honey at €7 (excluding VAT), what is the company's margin rate?
5. If the VAT rate is 20%, what is the selling price including VAT of the jar of honey?
Work to do :
1. Calculate the company's closing inventory for the month of January.
2. Calculate the company's closing inventory for the month of February.
3. Calculate the total number of jars of honey to be produced in March.
4. Calculate the company's margin rate.
5. Calculate the selling price including tax of the jar of honey.
Proposed correction:
1. January ending stock = Opening stock + Production – Sales = 150 + 1000 – 800 = 350 jars of honey.
2. February ending stock = Opening stock + Production – Sales = 350 + 1200 – 1100 = 450 jars of honey.
3. Production in March = Sales + Safety stock – Initial stock = 1300 + 100 – 450 = 950 jars of honey.
4. Margin rate = ((PV HT – PA HT) ÷ PA HT) x 100) = ((7 € – 3 €) ÷ 3 €) x 100 = 133,33%.
5. Selling price including tax = PV excluding tax x (1 + VAT rate / 100) = €7 x (1 + 20 / 100) = €8,4.
Summary of Formulas Used:
| Concept | Formulas |
|---|---|
| Ending stock | Initial stock + Production – Sales |
| Production required | Sales + Safety Stock – Initial Stock |
| Margin rate | ((PV HT – PA HT) ÷ PA HT) x 100 |
| Sales price including tax | PV HT x (1 + VAT rate / 100) |
Application: “Le Bon Deal” store
Statement:
The store "Le Bon Deal" located in the city center is known for its quality high-tech products. To have enough stock, the company ordered 1000 smartphones at the beginning of the year at a price of €150 each. It is estimated that "Le Bon Deal" will sell around 100 smartphones per month. The cost of placing an order is €100 and the stock holding rate is 20% of the purchase price.
Work to do :
1. What is the total cost of purchasing smartphones for “Le Bon Deal”?
2. What is the total cost of inventory ownership for the company?
3. What is the total cost of orders placed through “Le Bon Deal”?
4. Calculate the company's average inventory for the year.
5. What is the total inventory management cost for “Le Bon Deal”?
Proposed correction:
1. To calculate the total purchase cost for “Le Bon Deal”, we must multiply the quantity of smartphones ordered (1000) by the unit purchase price (€150).
Total purchase cost = Quantity x Unit price = 1000 x 150€ = 150000€
2. The total cost of inventory ownership is calculated by taking 20% of the total purchase cost.
Total cost of ownership = Total cost of purchase x Ownership rate = €150000 x 20% = €30000
3. The total cost of orders is simply the cost of placing an order (€100) multiplied by the total number of orders. In this scenario, we only have information for one order.
Total cost of orders = Cost per order x Number of orders = €100 x 1 = €100
4. Average stock is calculated by dividing the total stock (1000 smartphones) by the number of months in a year (12 months).
Average stock = Total stock ÷ Number of months = 1000 ÷ 12 ? 83 smartphones
5. The total cost of inventory management for the year is equal to the total cost of purchase, added to the total cost of inventory holding and the total cost of orders:
Total management cost = Total purchase cost + Total cost of ownership + Total order cost = €150000 + €30000 + €100 = €180100
Summary of Formulas Used:
| Formulas | Description |
|---|---|
| Total purchase cost = Quantity x Unit price | Calculating the total purchase cost |
| Total Cost of Ownership = Total Cost of Purchase x Ownership Rate | Calculating the total cost of inventory ownership |
| Total cost of orders = Cost per order x Number of orders | Calculating the total cost of orders |
| Average stock = Total stock ÷ Number of months | Calculation of average stock |
| Total Cost of Ownership = Total Cost of Purchase + Total Cost of Ownership + Total Cost of Orders | Calculating the total cost of inventory management |
Application: Meringuerie
States :
La Meringuerie is a food company that has been involved in the production of artisanal meringues since 2010. It has an initial stock of 4 boxes of meringue that it purchased at €000 per unit excluding tax. During 8, it made purchases totaling €2020, and sold 60 boxes of meringue. At the end of the year, the final stock amounted to 000 boxes.
Work to do :
1. Calculate the cost of purchasing the goods available for sale.
2. Determine the cost of goods sold.
3. What is the holding cost of the ending inventory?
4. What is the value of the ending stock?
5. Estimate the level of inventory turnover.
Proposed correction:
1. The cost of purchasing goods available for sale is calculated as follows: (Initial stock x PA excluding VAT) + Purchases for the year. Which is equal to (4 x €000) + €8 = €60.
2. The cost of goods sold is calculated as follows: Cost of goods on hand – cost of holding ending inventory, i.e. €92 – (000 x €2) = €000.
3. The holding cost of the ending inventory is calculated by multiplying the ending inventory by the unit purchase price, which gives: 2 x €000 = €8.
4. The value of the ending inventory is simply the ending inventory multiplied by the purchase price excluding taxes. Thus, the value of the ending inventory will be: 2 x €000 = €8.
5. The inventory turnover level is the quantity of goods sold divided by the average inventory ((opening inventory + closing inventory) ÷ 2). We then obtain: 10 / ((000 + 4) ÷ 000) = 2 times.
Summary of Formulas Used:
| Formulas | Meaning |
|---|---|
| (Initial stock x PA excluding tax) + EM purchases | Cost of purchasing goods available for sale |
| Cost of goods available – (Ending stock x HT PA) | Cost of goods sold |
| Final stock x PA HT | Cost of holding final inventory |
| Final stock x PA HT | Final stock value |
| Quantity sold / ((Initial stock + Final stock) ÷ 2) | Inventory turnover level |