It turned out that the pharmacy business is quite simple in terms of start-up (a pharmacy of a large franchise is opened almost by the mechanics of “further, further-ok”), but it is quite difficult to manage. However, he does not seem so. The difficult part is in inventory management, that is, in the supply of medicines, medicines and other things to the final retail point. In reality, people do this with their hands and often get it hard.
There are very frequent situations of under-ordering of goods (when the needed popular medicine is simply not available at the pharmacy), re-ordering (the goods are delivered for the next couple of years), or improper distribution over the network of pharmacies (in one there is no and in the other for six months of supply). The tablets are compact, laying out in pharmacies is good if 5% of the inventory, so eight to ten million rubles can easily be hidden even in 15 square meters in boxes. And then these medicines suddenly expire after a year.
The problem is in manual inventory management and in incorrect forecasting of demand: the market is such that often at the beginning of the year obligations are signed for the year ahead, and the manufacturer pushes tons of slow-moving goods to the pharmacy chains.
Of course, in this situation, a mathematical model is sorely lacking. Well, we came with her. In the process, we made some more wonderful discoveries about the market.
How it works
There are two types of medications:
- Which can only be obtained from one supplier. For example, last year there was a market defecture for diprospan (joint injections): at some point, the management at the last pharmacy where he was bought ten packs and sold or used them on the secondary market.
- Which have generics (that is, direct analogues, this term in the pharmacy environment does not imply a deterioration in quality). Aspirin or omeprazole is clearly a lot of different things, and if it disappears from one supplier, then you can always find a medicine with the same INN.
It is much more difficult with the first type of goods. If the manufacturer was unable to produce or if something was not calculated in the order, then there is a chance to leave several pharmacies or even the entire network without the necessary medication. Approximately from here follows a logical consequence, according to which most serious contracts are concluded for a year, and you need to buy a lot of everything at once. That is, agree on a certain amount, and then gradually receive it, distribute it to pharmacies and pay.
At the beginning of each year, you sign up to sell a certain amount of some medicine. This lies in the distributor’s supply plan (there are three of them large in Russia) and further into the production plan.
Another feature is that, since you can not always get the goods directly from the manufacturer, you need to negotiate with the distributor. And there is such a thing as a volume discount. That is, this is the second reason to sign large volume agreements in advance.
As a result, with incorrect forecasting of their capabilities, pharmacies find themselves in a tight recession. They get out in different ways: they make motivation for staff, promotions. But here they have no right to advertise a product, because it’s medicine. In the end, everything goes through the first-time pharmacist in the pharmacy. They have motivation: they sold more expensive nitroglycerin from such a company instead of the usual one - you get more. Or he sold vitamins after every twentieth order, too.
It can be seen from this graph that for this drug, the remainder is almost twice as large as necessary.
Demand forecasting
Before our arrival, the pharmacy chain independently made a forecast using a moving average and made a lot of manual adjustments, because the model did not always fit. More precisely, they used the autoforming of the order and the ability to edit it. In our opinion, editing was somewhat abused, because almost every order involved manual intervention. This happened more likely due to the lack of a model: thus, auto-order did not take into account what was already going to the pharmacy, but it had not yet been taken to the warehouse of a retail outlet.
As a result, they wrapped each order with a cloud of mental crutches and made corrections on their fingers: “it rides, it’s necessary twice as much, here the supplier is bad, we will order more, and this season is cold, this also needs to be done.” The approach has the right to life, but it turns out to be very inaccurate in the bottom line. It is impossible to work purely according to the existing forecast: they made a patch - it worked in one place, but it broke in another - they made a patch there. As a result, no one can remember how it all began and why. But a bunch of corrections work for everything:
“Why did you add ten packs of all the drugs on this list?”
- Yes, the system always makes mistakes down here, so I correct up.
- And why did you take 12 packs per point, when the average monthly consumption is two pieces?
- Well, I thought it was not enough, so I added it!
“Do you understand that this is five pharmacies, and now someone will be left without them?”
- Yes? Oh. Well, order more.
“Why are you only transporting three bottles?”
- Because it takes you one or two pieces in a month.
“They took five pieces last week!” Carry ten, Herods !!!
It turned out that the KPI of the buyer are considered for missing goods. Not by the effectiveness of turnover, not by some criteria that are even closer to commerce, but by how many tablets he pushes into each pharmacy. The fact that they are then written off after the expiration date is no longer his headache. Reduced margins and sales at cost at the end of the shelf life, too.
Actual execution of the order was about 20%.
What did
Next, we solve the classic retail forecasting problem. It has been sucked in a hundred times and is known, perhaps, to everyone.
Restored demand, forecast and optimal reserves for the critical drug - Viagra.
The question is in setting up the model. We use the following rules:
- The usual approach to optimizing stocks is restoring demand (discarding plots without this product available for statistics), forecasting demand with autoregressive models taking into account trends, seasonality, calendar events, creating a supply schedule, calculating optimal stocks in pharmacies and stocks, creating orders.
- Taking into account the supplier’s unreliability: the more surprises a supplier can present, the more stock is needed for the network.
- Accounting for the indispensability of the drug for operational procurement: there is no point in storing activated carbon: each supplier has it. Medicines according to the formula after patent disclosure, too. But unique new drugs - yes.
- Some products require cold storage: there is more stringent optimization of inventory at the retail point.
- Narcotic drugs are supplied according to special conditions.
- You can’t move goods between pharmacies, so if possible you need to use more frequent and smaller supply transactions.
The last point may seem strange, but the right process for medicines is that you need to take them from the pharmacy and send them back to the central warehouse. There, accept, conduct a new batch account (that is, start as a new SKU with a different expiration date actually) and distribute it again. Moreover, each batch of goods from the supplier goes through the process of its certification, which somewhat complicates the workflow. The whole process, of course, is manual.
For some goods there is a refund (the required supply, which should always be in the pharmacy, in the opinion of assortmentists), here is the restoration of demand and modeling of balances for one of them.
As a result, it is easier for them to throw out goods after the expiration date than to try to redistribute it. There is a life hack with “bushes” of pharmacies when several retail outlets belong to the same director, and it can be relatively easily redistributed between them, but there are a couple of pitfalls there too. Correctly build a multi-echelon optimization system where the warehouse requirement is formed from the needs of pharmacies with regulated coverage. The need for pharmacies is calculated on the basis of a statistical forecast taking into account the trend and seasonality. The product is distributed among all pharmacies by sales, reducing the negative function for lost profits. The stock of the warehouse is divided into several virtual stocks, one of which provides pharmacies with goods, and the others are used to fulfill marketing obligations, wholesales, etc. But it is very difficult to prove the second. As a result, we are now building an average market forecast for all pharmacies, then we form the correct general order for the warehouse, and then distribute it with small transactions as we spend it.
This graph shows that the goods were in one pharmacy in the stock market, and in the other two they were not there at that time.
The first results went, here are the graphs for some drugs:
The first example.
Second example.
Prior to this, the distribution of pharmacies had another feature. The product went to those who are closer to the top of the list alphabetically! That is, in the case of 12 packs of drugs with an average consumption for a period of two, the first six pharmacies on “A” receive it, and the rest do not. In another network, priority pharmacies were allocated: there, at first, the goods went according to one list, then - according to another. But about the same. If you are opening a pharmacy, try not to call it “Y”. Better name it “Aachenosaurus”, and the legal entity is “AAA” LLC. There will be many questions, but you will be with the goods.
Pharmacy directors knew this feature and tried to agree in advance in order to snatch all the goods: it is better to store at home than to wait for the weather by the sea. This also caused unevenness in the old model and created a unique taste of opposing IT measures, because the usual scheme was breaking in the direction of more cost-effective for the entire network.
There was a problem with parapharma, that is, with the same therapeutic shampoos. They simply cannot deliver one tube that is being sold: an entire shelf needs to be delivered. Suppliers pay for open display, this is a pharmacy in half with an advertising business. But in the purchasing model, they simply re-sell this segment. In theory, supply management there is more flexible than in medicines, but much less attention is paid to the assortment matrix. While the time of the purchasers is needed on the main product, on a parapharm, it was decided to revise the matrix once a year. This means that during the whole year a new product cannot be introduced or a product that is not for sale is withdrawn. Bottom line: yes, they get bonuses, but operational efficiency suffers.
As it turned out, roughly similar things are sometimes done with the laying out of drugs: there may well be a drug that will not be sold for years.
Another direction where the network is trying to develop is to move your wholesale. Since they already buy large quantities at good prices, it would be logical to sell them further. There is a right to resell, the market does not suffer from dumping, because it is very easy to control the recommended retail price. Only now, wholesale is also sold in large quantities, and sometimes it happens that such a consignment has left that might be useful to its own retail network. There was not much balancing there.
And finally: contracting for supplies is peculiar, that is, there are interruptions in payment. It’s so straight down the nail. Since there is no proper money management (again, because there are not always the best personnel and there is not always at least some kind of routine automation), this means problems with the following orders.
Why there are no better frames? Because in the sphere of high staff turnover, including in the leadership. This leads to chaos in the management of reporting systems and an inaccurate understanding of their own business processes. The phrases “I don’t know where to look” and “Yes, this cannot be! How did you find this? ”- one of the most frequent in our dialogs for IT systems.
Think enough? No. If you come to automate something in the real world, then there is far from pure mathematics, but many, many searches for source data and their unification. In our case, more features:
- Business uses several directories of goods at once, in the directories there are often grammatical errors or spelling features, because of which similar products can fall into different categories.
- Tareness, that is, the number of packs of tablets in a transport package, is constantly changing and stored in people's heads in places.
- Logistic arcs are not always described correctly: they refer to closed pharmacies or to products derived from the matrix.
- You can’t see the whole product distribution. Not from one system, but simply impossible. It is necessary to collect it by echo from many systems and papers.
As a result, we reduced the deficit by 15% and reduced our inventory by five days simply by correct redistribution of goods and more accurate forecasting of demand (excluding promos, they were re-ordered with our hands). Sales rose up 1.5%. Math won this round.