Variable and Absorption Costing Spreadsheet Instructions 1. Download and complete the spreadsheet found in the Variable and Absorption costing Spreadsheet Dropbox by inserting formulas or cell references in all cells highlighted yellow. Each cell that requires a formula or cell reference has a question mark in it. Originally, in Year 1, 20,000 units are produced, and 16,000 units are sold, and 28,000 units are produced in Year 2 and 32,000 units are sold. Since more units are produced in Year 2, the absorption unit product cost will decrease because you're spreading the fixed overhead over more units. So....when you calculate absorption COGS in Year 2, you will need to use the year 1 product cost for the older units you sold and the Year 2 product cost for the newer units you sold. The absorption costing net operating income should be $176,000 in Year 1 and $488,000 in Year 2 and the variable costing net operating income should be $148,000 in Year 1 and $516,000 in Year 2. 2. Check the accuracy of the formulas in your spreadsheet by changing the number of units produced in Year 2 to 17,500 (cell C17) and the number of units sold in Year 2 to 21,500 (cell C18). The absorption costing net operating income in Year 2 should now be $246,500 and the variable costing net operating income in Year 2 should be $274,500. Your Year 1 net operating incomes shouldn't change. If the new Year 2 net operating incomes don't calculate correctly, fix the errors in your spreadsheet before you submit it. 1 Miller Corporation produces and sells a single product, a wooden jewelry box. Cost and operating data are given below: 3 Selling price per unit 4 Manufacturing costs: 5 Variable (per unit): 6 DM 7 DL 8 Variable Manuf O/H 9 Fixed Manuf O/H per year 10 11 Selling and Admin costs: 12 Variable (per unit) 13 Fixed Selling and Admin expense per year $140,0 $80,0 Year 1 Year 2 16 Units in beginning inventory 17 Units produced during the year 18 Units sold during the year 20,000 16,000 4,000 28,000 32,000 20 Absorption Costing: 22 Calculate Unit Product Cost: 23 Year 1 Year 2 24 DM 25 DL 26 Variable Manuf O/H 27 Fixed Manuf O/H 28 Total Unit Product Cost 29 30 Absorption Costing Income Statements: Year 1 Year 2 32 Sales 33 Cost of Goods Sold 34 Gross Profit 35 Selling and Admin Expense: 36 Variable Selling and Admin expense 37 Fixed Selline and Admin expense Absorption Costing Income Statements: Year 1 Year 2 Sales Cost of Goods Sold Gross Profit Selling and Admin Expense: Variable Selling and Admin expense Fixed Selling and Admin expense Net Operating Income Variable Costing: Calculate Unit Product Cost: Year 1 Year 2 DM Variable Manuf O/H Total Unit Product Cost Variable Costing Income Statements: Year 1 Year 2 Sales Variable expenses: COGS Variable Selling and Admin expense Contribution Margin Fixed expenses: Fixed Manuf O/H Fixed Selling and Admin expense Net Operating Income 1 Miller Corporation produces and sells a single product, a wedan jaway box Cost and operating data are given below: 3 Selling price per unit 4 Manufacturing cost s Variable (per unit) 8 Variable Manuf/H 9 Fixed Manuf/ per year $140,000 11 Selling and Admin case 12 Variablager unit) 13 Fixed Sling and Admin pas per year 16 Units in banning in watery 17 Units produced during the year 18 Unisseld during the year 20 Absolon Casting 22 Calculane Unit Product Cost 24 OM 26 Variable Manuf/H 27 Fixed Manuf/ 28 Tool Unit Product Cost 30 Absorption Costing Income Statement 32 Sales 33 Cost of Goods Sold 34 Gross Profit 35 Selling and Admin Expanse 36 Variable Sling and Admin panse 37 Fixed Sling and Admin panse 38 Net Operating income 40 Valle Casting 42 Calculane Unit Product Cost 44 OM 45 DL 46 Variable Manuf/H 47 Toul Unit Product Cost 18 49 Variable Costing income Sutement S2 Variable S3 COGS 54 Variable Selling and Admin wense Ss Contibution Marin 56 Find S7 Fixed Manuf/H Sa Fixed Siling and Admin wense 59 No Owing income