Trying to work out a relationship brtween variables

Posted by Damon on July 29, 2001 1:00 PM

I'm not sure if trend is the right word but here goes

I have a bunch of data similar to what follows

sales..3175....sales 57176.....sales 59111
cost...75403...cost 40425......cost 44222
margin.9.34%...margin 29.29%...margin 25.18%

There is a relationship between margin and sales. Generally lower margin reflects markdowns which in turn push higher retail sales.

Given similar sets of data to those above I am trying to find a way to get a figure based on margin. Ie given these variables what would sales be at a 10% margin or a 20% etc

Any help would be awesome

Posted by Aladin Akyurek on July 29, 2001 2:21 PM

What about

=FORECAST(10,{3175,57176,59111},{9.34,29.29,25.18})?

Note that this function predicts the value of interest using linear regression. Other two possibilities are Goal Seek & Solver.

Hope this helps.

Aladin

Posted by Damon on July 29, 2001 2:53 PM

Thanks
I think forecast may be a little too linear.

I am a little unsure how goal seeker works- although i see it on my toolbar. I dont have access to solver :(

cheers

Posted by Aladin Akyurek on July 30, 2001 12:54 AM

Yep.

Goal Seek requires a formula that states the relationship between the variables of interest.

Your problem boils down to having an equation that states the relationship you're interested in. IMHO, this is not an Excel problem/question, rather a "domain" question. I think you should come yourself with an equation or consult domain experts (e.g., economists) to get one. Or, all else failing, and if you have historical data, you could establish one or more empirical relationships. After that, you need to map it to Excel. Otherwise, you're stuck with a linear approximation (as done by FORECAST).

Aladin