Over the years, I have been involved in many accounting based automation projects. There is one project in the retail petroleum space that seems to give distributors and suppliers the biggest headaches, that is Consignment Billing. Consignment Billing is when a distributor or supplier manages the fuel inventory aspects for the dealer and then bills them for the loads serviced. However, this process has a great amount of risk for the distributor or supplier as they are at risk of losing or delaying payment of thousands of dollars if the dealer decides to delay payment or not pay altogether. Also, there are fraud risks associated with this method if you have no way to check the inventory of the load at the time of delivery. However, putting in an effective and efficient system has huge benefits for the distributor as well as the dealer. Not only can you create a more accurate and efficient billing system, you can also add features such as automated fuel order and environmental control monitoring for the dealer as a service. Unfortunately, many distributors as well as suppliers do not fully understand the various components involved to make this process work efficiently and end up abandoning the implementation or putting in a partial solution that is ineffective. This article attempts to cover the main components of consignment billing automation and the many pitfalls that can be avoided to achieve a successful implementation.
<b> The Components of Consignment Automation </b>
In order to architect an effective Consignment Automation program, it is vital to understand all the necessary components that go into the program. There are three key aspects to the program, in general: Accounting, Delivery Distribution (Business Process) and Technology. Not understanding the how each of these areas play in the world of consignment automation will cause a failure in the implementation of the program. The following are the major components explained:
Dealer pricing (Accounting Component)
Everything starts with dealer pricing, how much a dealer is expected to pay for a load of fuel. Having worked in the industry for a several years, the deals made between the distributor and the dealer are often like an episode from "Let's Make A Deal". The pricing structures offered from the distributor to the dealer can vary greatly and make it hard to put in a standardized pricing scheme. For example, some distributors may offer a dealer a RACK + $ .01 price, which is the price from the supply rack plus a $ .01 mark up per gallon. Others may offer a similar deal based on an industry standard pricing model such as OPIS. In addition, freight charges can come into play where some companies offer a standard flat rate for delivery. Others may offer a freight price based on miles from the rack (ie terminal). Whatever the deal, it is important that the accounting system is able to take the input and apply to the Accounts Receivable based on the data received and not through any manual manipulation. This can usually be done through a pricing table within the accounting system. However, the best rule of thumb is to try to set up a standardized pricing model. This has the benefit of eliminating confusion between distributor representatives and the dealer or customer and makes your billing and delivery more efficient. Your dealer will appreciate that reduction in complexity of their billing statement.
Supplier Settlement Process (Business Process / Accounting Component)
This process is important to understand as even though it affects the distributor's Account Payable process from the Supplier, it also translates to the Account Receivables to the dealer. In addition, area benefits from a standardized pricing scheme to reduce the complexity of the AP process. Supplier prices can change at the rack by day or even by hour during a natural disaster or other. As such, a consignment automation system should have the ability to pull in prices from the suppliers for all racks automatically and at a frequency that matches the price changes. This is done for two reasons, 1) pricing provided to the dealer and 2) pricing used to pay the supplier per load.
Some suppliers will batch prices and distribute them at the end of each day. Others may change prices in real-time. In either case, the accounting system needs to support the ability to import the accurate price for the period the price will be honored within the system. Again, a pricing table is a good mechanism to meet this need. The table should be able to import all prices and the duration the price is valid. When Bill Of Lading information is entered (which is discussed later) the pricing table is referred to based on the date and time the price was valid and that is the price that will be applied for both the AP and AR values for payment and billing. The reason this is necessary is that between the order coming in and deliver, there is a 24 -48 hour lag time, best case, before all necessary billing components are available for proper billing. There for, a price history must be maintained on the supplier / distributor side of the accounting equation. This becomes clearer when discussing the dealer business process below.
Bill Of Lading (BOL) Process (Accounting Component)
The Holy Grail of consignment automation is the ability to automate the Bill Of Lading process. This is basically the process that occurs when a delivery tanker goes to the Rack (terminal) and fills up a load of fuel. At that time, the delivery person receives their Bill of Lading from the supplier rack aka terminal for that load of fuel. The other side of the Bill of Lading process is when the fuel is dropped in the ground tanks at the dealer's site. The driver checks the current inventory in the ground, adds the new deliver, checks the fuel again for what is delivered. What gets over looked in this part of the process are the two differences in the accounting of the fuel between what was picked up in the rack and what is actually delivered. For example, what happens if the driver has 4000 gallons of fuel to deliver and when he checks the tank at the site, the site still has 6000 gallons in its 8000 gallon tank? The driver now has 2000 gallons left in his truck that he as to get rid of. This extra fuel is called a Retain and now has to be delivered to another site as it cannot be returned to the terminal. Without the compensating control of balancing the load at the rack with the load (s) delivered, that 2000 gallons can easily go missing. Another problem would be a short load, where a load of 4000 gallons to a site is expected but only 2000 is delivered and dropped. Again, what happened to the other 2000 gallons? As such, capturing both BOL amounts is key.
Unfortunately, many supplier terminals are not automated enough to supply a dealer with the necessary BOL information electronically. Also, many carriers still use manual methods to track BOL information. Carriers typically have the drivers drop their BOL forms back at a central office where data entry clerks enter it into their accounting systems and bill their distributors for the loads. This, again, results in a paper process if this is not automated. There are, however, systems that can take the ordering process through the BOL and "automate" it so that the BOL can be balanced and entered into the distributor accounting system electronically. Companies such as Fuel Quest, Telapoint, NPN and others help to provide automation between the order, the carrier and the distributor. Some can also use historical data to do trend analysis and look at seasonal demands to insure fuel managers are able to keep their sites supplied effectively during peak times. The systems use a variety of methods to obtain information, either through electronic meters or personnel data entry via the different connection points (rack, truck, carrier and distributor). The key for Consignment Automation is that in the end, the BOL, Delivery and store inventory and sales readings are all in an electronic format to enter in the distributor's accounting system.
Dealer Billing Process (Accounting Component)
The Dealer Billing Process is where all components for the accounts receivable come together to implement billing. A good consignment automation program should have this process completely automated. There should only be exception reporting validation in this area as all inputs and outputs are electronically applied from the various sources into each dealers account.
First, for the billing process to accurately occur there needs to be a settlement of all transactions that make up the statement for the specified account. Meter Sales, Meter Inventory and Sales price are the key factors from the site that are required to provide the actual sales side of the settlement equation. The next components that need to be settled and then applied to the dealer accounts are the delivery information. As discussed above, the final actual delivery, carrier BOL, that was dropped in the tanks at the site and the supplier BOL information is reconciled in the distributors accounting system. If they are in agreement, then the dealer is billed the actual based on the negotiated dealer price for the load. That is cross-referenced with the pricing chart for that dealer. If there is a discrepancy, then an exception report is generated prior to billing and the proper accounting group works to identify the problem and correct it. Again, the exception is where the human factor comes into play. This is also the area where both the AP and AR data interchange as you need the settlement data from the supplier and distributor (AP) to reconcile with the Dealer and the delivery (AR).
One important factor that is common in implementing automation on this level is the price at time of sale. Most EPOS systems in the petroleum industry DO NOT provide price history for gallons sold between price changes. Unfortunately, a site may change their prices more than one a day depending on the market, especially if a price war with a competitor is underway or there are supply issues. It is not uncommon for a site to have 2 – 4 price changes in a single day. Combine that with a mid-day fuel delivery and now you have an accounting nightmare that is extremely difficult to balance with delivery actual. Because of this problem, most automated processes end up using average daily pricing verses actual price per price change. This concept usually results in differences of opinion between the sales department and accounting department of a distributorship because of the perception of lost profit.
Consider the following situation, assume a site experiences 3 price changes during the day: $ 3.569, $ 3.609, $ 3.669. The average price per gallon is $ 3.629. Assuming 10000 gallons sold during that period, average sales would be $ 36,290 in sales. Now assume price by time period using the same data, for 7 hours, fuel sold at $ 2.569 and 2800 gallons were sold, for 5 hours it sold for $ 3.609 and 2800 gallons sold and for the remaining 12 hours of the day, 4400 gallons sold for $ 3.669. The actual sales would be $ 33,442. Using this same method for a decline in price during the same day period, reversing the order of the price change using the same gallons for the periods defined above, the average price remains the same $ 36,290 but the actual sales now show $ 36,082, which is close to the average sales price. When this swing is stretched out over a long period of time looking at both the price increase and decrease, the negative impact is on the price increase, the positive is during a price decrease, but the net effect is zero, meaning over time between the swings, the overall price stays close to the average.
Another big part of the Billing process is the use of Electronic Funds Transfer (EFT) for payment of the loads from the dealer back to the Distributor. Distributor agreements last for 10 – 15 years in most cases. Again, to lower data entry errors and to speed payment processing between the distributor and dealer, it is highly recommended that the dealer be put on an EFT program. This way, once the load is delivered, the distributor can submit a request to draft the dealers account for the payment of the load. Although this seems to be highly in favor of the distributor, the dealer has an advantage as well if he can have his entire fuel delivery process automated and managed outside of his day to day business concerns. The dealer is really left with reconciling his store sales and inventory to his EFT statement and insuring regular deposits are made from his store sales. He does not have to concern himself with calling in a fuel delivery or constant monitoring of his tanks as that can be monitored and managed by the distributor. This is the key concept of consignment automation.
There is one word of caution. Distributors should be very mindful of their customer's cash flow. Remember, it can take 2 – 3 days for the BOL process to complete and a distributor bill to be made. Although the EFT process can be independent of the billing process, a close match up is recommend because of the problem of Retains and short loads. A distributor can, and often does, draft a dealer immediately after delivery only to find out some sort of problem with the load. As a result, the distributor ends up implementing a credit back to the dealer's account, usually on a next load. However, this type of transaction has a huge impact on a dealer, which is why a lot of dealers try to stay away from EFT. It is recommended that EFTs be applied only to in balance invoice transactions. Also, distributors should consider making use of a fixed day for the EFT draft. For example, a distributor can tell their dealers that by 4pm every Tuesday and Thursday, the company will issue an EFT for monies owed for loads that week. This at least provides the dealer notice of their draft days and can insure they have enough funds in the bank to avoid NSF charges and issues. Finally, distributors should limit EFT drafts to no more than twice per week.
As has been mentioned in a few of the business process and accounting components above, technology is key. If you want to automate this process, you need the right technology to automate with. First, look closely at the stores equipment. The store should have a good Electronic Point of Sales System (EPOS) and Automatic Tank Gauge (ATG). It is recommended that a industry standard unit be used that has its interfaces built on a NACSXML standard. The basic requirements for these devices are:
1) The EPOS can interface with the ATG for on demand reporting, especially shift and day close reporting. This is usually done through a serial or network interface.
2) Both the ATG and EPOS have remote access capabilities. The EPOS will be the dedicate device to pull accounting information for the consignment billing process. The ATG is used for daily inventory reporting for order and delivery monitoring.
3) The EPOS shift, daily, monthly and on demand reporting MUST be able to show Meter Sales, Meter Gallons (usually from the EPOS to the pumps) and Inventory (a feed from the ATG), at the time those reports are run. This gives you all of the critical data for consignment billing at an exact moment in time.
One thing to note about item # 3: There are some vendors who supply a solution that attempts to pull the data for consignment via the ATG and the EPOS independent of each other. This should be avoided. The reason is that even if the difference of pulling the information between the two devices is a few minutes, if customers are pumping gas at the time one of these is being pulled, particularly the ATG, your inventory is skewed and will not be accurate. Avoid this method at all costs. The best and ideal method, is get all totals to go to the EPOS system and pull your data from there.
Next, you want to look at your accounting system. In the petroleum industry, there are a variety of accounting systems that support consignment billing such as PDI, SSCS and SMS. A distributor should carefully look at their accounting package to make sure it can support multiple price structures from multiple suppliers as well as accept automated feeds for sales, order and delivery data. Depending on the system, there will most likely be some capital dollars spent on customization to allow for the automation to work effectively. Also, another benefit is having an order interface that would allow customers to place fuel orders over the Internet or PDA. Some accounting systems do this, but it is usually found in a carrier focused software or is something the distributor can custom build if they utilize the concept of a data aggregator.
Also, make sure you are dealing with a flexible bank interface to handle your EFT process to your dealers. It needs to be flexible enough to schedule EFT drafts for multiple days and to apply credits quickly should a mistake occur. Most EFT companies draft money quickly from their customers but take days to apply credits back. Again, it is important to help your dealer with their cash flow and drafting too much money too soon, can cause them problems and both the dealer and distributor sales profits.
The other component that often gets missed is a data aggregator. Many suppliers post their pricing or billing on websites for an accounting clerk to download manually. Pricing companies may also do the same. But it is worth asking the supplier and pricing entity about setting up an SFTP feed to pull your pricing and supplier billing automatically. PCATS has established standard data formats for the petroleum industry for various data interfaces. A supplier or pricing entity that uses this standard to provide an electronic data file increases your chances of implementing an aggregator system. But, even with that, the data aggregator should be able to handle a variety of file formats (csv, xml, flat file, etc.) based on what the carrier, supplier or pricing entity offers. If they offer an electronic files format, take advantage of it. Also, this system is a benefit when attempting to retrieve similar data from the terminals for BOL information. The data aggregator works as a SFTP type server, but basically should be designed to handle all electronic feed types (SFTP, FTP, SSL, or message queuing) are just a few examples. The systems job is to simply move data from the Supplier, Carriers and Dealers to the accounting system. The benefit of having a single unit or a "load balanced" system perform this task is that it can be maintained easily and the feeds can be modified should you change any part of the data flow sources.
Finally, the carrier software has a big benefit to consignment automation. Consignment automation can still be accomplished without a carrier interface, but getting the BOL information will still need to be entered in some way. A good carrier / dispatch software allows the distributor to fully manage dealer fuel orders, not just the billing aspect. Carriers can enter data into the same interface the distributor uses as well as the terminals. If you get a feed from the supplier terminals, then the carrier only needs to enter the BOL from their side of the BOL process. It also has the benefit of forecasting loads, making the distributor more effective in scheduling deliveries to their customers. Also, customers can use the Internet through the same interface to place a fuel order, reducing the number of data entry points humans make in the process. This system basically handles the BOL and fuel management process and then sends the net results to the accounting system for reconciliation and billing.
Putting It All Together
So, now with all the components in place, let's walk through the process in a nutshell.
1. Customer or Fuel Manager orders a load of fuel via an Internet Carrier Interface for a site.
2. Order is prioritized based on location, availability, terminal location, carrier and time of run out (all through carrier software) and an order request is transmitted to the driver's electronic queue.
3. Driver receives order and goes to terminal and picks up load.
4. Terminal BOL is generated. Driver gets a copy, electronic data sent to carrier system.
5. Driver takes load to site and drops off load.
6. Driver checks ATG. Drops load. Enters actual fuel drop on BOL record in Carrier system.
7. Carrier system closes order and sends to distributor as electronic invoice.
1. Distributor receives daily EPOS report data from sites, BOL information from carriers and suppliers and pricing information from suppliers and central pricing sources via the data aggregation system.
2. Data aggregation system moves data into respective holding areas in the accounting system for import.
3. Accounting system imports respective data.
4. Accounting AR and AP processes execute and exception reports are generated.
5. Exception reports are reviewed and corrected.
6. EFT reports for billing are generated and an EFT request is requested from the Customer's bank.
7. EFTs are drafted and accounts are credited.
With the right investment in equipment, a good overall business process and good accounting practices, a distributor group can achieve a highly successful and profitable consignment program for their customers with increase efficiency and lower costs to their bottom line.