Building Your Business Plan Spreadsheet II

[ATeam]

To step out of the clutter and get back on topic, "Building Your Business Plan Spreadsheet II" (this thread) picks up clean from "Building Your Business Plan Spreadsheet."

I've added a couple of monthly expense items to the list and request that interested readers provide more.

We are building a monthly profit and loss statement for a hypothetical dry-box, D-unit, straight truck running under the FedEx Custom Critical flat rate program. When that part is done, the spreadsheet will be expanded to include other types of trucks and compensation arrangements.

Here's what we have so far:

MILES THIS MONTH

1. Dispatched miles (13,000 to use jjoerger's example)

REVENUE (Monthly)

2. Mileage pay: $13,000 ($1.00 per mile x line 1 in this case)
3. Fuel surcharge: $3,978 ($0.306 x line 1)
4. Accessorials: (zero assumed in this case)
5. Toll money paid to the truck: $150 (assumed per ATeam)

EXPENSES (Monthly)

5. Work Accident Insurance: $260 (13,000 x $0.02, per LDB)
6. Qualcomm Fees: $152 ($35x52/12, per LDB)
7. Bobtail Insurance: $150 (per jjoerger)
8. Fuel: $5,388 (9 mpg, line 1 miles, $3.73/gal. per TeamCaffee)
9. Tolls: $150 (per ATeam)
10. PM service - Lube, Oil, Filter: $250 (per jjoerger)

Notes:

Dispatched miles: (line 1) is a term used by FDCC to describe all miles paid under the flat rate program. This differs from other pay type like loaded miles, deadhead miles and percentage of load. Those will be accommodated later in the project.

Tolls: (lines 5 and 9) is a presumed amount based on what Diane and I pay (3 axles). If anyone has a better number to suggest, kindly do so. In the FDCC flat rate program, whatever is paid out in tolls (line 9) will be paid back by the company (line 5), or approximately so, I believe. Correct me if I am wrong.

Fuel: FDCC uses a 9 mpg fleet average when calculating its fuel surcharge for straight trucks so I did too. If you get better or worse than that, simply adjust the number. The $3.73/gallon figure came from TeamCaffee who shared her recent average (with FDCC discount). If your number is higher or lower, again, simply adjust the number.

May we have some more business expense items and suggested amounts, please?

EXPENSES (continued)

11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

 

[greg334]

Well I was put off by finding a locked thread so here is where I will post.

This has been done, a few years back I took the OOIDA spreadsheet and extensively modified it. I was ****** slapped for saying anything here on EO, I think the poster said it was a pathetic attempt. Well last year my company came up with a good one which is used as a base for my present spread sheet.

So to help others out and not to have Phil take credit for reinventing the wheel, here is how I use it.

I have three spreadsheets, all linked together with a specific purpose for each. One is for my loads within my fiscal year, one is my business costs outside of the everyday operations of the truck and the third is an interface sheet so I can use DDL and export to quickbooks.

The load spread sheet is the most complex. I put in the waybill number, the shipment number, the contact info, the starting odometer, arrival odometer, unloaded odometer for each load. I can indicate mileage for personal use, company move and a number of reasons - all drop down boxes. I also have it broken down in categories, general freight, reefer freight, hazmat, etc.... If I have the third sheet open, it will populate DDL with the waybill number and load info.

On the load finance sheet, I put in the total revenue, I could put in the dh rate, the fsc and a comprehensive accessorial charge area - reefer, pallet jack, dolly, blanket use, etc... with check boxes to indicate used but not compensated for the use.

The monthly costs are linked to this spreadsheet, and it allows me to put my monthly reoccuring costs in a separate sheet to make it simple. I do have an aggregate sheet with running expenses on my load sheet.

When I fuel, I kept it simple, odometer mileage, total cost - if I didn't add in an unloaded odometer, it ties the waybill number to the fuel stop. It figures the cost per gallon and I have a collum for reconciling my comdata charges which gives me my discount price. Again if I have the third sheet open, I put the info into DDL and it shows up in the load spread sheet.

It has a go/no go indicator that will show based on an accumulated data the validity of an offer.

Now it also has a few things that make it real nice for reporting a P&L statement, a break even point indicator based on accumulated mileage and running costs, and a comparison sheet for scenario comparison based on actual operating costs. I can graph my fuel mileage, my cost per mile, my revenue, and a whole bunch more.

I also have another component to it that taps the ECM for the data on fuel mileage which I am working on now.

THIS isn't rocket science, the best way to tackle this is to write it down to work the numbers and start putting together.

 

[jjoerger]

Prepass $14.50 per month
Laundry $32 per month
Truck Wash $52 per month
IRP Plate $50 ($600 per year)
Washer fluid and wiper blades $9.23 (5 month average year to date)
Showers $10 (we usually have to buy 1 a month)
Postage $10 per month
Minor mechanical repairs and parts $150 (5 month average year to date)
DOT Inspection $60 twice a year or $10 per month
Tires based on 200,000 miles $2100 or about $.0105 per mile

 

[jjoerger]

The software blocked p r e p a s s and p o s t a g e?

 

[ATeam]

Comments from pjjjjj and information made available in the Rate per Mile? thread prompted me to focus early on the various revenue types while building my "All Expediters" spreadsheet.

The very, very beginning of this work can now be viewed online. I am using Google docs to develop the spreadsheet. The spreadsheet can be viewed by clicking this link.

The question I have for the group is about revenue types. Please look at the revenue types listed. Does that about cover it, or are there other ways that expediters get paid?

Please keep in mind that this is a profit and loss statement. This is not about the miles. It's about the money.

In the revenue section, it does not matter what your rate per mile is or how many miles you drove. It only matters that you enter the appropriate dollar amounts in the appropriate cells. The monthly dollar amounts would be totaled up from your weekly settlements.

 

[Dreamer]

The software blocked p r e p a s s and p o s t a g e?

My fault, glitch fixed, sorry.

Dale


Posted with my Droid EO Forum App

 

[ATeam]

My all expediters spreadsheet project will require dozens of hours to complete, if not more. But the project is now far enough along to show where I am headed and what the final product will look like.

I am open to feedback and suggestions. Please review the instructions, spreadsheet and sample data pages and share, publicly or privately, whatever you care to share.

You will see that a big part of the project will be collecting the sample data. If you would like to help in this labor of love, kindly let me know.

One task will be to populate the sample data page. We will do it in small chunks, requiring as much or little time per day or week as you care to contribute. Notice what jjoerger contributed above. That is the kind of data I seek. It does not have to be your data exactly, just realistic numbers that show real-world revenue and expenses for each truck type.

As a few people get involved, it may be easier to do this work in private while the spreadsheet remains public. I will likely set up a private discussion page for that purpose.

It is already the case that this product is available to anyone who wants to download it free of charge. That will remain so. I am retaining copyright rights to maintain branding and quality control.

When we reach the point that Lawrence deems this a worthy product, it will be named EO's All Expediters Spreadsheet and made available as he deems fit.

Note that it may happen that one or more carriers may object to putting this information out there in a way that makes it easy for expediters and expediter wannabees to easily compare and contrast their options. If that happens, and if they pressure EO to not endorse the project, I will continue it in my own name.

With this online resource and with real-world numbers contributed by real-world expediters, expediter wannabees will no longer have to jump into the business blind. Experienced expediters will be able to enter their own numbers and run various scenarios to enhance their business decision making.

 

[greg334]

See I knew this is another "Phil Project".

It is already the case that this product is available to anyone who wants to download it free of charge. That will remain so. I am retaining copyright rights to maintain branding and quality control.

How do you Phil maintain copyright control over something that is in public domain already (OOIDA) and what others have done for themselves?

Well I decided to pull my from my website so not to interfere with the 'copyrighted' version of what Phil is doing. So those who have it, I will still maintain it but outside of that ...

 

[chefdennis]

greg...who cares....

 

[greg334]

greg...who cares....

Well Polly a few seem to like what I did, I describe it for others to get the idea of what I did and maybe that would help them.

So I did something, gave it to others, what did you do to share info or help others out?

 

[chefdennis]

I just shared the opinion of more here then you think..."Who cares"... greg.....

 

[lugnut1]

My all expediters spreadsheet project will require dozens of hours to complete, if not more. But the project is now far enough along to show where I am headed and what the final product will look like.

I am open to feedback and suggestions. Please review the instructions, spreadsheet and sample data pages and share, publicly or privately, whatever you care to share.

Some simple googie changes, you do know that open docs is crap for making a spreadsheet and free in this case is not better, EXCEL EXCEL EXCEL. 20 mins to make the changes you see on sample sheet page 2, 2, 2, ................................ LINK HERE https://spreadsheets.google.com/spr...eG9Udk0zOGp2ckpfRHc&hl=en_US&authkey=CKnTmYwD LINK HERE

 

[lugnut1]

My all expediters spreadsheet project will require dozens of hours to complete, if not more. But the project is now far enough along to show where I am headed and what the final product will look like.

I am open to feedback and suggestions. Please review the instructions, spreadsheet and sample data pages and share, publicly or privately, whatever you care to share.

Some simple googie changes, you do know that open docs is crap for making a spreadsheet and free in this case is not better, EXCEL EXCEL EXCEL. 20 mins to make the changes you see on sample sheet page 2, 2, 2, ................................ LINK HERE https://spreadsheets.google.com/spr...eG9Udk0zOGp2ckpfRHc&hl=en_US&authkey=CKnTmYwD LINK HERE

 

[ATeam]

Some simple googie changes, you do know that open docs is crap for making a spreadsheet and free in this case is not better, EXCEL EXCEL EXCEL. 20 mins to make the changes you see on sample sheet page 2, 2, 2, ................................ LINK HERE https://spreadsheets.google.com/spr...eG9Udk0zOGp2ckpfRHc&hl=en_US&authkey=CKnTmYwD LINK HERE

Thank you for your feedback but I am not sure I get your meaning. What are "googie changes?"

Excel is my spreadsheet product of choice but not everyone owns it or wants to. For someone starting out, a free spreadsheet program may be better than Excel because without a free program there may be no spreadsheet at all.

There has been no activity on my All Expediters spreadsheet in the last few days. As the flow of incoming truck expense information from readers has slowed, this has become an ongoing project that will be worked on as time permits.

I will be contacting drivers and fleet owners privately to get the expense infomation I need to complete the sample data tables.

 

[lugnut1]

Thank you for your feedback but I am not sure I get your meaning. What are "googie changes?"

Googie???.... Yeah, Googie is a form of modern architecture.

Some of the simple changes made in EXCEL were to give your spreadsheet a lift in looks. Unfortunately exporting the EXCEL file as an ods. for use in Google docs does lose some of the EXCEL enhancements.

I’m sure the goal is to keep it simple for the folks.

 

[ATeam]

Googie???.... Yeah, Googie is a form of modern architecture.

You learn something new every day.

 

[workaholic48]

A Team: I truly appreciate your efforts. This is exactly the sort of thing I need to project anticipated costs per mile, and to see if this would be more profitable than working my 3 part time (60-70hrs/wk) jobs. Right now I am gathering information, and saving money for a down payment on a used straight truck. Last week I posted on EO that I calculated 85.5cpm, and spent the week wondering how anyone could make a living as a solo D unit O/O.

Thanks again

Workaholic48

 

[nightcreacher]

I use a 2 sided envelope I had made 30 years ago.Each envelope is for each load I do
On the front is where trip starts,tghen where loaded and of course where going to deliver
there are 11 columns from fuel additves to fuel gals and fuel price both for truck and reefer if you have one.There is also place for oil and tolls,start mileage stop mileage ,and a place to show what the loaded miles and dead head miles were.
Back of enevelope has from left to right,mkiles of load,pay wash jobs repairs parts,oil chnges misc motel and driver pay.
now going down the enevlope shows dead head to pu,load pay,hazard pay,detention pay,stop off charge,total,advance on load,charges for advances total expense and balance of that load
In the envelopes all the receipts are kept,and at the end of week,everything on the envelope is put into my computer

this might not work for everyone,but works great for me

 

[skyraider]

I write down each days expenses in a notebook. By the end of the week u will either have a stroke on how much u waste or be a happy camper on how frugal u were. Ps, dont forget to eat.

 

[ConfusedMuse]

I've noticed that no one has included these items in their spreadsheets, truck payment, physical damage insurance, and of course 28% tax ( can't forget Uncle Sam), and aren't we all saving something for the next truck, Tires, brakes, and when you run into a DOT officer that is having a bad day,and they decide to make YOU the example, do you have something set aside somewhere that allows you to keep running? ie a typhoon day fund.