Using Excel for variable costing

Download an Excel template for this problem online in MyAccountingLab or athttp://www.pearsonhighered.com/Horngren. Tiger Mountain Gelato incurs thefollowing costs for its premium ice cream in May 2018:

Direct materials cost per pint $ 2.50 perpint

Direct labor cost per pint 0.75 per pint

Variable manufacturing overhead cost per pint 0.25 per pint

Fixed manufacturing overhead costs 6,000 per month

Total fixed selling and administrative costs 5,000 per month

Sales price per pint 8.00 per pint

Pints of gelato produced 12,000 pints

Pints of gelato sold 11,500 pints

There were no beginning inventories, so Tiger Mountain Gelato has 500 pintsin ending Finished Goods Inventory (12,000 pints produced less 11,500 pintssold).

Requirements

1. Calculate Tiger Mountain Gelato’s product cost per pint under absorptioncosting and variable costing.

2. Calculate the balance in Finished Goods Inventory on May 31, 2018, usingabsorption costing and variable costing.

3. Prepare income statements in good form for Tiger Mountain Gelato for May2018 using absorption costing and variable costing.

4. Reconcile the differences between operating incomes and Finished GoodsInventory balances between the two-costing method

Step by step solution

01

Calculation of product cost per unit

• Use the selections in the drop-down menu to select the correct description.
• Use cell references to point to the correct unit or dollar amount from the data table

	Absorption costing	Variable costing
Direct materials cost per pint	$ 2.50	$2.50
Direct labor cost per pint	$0.75	$0.75
Variable manufacturing overhead cost per pint	$0.25	$0.25
Fixed manufacturing overhead costs	$0.50
Product cost per unit	$4.00	$3.50

02

Calculation of finished goods inventory on May 31, 2018

• Use cell references to point to the units produced and the units sold.

Beginning Inventory in units	0.00
Units Produced for sale	12,000.00
Units available for sale	12,000
Units sold	11,500
Ending Inventory in units	500

• Use cell references to point to the correct product cost.

	Absorption costing	Variable costing
Ending Inventory in units	500	500
Product cost per unit	4	4
Balance in finished goods Inventory	2,000	1,750

03

Income statement as per variable costing and absorption costing

• Use the selections in the drop-down menu to select the correct description.
• Use cell references in the formula to point to the selling price per unit, product cost per unit and units sold from the data table.
• Indent the descriptions for any subtotals using the increase indent button, Double underline operating income.

Absorption costing
Net sales Revenue$\left(\$8\times 11,500\right)$	$92,000
Cost of goods sold$\left(\$4\times 11,500\right)$	$46,000
Gross profit	$46,000
Selling & Administrative costs (fixed)	$5,000.00
Operating Income	$41,000.00
Finished goods inventory, ending balance	$2,000.00

Variable costing
Net sales Revenue$\left(\$8\times 11,500\right)$	$92,000
Variable cost of goods sold $\left(\$3.50\times 11,500\right)$	$40,250
Contributing margin	$51,750
Fixed costs:
Fixed manufacturing overhead	6,000
Fixed selling & administrative overhead	5,000	$11,000.00
Operating Income		$41,750
Finished goods inventory, ending balance		$1,750.00

04

Reconciliation statement

• Use cell references to point to the correct amounts.
• Use the ABS function (absolute value) to calculate the difference between operating and finished goods Inventory.

	Absorption costing	Variable costing	Difference
Operating Income	41,000	41,750	750
Finished goods Inventory, ending balance	2,000	1,750	250

Unlock Step-by-Step Solutions & Ace Your Exams!

• Full Textbook Solutions

  Get detailed explanations and key concepts

• Unlimited Al creation

  Al flashcards, explanations, exams and more...

• Ads-free access

  To over 500 millions flashcards

• Money-back guarantee

  We refund you if you fail your exam.

Start your free trial

Over 30 million students worldwide already upgrade their learning with Vaia!