Case 1 [10 marks] Managerial Accounting- MBA Midwest Products is a manufacturer of A/C appliances. Thus, peak sales occur in July of each year as shown in the company's sales budget (given) for the third quarter, given below: Budgeted sales (in units): July=9,000 units; August=6,000 units; September=5,000 units; selling price per unit= $200 Cash collection schedule (given) From past experience, the company has learned that 20% of a month's sales are collected in the month of sale, another 70% are collected in the month following sale, and the remaining 10% are collected in the second month following sale. May sales totalled $500,000, and June sales totalled $600,000. Production budget (given) Past experience has shown that end-of-month finished goods inventories must equal 15% of the next month's sales. The inventory at the end of June equal 1,350 units. 3,000 units are expected to be sold in October. Materials, Labor and Overhead costs Each A/C requires 10 kilograms of material. The price per kilogram is $5. The management of Midwest requires that the ending inventory of materials to be 20% of the coming month needs. The beginning materials inventory of October equal 5,000 kilograms. All purchases of materials are made using cash. Direct labor produce each unit in 30 minutes (half an hour) and are paid $20 per working hour. The company follows a layoff policy and workers are only paid for the actual hours worked. No overtime rate is applied. The variable manufacturing overhead cost is $30 per labor hour and the fixed manufacturing costs are $100,000 (of which the depreciation charges are $30,000). Cash budget Cash payments for selling and administrative expenses are $200,000 in July, $220,000 in August and $200,000 in September. Midwest Products started the year with a cash balance of $10,000. The minimum cash balance required is $10,000. The company maintains a 20%, $300,000 line of credit, whereby any borrowing is made at the start of the month and any repayments are made by the end of the month. The company will buy equipment for $600,000 and $500,000 cash on August and September. Required: i. Prepare a sales budget for the third quarter 2021. 2. Prepare a schedule of expected cash collections from sales, by month and in total, for the third quarter 2021. Calculate the accounts receivable balance at the end of September 30, 2021. 3. Prepare a production budget for the quarter ending September 30, 2021. 4. Prepare the direct materials, direct labor and manufacturing overhead budgets for the third quarter of 2021. 5. Prepare the cash budget for the quarter ending September 30, 2021. Provide the master budget using excel