In excel complete the following and be sure to include cell references
A E Joint Cost Allocation Alternative methods of joint-cost allocation, ending inventories Eree work cells Answer cells The Cook Company operates a simple chemical process to convert a single material into three separate items, referred to here as X, Y, and Z. All three end products are separated simultaneously at a single splitoff point. Products X and Y are ready for sale immediately upon splitoff without further processing or any other additional costs. Product Z, however, is processed further before being sold. There is no available market price for Z at the splitoff point. Use the information provided below to calculate the gross margin and ending inventory for the three products under various methods of joint-cost allocation. Answer cells must remain in the same location so do not insert/delete columns or rows in the file. Unless otherwise indicated, numeric answers must include a formula or X Y z Total PROVIDED INFORMATION Volume Sold (tons) Beginning Inventory (tons) Ending Inventory (tons) Volume Produced tons) 68 0 132 480 0 120 672 0 28 $1,200.00 $900.00 Sales Price per ton at Split-off Separable Processing Cost per ton produced Final Sales Price per ton Joint Cost $0.00 $286 $600.00 $1,200.00 $900.00 $580,000.00 X Y IN Total 1. Sales At Split-off Method Revenue at Split-off % of Revenue at Split-off Joint Cost Allocated to Production +Separable Costs Traced to Production Total Costs Assigned to Production Cost per unit Select Financial Figures Revenue -Cost of Good Sold Gross Margin GM% Ending Inventory Y IN Total 2. NRV Method Revenue Potential - Separable Costs Traced to Production NRV Potential % of NRV Joint Cost Allocated to Production +Separable Costs Traced to Production Total Costs Assigned to Production Cost per unit Select Financial Figures Revenue - Cost of Good Sold Gross Margin GM% Ending Inventory X Y IN Total 3. Constant GM% Method Revenue Potential -Total Joint Costs -Total Separable Costs Total Gross Margin Total GM % Gross Margin for Each Product Implied Total Cost for Each Product Joint Cost Allocated to Production +Separable Costs Traced to Production Total Costs Assigned to Production Cost per unit Select Financial Figures Revenue -Cost of Good Sold Gross Margin GM% Ending Inventory X Y IN Total 4. Physical Units Method Physical Units Produced % of Physical Units Joint Cost Allocated to Production +Separable Costs Traced to Production Total Costs Assigned to Production Cost per unit X Y IN Total 3. Constant GM% Method Revenue Potential -Total Joint Costs -Total Separable Costs Total Gross Margin Total GM % Gross Margin for Each Product Implied Total Cost for Each Product Joint Cost Allocated to Production +Separable Costs Traced to Production Total Costs Assigned to Production Cost per unit Select Financial Figures Revenue -Cost of Good Sold Gross Margin GM% Ending Inventory X Y Z Total 4. Physical Units Method Physical Units Produced % of Physical Units Joint Cost Allocated to Production +Separable Costs Traced to Production Total Costs Assigned to Production Cost per unit Select Financial Figures Revenue -Cost of Good Sold Gross Margin GM% Ending Inventory A E Joint Cost Allocation Alternative methods of joint-cost allocation, ending inventories Eree work cells Answer cells The Cook Company operates a simple chemical process to convert a single material into three separate items, referred to here as X, Y, and Z. All three end products are separated simultaneously at a single splitoff point. Products X and Y are ready for sale immediately upon splitoff without further processing or any other additional costs. Product Z, however, is processed further before being sold. There is no available market price for Z at the splitoff point. Use the information provided below to calculate the gross margin and ending inventory for the three products under various methods of joint-cost allocation. Answer cells must remain in the same location so do not insert/delete columns or rows in the file. Unless otherwise indicated, numeric answers must include a formula or X Y z Total PROVIDED INFORMATION Volume Sold (tons) Beginning Inventory (tons) Ending Inventory (tons) Volume Produced tons) 68 0 132 480 0 120 672 0 28 $1,200.00 $900.00 Sales Price per ton at Split-off Separable Processing Cost per ton produced Final Sales Price per ton Joint Cost $0.00 $286 $600.00 $1,200.00 $900.00 $580,000.00 X Y IN Total 1. Sales At Split-off Method Revenue at Split-off % of Revenue at Split-off Joint Cost Allocated to Production +Separable Costs Traced to Production Total Costs Assigned to Production Cost per unit Select Financial Figures Revenue -Cost of Good Sold Gross Margin GM% Ending Inventory Y IN Total 2. NRV Method Revenue Potential - Separable Costs Traced to Production NRV Potential % of NRV Joint Cost Allocated to Production +Separable Costs Traced to Production Total Costs Assigned to Production Cost per unit Select Financial Figures Revenue - Cost of Good Sold Gross Margin GM% Ending Inventory X Y IN Total 3. Constant GM% Method Revenue Potential -Total Joint Costs -Total Separable Costs Total Gross Margin Total GM % Gross Margin for Each Product Implied Total Cost for Each Product Joint Cost Allocated to Production +Separable Costs Traced to Production Total Costs Assigned to Production Cost per unit Select Financial Figures Revenue -Cost of Good Sold Gross Margin GM% Ending Inventory X Y IN Total 4. Physical Units Method Physical Units Produced % of Physical Units Joint Cost Allocated to Production +Separable Costs Traced to Production Total Costs Assigned to Production Cost per unit X Y IN Total 3. Constant GM% Method Revenue Potential -Total Joint Costs -Total Separable Costs Total Gross Margin Total GM % Gross Margin for Each Product Implied Total Cost for Each Product Joint Cost Allocated to Production +Separable Costs Traced to Production Total Costs Assigned to Production Cost per unit Select Financial Figures Revenue -Cost of Good Sold Gross Margin GM% Ending Inventory X Y Z Total 4. Physical Units Method Physical Units Produced % of Physical Units Joint Cost Allocated to Production +Separable Costs Traced to Production Total Costs Assigned to Production Cost per unit Select Financial Figures Revenue -Cost of Good Sold Gross Margin GM% Ending Inventory
