The case (Zauner Ornaments) presents five potential allocation bases for assigning the company's $300,000 in plant-administration costs. Using the five years of data presented in case Exhibit 4, you should hypothesize which potential base would be the best choice. To make this decision, you need to look for the best correlation coefficient or regression fit. You need to show your support below, using Excel (insert --> charts--> scatter)

1. Determine the best allocation base for allocating plant-administration costs.

Exhibit 4: Historical Five-Year Plant Administration Costs and Other Data Information Shown Below was in an Excel Spreadsheet that I Could Not Input, so I had to type it out. I hope this helps:

Total Plant Administration Costs

2003 - $300,000

2002 - $295,000

2001 - $285,000

2000 - $280,00

1999 - $290,000

Ending Inventory Cost

2003 - $90,000

2002 - $83,000

2001 - $58,000

2000 - $50,000

1999 0 $76,000

Plant Personnel

2003 - 122

2002 - 122

2001 - 119

2000 - 112

1999 - 116

Number of Customers

2003 - 1200

2002 - 1250

2001 - 1100

2000 - 800

1999 - 600

Total Direct Labor Costs

2003 - $520,000

2002 - $515,000

2001 - $555,000

2000 - $540,000

1999 - $500,000

% of Plant Capacity Used

2003 - 70%

2002 - 75%

2001 - 85%

2000 - 90%

1999 - 95%