Analytics and automation for transport companies
Recently, we have implemented a number of projects for clients with similar tasks. These are transport companies that provide services for the transportation of goods by their own or hired vehicles and drivers.
The basic needs of these companies are:
What our clients were not satisfied with the existing accounting systems designed specifically for transport companies, and how we helped them?
As it turned out, almost all of them do not fully cover all the tasks described. For example, they may be protected by certain algorithms for calculating driver salaries and profitability of flights that are not suitable for the customer, or there may not be enough fields for analyzing trips and routes.
Of course, almost all systems can be modified to the desired calculation algorithms, but as a result, they most likely will not have enough flexibility to change the algorithms on the fly and search for the best options.
')
Below I will try to tell you more about the tasks to be solved.
It is clear that for such an assessment we will need information on expenses and income up to the flight, which is actually not so easy to obtain.
Consumption consists of
There are more complex options. In addition, the driver's salary, for example, can be fixed per day or per month, and can be piecework for mileage or depend on the value of the cargo. Gasoline can be paid for by the company, and maybe by the driver, with or without a fuel card, with “by-become” or without, and so on. And in a particular company, everything is organized quite harmoniously, but between companies it is very different.
There is a noticeable minus of specialized accounting systems - in pursuit of “universality”, the number of checkboxes and settings in such a system exceeds all conceivable limits, and users return to the good old Excel, simply because they cannot master the system’s manuals and do not understand where they come from certain numbers.
Income is also quite interesting. Customers can pay for the services provided weekly (or monthly, or one way - the other commercials). In addition, sometimes in one car there are loads from several customers. Accordingly, in order to calculate income from a particular trip - for example, you can divide the total monthly income in proportion to the expenditure on the trip or the weight of the cargo or the volume of cargo or some other parameters.
In this case, QuBeQu helps automate the calculation of flight profitability, and do it very quickly. We set the necessary formulas, create input forms for the data, and in a few days we receive a fully automated report on the profitability of each particular flight, with full tracking of all calculations and the ability to quickly change them.
In some cases, we have practically no influence on the work of accounting systems, automatically loading information from them, in others - clients completely abandon the systems used (for example, excel or google spreadsheets) in favor of QuBeQu.
In transport companies, a lot of operations are tied to a specific driver, so it is important to be able to quickly track the current status of each of them.
Drivers are given money:
With them, one must not forget to receive:
All this flow often passes by accounting systems and is conducted in excel, and sometimes even in paper journals.
When implementing QuBeQu to solve such a problem, we develop special driver dashboards, to which all basic information on operations is displayed. And it can be both information from 1C on salaries issued or from accounting systems on the cost of flights, as well as “extra-accounting” debts, advances and traffic police fines.
At the same time, accountants and managers have their own interfaces for entering and viewing the necessary data.
Often, during the implementation process, the type of dashboards changes several times, and after several months of use, users continue to add and remove some blocks and parameters.
After solving the previous two tasks in QuBeQu, generating reports on payables and receivables is almost automatic. And these are not periodic reports, but a report at any point in time. As a result, managers can quickly respond to global changes in their business, without waiting for the "reporting period."
What functions of QuBeQu were useful to us in solving the above problems:
1. Connectors to external sources.
With their help, we quickly transfer historical data from accounting systems, provide automatic updating of data, integrate data from various sources among themselves (for example, from 1C and GPS trackers).
2. Data entry.
We are keeping records in excel with data entry directly in QuBeQu. The data fit into a single structure, thanks to which you do not need to copy-paste data between excel files, prepare special reports and spend time analyzing why a particular number is in a cell.
Thanks to data entry, new features are easily added to QuBeQu — for example, for customer segmentation and other additional analysis.
3. Formulas and calculated indicators.
This functionality allows us to:
Projects on initial setup took no more than two weeks, then within one and a half to two months, active user support and system improvements were carried out.
As a result, customers received a full-featured system in which they could not only see the actual report on their data at any time, but also build new ones and also keep additional records.
After the implementation, not only the efficiency of receiving reports increased, but a large amount of time was freed up, which used to be spent on preparing and mixing excel files.
The basic needs of these companies are:
- assessment of the profitability of routes;
- tracking relationships with drivers (collection, payment of salaries,
repair, refueling and other operations); - operational monitoring of receivables and payables.
What our clients were not satisfied with the existing accounting systems designed specifically for transport companies, and how we helped them?
As it turned out, almost all of them do not fully cover all the tasks described. For example, they may be protected by certain algorithms for calculating driver salaries and profitability of flights that are not suitable for the customer, or there may not be enough fields for analyzing trips and routes.
Of course, almost all systems can be modified to the desired calculation algorithms, but as a result, they most likely will not have enough flexibility to change the algorithms on the fly and search for the best options.
')
Below I will try to tell you more about the tasks to be solved.
1. Evaluation of the profitability of the flight
It is clear that for such an assessment we will need information on expenses and income up to the flight, which is actually not so easy to obtain.
Flight expenses
Consumption consists of
- driver salaries
- pay for gas,
- the cost of car repairs (if your car) or the cost of renting a car (if the car is hired),
etc.
There are more complex options. In addition, the driver's salary, for example, can be fixed per day or per month, and can be piecework for mileage or depend on the value of the cargo. Gasoline can be paid for by the company, and maybe by the driver, with or without a fuel card, with “by-become” or without, and so on. And in a particular company, everything is organized quite harmoniously, but between companies it is very different.
There is a noticeable minus of specialized accounting systems - in pursuit of “universality”, the number of checkboxes and settings in such a system exceeds all conceivable limits, and users return to the good old Excel, simply because they cannot master the system’s manuals and do not understand where they come from certain numbers.
Flight Income
Income is also quite interesting. Customers can pay for the services provided weekly (or monthly, or one way - the other commercials). In addition, sometimes in one car there are loads from several customers. Accordingly, in order to calculate income from a particular trip - for example, you can divide the total monthly income in proportion to the expenditure on the trip or the weight of the cargo or the volume of cargo or some other parameters.
Implementation
In this case, QuBeQu helps automate the calculation of flight profitability, and do it very quickly. We set the necessary formulas, create input forms for the data, and in a few days we receive a fully automated report on the profitability of each particular flight, with full tracking of all calculations and the ability to quickly change them.
In some cases, we have practically no influence on the work of accounting systems, automatically loading information from them, in others - clients completely abandon the systems used (for example, excel or google spreadsheets) in favor of QuBeQu.
2. Tracking relationships with drivers
In transport companies, a lot of operations are tied to a specific driver, so it is important to be able to quickly track the current status of each of them.
Drivers are given money:
- for car repairs,
- for gasoline
- salary,
- and just in debt.
With them, one must not forget to receive:
- cash from customers
- confirmation of repairs made,
- deduct traffic police fines,
- etc.
All this flow often passes by accounting systems and is conducted in excel, and sometimes even in paper journals.
Implementation
When implementing QuBeQu to solve such a problem, we develop special driver dashboards, to which all basic information on operations is displayed. And it can be both information from 1C on salaries issued or from accounting systems on the cost of flights, as well as “extra-accounting” debts, advances and traffic police fines.
At the same time, accountants and managers have their own interfaces for entering and viewing the necessary data.
Often, during the implementation process, the type of dashboards changes several times, and after several months of use, users continue to add and remove some blocks and parameters.
3. Receivables and payables
After solving the previous two tasks in QuBeQu, generating reports on payables and receivables is almost automatic. And these are not periodic reports, but a report at any point in time. As a result, managers can quickly respond to global changes in their business, without waiting for the "reporting period."
Summary
What functions of QuBeQu were useful to us in solving the above problems:
1. Connectors to external sources.
With their help, we quickly transfer historical data from accounting systems, provide automatic updating of data, integrate data from various sources among themselves (for example, from 1C and GPS trackers).
2. Data entry.
We are keeping records in excel with data entry directly in QuBeQu. The data fit into a single structure, thanks to which you do not need to copy-paste data between excel files, prepare special reports and spend time analyzing why a particular number is in a cell.
Thanks to data entry, new features are easily added to QuBeQu — for example, for customer segmentation and other additional analysis.
3. Formulas and calculated indicators.
This functionality allows us to:
- quickly adjust the calculations for a specific client
- to test one or another algorithms - just change the formula
- combine data from different levels of detail
- track calculations and find problem areas
Total
Projects on initial setup took no more than two weeks, then within one and a half to two months, active user support and system improvements were carried out.
As a result, customers received a full-featured system in which they could not only see the actual report on their data at any time, but also build new ones and also keep additional records.
After the implementation, not only the efficiency of receiving reports increased, but a large amount of time was freed up, which used to be spent on preparing and mixing excel files.
Source: https://habr.com/ru/post/294910/
All Articles