Welcome to this article with the aim of helping you with no less than 11 BTS MCO 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.
The 11 BTS MCO inventory management exercises cover storage cost, transfer cost, possession cost, alert stock calculation, minimum stock.
In this section:
Application: Oceanides
States :
Oceanides is a retail company, which operates in the fashion sector. Its inventory management process is crucial to maximize profits and minimize costs. Recently, the company recorded the following inventory transactions:
– Day 1: Initial Stock: 2800 products
– Day 2: Reception of a delivery of 500 scarves
– Day 2: Sale of 300 scarves
– Day 5: Sale of 400 scarves
– Day 6: Reception of a delivery of 700 scarves
– Day 7: Sale of 600 scarves
– Day 10: Reception of a delivery of 900 scarves
Work to do :
1. Calculate the stock of scarves at the end of each day?
2. Evaluate inventory management performance after 10 days?
3. What are the potential implications of inventory levels on the company's cash flow?
4. Propose recommendations to improve inventory management at Oceanides.
5. What role can management play in inventory management?
Proposed correction:
1. The stock of scarves at the end of each day can be calculated as follows:
| Day | Initial Stock | Purchase | Selling | Final Stock |
|---|---|---|---|---|
| 1 | 2800 | 0 | 0 | 2800 |
| 2 | 2800 | 500 | 300 | 3000 |
| 5 | 3000 | 0 | 400 | 2600 |
| 6 | 2600 | 700 | 0 | 3300 |
| 7 | 3300 | 0 | 600 | 2700 |
| 10 | 2700 | 900 | 0 | 3600 |
2. After 10 days, Oceanides has managed to maintain a stable level of inventory while making sales. However, the company appears to have excess inventory, which could become problematic if sales momentum slows down.
3. High inventory levels can lead to additional costs for the company in terms of storage, insurance, etc. They can also tie up cash that could be used elsewhere in the company. On the other hand, too low an inventory level can lead to a stock-out, which could negatively affect customer satisfaction and loyalty.
4. Oceanides should consider implementing a real-time inventory management system to track current inventory levels accurately and facilitate decision-making on replenishment orders. In addition, the company could use the “Just in Time” system to minimize storage costs and maximize efficiency.
5. Management is responsible for developing and implementing policies and procedures for inventory management. This may involve setting a target stock level, approving the purchase of new merchandise, and monitoring and regularly reviewing stock levels and procedures.
Summary of Formulas Used:
| Formulas | Definition |
|---|---|
| Initial Stock (IS) | This is the stock at the beginning of the period. |
| Purchases (A) | Goods added to inventory during the current period. |
| Sales (V) | These are the goods removed from stock for sale during the current period. |
| Final Stock (FS) | This is the stock at the end of the period. Closing Stock = Opening Stock + Purchases – Sales. |
Application: Glam' Shoes
States :
You are the management controller of the Glam' shoe store located in the center of Nice. Your manager wants you to carry out an analysis of the stock management of the "Derbies Chic" shoe model which is in high demand by customers.
Here is the information you have:
– Purchase price excluding tax (PA HT): €40
– Sales price excluding tax (PV HT): €90
– Quantity sold: 200 pairs
– Initial stock: 250 pairs
– Final stock: 150 pairs
– VAT rate: 20%
Work to do :
1. Calculate the overall margin on sales of “Chic Derbies”.
2. Calculate the margin rate on sales of “Chic Derbies”.
3. Calculate the markup rate on sales of “Chic Derbies”.
4. Calculate the cost of goods sold (COGS).
5. Calculate the inventory turnover rate.
Proposed correction:
1. To calculate the overall margin, we use the following formula: Overall margin = Unit margin x quantity sold. Unit margin = PV HT – PA HT. Therefore, Overall margin = (€90 – €40) x 200 = €10.
2. The margin rate is calculated as follows: Margin rate = ((PV HT – PA HT) ÷ PA HT) x 100. Therefore, Margin rate = ((90 € – 40 €) ÷ 40 €) x 100 = 125%.
3. To determine the markup rate, we use this formula: Markup rate = ((PV HT – PA HT) ÷ PV HT) x 100. Therefore, the markup rate = ((90 € – 40 €) ÷ 90 €) x 100 = 55,56%.
4. CAMV is calculated as follows: CAMV = (Initial stock + Purchases) – Final stock = (250 + 200) – 150 = 300 x €40 (purchase price) = €12.
5. The inventory turnover rate is calculated as follows: Inventory turnover rate = CAMV ÷ ((Initial inventory + Final inventory) ÷ 2) = €12000 ÷ ((250 + 150) ÷ 2) = 57,14 times.
Summary of Formulas Used:
| Overall margin | Unit margin x Quantity sold |
|---|---|
| Margin rate | ((PV HT – PA HT) ÷ PA HT) x 100 |
| Brand taxes | ((PV HT – PA HT) ÷ PV HT) x 100 |
| CAMV | (Initial stock + Purchases) – Final stock |
| Inventory turnover rate | CAMV ÷ ((Initial stock + Ending stock) ÷ 2) |
Application: The Good Deal
States :
La Bonne Affaire is an independent supermarket located in a small town. They have recently noticed an increase in their costs, largely due to inefficient inventory management. The finance department manager has gathered the following information:
– Initial stock on January 1: €80
– Purchases of the year: €150
– Sales of the year: €230
– Final stock as of December 31: €100
Work to do :
1. Calculate the average stock for the year.
2. Determine the inventory turnover rate.
3. Calculate the average storage duration.
4. Although the turnover is high, the manager has noticed high storage costs. Why might this be happening?
5. What strategies could management adopt to improve inventory management in the supermarket?
Proposed correction:
1. Average stock = (Initial Stock + Final Stock) ÷ 2 = (€80 + €000) ÷ 100 = €000.
2. Cost of goods sold = Opening Stock + Purchases – Closing Stock = €80 + €000 – €150 = €000.
Inventory Turnover Ratio = Cost of Goods Sold ÷ Average Inventory = €130 ÷ €000 = 90.
3. Average storage duration = 365 days ÷ Inventory turnover rate = 365 ÷ 1,44 = 253 days.
4. 253 days is a fairly long storage period. This means that the goods remain in the warehouse for a long time before being sold. This results in high storage costs and tied up capital that is not used productively.
5. To improve inventory management, the supermarket could adopt a variety of strategies. This could include implementing just-in-time (JIT) replenishment strategies, optimizing replenishment levels, implementing a computerized inventory management system, and regularly retraining staff on inventory management best practices.
Summary of Formulas Used:
"`
Initial Stock + Purchases – Sales = Final Stock
Average stock = (Initial stock + Final stock) ÷ 2
Inventory Turnover Ratio = Cost of Goods Sold ÷ Average Inventory
Average storage duration = 365 days ÷ Inventory turnover rate
"`
Application: OptiStock
OptiStock is a company that specializes in selling electronic products. You have recently been hired as an inventory manager. You have received the following information about a particular product, the iPad Air:
– Initial stocks (start of period): 500 units
– Purchases during the period: 1000 units at €450 excluding VAT/unit
– Sales during the period: 800 units at €750 excluding VAT/unit
– Final stocks (end of period): 700 units
The applicable VAT rate is 20%.
States :
Your role as an inventory manager is to provide essential financial information about iPad Air product inventory.
Work to do :
1. What is the purchase cost excluding tax of iPads purchased during the period?
2. What is the amount excluding tax made on sales during the period?
3. What is the margin rate on purchase cost?
4. What is the markup rate on the sale price?
5. What is the value of the ending stock?
Proposed correction:
1. The purchase cost excluding tax of iPads is 1000 units x €450/unit = €450.
2. The amount excluding tax realized on sales is 800 units x €750/unit = €600.
3. The margin rate is calculated by the formula ((PV HT – PA HT) ÷ PA HT) x 100. Let us replace with the given values: ((750 € – 450 €) ÷ 450 €) x 100 = 66,67%.
4. The markup rate is given by the formula ((PV HT – PA HT) ÷ PV HT) x 100. Let us replace with the given values: ((750 € – 450 €) ÷ 750 €) x 100 = 40%.
5. The value of the ending stock is given by the purchase cost of the remaining products. In this case, there are 700 iPads remaining that were purchased at €450/unit, so: 700 x €450 = €315.
Summary of Formulas Used:
| Concept | Formulas |
|---|---|
| Purchase cost excluding VAT | Number of units x Unit cost excluding VAT |
| Sales amount excluding VAT | Number of units sold x Unit selling price excluding VAT |
| Margin rate | ((Selling price excluding VAT – Purchase cost excluding VAT) ÷ Purchase cost excluding VAT) x 100 |
| Brand taxes | ((Selling price excluding VAT – Purchase cost excluding VAT) ÷ Selling price excluding VAT) x 100 |
| Final stock value | Number of units in stock x Unit purchase cost excluding VAT |
Application: Atout Sport
States :
Atout Sport Company is a sports specialty store that sells various items ranging from clothing, shoes to sports equipment. The company wants to optimize its inventory management and conduct a financial analysis to improve the performance of its business. It needs an expertise on its figures for the 2020 fiscal year.
The company Atout Sport buys pairs of running shoes at €80 per unit excluding tax. It resells them at €120 per unit excluding tax. The VAT rate is 20%. The company Atout Sport sold 1000 pairs of these shoes during the year 2020.
Work to do :
1. What is the total purchase price excluding and including VAT of the shoes?
2. What is the total selling price excluding and including VAT of the shoes?
3. What is the overall margin and margin rate achieved on these shoes?
4. What is the unit margin and markup rate achieved on these shoes?
5. What is the amount of VAT deductible and collected by the company?
Proposed correction:
1. The total purchase price excluding VAT of the shoes is €80 x 1000 = €80. The total purchase price including VAT is €000 x (80 + 000/1) = €20.
2. The total selling price excluding VAT of the shoes is €120 x 1000 = €120. The total selling price including VAT is €000 x (120 + 000/1) = €20.
3. The overall margin made on these shoes is (€120 – €80) x 1000 = €40. The margin rate is ((€000 -€120) ÷ €80) x 80 = 100%.
4. The unit margin made on these shoes is €120 – €80 = €40. The markup rate is ((€120 -€80) ÷ €120) x 100 = 33,33%.
5. The amount of deductible VAT is €80 x (000/20) = €100. And the amount of collected VAT is €16 x (000/120) = €000.
Summary of Formulas Used:
| Total purchase price excluding tax | Unit price HT x Quantity purchased |
|---|---|
| Total purchase price including VAT | Total HT PA x (1 + VAT rate/100) |
| Total sales price excluding tax | PV HT unit x Quantity sold |
| Total selling price including VAT | Total PV HT x (1 + VAT rate/100) |
| Overall margin | (Unit PV HT – Unit PA HT) x Quantity sold |
| Margin rate | ((Unit HT PV – Unit HT PA) ÷ Unit HT PA) x 100) |
| Unit margin | PV HT unit – PA HT unit |
| Brand taxes | ((Unit HT PV – Unit HT PA) ÷ Unit HT PV) x 100) |
| Deductible VAT | Total HT PA x (VAT rate/100) |
| VAT collected | Total PV HT x (VAT rate/100) |
Application: Chic Furniture
States :
The company Chic Furniture sells different types of home furniture. Their best seller is a luxury teak chaise longue. Their initial stock of this chair at the beginning of the year was 400 units. During the year, the company sold 1 chairs and repurchased 500. Therefore, the company's closing stock at the end of the year is 1 units.
Each chair is purchased at €80 excluding VAT and sold at €120 excluding VAT. The storage costs per unit per year are €5. In addition, there is an ordering cost of €500 per order.
Work to do :
1. Calculate the total cost of procurement for the year.
2. Calculate the total storage cost for the year.
3. Estimate the total cost of inventory for the year.
4. Calculate the inventory turnover rate.
5. Determine if the current inventory level is optimal.
Proposed correction:
Answer 1:
Total cost of placing for the year = Cost of placing per order x Number of Orders.
As it is mentioned that the company repurchased 1700 during the year, we assume that each order is for 1700 units.
So the total cost of handover for the year = €500 x (1 / 700) = €1.
Answer 2:
Total storage cost for the year = Storage cost per unit per year x Average inventory.
Average inventory = (Initial inventory + Ending inventory) ÷ 2 = (400 + 600) ÷ 2 = 500.
So the total storage cost for the year = €5 x 500 = €2.
Answer 3:
Total inventory cost for the year = Total transfer cost + total storage cost = €500 + €2 = €500.
Answer 4:
Inventory turnover rate = Number of chairs sold ÷ Average inventory = 1 ÷ 500 = 500 times.
Answer 5:
The inventory turnover ratio is 3, which means that the company has sold the equivalent of its inventory three times in the year. This is a good indicator of inventory management. However, optimizing the inventory level depends on other factors such as expected demand, replenishment time, etc. It is therefore up to the company to determine whether its inventory level is optimal.
Summary of Formulas Used:
| Designation | Formulas |
|---|---|
| Total cost of procurement for the year | Cost of placing order x Number of Orders |
| Average stock | (Initial stock + Final stock) ÷ 2 |
| Total storage cost for the year | Storage cost per unit per year x Average inventory |
| Total cost of inventory for the year | Total cost of handover + total storage cost |
| Inventory turnover rate | Number of chairs sold ÷ Average inventory |
Application: The Good Bread Bakery
States :
In a small provincial town, the bakery "Bon Pain" is known and appreciated by all the inhabitants for the quality of its products and its service. Mr. Bonpain, the owner, is in charge of stock management and he works hard for the efficiency of his business.
The most crucial element for him is flour. His initial stock for the month of January is 500 kg. During the month, Mr. Bonpain bought an additional 300 kg of flour and used 600 kg to make bread, pastries, etc.
Work to do :
1. Calculate the ending stock of flour for the month of January.
2. Determine the average inventory for the month of January.
3. Estimate the inventory turnover ratio for the month of January.
4. Calculate the number of storage days for the month of January.
5. Analyze Mr. Bonpain's inventory management for the month of January.
Proposed correction:
1. The ending stock is calculated by subtracting the amount of flour used from the beginning stock plus purchases. So in this case it is (500kg + 300kg) – 600kg = 200kg.
2. Average stock is calculated by taking the average of the opening stock and the ending stock. So in this case it is (500 kg + 200 kg) ÷ 2 = 350 kg.
3. The inventory turnover ratio is calculated by dividing consumption by average inventory. So here it is 600 kg ÷ 350 kg = 1,71 times.
4. The number of days in storage is calculated by dividing the number of days in the month by the inventory turnover ratio. So here it is 31 days ÷ 1,71 = 18,13 days.
5. According to the above calculations, we can say that Mr. Bonpain's inventory management is quite efficient. He manages to maintain a reasonable average stock and turns it over about 1,71 times per month. However, he probably needs to optimize his purchases to reduce the number of days of storage.
Summary of Formulas Used:
| Final Stock | = Initial stock + Purchases – Consumption |
| Average Stock | = (Initial Stock + Final Stock) ÷ 2 |
| Inventory turnover ratio | = Consumption ÷ Average stock |
| Number of days of storage | = Number of days in month ÷ Inventory turnover ratio |
Application: TechRevolution
States :
Located in the heart of Paris, the company TechRevolution specializes in the sale of high-tech electronic products. At the end of 2020, the company's manager, Mr. Durand, questions his inventory management, particularly for flagship products such as the latest smartphones. He provides the following information: the initial stock of smartphones in 2020 was 200 units. Purchases made in 2020 were 1500 units. Smartphone sales for the year 2020 amount to 1450 units. The replenishment time is estimated at an average of 7 days and the daily demand for smartphones is 10 units. The unit purchase price of a smartphone is €300.
Work to do :
1. Calculate the final stock of smartphones for the year 2020.
2. Determine the cost of goods sold for the year 2020.
3. Calculate safety stock.
4. Calculate the inventory turnover rate for the year 2020.
5. Determine the average stock for the year 2020.
Proposed correction:
1. The final stock of smartphones for the year 2020 is calculated as follows: Initial stock (200 units) + Purchases (1500 units) – Sales (1450 units) = 250 units
2. The cost of goods sold for the year 2020 is calculated as follows: Unit purchase price (€300/unit) x Quantity sold (1450 units) = €435
3. Safety stock is calculated as follows: Daily demand (10 units) x Replenishment time (7 days) = 70 units. Since there is no alert stock mentioned, the safety stock is therefore 70 units.
4. The inventory turnover rate for the year 2020 is calculated as follows: Cost of goods sold (€435) ÷ Average Inventory. Knowing that the Average Inventory is (Beginning Inventory (000 units) + Ending Inventory (200 units)) ÷ 250 = 2 units, the turnover rate is therefore: €225 ÷ 435 = 000 times.
5. The average stock for the year 2020 is: (Initial stock (200) + Final stock (250)) ÷ 2 = 225 units.
Summary of Formulas Used:
| Packages | Explanations |
|---|---|
| Initial stock + Purchases – Sales = Final stock | This is the basic inventory management formula, which allows calculating the final stock after a certain period of activity. |
| Cost of goods sold = Unit purchase price x Quantity sold | This formula is used to determine the purchase cost of products that have been sold. |
| Safety Stock = (Daily Demand x Replenishment Time) + Alert Stock | This is the formula used to calculate the safety stock to avoid the risk of stock shortages. |
| Inventory Turnover Ratio = Cost of Goods Sold ÷ Average Inventory | This formula shows the number of times the company sells and replaces its inventory during a given period. |
| Average stock = (Initial stock + Final stock) ÷ 2 | This formula is used to calculate the average stock over a certain period. |
Application: Durand Optics
States :
Optique Durand, a company located in Nice, specializes in the sale of eyeglass frames. In order to optimize its inventory management, it wants to carry out a precise financial analysis of its inventory.
The information available is as follows:
– Stock at the start of the financial year (as of 01/01/2021): 300 frames at €50 each excluding VAT.
– Purchases during the year: 200 frames at €60 each excluding VAT.
– Sales made during the year: 350 frames at €100 each excluding VAT.
– End of year stock (as of 31/12/2021): 150 frames.
– VAT rate: 20%
Work to do :
1. Calculate the value of the opening and closing inventory.
2. Calculate the consumption for the year.
3. Calculate the overall margin achieved.
4. Calculate margin rate and brand rate.
5. What can we deduce from these results for Optique Durand?
Proposed correction:
1. The value of the initial stock = quantity x unit price = 300 x 50 € = 15 €
Final stock value = quantity x unit price = 150 x €60 = €9
2. Consumption for the year = value of initial stock + purchases – value of final stock = €15 + (000 x €200) – €60 = €9
3. The overall margin achieved = Selling price excluding tax – Purchase price excluding tax = (350 x €100) – €12 = €000
4. Margin rate = ((Sale price excluding VAT – Purchase price excluding VAT) ÷ Purchase price excluding VAT) x 100 = ((€100 – €60) ÷ €60) x 100 = 66,67%
The markup rate = ((Sale price excluding VAT – Purchase price excluding VAT) ÷ Sale price excluding VAT) x 100 = ((€100 – €60) ÷ €100) x 100 = 40%
5. Optique Durand can conclude that it has a high margin compared to its purchase price, which is good for profitability. However, its markup rate is 40%, which indicates a high selling price, which could be unfavorable for the company's competitiveness in the market.
Summary of Formulas Used:
| Concept | Formulas |
|---|---|
| Stock value | Quantity x Unit Price |
| Consumption of the year | Value of initial stock + Purchases – Value of final stock |
| Overall margin | Sales price excluding tax – Purchase price excluding tax |
| Margin rate | ((Selling price excluding tax – Purchase price excluding tax) ÷ Purchase price excluding tax) x 100 |
| Brand taxes | ((Sales price excluding tax – Purchase price excluding tax) ÷ Sales price excluding tax) x 100 |
Application: TechnoPro
States :
TechnoPro is a retailer of electronics. In January, the company started with an initial inventory of 200 televisions at €400 each. During the month, the company sold 120 televisions, and purchased 80 new televisions for €450 each. The transportation costs for these purchases amounted to €2.
Work to do :
1. Calculate the company's closing inventory at the end of the month.
2. Determine the unit purchase cost of the newly purchased televisions.
3. Suppose the company wants to maintain a safety stock equivalent to one week of sales and the safety factor is 1,5. What would the safety stock be?
4. Calculate the company's average inventory for the month.
5. If the storage cost is €10 per unit and the holding cost is €5 per unit, calculate the inventory carrying cost for the company.
Proposed correction:
1. Final stock = Initial stock + Purchases – Sales = 200 + 80 – 120 = 160 televisions.
2. Unit purchase cost = (Purchase price excluding VAT + Transport costs) ÷ Quantity purchased = (€450 + €2) ÷ 000 = €80 per unit.
3. Average consumption per week = Sales ÷ 4 (assuming a 4-week month) = 120 ÷ 4 = 30 televisions. Safety stock = (Average consumption x Replenishment time) x Safety factor = (30 x 1) x 1,5 = 45 televisions.
4. Average stock = (Initial stock + Ending stock) ÷ 2 = (200 + 160) ÷ 2 = 180 televisions.
5. Cost of ownership = (Storage cost + Holding cost) x Average inventory = (€10 + €5) x 180 = €2.
Summary of Formulas Used:
Here is a summary table of the formulas we used, so you can refer to them in the future:
| Formulas | Description |
|---|---|
| Final stock = Initial stock + Purchases – Sales | Inventory Management Formula |
| Unit purchase cost = (Purchase price excluding VAT + Transport costs) ÷ Quantity purchased | Unit purchase cost formula |
| Safety stock = (Average consumption x Replenishment time) x Safety coefficient | Safety stock formula |
| Average stock = (Initial stock + Final stock) ÷ 2 | Average stock formula |
| Carrying cost = (Storage cost + Holding cost) x Average inventory | Cost of Ownership Formula |
Application: SuperFruits Co.
States :
SuperFruits Co. is a company that specializes in the sale of exotic fruits. Their stock of mangoes is particularly popular with their customers. Recently, management has noticed irregularities in the management of the mango stock. Monthly closing balances vary greatly and there is often a stock shortage towards the end of the month.
The following data was collected for the month of March:
– Opening stock of mangoes: 950 kg
– Purchases of the month: 2 kg
– Sales of the month: 2 kg
– Purchase price of mango: €6/kg
– Selling price of mango: €10/kg
– The current VAT rate is 20%
– The margin required by the company is 30%
Work to do :
1. Calculate the March Closing Stock for Mangoes.
2. Calculate the margin rate achieved in March.
3. Calculate the markup rate achieved in March.
4. Evaluate the performance of mango inventory management.
5. Suggest improvements to avoid stock shortages.
Proposed correction:
1. Closing stock is calculated by subtracting sales from the total opening stock and purchases. In this case, it is: 950 kg + 2 kg – 000 kg = 2 kg of mangoes in stock at the March closing.
2. The margin rate achieved in March is calculated using the formula (PV HT – PA HT) ÷ PA HT x 100. Here, PV HT (10 €/kg) and PA HT (6 €/kg). We therefore obtain: ((10 € – 6 €) ÷ 6 €) x 100 = 66,67%.
3. The markup rate achieved in March is calculated using the formula: ((PV HT – PA HT) ÷ PV HT) x 100. We have the following: ((10 € – 6 €) ÷ 10 €) x 100 = 40%.
4. The performance of mango inventory management is worrying. Indeed, although the margin rate and the mark-up rate are higher than the margin required by the company, the closing stock is insufficient to cover next month's sales. There is therefore a risk of stock-out.
5. To improve this situation, SuperFruits Co. could increase its purchases of mangoes to avoid stock shortages. In addition, they could implement a sales forecasting system to better anticipate variations in demand.
Summary of Formulas Used:
– Closing stock = Opening stock + Purchases – Sales
– Margin rate = ((Sale price excluding VAT – Purchase price excluding VAT) ÷ Purchase price excluding VAT) x 100
– Markup rate = ((Selling price excluding VAT – Purchase price excluding VAT) ÷ Selling price excluding VAT) x 100