AgentBilling

							Overview

Agents are local moving companies who are hired by national Carriers to perform some services on
behalf of the Carrier during a move of a customer’s household goods. After completion of the services,
the agent invoices the carrier for the services provided on that move. Our company, Daycos, is wanting
to offer a service of doing the billing to the carrier on behalf of the agent.

Functionality

We need to be able to handle invoicing for multiple agents, and multiple carriers. Again, each agent may
work with multiple carriers, and we do work on behalf of multiple agents. So there would need to be a
setup process for each agent, each carrier, and then the payment calculation formulas for each agent-
carrier combination.

We need the ability to select the agent we are doing work for, and then select the carrier to invoice,
then enter the applicable fields and select which services were performed. The program will then need
to perform the calculations, create and print and invoice, and then store the record of that invoice in the
database.

Payment Calculation

Each agent may do work on behalf of many carriers, and have different payment agreements with each
as far as payment terms. Each carrier and agent sign a Compensation schedule, which outlines the
payment that will be made for each service provided. The services rendered are generally standard, but
the calculation of payment will vary from carrier to carrier. For example, each carrier will pay agents for
packing services, but they all might use a different formula to determine how much is to be paid.

The challenge is to create a system that allows us to bill the same service for agents to multiple carriers
using different formulas. Almost all of the formulas are based on the same information, which is
contained on the spreadsheet on the first tab. The green fields are required on all shipments, and then
the yellow fields are required on some shipment, but not all. Then those data elements are used in the
calculations contained on the second sheet. The services listed in row 7 are all the items we currently
have to bill, although there is always a chance we could have new services added. The fields in rows 8-10
are different calculations used by each carrier for each service. For example, some carriers my give the
agent 75% of the amount they were able to bill the customer, while another might reduce the gross
amount of the charge by 4%, then apply the discount, then give the agent 80% of that result. The
calculations all end up in Row 17, which shows the final amount billable for each service. Keep in mind
this spreadsheet is just for one particular carrier, and that another carrier may use a different formula
for the same service.