Create Customer-specific Pricing Algorithm Based On Historical Pricing Data

Project Description

Project Overview:

We are looking for assistance in refining our quoting by creating a pricing algorithm. The end deliverable will be a custom (per customer per material/supplier) pricing model which maximizes revenue and minimizes costs for 1) labor and 2) material.

Company Profile:

We are a local DFW, TX sand and gravel trucking broker primarily serving the construction industry. We do not own trucks nor employ drivers.  We work with owner/operators to complete orders.  

We have two main input costs – 1) material (sand, gravel, rock, etc.) and 2) trucking costs (paying the owner/operators for their trucking services).  Trucking (labor) makes up approximately 80% of our revenue with the remaining 20% derived from material sales.

These two input costs are marked up a certain percentage and passed onto our customers. All jobs will incur trucking costs; only a subset of jobs involve materials costs too.

Data Source:

We have recently invested significant time and resources into a custom quoting, dispatch, and job completion system hosted on Amazon Web Services.  

The MySQL database houses all the quoting information and actual deliveries completed (successful quotes).

Current quoting methodology is based off drive time distance/time) for the labor and material costs from local pits/dumps (marked up a certain percentage).  

We would like to refine our pricing methodology based off individual customers (greater discount to high volume customers, higher mark up to riskier customers, higher costs for slower/less sophisticated customers) maximizing our revenue and minimizing our costs.

Deliverable:

The deliverable would integrate into our MySQL database and refine itself over time. The algorithm should take into consideration volumes/seasonality and cover the following areas:

1. Trucking costs – provide an estimated trucking cost to minimize cost on a per unit basis (ex per ton, cubic yard, load, hour) given different truck types (sizes). We are thinking about eventually allowing drivers to bid on each project to furhter refine our pricing. We utilize several different truck sizes which, all other things being equal, would result in a different trucking cost for different size trucks.

Problem to solve: what’s the lowest price we can pay our truckers to successfully find truckers to perform the work?

2. Material costs – material costs are “fixed” by our suppliers (local pits and dumps with whom we have purchasing accounts with) and priced by either the ton, cubic yard, or load. The algorithm could suggest lower cost alternatives which may help win the bid but may be a further distance away from the job site.

Problem to solve: are there suppliers which may be located a little further from the job site, but would offer a lower total cost to the customer (with a similar material quality)?

3. Trucking + Materials (when applicable) revenue – provide estimated values to maximize our chance of winning bids on a per customer basis. This should be a flexible calculation based on full and partial truck loads. When trucking costs and material costs are priced in the same unit of measure, the customer will only be presented with a single price quote; when trucking costs and material costs are priced in different units of measure, the customer will see separate price quotes for both materials and labor.

Problem to solve: Considering credit risk, what is a total, all-in cost for the customer to maximize our revenue and win the bid?

4. Credit Risk/Quality of Customer – items which maybe be used in determining credit risk

• average size ($$$) of invoices (higher average better)
• total billed revenue (more better)
• time as customer (longer better)
• Days Sales Outstanding (lower better) – how slow does this customer pay; our terms are typically N30.

5. Seasonality – Supply and demand. Rainy season = lower demand / higher (available) supply. Ability exists to pay the driver less when demand is lower.

6. Actual vs estimated time spent on each job – currently out labor cost estimates are based off estimated time to complete. Shortly we will be rolling out the ability to track precisely the time it takes to complete each job. This information will need to be planned for and included in any deliverable.

Applicants preferred from New Zealand, Australia, and the United States.