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 2’s 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.

Required: 

1. Prepare a cost report for the current year and a budgeted report for next year using a spreadsheet. Assume the firm uses weighted-average process costing. Your spreadsheet should contain the following: 

(i) A basic input data area – containing given data that may be referenced by other parts of the spreadsheet. 

(ii) A cost report area – containing the information that would be printed out and presented to management.