Can Solver Tackle This?

[jose]

Hello All,

I'm not sure if this is the correct group to post to but I had a
solver question. I have been looking at the Solver tool briefly, and
have also viewed some Sample spreadsheets that showed its
capabilities. I was wondering if it could handle something like the
following:

A construction company currently has four large jobs, and each job
needs dirt hauled to it (a lot of dirt). They have three different
sizes of trucks, each costing different amounts of money. Also, time
is an important factor and these jobs need to be done in the quickest
amount of time. So... if I entered:

1.The amount of dirt needed at each job, as well as the round trip
time to each job site
2.The number of each truck available (lets say there are only fifteen
trucks in total available - Consisting of the three types), and the
cost of each unit
3.How much each unit could haul

Could solver give me an answer showing how many of each truck I need
for each job site to give me the cheapest and fastest method to haul
the material? For some reason, I believe that there are too many
constraints for solver but I am not too sure. If anyone thinks this
could work, or knows of a different program that would work, could you
please let me know. Any help would be greatly appreciated.

Thanks In Advance,

Jose

 

[Tushar Mehta]

Yes, Solver should be able to solve this. No, no optimization product
can solve the problem of "cheapest and fastest." The two may require
different solutions!

If this is a homework assignment, what have you done so far in the
context of an XL worksheet? Where are you stuck?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[jose]

Hello Again,

Tushar, thanks for the quick response. In answer to your question, no
this is not a homework assignment. I work in a related industry and
thought this might be a useful tool. There are programs out there
that do "similar" calculations but they are very complicated and have
many parameters that have to be entered (most of those parameters dont
apply to my situation). I just thought that I'd try and simplify
things a bit. I see what you mean when you say two different
solutions would be required.

One other parameter I forgot to mention was that each job would
require a minimum amount of dirt hauled to it each day (or a minimum
total number of trucks) in order to keep production up. I know this
would create another parameter but would this eliminate the use of
Solver?

As for where I'm stuck, I actually havent started yet. I wanted to
see if it was even possible before I tried it (my experience with
solver is minimum at best). What I was planning on doing was taking a
look at the SOLVSAMP sample spreadsheet to get some ideas on how to
set up a solver situation, and then try to build something on my own
that would suit my needs. If you have any ideas or direction that you
think would be useful, please let me know.

Thanks Again for the Help,

Jose

 

[upshaw211]

Hi, I think your problem would be very suitable for Solver. I made
worksheet that will solve a simplified version of your problem. Yo
can private message me your email and I can send it to you. I'm sur
you can modify my template to suite your specific needs.

To answer the question about Time vs Cost, I think you can solve tha
at the same time. Basically there are two ways. One easy way (but no
really accurate) would be to set a constraint for the maximum number o
trips you want your trucks to take. So, if each trip takes 2 hours an
you want them done in an 8 hour working day, just set the max number o
trips to 4 and Solver will adjust the number of trucks accordingly.
The other way (the better way) would be to set a value for time. Yo
can set a value according to the hourly wage paid to the truck drivers
the opportunity cost of the trucks (what could they be doing instead o
hauling dirt), the maintanance and gas cost, and the value of custome
satisfaction for a fast job. Once a cost for time is set, you just ad
that to your objective function and Solver will give you minimum cos
cosidering both the number and size of trucks and the time taken. Th
time cost will be subjective and not concrete; however, you're gettin
paid for your good judgement.

Mik