Spread sheet expert, I need help

wiggyuk

wiggyuk

wiggyuk

Associate
Joined
1 Oct 2004
Posts
707
Location
Rowley Regis, West Mids
Hi all I was just wondering if there was anyone that could help me make up a spreadsheet for my work.

What I need is a spreadsheet for transport, to basically find out who would be the cheapest company to send our goods on.

I just want a sheet so that I can type the weight per KG in and it gives me a price for each different transport company we use so then I can send it on the cheapest.
 
This should be fairly easy to do - it is only complicated by the different ways in which each company choses their weight 'boundaries'.

How many companies are you talking about?

Is it possible to post each of their pricing schemes for the various weight categories they specify? Or are there too many?

You can have a 'generic' solution that will work for up to 'x' categories - but it would be overly complicated if we're only taking about 3 or 4 companies and some simple pricing scheme's.

/edit - different couriers specifications make it slightly less easy than expected ;)
 
Last edited:
The is 3 companies, one company as weight per KG depending on area post code. Another as price upto 20kg then price per kg up to 100kg then another price per kg over 100kg. plus there is n/day, 3/day & AM delivery.

See below first carriers list.
Carrier01.jpg
 
Last edited:
Ok I'll try to suggest a 'design'

User inputs (things you will type into excel)

* Weight of parcel being shipped
* Destination (Post Code?)
* Desired Delivery Time (next day, 3days, 7days, etc) [optional]


Excel 'knowledge' (things excel needs to know to solve the problem)

* Does the company have different rates for varying weights?
* If so, what are the boundaries (eg, 0-20kg, 20kg-100kg, 100+kg)?
* Also, what is the price/kg for each of these boundary groups?
* Does the comapany change the rate depending on Destination?
* If so, what is the 'formula'? (eg, do they charge £x.xx for the first 50miles, and £y.yy for the next 100miles? - or do they charge £a.aa to deliver to a certain Post Code, and £b.bb to deliver to another? If so, do you know what each of these values is for any possible Post Code you may want to ship to?)
* How do their charges change depending on 'Desired Deliver Time'? (eg, do they add £x.xx on for AM delivery, or do they charge a percentage of the total cost of the item?, what about for the other shipping options (nextday, 3days, etc)?


Output (what excel calculates for you, from the above information)

* The price of shipping the item with each company
* The cheapest company to use

As you can see, Excel can only arrive at the right Output if it has been given the 'knowledge' for any possible inputs. This knowledge that it needs can be quite detailed. But if you know all the answers to the questions I listed, then it is easy to make a spreadsheet that will do what you want.

As I said in my first post, the only thing that makes this complicated, is trying to provide excel with all the 'knowledge' it needs. It needs to know exactly how each company breaks down its shipping prices.

Sorry I can't offer a proper detailed description, but it really does require some more detailed knowledge of how each company break down their pricing scheme.
 
Ok cheers, so can I give you the info and you do the spreadsheet ? or am I asking too much now ?
 
If you know all that info. Then feel free to post it and I'll see whta I can do :)

/edit - just seen your edit - I'll have a go at getting started on courier 1
 
Ok, because each courier have their own specific 'boundaries/limits' then it is going to make it a bit more complicated :)

Basically all the information 'captured' in those screen shots - needs to be fed into Excel, for it to use when doing the calculations of the price for each courier.

I'm afraid that it'll take me a bit too long to write a solution on my lunch hour. I'll try and do something when I get home, if I've got time.

Maybe someone else will be able to suggest a good solution before then though :)

Sorry I couldn't be much more help - its going to take a bit more time and effort than I first thought :)
 
div0 said:
Sorry I couldn't be much more help - its going to take a bit more time and effort than I first thought :)
Click to expand...

You have been more than helpfull thanks for giving it a crack, if you can get chance to have a look I would be very gratefull.

I will post the 3rd carrier soon.
 
My advice would be to input the 3 couriers info into excel and then host it somewhere so some kind sole can sort out a few calculations and get you what you want.
 
No sorry - I've been house hunting the last 2 nights. I'm out tonight as well, so I'm not sure I'll be able to do anything in the immediate future. If I get a chance over the weekend I'll certainly have a go at something. Even if it's just to suggest a way for you to get started.

Mark M's advice was quite good.

The main time consuming part is just entering all the information from the screenshot's into Excel.

If you made a spreadsheet with some small tables containing the same info as the screenshots, then it wouldn't take too long for someone else to write the 'calculations' needed to work out the price of any given package.

Might be your best bet.
 
You must log in or register to reply here.
Back
Top Bottom