What Statistical Tool To Use

[ExcelHopeful]

Hello,

I hope you can all bare with me as I do not know the proper statistical terms.

Goal: I have 30% of my data (both numerical and text). How can I use statistics (probability) to estimate the remaining data?

Info: The table below is a simple example of my real data set. Instead of 9 suppliers, I really have 593. For every supplier I do have the data for their total spend, but I do not know their metal makeup, nore do I know their Fixed contract status. Therefore, I would like to estimate those by using that original 30% as the weight. I know this is very unreliable, but my manager says he'd rather see some data than no data...even if it's just estimates.

I appreciate any help, thank you.
[table="width: 700, class: grid"]
[tr] [td]Supplier[/td]
[td]Total Spend[/td]
[td]Fixed Contract (Y/N)?[/td]
[td]Metal 1[/td]
[td]Metal 2[/td]
[td]Metal 3[/td]
[td]Metal 4[/td]
[td]Metal 5[/td]
[/tr]
[tr]
[td]Supplier 1[/td]
[td]$132.88[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Supplier 2[/td]
[td]$122.67[/td]
[td]Yes[/td]
[td]20%[/td]
[td]20%[/td]
[td]20%[/td]
[td]20%[/td]
[td]20%[/td]
[/tr]
[tr]
[td]Supplier 3[/td]
[td] $187.54[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Supplier 4[/td]
[td]$126.77[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Supplier 5[/td]
[td]$108.55[/td]
[td]Yes[/td]
[td]5%[/td]
[td]75%[/td]
[td]10%[/td]
[td]5%[/td]
[td]5%[/td]
[/tr]
[tr]
[td]Supplier 6[/td]
[td]$101.82[/td]
[td]No[/td]
[td]30%[/td]
[td]30%[/td]
[td]5%[/td]
[td]5%[/td]
[td]30%[/td]
[/tr]
[tr]
[td]Supplier 7[/td]
[td]$115.05[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Supplier 8[/td]
[td]$125.87 [/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Supplier 9[/td]
[td]$147.72[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[/table]

 

[Ben Nevis]

If you think of the suppliers where you have the data for their metal make up and fixed contract status as a sample, then it's actually quite a big one; 30% of 593 = 178, so if you calculate the averages for the suppliers where you have this information you can use them as estimates for those you don't. Taking the data for Suppliers 2, 5 & 6 from your table above; 1 in 3 has a fixed contact and the proportions of Metals 1 to 5 are 18%, 42%, 12%, 10% 18% respectively. So if you apply those numbers to the other 6 suppliers you will have a calculated a basic estimate based on the average of your known data.

If the Suppliers you have information for are not typical of those you where you don't, statistics would allow you to adjust for that, but you then need data that defines what the differences are. Another situation that requires statistics is where you want to state how accurate your your estimated figures are but this requires understanding on the part of whoever you are presenting the figures to, as well. if all you need is an estimate of the averages then given a 30% sample you can use the information you do know to predict what you don't know and it will turn out reasonably close.

 

[ExcelHopeful]

Hey Ben, thank you for your input.

I am presenting this tool to some highly intelligent engineers/supply chain professionals. I'm assuming that statistics isn't going to be too difficult for them to comprehend. However, for myself it is...or at least starting out correctly. Therefore, if I was trying to state how accurate my figures are...do you know where I would begin? Thank you again!

 

[Ben Nevis]

Hi ExcelHopeful, I've constructed a confidence interval for Metal 1 which you can copy to Metals 2 to 5, there are more sophisticated ways of doing this but it would be much more complicated to explain if you've not had any previous exposure to the statistical concepts involved.
Point Estimate: P is the average of the metal makeup - make sure these sum to 100%
Z - Score is calculated from the Confidence level in cell D1 This can be looked up in a Normal Distribution Table but I've used the Excel function NORSMINV to do this for you
Confidence Level This is set at 0.95 (95%). It is normal practice to use either 0.95 or 0.99. A 95% Confidence Level means that there is a 5% (100% - 95% = 5%) chance that the true average of the metal makeup is outside the Confidence Interval. If you use 0.99 (99%) instead you will end up with a wider Confidence Interval but you can be more certain of the result
Sample Size I've set this at 178 based on 30% of your 593 suppliers
Confidence Interval There are two figures, an upper and a lower one.
The way to interpret the figures is to say that the 95% Confidence interval for the average percentage of Metal 1 is between 13% and 24%. If you want to display this graphically, graph the Point Estimate as a Bar or Column chart and add + - error bars to display the confidence interval.
Important (i) The Point Estimate is an estimate of the Average percentage for each metal. It isn't a prediction for a specific supplier (ii) All the percentage figures are based on the Excel cells being formatted as % so that 0.5 shows as 50%
You may want Google terms such as Confidence Level and Confidence Interval for a proportion to get more background understanding.

Excel 2007
	A	B	C	D	E	F	G	H
1	Supplier	Total Spend	Fixed Contract (Y/N)?	Metal 1	Metal 2	Metal 3	Metal 4	Metal 5
2	Supplier 2	$122.67	Yes	20%	20%	20%	20%	20%
3	Supplier 5	$108.55	Yes	5%	75%	10%	5%	5%
4	Supplier 6	$101.82	No	30%	30%	5%	5%	30%
5
6			Point estimate: P	18%	42%	12%	10%	18%
7			Z - score	1.96
8			Confidence level	0.95
9			Sample size	178
10			Confidence interval	13%
11				24%
Sheet1

Cell Formulas
Range	Formula
D6	=AVERAGE(D2:D4)
D7	=-NORMSINV((1-D8)/2)
D10	=D6-D7*SQRT((D6*(1-D6))/D9)
D11	=D6+D7*SQRT((D6*(1-D6))/D9)
E6	=AVERAGE(E2:E4)
F6	=AVERAGE(F2:F4)
G6	=AVERAGE(G2:G4)
H6	=AVERAGE(H2:H4)