Log In
Register
Home
Active Member
September 23, 2020
Offline
I want to make a chart where prices for different parts can be compared. So, I have a product that consists of many parts. For each part a price has been requested from different suppliers. This means that the different parts has a price offer from different suppliers. The goal is then to find the price offer that is cheapest and multiply that with how many of that specific part is needed and then calculate a total price for the whole machine/product based on the cheapest price from each part. So, how can this be best visualised so it doesn't leave many boxes blanc? Any suggestions?
Thanks!
Trusted Members
December 20, 2019
Offline
Can you post some examples so we dont have to guess or make up incorrect data?
Active Member
September 23, 2020
Offline
This shows an example with just a few parts and suppliers, but in real there will be up to almost 200 parts, and maybe 20-30 different suppliers. And not all parts will have an price offer from all suppliers, they make different things. So, to avoid having too many columns, is there a better way to present this, so that the last column will find the cheapest price between the relevant dealers and calculate in the last row a total price for the whole machine?
[Image Can Not Be Found]
Active Member
September 23, 2020
Offline
October 5, 2010
Offline
Hi Rebekka,
How about using conditional formatting to highlight the lowest price for each part, the use some MIN functions to pull out the lowest price, which you've already done in the last column?
Then just SUM those values in the last column to get the cost or each product.
But without your actual workbook I can't offer anything else. I can't recreate your data.
Regards
Phil
Active Member
September 23, 2020
Offline
Here is the file.
But my main question/problem is tips on how this can be presented in a good visualizing way so that one does not have to scroll far to the right to find the best price. Is there a way to avoid getting so many blank routes? Since there are many parts that do not have a price at all dealers.
This list will be used as a BOM list where one can go in to see the different prices dealers have given and then see if they should look for even cheaper prices to reduce the total cost of the machine, if it makes sense.
PS. I just used 4 suppliers and 14 different parts in this example, but there will be way more suppliers-columns.
Thanks for help!
July 16, 2010
Offline
Hi Rebekka,
Thanks for sharing your file. You need to unpivot your data and put it into a proper tabular format. Then you can use Tables or PivotTables to extract the data and with Slicers your users can filter the data to only see the type, part number or supplier they're interested in.
In the attached file I've used Power Query to unpivot the data layout. Then on the Query sheet you'll see there are Slicers that allow you to filter the table. Alternatively, you can use a PivotTable to filter the data (see Pivot sheet), which will also enable you to sort the total cost from smallest to largest so the cheapest price always appears at the top of the list.
I hope this gives you some ideas to work with.
Mynda
1 Guest(s)
