Prepare your solution to the following three situations at Lynbrook, Inc. using Excel. A formula page is not required to be submitted with your solution. Situation #1 Lynbrook Inc. manufactures soccer balls and sells them internationally. The company uses a job-order costing system in which overhead is applied to jobs on the basis of direct labor cost. Its predetermined overhead rate is based on a cost formula that estimated $310,000 of manufacturing overhead for an estimated activity level of $250,000 direct material dollars. At the beginning of 2021, the inventory balances were as follows: Raw materials Work in process Finished goods $40,000 $14,000 $38,000 . . During the 2021, the following transactions were completed: Raw materials purchased for cash, $255,000. Raw materials used in production, $290,000 (materials costing $220,000 were charged directly to jobs; the remaining materials were indirect). Cash paid to employees as follows: Direct labor $160,000 Indirect labor $70,000 Sales commissions $65,000 Administrative salaries $81,000 Cash paid for rent during the year was $20.000 (513,000 of this amount related to factory operations, and the remainder related to selling and administrative activities). Cash paid for utility costs in the factory, $52,000. Cash paid for advertising, $130,000. Depreciation recorded on equipment, $100,000. (592,000 of this amount related to equipment used in factory operations; the remaining $3,000 related to equipment used in selling and administrative activities.) Manufacturing overhead cost was applied to jobs based on the predetermined overheard rate. Goods that had cost $645,000 to manufacture according to their job cost sheets were completed. Sales for the year (all paid in cash) totaled $1,275,000. The total cost to manufacture these goods according to their job cost sheets was $637,000. Required: 1. Based on the above information determine the following: Ending balances the three inventory accounts Cost of Good Manufactured Cost of Goods Sold (before the adjustment for over or Underapplied overhead. 2. Is Manufacturing Overhead under or overapplied for the year? Determined the cost of Goods Sold adjusted for the under or overapplied overhead. 3. Without preparing a schedule of Cost of Goods Manufactured, prepare an Income Statement for the year ending December 31, 2021. Situation #2 At the beginning of 2022, the president of Lynbrook asked you to evaluate the following alternatives relating to one of the lines of soccer balls in an effort to improved profitability. The company has a soccer ball that sells for $32. At present, the ball is manufactured in a small plant in south Texas that relies heavily on direct labor workers. Thus, variable expenses are high, totaling $24 per ball, of which 65% is direct labor cost. During 2021, the company sold 20,000 of these balls, with the following results: Sales (20,000 balls) $640,000 Variable expenses 480,000 Contribution margin 160,000 Fixed expenses 110,000 Net operating income S 50.000 Required: 1. Compute (a) the CM ratio and the break-even point in balls 2. Due to an increase in labor rates, the company estimates that variable expenses will increase by $2 per ball next year. If this change takes place and the selling price per ball remains constant at $32, what will be the new CM ratio and break-even point in balls? 3. Refer to the data in (2) above. If the expected change in variable expenses takes place, how many balls will have to be sold next year to earn the same net operating income, $50,000, as last year? 4. Refer again to the data in (2) above. The president feels that the company must raise the selling price of its basketballs. If Lynbrook wants to maintain the same CM ratio as last year, what selling price per ball must it charge next year to cover the increased labor costs? 5. Refer to the original data. The company is discussing the construction of a new, automated manufacturing plant. The new plant would slash variable expenses per ball by 45%, but it would cause fixed expenses per year to double. If the new plant is built, what would be the company's new CM ratio and new break-even point in balls? 6. Refer to the data in (5) above. If the new plant is built, how many balls will have to be sold next year to earn the same net operating income, $50,000, as last year? 7. You are a member of top management; would you be in favor of constructing the new plant? Explain. Situation #3 Lynbrook has two other popular soccer balls that it is currently selling, a high-end, high-quality (Professional) ball and an inexpensive daily use (Backyard) ball. Each product's selling price, variable expense per unit and annual sales volume are as follows: Backyard Professional Selling price per unit $20 $90 Variable expense per unit $8 $25 Number of units sold annually 24,000 4,000 Fixed expenses total $500,000 per year. Required: 1. Based on the above sales mix, determine the number of Backyard soccer balls and Professional soccer balls that must be sold to break-even. 2. Determine the Margin of Safety in sales dollars based on the current level of sales listed above