0
$\begingroup$

My question is not typical of what I see being asked in this forum but it is math related so I am hoping I can get some suggestions -

I have a dataset as shown in image 1. I have data for last 20 years with some missing years. sample dataset

Now, I am trying to create a measure 'V' using these that capture the following: From one year to next, 1) An increase in sales with a decrease in cost is good-implied by a higher value of V. 2) An increase in sales and a relatively lower increase in costs is good-implied by a higher value of V. 3) A decrease in sales and a decrease in costs is good-implied by a higher value of V. 4) A decrease in sales and a relatively higher decrease in costs is also good in fact better than point 3 above - -implied by a higher value of V than what is obtained in point 3. 5) A decrease in sales but an increase in costs is NOT good-implied by a higher value of V.

Given below is how I created the measure V -

V = [Sales(current year)-Sales(previous year)]/[Costs(current year)-Costs(previous year)]

Calculating V as above sort of captures what I am trying to do but is not completely correct, as I demonstrate in the data example in image 2- example of calculation of V

Column 1 gives the numerator in the formula for V. Column 1 gives the denominator in the formula for V. As you will notice, row 4 gives an incorrect result. sales went up, costs went down but the resulting value of V is negative and lowest among all four. Also, row 2 should have the value of V higher than that of row 1.

So this is where I need help, how to improvise formula of V so it captures both the change in sales and change in costs - the direction of change as well as the magnitude.

Thank you!

$\endgroup$
2
  • $\begingroup$ If you had data from more than three years, you might use correlation of sales and costs as a guide. If there are many anomalies like your second year, I'd expect to see a correlation not significantly different from 0. $\endgroup$
    – BruceET
    Feb 9, 2017 at 7:17
  • $\begingroup$ BruceET : I have data for more than 20 years. The correlation in fact is very strong between the two variables - 0.81 $\endgroup$
    – singhalc
    Feb 9, 2017 at 14:17

0

You must log in to answer this question.

Browse other questions tagged .