Results 1 to 10 of 10
Thread: Statistical Question (2000)

20020120, 01:34 #1
 Join Date
 Feb 2001
 Location
 Dallas plus 20 miles or so, Texas, USA
 Posts
 876
 Thanks
 0
 Thanked 0 Times in 0 Posts
Statistical Question (2000)
In the attached workbook, I have in Column B an "ideal" mix which conceivably generates an "ideal" food cost. In Column C, I have another mix of numbers. Is there any way to generate or otherwise project a food cost from the numbers in Column C, based upon the correlation between the numbers in Column B?
Thanks,

20020120, 07:23 #2
 Join Date
 Dec 2000
 Location
 New Hampshire, USA
 Posts
 3,386
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Statistical Question (2000)
What about the LINEST function?

20020120, 08:34 #3
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Statistical Question (2000)
Michael,
There must be something missing here, to solve your problem. Please tell me how you calculated the ideal food cost of 26.5%. Or is this what you actually want to do: make a relation between the figures in column B and this forecasted ideal food cost of 26.5% and then use the same model to forecast the food cost based on the figures in column C?
If all food costs are independent figures then the normal way to approach these kind of problems is to make a model (that can be anything, linear, polynomial, exponential, multilinear, ...) that fixes the relation between your Xvalues and your Yvalues. What can be done is depending on the availability of data. In your case, I don't see that many data to build the model. e.g. should I consider 'Chicken' and 'Meat' both as variables or should I just consider it as 'food'? That makes a difference.
The previous post suggested to build a model with LINEST, the builtin Excel function that allows you to make a linear model. The question here is, what is X and what is Y. I only see one column of data, containing all different % for goods, and one 'food cost'. Then there is no way you can use LINEST.
As far as I see, the only way is to make an average, or a weighed average. Or do I mis something here?

20020120, 08:45 #4
 Join Date
 Dec 2000
 Location
 New Hampshire, USA
 Posts
 3,386
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Statistical Question (2000)
It was stated that the poster does have two columns of numbers, so they can be used for X and Y to determine the relationship between he two columns.
However, I had not looked at the spreadsheet before I made my previous posting. A linear regression is not the right tool for this task.

20020120, 09:33 #5
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Statistical Question (2000)
You are right. The (Pearson or linear) correlation between the data in column B and C is statistically NOT significant and so a linear regression is not the right tool for this task. If you plot the data in columns B and C against each other in a XY scatter plot, then you'll see that it will be very difficult to find any relation at all. From visual inspection of the data I think it will be impossible to build a model that has predictive value.

20020120, 14:07 #6
 Join Date
 Feb 2001
 Location
 Dallas plus 20 miles or so, Texas, USA
 Posts
 876
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Statistical Question (2000)
Hans,
<<make a relation between the figures in column B and this forecasted ideal food cost of 26.5% and then use the same model to forecast the food cost based on the figures in column C?>>
That is exactly what I need to do. All items in the list should be considered food (please forgive me for my regress, it was late last night when I put the items in the list!)

20020120, 16:39 #7
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Statistical Question (2000)
If this is what you want to do, then there is no way to do that with the data of only column B. I assume here that you want to kind of weigh each individual food item giving an end result of 26.5%. With only the data from column B, there are many ways to do that. Not even speaking of the mathematical functions that you can use. The simples approach would be w1 x Steak + w2 x Chicken + w3 x Catfish + ... = 26.5%, where you try to estimate the wcoefficients. There is no way to do that in a statistical way with only the data available from column B. You need more columns than coefficients you want to estimate.
Trial and error is the only way and even then the model you obtain will have no predictive power.

20020120, 16:57 #8
 Join Date
 Feb 2001
 Location
 Dallas plus 20 miles or so, Texas, USA
 Posts
 876
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Statistical Question (2000)
Thanks Hans,
I am thinking that I might use the data which generates the sales mix, instead of the sales mix percentage itself, to try to estimate a food cost. The base data is more closely tied to the actual food cost (say in Column [img]/forums/images/smilies/cool.gif[/img]. There is so much of it though, I was hoping I could draw an inference from the resulting sales mix. Thanks for your thoughts.

20020121, 12:52 #9
 Join Date
 Dec 2000
 Location
 New Hampshire, USA
 Posts
 3,386
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Statistical Question (2000)
The model would have be based on the varoables used to determine the individual percentages.
For example, the steak % may be based on, say, 3 variables; the pantoes % is likely based on other variablesm etc.
This may be a case for generalized least squares, e.g., twostage least squares, to solve a system of multiple regression equations.
I do not know whether Excel provides such a function.
I wrote such software many years ago when I worked for the Center for Mathematical Studies in Busiiness and Economics of the Graduate School of Business of the University of Chicago.
Back then, the software was distributed as part of ESP (Econometric Software Package) and as a standalone program.
When I worked for Northwestern University, the software was converted to be part of the version of SPSS that ran on Control Data computers. It is no longer distributed as part of SPSS.
I'd suggest loking for ecomometric software.
Of course, such software is of no use unless one can come up with a model of the data.

20020121, 12:54 #10
 Join Date
 Dec 2000
 Location
 New Hampshire, USA
 Posts
 3,386
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Statistical Question (2000)
You should investigate using an econometric model.
I'm sure my software, or the functional equivalent, is available in some product.