Hello,
I am brand new here and just found this site today. I have average to probably below average Excel skills. I am using Excel 2003. I am currently trying to setup a spreadsheet to perform a comparison of products. I want the spreadsheet to be able to use drop down lists and user defined inputs to calculate a final cost. This sounds simple but there are several factors that are throwing me. Here is a text description of my spreadhseet
Column A is a drop down list for the products (easy enough)
Column B is the rate of the product that is user defined. Again straightforward except that all the products are not used in the same type of units, some are used in ounces, some in quarts, and some in pints.
Column C is the Pricing of the product which unfortunately is based on what month you are buying the product. This changes with each product and month.I have this set up as a drop down box but again it needs to know what product we are talking about and while it shows a month it needs to use the $ value in the calculation
Column D is the Program % discount . This is a user defined number from 0 to 100.
Column E is the final cost calculation (and most difficult for me) which would be the the rate of the product equated out to a gallon basis (again I need a way to set the proper calculation based on the unit of the particular product, for example if its pints I divide by 8 and if it is ounces I divide by 16, etc.) multiplied by the price per gallon (appropriate for month selected) multiplied by
(100 minus program %) = final cost
For Ex
Product = G
Rate = 3.0 pts
Pricing = $49 / gal
Program = 12%
So final cost for G in the case would = 3.0pts / 8 pts/gal * $49 * (100-12)
But I want the fleixibility to choose products, choose any reasonable use rate and automatically choose units based on product, have the price for the chosen product be picked from month selected via drop down list, and have the final cost calculation work.
The trick is to have an easy to read calculation sheet that allows maximum flexibility rather than show some complicated matrix spreadsheet than forces you to choose only a couple rates for comparison.
I hope I have been clear enough and I would greatly appreciate any and all help. I have been working on this all day and have been to so many websites I am just confusing myself more and more!
On the flipside if I can figure this out then I can utilize this for more than one product segment.
Thanks again in advance,
Wump
I am brand new here and just found this site today. I have average to probably below average Excel skills. I am using Excel 2003. I am currently trying to setup a spreadsheet to perform a comparison of products. I want the spreadsheet to be able to use drop down lists and user defined inputs to calculate a final cost. This sounds simple but there are several factors that are throwing me. Here is a text description of my spreadhseet
Column A is a drop down list for the products (easy enough)
Column B is the rate of the product that is user defined. Again straightforward except that all the products are not used in the same type of units, some are used in ounces, some in quarts, and some in pints.
Column C is the Pricing of the product which unfortunately is based on what month you are buying the product. This changes with each product and month.I have this set up as a drop down box but again it needs to know what product we are talking about and while it shows a month it needs to use the $ value in the calculation
Column D is the Program % discount . This is a user defined number from 0 to 100.
Column E is the final cost calculation (and most difficult for me) which would be the the rate of the product equated out to a gallon basis (again I need a way to set the proper calculation based on the unit of the particular product, for example if its pints I divide by 8 and if it is ounces I divide by 16, etc.) multiplied by the price per gallon (appropriate for month selected) multiplied by
(100 minus program %) = final cost
For Ex
Product = G
Rate = 3.0 pts
Pricing = $49 / gal
Program = 12%
So final cost for G in the case would = 3.0pts / 8 pts/gal * $49 * (100-12)
But I want the fleixibility to choose products, choose any reasonable use rate and automatically choose units based on product, have the price for the chosen product be picked from month selected via drop down list, and have the final cost calculation work.
The trick is to have an easy to read calculation sheet that allows maximum flexibility rather than show some complicated matrix spreadsheet than forces you to choose only a couple rates for comparison.
I hope I have been clear enough and I would greatly appreciate any and all help. I have been working on this all day and have been to so many websites I am just confusing myself more and more!
On the flipside if I can figure this out then I can utilize this for more than one product segment.
Thanks again in advance,
Wump
