November 16, 2019 at 7:10 pm
Hello all,
Looking for all the smart people out there who can hear a problem/desired outcome and come up with a solid formula or process for coming up with that outcome. Ideally using SQL, but I don't want to hinder brainstorming here.
I have trip data for a company who transports passengers. We often have to predict certain aspects of the business, like how we can gain efficiencies by certain changes in the service. Using certain known factors about the business, I'm trying to come up with a way(s) to predict exactly how many hours (and therefore dollars) we could save by transitioning some of the service to a different service provider. This would consider things like, how many trips are done per route, how many drivers are used to drive the routes, how productive are the routes (=trips/hours), and what is the impact of removing some trips (which trips would be best?) to move to a lower cost provider, keeping in mind that the more trips we do, the better (for productivity). But the trade offs would be if we move enough/the right/right amount of trips, we could be even more efficient by reducing overall driver headcount or even closing the garage at specific periods of the day.
If anyone is interested in discussing, let me know.
Thanks!
This can get very complex and I'm not sure how to go about seeking help like this, just looking for someone who is interested in solving problems and has a strong mathematical background.
November 17, 2019 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 18, 2019 at 5:59 am
Interesting... what constitutes a lower cost provider? In other words, why would they possibly be a lower cost on one route but not another? I might see a difference depending on where their hubs might be so I'll ask, what kind of vehicles are you dealing with??? Planes, Busses, Cars, Trains, what??? And, again, what makes one provider less expensive on a given route?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 18, 2019 at 10:57 pm
That sounds dangerous, simply because reduced availability will likely drive customers away, so the efficiencies that are assumed to be gained are really lost completely. Customers pay for their own efficiency of time, not yours.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
November 19, 2019 at 12:12 am
That sounds dangerous, simply because reduced availability will likely drive customers away, so the efficiencies that are assumed to be gained are really lost completely. Customers pay for their own efficiency of time, not yours.
You've stated all of that much more eloquently than I ever could. My initial reaction was "This sounds like a "going-out-of-business" plan. I'll also admit that business plans are frequently like humans... the differences between genius and insanity are sometimes indistinguishable. What looks like a "going-out-of-business" plan on the one hand could be very effective outsourcing on the other.
Like SQL Server and every other thing in this world, "It Depends". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2019 at 5:23 pm
Hello all,
Thank you for your input here. It's interesting to see how the question is being interpreted; I'm seeing a few assumptions being made but I'm not sure if that's because I failed to provide some piece of information that I didn't think to add at first. You're all assuming this is a business that is trying to be on the road as much as possible to get as much business as possible, when that's not technically true. This is for a bus service, and we have to stay within budget on number of hours we put out each day to meet the demand which changes every day, as this service is an hourly cost. So we need to be able to transport as many passengers as possible in the fewest number of hours/drivers possible, which means we need to group trips wherever possible. There are some trips which don't group well and cause routes/drivers to travel unproductively to complete the trips so those would be ideal to send to the other service provider, which is a per trip cost.
Ideally we would try to see the schedule of how trips are scheduled onto the hourly routes and cherry pick trips which we can kick off to the other provider but that involves some local geographic knowledge of the area and gets far more complicated for this exercise. I'm looking for some generalizations to be had from a high level perspective in how to determine which trips/how many could be identified to send to the other service provider. What are some of the factors we would need to know for this? If we move x number of trips from the hourly provider to the per trip provider, how does that impact our hourly costs? Can we realize any savings in hours (by cutting routes/drivers entirely? or reducing how long routes are in service?). If we know the per trip costs and the hourly costs we can determine savings, but determining the actual number of hours to be saved is the real question here.
Again, this is a very complex situation and I don't want to ask for more than can be provided. Just trying to expand my thought process and see if there are any ideas out there I'm not thinking of for figuring this out and trying to make it a model to be used repeatedly. I may be crazy to think there's even a methodical way to do this, but thought I'd try.
; )
November 20, 2019 at 9:31 pm
Well, how do you identify "travel unproductively"? Score each driver/route based on what is there currently, see who your underperformers are, search for common traits or something.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
November 21, 2019 at 12:05 am
Productivity is defined on a route level (and rolled up to all hours for the day) divided by trips completed. So if we did 1500 trips in a total of 500 hours our productivity would be 3.0 (which is good). Generally speaking, if trips are grouped together where we travel fewer miles to complete them (because we're picking and dropping at or near the same address) then that means good productivity, but I couldn't necessarily say what quantifies a "good" trip vs a "bad" trip, in relation to what improves or decreases productivity. It's all about reducing total miles and time overall.
Perhaps if we totaled miles and time on each route with trips scheduled as-is, calculated productivity, then started picking off trips on each route that were not within a specific radius of other picks and drops on that route, then figure out how many minutes are saved by kicking that trip off the route. Then I would be able to quantify it in minutes/hours saved. So, two things:
1. how to identify trips considered "unproductive" or driving down productivity of a route (by increasing miles/time spent to complete them).
2. how to determine the minutes saved on that route if that trip was removed from the route.
Keeping in mind that these trips (pickups and drop-offs) are mixed in with each other resulting in shared rides in some cases. So it's not just a simple matter of seeing this trip picking up at this time and dropping off at this time and just calculating direct travel time for impacts on a route.
For #1 above, for example, would it make sense to take each event address scheduled on a route and draw a radius around the address and do a comparison to each of the other addresses on the route to see if they all fall within a specific radius of each other? to try ensuring they're all travelling in the same general area. I might inadvertently be venturing into the far more complex situation of trying to create a scheduling algorithm....
November 21, 2019 at 1:38 am
All of that is fine but... how do you make money? You could achieve a huge number of trips per hour (your definition of "productivity" above) and not make a penny (which is actually losing money because trips cost money in so many ways).
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2019 at 1:41 am
I think we're getting off-track here.
Without going into too much detail, this is the business process and the goal here is to see how we can identify specific trips to move to a lower-cost per-trip cost provider, while not too negatively impacting the productivity of the hourly provider. It's a balance that needs to be determined to plan and estimate costs from a high level.
November 21, 2019 at 7:12 pm
Seems to me the problem is "exploratory" in nature. I'd like to share what I'd do in this case.
First, I would start off by creating a database of whats going on now, in its "perfect" form. I'd imagine you have a "schedule" of your current routes, I'd try to get this in a few tables, the header could be a schedule, the detail could be the stops, miles between stops, arrival, departures, etc. Maybe some supplemental like fixed costs, estimated variable costs, maybe possibilities to speculate on low and high cost ranges, guesses at returns, sales projections, etc...
Next, I would add to this whats actually going on. Do you have any history of your trips? I'll call them "buses" because that's what I'd probably be experienced with. For each actual schedule or trip execution, can you fill out the costs, the stops, times (sure to be variable, etc)? Really, while I know you can't "judge future returns from past performance", at least you can get an idea of what has historically varied over time right?
Then maybe you can instantiate some "schedule templates" to attempt to predict future stats based on how you instantiate those schedule templates, those bus runs, for instance maybe you've tentatively identified some of those schedules to "outsource?" Absolutely keep those records identified as "prospective" instead of "history". You'll want to really hedge your bets here tho, as you're really stuck making some assumptions no matter which path you take. Maybe your previous bus trips have been profitable, but now they've torn up highway 95 adding hours to a schedule and your bus is running really really late and you didn't take that into account...
At best you are really just offloading calculations, its up to you to temper those calculations by not feeding them unrealistic assumptions or inaccurate history (hopefully you have some history right?) Still, the entire thing is speculative in nature to SOME extent and you have to keep that in mind at all times, all you can go on is what you've learned by previous trips and your knowledge of reasonable expectations for any future projections, right?
Also remember, you're getting suspiciously close to the "travelling salesman" problem which is "NP hard" where the "NP" is an abbreviation for "not gonna program that because its too".
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply