Weird Calculations for Wrecker Service

[breakingme10]

Ok so I recently read the fine print in our states wrecker nonconsensual towing laws and saw that I am allowed to charge a fuel surcharge on top of everything else. So...I now need to add that into my database as a charge. The question is ... how am I going to go about doing this?

This is the fine print:

The fuel surcharge shall be based on the Department of Energy "weekly retail on-highway diesel prices" for the “Midwest Region" using Two Dollars ($2.00) per gallon as the base price with no fees added. The wrecker fees may be adjusted to allow a one-percent increase in fees for every ten-cent increase in fuel cost starting at Two Dollars and ten cents ($2.10) per gallon.

So my fuel surcharge base price is: $2.00

One of the examples for the new OCC rates distance based tows is:

10 Mile Simple Tow
Distance: 10 miles
Hookup: $81.25
Mileage ($3.75/mi): $37.50
Surcharge ($3.80/gal): $21.38
Total: $140.13

I have no clue how they are calculating this! This isn't how i'm used to calculating fuel surcharges!


Maybe I'm trying to over-think this but I can't come up with a NORMAL MATH FORMULA to get this accomplished much less put it into a invoice line as a charge on a tow bill.

Here is yet another example on the sheet:

5 Mile Simple Tow w/Rollback
Distance: 5 Miles
Hookup: $81.25
Mileage ($3.75/mi): $18.75
Surcharge ($3.80/gal): $18.00
Rollback: $30.00
Total: $148.00

Can someone please help me understand this better?

[June7]

Looks like you need to record the miles and gallons for each towing. You can either enter an amount for gallons or calculate the gallons used based on the vehicle's MPG (eg, 15 miles per gallon).

I presume the state will periodically adjust this allowed surcharge rate so you need to decide whether to save this rate or the calculated data into the towing record or to create a new record for each surcharge rate and apply the correct surcharge record when calculating.

[pbaldy]

As to the math, 3.80-2.00 = 1.80 or 18% (1%/10 cent). 81.25 + 37.50 = 118.75 * .18 = 21.375 or 21.38 rounded.

[breakingme10]

As to the math, 3.80-2.00 = 1.80 or 18% (1%/10 cent). 81.25 + 37.50 = 118.75 * .18 = 21.375 or 21.38 rounded.

Ya that came to me last night around 3:15 AM! Yesterday i was trying to think of it like an individual charge on its own because of the way they list it. Thank You!

[breakingme10]

Looks like you need to record the miles and gallons for each towing. You can either enter an amount for gallons or calculate the gallons used based on the vehicle's MPG (eg, 15 miles per gallon).

I presume the state will periodically adjust this allowed surcharge rate so you need to decide whether to save this rate or the calculated data into the towing record or to create a new record for each surcharge rate and apply the correct surcharge record when calculating.

Well that's going to be really tricky we have to go off the US Energy prices. http://www.eia.gov/petroleum/gasdiesel/

I made an excel spreadsheet of the percentages. and the gas price is updated weekly by the eia.gov website (yet the oddest thing ever when i subscribe for email updates it goes through gets confirmed then i get an email about 30 minutes later saying that my "request to cancel subscription" went through, so i'm just going to check it every monday)

DOE Fuel Charge Range	Surcharge Percentage
2.00-2.099	0%
2.10-2.199	1%
2.20-2.299	2%
2.30-2.399	3%
2.40-2.499	4%
2.50-2.599	5%
2.60-2.699	6%
2.70-2.799	7%
2.80-2.899	8%
2.90-2.999	9%
3.00-3.099	10%
3.10-3.199	11%
3.20-3.299	12%
3.30-3.399	13%
3.40-3.499	14%
3.50-3.599	15%
3.60-3.699	16%
3.70-3.799	17%
3.80-3.899	18%
3.90-3.999	19%
4.00-4.099	20%
4.10-4.199	21%
4.20-4.299	22%
4.30-4.399	23%
4.40-4.499	24%
4.50-4.599	25%
4.60-4.699	26%
4.70-4.799	27%
4.80-4.899	28%
4.90-4.999	29%
5.00-5.099	30%
5.10-5.199	31%
5.20-5.299	32%
5.30-5.399	33%
5.40-5.499	34%
5.50-5.599	35%
5.60-5.699	36%
5.70-5.799	37%
5.80-5.899	38%
5.90-5.999	39%

What throws me off the most is that they want us using the percentages instead of the mpg

[breakingme10]

Now how can I begin to add a charge like that in? I'm thinking maybe a prompt for the weekly gas price, then have it subtract 2 from that, then according to the result of that, find the price range from the list above and apply the corresponding percentages?

[pbaldy]

I wouldn't prompt for it, I'd have it entered weekly in a table and look it up. Same with the hookup and mileage fees in case they change. Then you just need the date and mileage from the user and all the rates can be calculated.

[June7]

The range ends should be in separate fields so can do a DLookup() on the surcharge records. You download this info as just the two fields? A query could parse the range ends into two fields with string manipulation functions.

SELECT CDbl(Left([DOE Fuel Charge Range], InStr([DOE Fuel Charge Range],"-")-1)) AS RangeStart, CDbl(Mid([DOE Fuel Charge Range], InStr([DOE Fuel Charge Range],"-")+1)) AS RangeEnd, [Surcharge Percentage] FROM tablename;

The alternative is to do as Paul suggests and retain each weekly download and save ID of surcharge table record into the towings record, then also have a field that identifies surcharge records as active/inactive and exclude the inactive from combobox RowSource list. Build query that joins the tables on the pk/fk fields and that will make the percentage available for calcs.

[pbaldy]

A combo for what? They won't be choosing a rate, it will be derived from the effective gas price. And an active field for what? I'm suggesting a table with fields for the gas price and its effective date or date range. From that, all I need to know is the date of a transaction to calculate the fuel surcharge rate.

[June7]

Good point, the selection of surcharge percentage could be automated with the date and fuel price criteria (again, with range ends in separate fields) and not need user to select the appropriate record from combobox. And would not need record ID saved to towings table.

[breakingme10]

Now how can I begin to add a charge like that in? I'm thinking maybe a prompt for the weekly gas price, then have it subtract 2 from that, then according to the result of that, find the price range from the list above and apply the corresponding percentages?

Yes i have a charges table.
Since the surcharge is a percentage of the total of the other charges, (except storage) I am still working on implementing them all together in the simplest most direct way possible without losing my mind. I am working on the weekly diesel rates table. i downloaded the data from eia.gov. i'm wanting to get this database up and running by jan 1.

PriceID	ChargeName	Price	UnitQuantity	Description
13	Long Distance Mileage Charge	$3.13	per Mile	Single vehicle: 8,000 or less. Over 25 miles.
2	Regular Mileage Charge	$3.75	per Mile	Single vehicle: 8,000 or less. 25 miles or less.
15	Long Distance Mileage Charge	$3.75	per Mile	Single vehicle: 8,001 - 12,000. Over 25 miles.
14	Mileage Charge	$4.25	per Mile	Single vehicle: 8,001 - 12,000. 25 miles or less.
16	Mileage Charge	$7.19	per Mile	Single vehicle: 12,001 - 40,000. Any distance.
18	Mileage Charge - Multiple Vehicles	$8.44	per Mile	Combination of vehicle. Any distance.
17	Mileage Charge	$8.44	per Mile	Single vehicle: 40,001 or over. Any distance.
28	Disconnect Drive Line; Remove Axle	$12.00	Flat Rate	8,000 or less
12	After Hours Fee	$15.00	per Quarter Hour	5:00 pm - 12:00 am and 12:00 am to 8:00 am, Monday-Friday, anytime Saturday, Sunday or a national holiday. If the holiday is on a Saturday or Sunday, the previous Friday and following Monday are considered to be included.
23	Registration Check	$15.00	Flat Rate	(Law Enforcement Tows)
29	Disconnect Drive Line; Remove Axle	$18.00	Flat Rate	8,001-12,000
4	Regular Outside Storage Charge	$18.00	per Day	Single vehicle: motorcycle, automobile, or light truck up to 20 feet in length.
31	Reconnect Drive Line; Replace Axle	$18.00	Flat Rate	8,000 or less
32	Reconnect Drive Line; Replace Axle	$24.00	Flat Rate	8,001-12,000
33	Reconnect Drive Line; Replace Axle	$24.00	Flat Rate	12,001 or over.
6	Outside Storage Charge	$24.00	per Day	Single vehicle or combination of vehicles over 20 feet in length but less than 30 feet in length.
30	Disconnect Drive Line; Remove Axle	$24.00	Flat Rate	12,001 or over.
24	PPI-Ownership/Lien Check	$25.00	Flat Rate
3	Regular Inside Storage Charge	$30.00	per Day	Single vehicle: motorcycle, automobile, or light truck up to 20 feet in length.
26	Rollback: 8,000 or Less	$30.00	Flat Rate	Dolly or rollback fee.
7	Outside Storage Charge	$30.00	per Day	Single vehicle or combination of vehicles over 30 feet in lenth and up to 8 feet in width.
9	Inside Storage Charge	$36.00	per Day	Single vehicle or combination of vehicles over 20 feet in length but less than 30 feet in length.
27	Rollback: 8,001-12,000	$36.00	Flat Rate	Dolly or rollback fee.
8	Outside Storage Charge	$42.00	per Day	Single vehicle or combination of vehicles over 30 feet in length and over 8 feet in width.
10	Inside Storage Charge	$42.00	per Day	Single vehicle or combination of vehicles over 30 feet in lenth and up to 8 feet in width.
11	Inside Storage Charge	$54.00	per Day	Single vehicle or combination of vehicles over 30 feet in length and over 8 feet in width.
25	PPI-Drop Fee GVWR <10,000	$75.00	Flat Rate
1	Regular Hookup Charge	$81.25	Flat Rate	Single vehicle: 8,000 or less.
5	Other Tow Charge	$85.00	Flat Rate
19	Hookup Charge	$93.75	Flat Rate	Single vehicle: 8,001 - 12,000.
20	Hookup Charge	$106.25	Flat Rate	Single vehicle: 12,001 - 24,000.
22	Hookup Charge	$118.75	Flat Rate	Combination of vehicles.
21	Hookup Charge	$118.75	Flat Rate	Single vehicle: 24,001 or over.

[breakingme10]

The mileage does change on a per tow basis, if the mileage 25 miles or over, we charge $3.13/mile, less than 25 miles is $3.75/mile. considering we are in a rural area, the mileage fees can get costly. upwards of $250 on one i saw the other day. I couldn't believe our driver drove all that way honestly.