We sell services to our customers and we form our prices based on our production costs and on our subprovider's prices.
Our subproviders give us their pricelist (e.d. for Service 1) which is formed based on several input parameters e.g.
Parameter 1 (speed)
Parameter 2 (distance)
Parameter 3 (technology)
and the result is subprovider's price for Service 1.
I made spreadsheet which calculates price for Service 1 from subprovider 1, based on input parameters 1, 2 and 3, and it is fairly complex model.
We have several our services e.g. Service A, Service B, Service C, Service D, and in their price calculation we use Service 1 (Subprovider 1) price calculated as described above + other costs related to work done by us.
For that purpose I created main sheet where I define all input parameters and display the result (i.e. it displays an offer), created the second sheet where calculations for the total service price are perfomed and created third sheet which contains subprovider's pricelist and where subprovider's price for Service 1 is calculated.
Logic is that I pass parameters from main sheet to third sheet, calculate the price of Service 1 provided by subprovider and then return result (price of Service 1) to second sheet where additional computation is done, additional costs are calculated and end price for Service A calculated. Then I return price to main sheet (our offer for Service A)
That all works fine.
Then I needed to do the same for the Service B.
Again, I did the same logic:
Sheet 1 (offer with input parameters and result) -> Sheet 3 (calculates Service 1 price) -> Sheet 2 (calculates total Service A price) -> Sheet 1 (displays Service A price)
The problem with this model is that for each of our services where we use subprovider's Service 1 (Service A, Service B,...) i need to copy Sheet 3 (containing calculation of subprovider's Service 1) to be able to complete the calculation.
Copying for the first time is not a problem, but problem is if subprovider changes his prices or the way he calculates the cost of his service (which can be a complicated model).
In that case I would need to know in which of our services subprovider's Service 1 is used and change it in all those places, which can be complicated.
For that reason, I would like to put sheet 3 (price and calculation of subprovider's Service 1 price) into separate file (workbook) and use it in multiple models (calculations) i.e. in calculating prices for Service A, Service B, Service C etc. In that way, if subprovider changes price or model, I would need to change it in one workbook (file) only.
Additionaly, if subprovider's prices and calculation model are in a separate workbook, I should be able to use several subprovider's each having their own workbook with prices and calculation and easily switch between them.
If the prices of the subprovider's Service 1 are in a simple table, I know it's possible to reference to the table in external workbook (file) and return result, and I can reference the same table from multiple external workbooks (Service A, Service B,...)
However, prices of the subprovider's Service 1 use several input parameters (e.g. 4 or 5) and complex computations to calculate the Service 1 price.
So, my question is: Is it possible to pass the same input parameters from multiple external workbooks (files) into Service 1 workbook, which will do the computation and return different result (i.e. price) to each external workbook where it got input parameters from?
If not, is there any workaround?
Our subproviders give us their pricelist (e.d. for Service 1) which is formed based on several input parameters e.g.
Parameter 1 (speed)
Parameter 2 (distance)
Parameter 3 (technology)
and the result is subprovider's price for Service 1.
I made spreadsheet which calculates price for Service 1 from subprovider 1, based on input parameters 1, 2 and 3, and it is fairly complex model.
We have several our services e.g. Service A, Service B, Service C, Service D, and in their price calculation we use Service 1 (Subprovider 1) price calculated as described above + other costs related to work done by us.
For that purpose I created main sheet where I define all input parameters and display the result (i.e. it displays an offer), created the second sheet where calculations for the total service price are perfomed and created third sheet which contains subprovider's pricelist and where subprovider's price for Service 1 is calculated.
Logic is that I pass parameters from main sheet to third sheet, calculate the price of Service 1 provided by subprovider and then return result (price of Service 1) to second sheet where additional computation is done, additional costs are calculated and end price for Service A calculated. Then I return price to main sheet (our offer for Service A)
That all works fine.
Then I needed to do the same for the Service B.
Again, I did the same logic:
Sheet 1 (offer with input parameters and result) -> Sheet 3 (calculates Service 1 price) -> Sheet 2 (calculates total Service A price) -> Sheet 1 (displays Service A price)
The problem with this model is that for each of our services where we use subprovider's Service 1 (Service A, Service B,...) i need to copy Sheet 3 (containing calculation of subprovider's Service 1) to be able to complete the calculation.
Copying for the first time is not a problem, but problem is if subprovider changes his prices or the way he calculates the cost of his service (which can be a complicated model).
In that case I would need to know in which of our services subprovider's Service 1 is used and change it in all those places, which can be complicated.
For that reason, I would like to put sheet 3 (price and calculation of subprovider's Service 1 price) into separate file (workbook) and use it in multiple models (calculations) i.e. in calculating prices for Service A, Service B, Service C etc. In that way, if subprovider changes price or model, I would need to change it in one workbook (file) only.
Additionaly, if subprovider's prices and calculation model are in a separate workbook, I should be able to use several subprovider's each having their own workbook with prices and calculation and easily switch between them.
If the prices of the subprovider's Service 1 are in a simple table, I know it's possible to reference to the table in external workbook (file) and return result, and I can reference the same table from multiple external workbooks (Service A, Service B,...)
However, prices of the subprovider's Service 1 use several input parameters (e.g. 4 or 5) and complex computations to calculate the Service 1 price.
So, my question is: Is it possible to pass the same input parameters from multiple external workbooks (files) into Service 1 workbook, which will do the computation and return different result (i.e. price) to each external workbook where it got input parameters from?
If not, is there any workaround?
