recapping scenarios

Topic

New Reply

HI, I’ve been browsing here a bit and seems like there’s a few people here who really know their excel.

I wonder if anyone can help me with a problem I am having.

I have created several scenarios for a pricing bid spreadsheet I constructed. I am using 6 bidders. The variables I enter are basically for two different parts of it, however a third and fourth part do calculation with the first two parts.

Part A ..points out of 10
Part B ..points out of 10
Part C (which is Part A & ..points out of 20
Part D (which is Part A/ ..points out of 10

In each case the bidder with the lowest score gets full marks, then the other bidders marks are prorated according to how their score compares with the lowest score. There are valid reasons for this which aren’t really important to what I want to accomplish.

I can run various numbers thru my scenarios and get resulting scores. However I need a method for comparison.
With so many potential combinations I need some method of showing what various bidding stategies will do to the total scores. For instance I have found if someone zero bids part A, and isn’t too outlandish on Part B, then that bid is almost impossible to beat. These points will be added together with some qualitative points to come up with a final winner, but I need to be able to show what various pricing strategies will do to the spread in points with this piece. Can anyone suggest a method of recapping the various combinations I might try. At the moment I can only fit 2 full scenarios to a page which makes it difficult when trying to show the different strategies.

Hope I’ve explained it well enough.
Any suggestions would be greatly appreciated.

Reply | Quote

Replies

Hmm… Capri, what are you trying to do exactly?

Reply | Quote

It’s hard to tell what you are looking for but how about one scenario to a sheet, create a new Summary sheet, and develop a summary like this:

r/c	A	B	C	D	E	F
1	Scenario #	1	2	3	4
2	Category	Weight				
3	A	10	0	1	2	3
4	B	10	5	4	3	2
5	C	20	4	0	2	4
6	D	10	1	4	0	6
7		Score	2.8	1.8	1.8	3.8

The category/scenario rankings in c3:f6 come from the individual scenario evaluation sheets.

The score formula in cell C7 is:
=SUMPRODUCT(c4:c7,$b$4:$b$7)/SUM($b$4:$b$7)

which is copied across the scenarios in row 7.

If this is nothing like what you are looking for, perhaps you could attach a simplified example workbook?

Reply | Quote

Here’s a sample of just the scenarios….the shaded cells are the ones that change…I have dozens of combinations that I need to try….but need a consise way to show the results…eg low or no bid for A…other bids..low…or other bids a range..

Reply | Quote

Most important is your presentation objective, e.g., what are you trying to demonstrate; that will help frame how you summarize the data.

However, I played with this and added a summary sheet, attached in a self extracting pkzip file for brevity. Copy the attachment to this message (in IE, right click the attachment, Save Target As) to wherever and double-click it from File Explorer to extract. Also for fun I was able to recreate the entire scenario 6 calculation on the summary page, independent of the scenario pages, in row 27.

Reply | Quote

Thanks John,

I really appreciate your help. I just couldn;t think of a more concise way to show results and you solution is great. What I have to do is be able to show management a number of what ifs, so in addition to the numbers on one page I might show several scenarios where the one bidder zero bids part A, in relation to other possible bids by competitors and what effect if would have on the points. This isn;t the complete picture, but say they barely scraped a pass mark in the quality portion, but zero bid Part A. In most cases that would give them a considerable margin of points in the pricing section. There doesn;t seem to be as much a spread in the quality section, so we might end up with a low quality winner who go there because of their pricing strategy, which is not what we want. I also have to see how they would do in relation to a very high quality bid, that comes with a high price. Obviously we are looking for a good price, but we don’t want the quality to suffer so it’s a juggling act to be able to put a fair and equitable process in place that ensures we get the best of both worlds.

There are also a number of other possible situations…that was just one example…but we have to be prepared for all possibilities and our scoring system has to be defensible so that we aren;t accused of bias for or against any one bidder. Because of the size and nature of the project, all bidders will have spent a great deal of time and money preparing their bids and the ones that don’t win are going to want to know why?

Reply | Quote

This is less of a technical Excel problem and more of a presentation challenge. I often (OK, nearly always!) use the summary page approach to presenting complex data. In your case you may want to sort the scenarios by contrasting groups; low price & low quality, set next to high price & high quality, etc. Depending on your audience, you may want to present the results graphically.

Something I’ve done once or twice is make up a fake logarithmic ranking scale to inflate or deflate a ranking. =MAX(0,-LOG(ACTUAL SCORE/OBJECTIVE)/FUDGE FACTOR) is a price inflator where I’m pushing up extra low prices towards a common objective; the fudge factor changes the slope of the logarithmic effect. This example does the opposite of lowering the ranking of a low bidder, but I hope you see the concept. A straight arithmetic scale will let a low quality very low price bidder look feasible.

Good luck with your analysis.

Reply | Quote