The Excel worksheet form that follows is to be used to recreate portions of Review Problem 1. Download the workbook containing this form from Connect. On the website you will also receive instructions about how to use this worksheet form.

You should proceed to the requirements below only after completing your worksheet. The LIFO inventory flow assumption is used throughout this problem.
Required:
1. Check your worksheet by changing the units sold in the Data to 6,000 for Year 2. The cost of goods sold under absorption costing for Year 2 should now be $240,000. If it isn't, check cell C41. The formula in this cell should be = IF (C26 Why is the absorption costing net operating income now equal to the variable costing net operating income in Year 2?
2. Enter the following data from a different company into your worksheet:

Is the net operating income under variable costing different in Year 1 and Year 2? Why or why not? Explain the relation between the net operating income under absorption costing and variable costing in Year 1. Explain the relation between the net operating income under absorption costing and variable costing in Year 2.
3. At the end of Year 1, the company's board of directors set a target for Year 2 of net operating income of $500,000 under absorption costing. If this target is met, a hefty bonus would be paid to the CEO of the company. Keeping everything else the same from requirement 2, change the units produced in Year 2 to 50,000 units. Would this change result in a bonus being paid to the CEO? Do you think this change would be in the best interests of the company? What is likely to happen in Year 3 to the absorption costing net operating income if sales remain constant at 12,000 units per year?

Transcribed Image Text:

1 Chapter 5: Applying Excel 3 Data 4 Selling pice per unt 5 Manufacturing coste 6 Vanable par unit produced: 511 56 7 Direct matenals 8 Diract labor 9 Vaable manufacturing overhead 10Foxed manufacturing averhaad per yar $120,000 11 Selling and administrative expenses 12 Vaiable per unit sold 13 Fixed per year 14 15 o Units in beginning inventory 17 Units produced during the year 8Units sold during the year 19 20 Enter a formula into esch of the cels marked with a ? below 21 Review Problem 1: Contrasting Variable and Absorption Costing $4 $70,000 Year 1 Year2 10,000 6.000 23 Compute the Ending hventory 24 25 Units in beginning inventory 26 Units produced dunng the year 27 Units sold during the year 28 Units in ending inventory 29 30 Compute the Absorption Costing Unit Product Cost Yr 1 Year 2 Year 1 Year 2 32 Direct materials Direct labar 4 Variable manufacturing overhead 35 Fixed manufactuning overhead 36 Abeorption costing unit product coet 37 38 Construct the Absorpion Costing Income Statement Year 1 Year 2 40 Sales A1 Cost of goods sold 2 Gross margin 43 Salling and administrative expanses 44 Net operating income 46 Compurte the Variable Costing Unit Product Cost Year 1 Year 2 48 Direct materials 49 Direct labor 50 Variable manufacturing overhead 51 Vaniable costing unit product cost 53 Construct the Variable Costing Income Statement 54 55 Salas 56 Variable expenses 57 Vaiable cost of goods sold 58 Vaiable ealling and administrative expenses 59 Contnibution margin 60 Fixed expenses 61 Foxed manufactuning ovemead 62 Foxed selling and acministrative expenses 63 Nat oparating incoma 04 Year 1 Year 2 Data $75 Manufacturing costs: Varlable per unlt produced: $12 $5 %7 $150,000 Varlable manufacturing overhead.. Flxed manufacturing overhead per year.. Selling and administrative expenses: $1 $60,000 Year 1Year 2 0 Units produced during the year Units sold during the year. . .. . . 15,000 10,000 12,000 12,000