Excel Homework Problem #3
Navigation Inc. produces gyroscopes for electronic navigation systems used in aircraft. Company income statements for the last three years using absorption costing are:
| Year 1 | Year 2 | Year 3 | ||||||
| Sales | 2,250,000 | 2,025,000 | 3,375,000 | |||||
| COGS: | ||||||||
| Beginning inventory | 246,600 | 371,382 | 802,620 | |||||
| COGM | 1,459,000 | 1,592,500 | 1,370,000 | |||||
| Goods ava. For sale | 1,705,600 | 1,963,882 | 2,172,620 | |||||
| Ending Inventory | 371,382 | 802,620 | 178,100 | |||||
| COGS | 1,334,218 | 1,161,262 | 1,994,520 | |||||
| Gross Margin | 915,782 | 863,738 | 1,380,480 | |||||
| Selling and Admin Exp | 510,000 | 484,000 | 640,000 | |||||
| NOI | 405,782 | 379,738 | 740,480 | |||||
The beginning inventory in Year 1 was 1,800 units valued at $137 per unit, which included $48 per unit of allocated fixed manufacturing overhead. After selling 10,000 units in Year 1, the company anticipated increased sales of 13,000 units in Year 2 and scheduled production accordingly. Unfortunately, a slump in aircraft sales in Year 2 due to a nation-wide recession resulted in sales of only 9,000 units and a higher than desired ending inventory. By the end of Year 2, the economy was much improved and aircraft sales were rebounding sharply. Navigation planned for improved sales of at least 14,500 units in Year 3. Production was scheduled to meet the anticipated demand while lowering inventory levels. Actual production and sales figures for the three years were as shown:
| Year 1 | Year 2 | Year 3 | ||
| Production in units | 11,000 | 12,500 | 10,000 | |
| Sales in units | 10,000 | 9,000 | 15,000 |
Additional information:
a. Variable manufacturing costs per unit were $49.00 for direct materials, $25.00 for direct labor, and $15.00 for variable manufacturing overhead in each of the three years. Fixed manufacturing overhead was $480,000 in each year.
b. The company uses First in first out (FIFO) inventory flow assumption to value inventory.
c. The company applies fixed manufacturing overhead per unit to each year’s product based on each year’s production (i.e. the fixed manufacturing overhead per unit is different each year).
d. Variable selling and administrative costs each year were $26.00 per unit. Fixed selling and administrative costs were $250,000 in each of the three years.
Required:
1. Compute the absorption costing unit product cost for each year. (Show each variable cost per unit, fixed cost per unit, and total cost per unit.)
2. Compute the variable costing unit product cost for each year.
3. Prepare variable costing income statements in contribution format for each of the three years.
4. Show a reconciliation to explain the difference between variable costing net operating income and absorption costing net operating income each year.
Additional instructions:
1. Submit the problem on electronic media (CD, flash drive, etc.) in the manila envelope provided. The project will be graded on the accuracy of the answers, the appearance of the schedules and statements, and the use of the functionality of Excel.
2. Use good form for the income statement. Include the calculation of COGS using beginning and ending finished goods inventory.