Hope someone here can help.
I'm trying to create a pricing tool for (insert large British monopolistic telecommunications organisation here) private circuits - basically on some products you pay for each end connection and then you pay a set price per metre or kilometre for the main link in between, which changes after a certain threshold. EG;
If site A and site B are 26 kilometres apart, you pay £3500 per end, then £310 per kilometre for the first 15 kilometres and £105 per kilometre for the subsequent ones.
There are various products that have different criteria and I am trying to come up with a way of pricing the same 2 postcodes (sites) using different product, quickly on the same spreadsheet.
IE:
sheet one would be the cover sheet with the postcode info and the main link distance.
sheet two would calulate the main link distance charge based on the above criteria (15km X £310 + 11km x 105)
sheet three would be for a different circuit product with a different main link charge scale and so on.
The problem is what is the calculation in the main link cell? You have get the cell to calulate the first portion of the main link distance at a certain rate and the next portion at the next rate. It has to be done on a cell reference basis in case of price changes and also to enable multiple site to site quotes.
Alternatively someone who works for said organisation could send me the proper tool.
Any help appreciated.
I'm trying to create a pricing tool for (insert large British monopolistic telecommunications organisation here) private circuits - basically on some products you pay for each end connection and then you pay a set price per metre or kilometre for the main link in between, which changes after a certain threshold. EG;
If site A and site B are 26 kilometres apart, you pay £3500 per end, then £310 per kilometre for the first 15 kilometres and £105 per kilometre for the subsequent ones.
There are various products that have different criteria and I am trying to come up with a way of pricing the same 2 postcodes (sites) using different product, quickly on the same spreadsheet.
IE:
sheet one would be the cover sheet with the postcode info and the main link distance.
sheet two would calulate the main link distance charge based on the above criteria (15km X £310 + 11km x 105)
sheet three would be for a different circuit product with a different main link charge scale and so on.
The problem is what is the calculation in the main link cell? You have get the cell to calulate the first portion of the main link distance at a certain rate and the next portion at the next rate. It has to be done on a cell reference basis in case of price changes and also to enable multiple site to site quotes.
Alternatively someone who works for said organisation could send me the proper tool.
Any help appreciated.
