A school bus transportation company needed to automate their billing process. In the beginning of the school year, they negotiate the contracts with the department of education, including how much they are going to get paid for regular trips, how much for extended days, and how much for overtime. The conditions for extended days and overtime and also part of the contracts. Every month, the department of education sends import files to this company, indicating what the department of education thinks the billing should be, based on the contracts, the schools start and dismissal times, holidays, etc. The transportation company needs to be able to review those files, make amendments, if necessary, and then convert this information into an invoice, which is then sent to the department of education for payment.
The billing process itself is complicated, involving a lot of variables while calculating the regular runs, extended days, and overtime. Needless to say, the billing process used to be very labor intensive without automation. Hence the need for the application.
The requirements for the appliation included configuation options for the schools, contacts, and routes, plus all the logic to calculate the invoice, and finally generate the invoice in Excel format. It also needed to have proper user security, so only authorized users can access certian features of the application.
Below are some screenshots with explanations how the application works.
User access can be restricted to the configuration functionality, as well as billing functions by company.
The list of schools is defined in the application. For each school, the dismissal time is important as it is used in calculating the extended days and overtime. Also, some schools are defined as General Education, some are Special Education, and some have specific dismissal times for specific routes. All this is taken into account when generating the bill.
The contract definition is split into two screens: Contract Series and Contracts. The former captures some configuration options that are commot for all the contracts under the same series.
This screen is used to review previous bills, create new bills, and create invoices.
The Billing Worksheet screen consists of several tabs that are used to review the summary, regular runs, extended and overtime runs, as well as manually modify the information used for billing. The Summary tab contains the billing totals.
The Regular tab provides information about regular runs. The Comments column contains important tips that help understand deviations from the standard bills, for example, if a school was closed for certain days of the month.
The Extended tab provides information about extended days and overtime calculations.
Once the Billing Worksheet is calculated and reviewed, the invoice is generated in Excel format and submitted to the Department of Education for payment.