Mikel Remesar

I remember a situation in the company in which, due to liquidity problems, we were unable to make payments to the company that distributed the newspaper for us. The distribution company informed us that it would stop working with us from one day to the next, giving us less than 24 hours to find a solution to this problem. It was a difficult decision because around 6,000 subscribers were going to be left without the newspaper the next day, so many would probably unsubscribe, causing a loss of income. Once we knew the decision was irrevocable, I contacted a software company dedicated to delivery software to purchase the program for a month. I informed the company management that We need to gather a group of people to enter the personal data (address, city, postal code) of the 6000 subscriptions in order to have the correct data for the new distribution maps. Once my CEO received the data he chose the option to implement the maps, which was suggested by the system. I started reviewing the plan and recognized a few problems with empty runs, each courier had different distances. I contacted the CFO and presented the weaknesses of the actual distribution routes. Next I set up the meeting with the CEO and presented the loss, which the company will have using that routes. The CEO accepted my arguments and made me responsible for the creation of new routes. I used the data about our clients, their orders, calculated the needed amount of the couriers and started creating new profitable road connections. So I defined these delivery routes based on the points located on a map. Additionally I created the contingency plan - one additional car, which can supply orders to the random orders. At the same time that this was being done, I contacted a temporary employment company to hire 8 drivers and assign them 8 delivery routes. Over 5,000 newspapers were delivered on the first day and by the end of the week over 95% of subscriptions were being delivered successfully.

Reviewing the number of accounts receivable, I detected that more than half of the pending debts came from more than a year ago, they were long term. It was not normal considering that they were subscriptions and advertising that had already been published in the newspaper, so I began to dive deep in order to discover what was happening. As far as I could verify, many of these clients had been reassigned verbally and, in the database, when it came to dividing the debts of each client by each commercial, these appeared with the name of commercials that no longer worked in the company. As you will understand, it was very difficult for a commercial to claim a debt if he did not know that he had to claim it. Knowing this, I decided to find a way to solve this situation so the debts will be claimed as soon as possible and do not exceed 12 months. While reviewing the process to extract the debt information, I realized that it was a long and tedious process and that it took almost half a day to extract the information and communicate it to the commercials. In a company where every minute you need to be looking for new clients or selling advertising, those minutes count a lot. I decided to see how the program extracted the data and see if there was a way to automate it. I noticed that the program had an option to extract data to an excel, and through there, I created a Dashboard in Looker Studio in which I included the clients of each one, creating a separate document with the remaining clients, so that in idle hours the commercials could give me indications of which clients had been reassigned to each one. In the end, thanks to the tool that I created, my team was able to claim 80% of the long-term debts, obtaining more than €80,000 and reducing the process of notifying the debts from half a day to 10 minutes.