Ace Appliance: Productivity Analysis by Plant Location Mini-Case (This is all the information I have)

The Ace Appliance Co. currently assembles one of its major products, Elite Dishwashers, at five different manufacturing locations (Seattle WA, Los Angeles CA, Phoenix AZ, Detroit MI, and Pittsburg PA). Most (90 %) of the companys customers are in the US. Most of the suppliers for this product are in California.

To increase efficiency, company executives are considering consolidating production from five to two locations. Your job is to help them make the location decision. Productivity and gross revenue are two factors among many factors that should be considered.

1.The average weekly sales and costs for the last year are shown in Table 1. The average sales price to its customers is $1,000 unit and does not vary by region. Apply the concepts that you learned to create a spreadsheet to analyze the data provided in Table 1. Save the spreadsheet using the name Last Name_Ace Review.

2.Enter the data shown in Table 1 using the most efficient method. Hint: use a range as discussed in Chapter 1.

Table 1: Average Weekly Sales and Costs by Plant Location

Seattle

Los Angeles

Phoenix

Detroit

Pittsburg

Units Produced

510

740

360

650

485

Sales Revenue ($/week)

Direct Labor ($/week)

$117,800

$203,030

$80,800

$177,400

$130,060

Direct Materials ($/week)

$98,030

$102,146

$72,760

$93,174

$81,080

Operations Overhead ($/week)

$200,110

$169,871

$140,891

$158,820

$146,100

3.Use a formula and an absolute cell reference to selling price to calculate the average weekly sales revenue. Format the data appropriately. For example, consider how many decimal places to show.

4.Make a copy of the sheet and rename the sheet gross profit margin.

5.Return to the first sheet and rename the sheet productivity ratios. Add titles, headings, and format appropriately.

6.Still on the first sheet, calculate labor productivity ratio, materials productivity ratio, and multifactor productivity ratio for each location using sales revenue as the output and the input values in costs.

7.Create and format a column chart that shows all three types of productivity measures for all the locations on the same chart. Be sure to add titles to each axis.

8.On the second sheet, calculate the total operating cost using the SUM function and then calculate gross profit. Finally, determine the gross profit margin by location (=gross profit/sales revenue).

9.Add titles, headings, and format appropriately.

10.Create and format a 3D pie chart that shows the sales revenue by location. Move the pie chart to its own sheet labeled revenue pie chart

a.Add data labels to indicate the sales revenue $ on each slice of the pie chart

b.Bevel the top and bottom, setting the width and heights at 500 pt

c.Angle the first slice 90 degrees so the largest slice (Los Angeles) is in the lower left hand corner

d.Explode the Los Angeles slice 20 degrees

e.To ensure legibility of the title, legend, and Sales $, increase their font sizes to 24 pt

11.Add a sheet and rename questions. In a text box answer the following questions:

a.Based on the current productivity ratios and gross profit margin at each location, which two locations do you recommend continuing to use? Why?

b.What could be one concern with moving production to these locations? Why?