Welcome to this article with the aim of helping you with 3 exercises on Inventory Management from the Operational Management subject of the BTS MCO.
This Inventory Management Exercise theme covers inventory monitoring, monitoring of inventory management indicators, CMUP method and PEPS method stock sheets.
If you would like to first review the course on the same theme, 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.
You will also find in this theme "Stock Management Exercise" concepts of stock management on stock valuation, the evaluation of known and unknown shrinkage.
Also find 9 other exercises on the 20/80 method and other concepts on Inventory Management.
In this section:
Inventory Management Exercise: How to calculate unknown shrinkage
States
You have the following information to analyze the unknown shrinkage of the store. The store distributes products P1, P2, P3, P4 and P5. You are given the purchase prices for these same products: €0,81, €0,85, €1,07, €1,09 and €0,98.
Furthermore, the theoretical stocks are as follows: 7, 14, 8, 11 and 15 and the actual stocks are 3, 10, 5, 13 and 13. In addition, we specify that the store applies a multiplier coefficient of 1,33 and that the turnover of the point of sale is €1 including tax.
Work to do
1/ Analyze the store’s unknown shrinkage.
2/ Determine the amount of the markdown.
3/ Calculate the unknown shrinkage rate. Conclude by taking into account the sector rate which is 1,4%.
Proposed correction
1/ Analyze the store’s unknown shrinkage.
Analysis of product P1 :
We will first calculate the markdown in quantity. To do this we will calculate the difference between the actual stock and the theoretical stock.
Gap = 3 – 7 = – 4
Then we will determine this difference in value by taking into account the purchase price of product P1.
Unknown markdown at PA HT: – 4 x €0,81 = €3,24
Analysis of product P2 :
We will first calculate the markdown in quantity. To do this we will calculate the difference between the actual stock and the theoretical stock.
Gap = 10 – 14 = – 4
Then we will determine this difference in value by taking into account the purchase price of product P2.
Unknown markdown at PA HT: – 4 x €0,85 = €3,40
Analysis of product P3 :
We will first calculate the markdown in quantity. To do this we will calculate the difference between the actual stock and the theoretical stock.
Gap = 5 – 8 = – 3
Then we will determine this difference in value by taking into account the purchase price of product P3.
Unknown markdown at PA HT: – 3 x €1,07 = €3,21
Analysis of product P4 :
We will first calculate the markdown in quantity. To do this we will calculate the difference between the actual stock and the theoretical stock.
Gap = 13 – 11 = 2
Then we will determine this difference in value by taking into account the purchase price of product P4.
Surmark at PA excluding tax: 2 x €1,09 = €2,18
Analysis of product P5 :
We will first calculate the markdown in quantity. To do this we will calculate the difference between the actual stock and the theoretical stock.
Gap = 13 – 15 = – 2
Then we will determine this difference in value by taking into account the purchase price of product P5.
Unknown markdown at PA HT: – 2 x €0,98 = €1,96
If you wish, you can summarize your results in a table, specifying all your calculations outside using references:
2/ Determine the amount of the markdown.
First, you need to calculate the overall markdown of the store by adding up the markdowns of all products.
Total unknown markdown = €3,24 + €3,40 + €3,21 – €2,18 + €1,96 = €9,63.
The loss in terms of turnover is therefore €9,63 x 1,33 or €12,81.
3/ Calculate the unknown shrinkage rate. Conclude by taking into account the sector rate which is 1,4%.
To determine the unknown shrinkage rate, divide the amount of the unknown shrinkage by the net sales figure.
Unknown shrinkage rate = [12,81 / (1 / 336,40)] x 1,2 = 100%
The store's shrinkage rate is lower than the industry average.
Inventory Management Exercise: How to create a CMUP and PEPS stock sheet
States
You work in the T-Vision store. The Flat Screens department offers different varieties of products. Mrs. Lapalette, who is responsible for this department, is asking you to help you prepare stock sheets for her flagship product, the WX-478 screen.
So, you have the following to help your manager figure it all out.
The WX-478 screen retails for €249 excluding VAT. During the month of July, the stock movements for this product were as follows: 01/07: initial stock: 850 units at €175.
Work to do
1/ Complete the stock sheet for the WX-478 screen using the CMUP method after each entry.
2/Create the stock sheet for the WX-478 screen using the PEPS method.
Proposed correction
1/ Complete the stock sheet for the WX-478 screen using the CMUP method after each entry.
In this method, the average stock value after each entry is calculated by applying the following formula:
CMUP after each entry = (stock amount value + entry amount value) / (stock quantity + entry quantity)
The movements must be written chronologically and the amounts calculated. The stock sheet is produced in the form of a table.
(1): You must always start by reporting in the “Entry” column the initial stock which corresponds to the final stock of the previous period.
(2): The initial stock must then be reported in the “Stock” column.
(3): An entry is entered in the “Entry” column. The “Amount” column is calculated by multiplying the quantity by the unit price.
(4): After an entry, we calculate the stock level. For the quantities, we add the quantity that we had available to that corresponding to our entry. We therefore have for 03/07 a quantity of 1 (350 + 850).
(5): To find the amount, we add the previous amount to that of the amount corresponding to our entry. We therefore have for 03/07 236 (750 + 148).
(6): This is the column in which the CMUP is calculated. We apply the formula cited above. Thus we have the following calculation: (148 + 750) / (88 + 000) or 850.
(7): An output is entered in the “Output” column. The “Amount” column is calculated by multiplying the quantity by the unit price.
(8): To evaluate the unit exit price, it is necessary to report the CMUP that has just been calculated (see point 6).
(9): After each movement, the stock must be determined. For the quantities, we make the difference between the quantity that we had available and that corresponding to our exit. We therefore have for 04/07 1 (150 – 1).
(10): The last line of the table corresponds to the final stock of the period, i.e. the month of July.As of 31/07, the T-Vision store has 650 WX478 screens at a unit price of €176,76 for a total amount of €114.
The following three images form the stock sheet (in the same order), but for the sake of visibility it has been divided into 3.
Summary table (section “Entries”):
Summary table (section “Outputs”):
Summary table (part “Stock”):
2/Create the stock sheet for the WX-478 screen using the PEPS method.
In this method, no average is calculated. In the stock sheet, the outputs are valued at the unit entry price. Priority is given to the oldest items.
(1): You must always start by reporting in the “Entry” column the initial stock which corresponds to the final stock of the previous period.
(2): The initial stock must then be reported in the “Stock” column.
(3): An entry is entered in the “Entry” column. The “Amount” column is calculated by multiplying the quantity by the unit price.
(4): You must copy the quantity, unit price and amount without any modification.
(5): We do the same for the day's entry. However, we must position ourselves well below the previous stock.
(6): When issuing an output, for the quantity, we check whether the quantity of the first line of the previous date is greater than the quantity to be issued. Here it is the case because 200 is less than 850.
(7): So we copy the unit price of the line concerned (175 is on the line of 850).
(8): On 03/07 there were 850 screens (on the first line) minus the day's output (200), so there are 650 screens left for this batch.
(9): This same lot is valued at the unit price of 175 euros (reprise of the PU of 03/07).
(10): Each “Amount” is obtained by multiplying the quantity by the unit price.
(11): This batch of 500 screens was not affected by the release so it must be carried over without any modification.
The three previous images form the stock sheet (in the same order), but for the sake of visibility it has been divided into 3.
(12): On 30/07, there is a release of 500 screens. On 18/07, on the first batch there are 250 left in quantity. We can therefore only release 250 initially at the unit price of €175.
(13): In a second step, the output must be completed to reach 500 in quantity. A second line is therefore required in the “Outputs” column with a quantity of 250 which is taken from the second batch of 18/07 at the unit price of €176.
(14): On 30/07, the stock is modified. The batch of 250 dated 18/07 no longer exists. The second batch of 18/07 is divided by 2 (500 – 250), so there are 250 left in quantity. On the other hand, the 3rd and 4th batches remain identical because they were not impacted by the release of the 500 screens.
As of 31/07, the T-Vision store has 250 WX478 screens at a unit price of €176 for a total amount of €19, a second batch of 000 screens at a unit price of €300 for a total of €180 and a third batch of 54 units at a unit price of €000 for a total amount of €100.
Inventory Management Exercise: Calculate inventory management indicators
States
You are an intern at the T-Vision company located in the Doubs department, which distributes electronic products. Mrs. Lapalette asks you to calculate the inventory management indicators for her 6 best-selling SD range screens.
She provides you with the following information in order to carry out your work:
Work to do
1/ Calculate the average storage duration, average stock and turnover ratio for the SD screen range for this quarter.
2/ Deduce the 3 best references in terms of stock management.
3/ Why are these 3 references the most sought after by customers?
Proposed correction
1/ Calculate the average storage duration, average stock and turnover ratio for the SD screen range for this quarter.
To answer this first question, there is a logical order to follow. First, you must determine the average inventory, then the inventory turnover ratio and finally the average storage duration.
For the SD-458 Screen range :
Le average stock corresponds to the following formula: (initial stock + final stock) / 2
With the encrypted elements we therefore have: (3 + 952) / 2 or a average stock of 3.
Le turnover ratio stocks correspond to the following formula: turnover for the period / average stock for the same period.
With the numerical elements we therefore have: 18 / 990,40 or a turnover ratio of 5,98.
La average storage time corresponds to the following formula: Period considered / Turnover ratio.
With the figures we therefore have: 90 days / 5,98 or a average rotation time of 15,05 days.
For the SD-895 Screen range :
Le average stock corresponds to the following formula: (initial stock + final stock) / 2
With the encrypted elements we therefore have: (4 + 264) / 5 or a average stock of 4.
Le turnover ratio stocks correspond to the following formula: turnover for the period / average stock for the same period.
With the numerical elements we therefore have: 19 / 333,60 or a turnover ratio of 4,04.
La average storage time corresponds to the following formula: Period considered / Turnover ratio.
With the figures we therefore have: 90 days / 4,04 or a average rotation time of 22,27 days.
For the SD-129 Screen range :
Le average stock corresponds to the following formula: (initial stock + final stock) / 2
With the encrypted elements we therefore have: (5 + 512) / 6 or a average stock of 6.
Le turnover ratio stocks correspond to the following formula: turnover for the period / average stock for the same period.
With the numerical elements we therefore have: 17 / 388,80 or a turnover ratio of 2,86.
La average storage time corresponds to the following formula: Period considered / Turnover ratio.
With the figures we therefore have: 90 days / 2,86 or a average rotation time of 31,47 days.
For the SD-738 Screen range :
Le average stock corresponds to the following formula: (initial stock + final stock) / 2
With the encrypted elements we therefore have: (2 + 704) / 3 or a average stock of 2.
Le turnover ratio stocks correspond to the following formula: turnover for the period / average stock for the same period.
With the numerical elements we therefore have: 17 / 617,60 or a turnover ratio of 5,94.
La average storage time corresponds to the following formula: Period considered / Turnover ratio.
With the figures we therefore have: 90 days / 5,98 or a average rotation time of 15,05 days.
For the SD-951 Screen range :
Le average stock corresponds to the following formula: (initial stock + final stock) / 2
With the encrypted elements we therefore have: (3 + 848) / 4 or a average stock of 4.
Le turnover ratio stocks correspond to the following formula: turnover for the period / average stock for the same period.
With the numerical elements we therefore have: 13 / 156 or a turnover ratio of 3,28.
La average storage time corresponds to the following formula: Period considered / Turnover ratio.
With the figures we therefore have: 90 days / 3,28 or a average rotation time of 27,41 days.
For the SD-667 Screen range :
Le average stock corresponds to the following formula: (initial stock + final stock) / 2
With the encrypted elements we therefore have: (3 + 016) / 2 or a average stock of 2.
Le turnover ratio stocks correspond to the following formula: turnover for the period / average stock for the same period.
With the numerical elements we therefore have: 19 / 104,80 or a turnover ratio of 7,49.
La average storage time corresponds to the following formula: Period considered / Turnover ratio.
With the figures we therefore have: 90 days / 7,49 or a average rotation time of 12,01 days.
We can summarize these elements in table form as follows:
2/ Deduce the 3 best references in terms of stock management.
To answer this question, you need to know the meaning of management indicators. A high turnover ratio is favorable for a product. We can therefore classify the different references in descending order and extract the first 3.
We will therefore have the following references: the SD-667 screen, the SD-458 screen and finally the SD-738 screen.
3/ Why are these 3 references the most sought after by customers?
A high turnover ratio means that the stock of this item is renewed very often. This also means that demand is high and sales are significant. This is why these references are in high demand.