Excel 2016 Skills Approach - Ch 8 Fix It 8.6

Financial Data

Grill on the Green
Financial Information
As of December 31, 2014	Underforming Quarters
Store ID	Location	Performance Status	Quarter 1	Quarter 2	Quarter 3	Quarter 4
GG.AV1	Austinville	Above Average	95,272	125,022	76,201	55,798
GG.BB1	Brownsburg	Above Average	52,109	97,544	67,972	53,881
GG.CV1	Chesterville	Below Average	(15,201)	27,908	22,003	(9,096)
GG.GR1	Grover	Below Average	22,109	37,449	32,633	28,704
GG.HD1	Hammond	Above Average	47,209	68,946	54,279	43,119
GG.HV2	Hawkinsville	Above Average	62,889	93,605	82,409	70,073
GG.GR3	Grover	Below Average	22,109	37,449	32,633	28,704
GG.HD2	Hammond	Below Average	(23,205)	(899)	12,467	28,795
GG.HV2	Hawkinsville	Below Average	45,329	44,576	46,921	48,234
GG.BC1	Bay City	Above Average	83,444	98,344	68,872	56,511
GG.WC1	Walton City	Above Average	71,901	105,749	66,784	54,444
GG.EV1	Esterville	Below Average	(10,242)	18,668	19,209	(5,720)
GG.BC2	Bay City	Above Average	57,109	98,344	68,872	56,511
GG.FA1	Four Acres	Below Average	29,013	47,523	37,978	33,376
GG.GR2	Grover	Below Average	22,109	37,449	32,633	28,704
GG.BB2	Brownsburg	Above Average	52,109	97,544	67,972	53,881
Criteria:
Store ID	Location	Performance Status	Quarter 1	Quarter 2	Quarter 3	Quarter 4
<10000	<10000	<10000	<10000

PivotTable

Row Labels	Sum of Quarter 1	Sum of Quarter 2	Sum of Quarter 3	Sum of Quarter 4
Austinville	95272	125022	76201	55798
Bay City	114218	196688	137744	113022
Brownsburg	104218	195088	135944	107762
Chesterville	-15201	27908	22003	-9096
Esterville	-10242	18668	19209	-5720
Four Acres	29013	47523	37978	33376
Grover	44218	74898	65266	57408
Hammond	47209	68946	54279	43119
Hawkinsville	62889	93605	82409	70073
Walton City	71901	105749	66784	54444
Grand Total	543495	954095	697817	520186

Sum of Quarter 1 Austinville Bay City Brownsburg Chesterville Esterville Four Acres Grover Hammond Hawkinsville Walton City 95272 114218 104218 -15201 -10242 29013 44218 47209 62889 71901 Sum of Quarter 2 Austinville Bay City Brownsburg Chesterville Esterville Four Acres Grover Hammond Hawkinsville Walton City 125022 196688 195088 27908 18668 47523 74898 68946 93605 105749 Sum of Quarter 3 Austinville Bay City Brownsburg Chesterville Esterville Four Acres Grover Hammond Hawkinsville Walton City 76201 137744 135944 22003 19209 37978 65266 54279 82409 66784 Sum of Quarter 4 Austinville Bay City Brownsburg Chesterville Esterville Four Acres Grover Hammond Hawkinsville Walton City 55798 113022 107762 -9096 -5720 33376 57408 43119 70073 54444

Financial Targets

Grill on the Green
What-If Analysis
Goal	Set targets for underperforming quarters to increase overall average quarter income to the maximum possible within the constraints
Constraints	Targets cannot be greater than half the average of all stores for that quarter
Target sales for the two lowest performers per quarter must be equal
Store ID	Location	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Average Quarter Target
GG.AV1	Austinville	109,563	143,775	87,631	64,168	101,284
GG.BB1	Brownsburg	59,925	112,176	78,168	61,963	78,058
GG.CV1	Chesterville	- 0	32,094	25,303	- 0	14,349
GG.GR1	Grover	25,425	43,066	37,528	33,010	34,757
GG.HD1	Hammond	54,290	79,288	62,421	49,587	61,396
GG.HV2	Hawkinsville	72,322	107,646	94,770	80,584	88,831
GG.GR3	Grover	25,425	43,066	37,528	33,010	34,757
GG.HD2	Hammond	- 0	- 0	14,337	33,114	11,863
GG.HV2	Hawkinsville	52,128	51,262	53,959	55,469	53,205
GG.BC1	Bay City	65,675	113,096	79,203	64,988	80,740
GG.WC1	Walton City	82,686	121,611	76,802	62,611	85,927
GG.EV1	Esterville	- 0	21,468	22,090	- 0	10,890
GG.BC2	Bay City	65,675	113,096	79,203	64,988	80,740
GG.FA1	Four Acres	33,365	54,651	43,675	38,382	42,518
GG.GR2	Grover	25,425	43,066	37,528	33,010	34,757
GG.BB2	Brownsburg	59,925	112,176	78,168	61,963	78,058
45,740	74,471	56,770	46,053	55,758