Williamson Incorporated began a new sports aromatic body product line this year. A cost report for the current year and a budget for next year is required by management.

The product requires processing in two departments. Materials are added at the beginning of the process in department 1. Department 2 finishes the product but adds no direct materials. During the year work was begun on 240,000 units in department 1, and 180,000 of these units were transferred to department 2. The remaining 60,000 units were 60% complete with regard to conversion costs in department 1, which incurred $486,000 of materials costs and $252,720 of conversion costs.

Department 2 completed and sent 140,000 units to the finished goods warehouse. It ended the period with 40,000 units 40% complete with regard to department 2s conversion costs, which were $589,680 for the period.

The plan for next year is to begin an additional 300,000 units in department 1. Management expects to finish the year with 100,000 units one-half converted in department 1. Department 2 is expected to complete 280,000 units, and its ending inventory is expected to be 70% complete. Materials are expected to be $656,100 and conversion costs for departments 1 and 2 are expected to be $261,810 and $1,063,350, respectively.

QUESTION

Note: Part (ii) above should be based on formulae and cell referencing. Recalculated figures simply keyed into the report area defeat the purpose of utilizing spreadsheets, and marks will be deducted if this is done.

1. Using the spreadsheet developed in requirement 1, answer the following what if questions. (Note: Treat them as separate questions.)

1. There were some problems on the product line in both departments of the current year and only 50% of the units were completed then what was expected, however, the material costs reduced by 40% and the conversion costs reduced by 10% in both departments.

It was suggested that only 80,000 units be started in department 1 in the following year, resulting in 130,000 units being completed in period 2. There was also a 50% reduction in output in department 2.

2. The production of the new product went better than anticipated in the current year. Both department 1 and 2 completed 20% more units than expected in the current year. The material costs in period one increased by 15% while the conversion costs remained the same. There seems to be consensus among the production workers that the projections for next year should be revised but this has not been done yet. Therefore projections for costs and numbers of units completed remain the same for the second year.

2. Include in your report to management some comments regarding your cost reports.

1. Current Year Statement of Equivalent Units - Department 1 1 Equivalent Units Particulars Input Units Particulars Output Units Material Conversion Work-in-progress Units started Transferred to Department 2 2,40,000 Work-in-progress 1,80,000 60,000 % Units 100% 1,80,000 100% 60,000 % Units 100% 1,80,000 60% 36,000 2,40,000 Equivalent units of production 2,40,000 2,40,000 2,16,000 Particulars WIP Incurred Total Eq. Units Material Conversion Cost 4,86,000 2,52,720 7,38,720 4,86,000 2,40,000 2,52,720 2,16,000 7,38,720 Cost per Eq. units 2.025 1.170 3.195 Process A/c - Department 1 Equivalent Units Particulars Input Units Amount ($) Particulars Output Units Total Cost Material Conversion Eq. Units Amount Eq. Units Amount 1,80,000 3,64,500 1,80,000 2,10,600 60,000 1,21,500 36,000 42,120 Work-in-progress Material Conversion Costs Transferred to Dep-2 4,86,000 Work-in-progress 2,52,720 1,80,000 60,000 5,75,100 1,63,620 2,40,000 2,40,000 7,38,720 2,40,000 4,86,000 2,52,720 7,38,720 Statement of Equivalent Units - Department 2 Particulars Input Units Particulars Output Units Equivalent Units Transferred from Conversion Department 1 % Units Units 100% 1,40,000 100% 1,40,000 100% 40,000 40% 16,000 Work-in-progress Transferred from Dept 1 Transferred to FG 1,80,000 Work-in-progress 1,40,000 40,000 1,80,000 Equivalent units of production 1,80,000 1,80,000 1,56,000 Particulars WIP Incurred Total Eq. Units Transferred from Department 1 Conversion Cost 5,75,100 5,75, 100 5,89,680 5,89,680 11,64,780 11,64,780 1,80,000 1,56,000 Cost per Eq. units 3.195 3.780 6.975 Process A/c - Department 2 Particulars Input Units Amount ($) Particulars Output Units Total Cost Equivalent Units Transferred from Conversion Department 1 Eq. Units Amount Eq. Units Amount 1,40,000 4,47,300 1,40,000 5,29,200 40,000 1,27,800 16,000 60,480 9,76,500 Work-in-progress - Transferred from Dept 1 1,80,000 Conversion Costs Transferred to FC 5,75,100 Work-in-progress 5,89,680 1,40,000 40,000 1,88,280 1,80,000 11,64,780 1,80,000 5,75,100 5,89,680 11,64,780 2. Budgeted Next Year Statement of Equivalent Units - Department 1 Equivalent Units Particulars Input Units Particulars Output Units Material Conversion % Work-in-progress Units started 60,000 Transferred to Department 2 3,00,000 Work-in-progress 2,60,000 1,00,000 Units 100% 2,60,000 100% 1,00,000 Units 100% 2,60,000 50% 50,000 3,60,000 Equivalent units of production 3,60,000 3,60,000 3,10,000 Particulars WIP Incurred Total Eq. Units Material Conversion Cost 1,21,500 6,56,100 7,77,600 42,120 2,61,810 3,03,930 1,63,620 9,17,910 10,81,530 3,60,000 3,10,000 Cost per Eq. units . 2.160 0.980 3.140 Particulars Input Units Amount ($) Total Cost Process A/c - Department 1 Equivalent Units Particulars Output Material Conversion Units Eq. Units Amount Eq. Units Amount 1,63,620 Transferred to Dept 2 2,60,000 2,60,000 5,61,600 2,60,000 2,54,909 6,56,100 Work-in-progress 1,00,000 1,00,000 2,16,000 50,000 49,021 2,61,810 Work-in-progress Material Conversion Costs 60,000 3,00,000 8,16,509 2,65,021 3,60,000 10,81,530 3,60,000 7,77,600 3,03,930 10,81,530 Statement of Equivalent Units - Department 2 Particulars Input Units Particulars Output Units Equivalent Units Transferred from Conversion Department 1 Units % Units 100% 2,80,000 100% 2,80,000 100% 20,000 70% 14,000 Work-in-progress Transferred from Dept 1 40,000 Transferred to FG 2,60,000 Work-in-progress 2.80,000 20,000 3,00,000 Equivalent units of production 3,00,000 3,00,000 2,94,000 Particulars WIP Incurred Total Eq. Units Transferred from Department 1 Conversion Cost 1,27,800 8,16,5099,44,309 60,480 10,63,350 11,23,830 1,88,280 18,79,859 20,68,139 3,00,000 2,94,000 Cost per Eq. units 3.148 3.823 6.970 Particulars Input Units Amount ($) Process A/c - Department 2 Equivalent Units Particulars Output Transferred from Conversion Total Cost Units Department 1 Eq. Units Amount Eq. Units Amount 1,88,280 Transferred to FG 2,80,000 2,80,000 8,81,355 2,80,000 10,70,314 19,51,669 8,16,509 Work-in-progress 20,000 20,000 62.954 14,000 53,516 1,16,470 10,63,350 Work-in-progress 40,000 Transferred from Dept 1 2,60,000 Conversion Costs 3,00,000 20,68,139 3,00,000 9,44,309 11,23,830 20,68,139 1. Current Year Statement of Equivalent Units - Department 1 1 Equivalent Units Particulars Input Units Particulars Output Units Material Conversion Work-in-progress Units started Transferred to Department 2 2,40,000 Work-in-progress 1,80,000 60,000 % Units 100% 1,80,000 100% 60,000 % Units 100% 1,80,000 60% 36,000 2,40,000 Equivalent units of production 2,40,000 2,40,000 2,16,000 Particulars WIP Incurred Total Eq. Units Material Conversion Cost 4,86,000 2,52,720 7,38,720 4,86,000 2,40,000 2,52,720 2,16,000 7,38,720 Cost per Eq. units 2.025 1.170 3.195 Process A/c - Department 1 Equivalent Units Particulars Input Units Amount ($) Particulars Output Units Total Cost Material Conversion Eq. Units Amount Eq. Units Amount 1,80,000 3,64,500 1,80,000 2,10,600 60,000 1,21,500 36,000 42,120 Work-in-progress Material Conversion Costs Transferred to Dep-2 4,86,000 Work-in-progress 2,52,720 1,80,000 60,000 5,75,100 1,63,620 2,40,000 2,40,000 7,38,720 2,40,000 4,86,000 2,52,720 7,38,720 Statement of Equivalent Units - Department 2 Particulars Input Units Particulars Output Units Equivalent Units Transferred from Conversion Department 1 % Units Units 100% 1,40,000 100% 1,40,000 100% 40,000 40% 16,000 Work-in-progress Transferred from Dept 1 Transferred to FG 1,80,000 Work-in-progress 1,40,000 40,000 1,80,000 Equivalent units of production 1,80,000 1,80,000 1,56,000 Particulars WIP Incurred Total Eq. Units Transferred from Department 1 Conversion Cost 5,75,100 5,75, 100 5,89,680 5,89,680 11,64,780 11,64,780 1,80,000 1,56,000 Cost per Eq. units 3.195 3.780 6.975 Process A/c - Department 2 Particulars Input Units Amount ($) Particulars Output Units Total Cost Equivalent Units Transferred from Conversion Department 1 Eq. Units Amount Eq. Units Amount 1,40,000 4,47,300 1,40,000 5,29,200 40,000 1,27,800 16,000 60,480 9,76,500 Work-in-progress - Transferred from Dept 1 1,80,000 Conversion Costs Transferred to FC 5,75,100 Work-in-progress 5,89,680 1,40,000 40,000 1,88,280 1,80,000 11,64,780 1,80,000 5,75,100 5,89,680 11,64,780 2. Budgeted Next Year Statement of Equivalent Units - Department 1 Equivalent Units Particulars Input Units Particulars Output Units Material Conversion % Work-in-progress Units started 60,000 Transferred to Department 2 3,00,000 Work-in-progress 2,60,000 1,00,000 Units 100% 2,60,000 100% 1,00,000 Units 100% 2,60,000 50% 50,000 3,60,000 Equivalent units of production 3,60,000 3,60,000 3,10,000 Particulars WIP Incurred Total Eq. Units Material Conversion Cost 1,21,500 6,56,100 7,77,600 42,120 2,61,810 3,03,930 1,63,620 9,17,910 10,81,530 3,60,000 3,10,000 Cost per Eq. units . 2.160 0.980 3.140 Particulars Input Units Amount ($) Total Cost Process A/c - Department 1 Equivalent Units Particulars Output Material Conversion Units Eq. Units Amount Eq. Units Amount 1,63,620 Transferred to Dept 2 2,60,000 2,60,000 5,61,600 2,60,000 2,54,909 6,56,100 Work-in-progress 1,00,000 1,00,000 2,16,000 50,000 49,021 2,61,810 Work-in-progress Material Conversion Costs 60,000 3,00,000 8,16,509 2,65,021 3,60,000 10,81,530 3,60,000 7,77,600 3,03,930 10,81,530 Statement of Equivalent Units - Department 2 Particulars Input Units Particulars Output Units Equivalent Units Transferred from Conversion Department 1 Units % Units 100% 2,80,000 100% 2,80,000 100% 20,000 70% 14,000 Work-in-progress Transferred from Dept 1 40,000 Transferred to FG 2,60,000 Work-in-progress 2.80,000 20,000 3,00,000 Equivalent units of production 3,00,000 3,00,000 2,94,000 Particulars WIP Incurred Total Eq. Units Transferred from Department 1 Conversion Cost 1,27,800 8,16,5099,44,309 60,480 10,63,350 11,23,830 1,88,280 18,79,859 20,68,139 3,00,000 2,94,000 Cost per Eq. units 3.148 3.823 6.970 Particulars Input Units Amount ($) Process A/c - Department 2 Equivalent Units Particulars Output Transferred from Conversion Total Cost Units Department 1 Eq. Units Amount Eq. Units Amount 1,88,280 Transferred to FG 2,80,000 2,80,000 8,81,355 2,80,000 10,70,314 19,51,669 8,16,509 Work-in-progress 20,000 20,000 62.954 14,000 53,516 1,16,470 10,63,350 Work-in-progress 40,000 Transferred from Dept 1 2,60,000 Conversion Costs 3,00,000 20,68,139 3,00,000 9,44,309 11,23,830 20,68,139