Year Over Year Contribution to Wtd Avg Change

[srund109]

I am trying to determine the impact that each state had on an overall US average increase. See sample below.

It begins with this formula for each state and the US as a whole.

Total Spend/Total Claims
$100,000/2,500
Jan 2012=$40 per

$200,000/6,000
Jan 2013 = $33.33 per

I then have year over year each state.
--------Jan-12 Jan-13
Alaska $40 $33.33
Alabama $35 $42
....
Total US $38 $40

What I am trying to determine is what impact each state had towards that total US change of $2.

Thank you

 

[srund109]

Thanks for your reply Denis, but it is a state by state change in average (determined by spend divided by claims) and then a total US average. The US average ends up being a weighted average of all the states and I want to get each state's year over year impact. I have added a more detailed chart below.

I want to know how each state impacted the $0.84.

	Total Spend	Total Claims	Spend / Claims
	2012	2013	2012	2013	2012	2013
Alabama	$ 100,000	$ 110,000	1,500	1,600	66.67	68.75
Alaska	$ 80,000	$ 85,000	1,200	1,250	66.67	68.00
Arizona	$ 75,000	$ 70,000	1,100	1,050	68.18	66.67
Total US	$ 255,000	$ 265,000	3,800	3,900	67.11	67.95
					US Chg	0.84