Franklin Canoe Company has several plants located around the United States. The company’s controller has imported its job cost and sales data from the past three years into Excel. The following is a sample of the table of the records in that Excel file.

For each of the situations listed below, assume that you would create a pivot table to answer the question. Indicate which field you would use for the Rows box, which field (if any) you would use for the Columns box, and which field you would use for the Values box. Also indicate what type of calculation you would use to summarize the value field.

1. What was the total sales revenue for each product group?
2. What was the total sales revenue for each product group by plant?
3. What was the average cost per unit for each canoe model?
4. What was the average cost for each canoe model in each plant?
5. How many units were produced in each plant?

Transcribed Image Text:

A 1 Date 2 1/12/2019 Kent 3 1/14/2019 Cordele 4 1/17/2019 Lincoln B Plant 5 1/20/2019 Eudora 6 1/20/2019 Lincoln 7 1/21/2019 Eudora 8 1/23/2019 Cordele 9 1/25/2019 Kent 10 1/26/2019 Kent C Job # Product group Recreational 1583 1717 Multi 1813 River 1970 Multi 1975 Recreational 1980 Recreational 2013 River 2035 Multi 2040 Recreational E Model S101 X220 F340 X220 S101 T201 F340 X230 S101 Units 10 15 25 30 10 15 30 20 50 G DM 400 750 H DL MOH 160 320 195 390 2,375 550 1,100 1,590 510 1,020 400 180 360 735 300 600 2,700 630 1,260 300 600 1,240 2,100 650 1,300 K Job cost Cost per unit 88 880 1,335 89 4,025 161 3,120 104 940 1,635 4,590 2,140 4,050 94 109 153 107 81 M Sales rev GP per unit 1,650 77 2,250 61 5,625 64 4,500 46 1,650 71 3,000 91 6,750 72 3,500 68 8,250 84