BA213 Project Acme Garage Door Company, Inc. Acme Garage Door makes overhead garage doors for two local construction companies and wants to prepare a master budget for the next month of operation, July 2022. The sales department estimates that it can sell 190 doors in July. Each door retails for $1,200. In order to avoid delays in shipping, management wants to maintain ending inventory each month equal to 10% of the estimated unit sales in that month. Beginning inventory of finished doors is expected to be 20 as of July 1, 2022, with a per unit cost of $630. Each door takes 38 square feet of lumber and 4 hours of direct labor. Lumber purchases are estimated at $11 per square foot and direct labor averages $17 an hour, including benefits. As of July 1, Acme Garage Door estimates it will have 1700 square feet of lumber in raw materials inventory and would like to have ending inventory of 1000 square feet. Variable overhead costs are estimated at $110 per door. Fixed overhead for the month is estimated to be $15,000. Acme Garage Door anticipates selling and administrative costs of $19,250 monthly and the monthly interest cost on its long-term debt is 1.5% of the outstanding balance, paid on the last day of each month. The principal payment on the debt is $26,000 per month. Acme Garage Door, as a corporation, expects to pay 30% of its net income in income taxes. Monthly estimates are sent to the appropriate taxing authority by the 10th of the next month (therefore, income taxes payable as of 6/30/22 will be paid on 7/10). Monthly depreciation of the building and the equipment is $8,500 and $9,000 respectively (this is the sales/administrative portion of depreciation and not a part of overhead production costs). All sales are on account. However, in estimating cash flows, Acme Garage Door expects 60% of the current month sales to be received by the end of the month and the balance to be collected in the next month. Therefore, all the Accounts Receivable owing at July 1 (which represents 40% of June sales) is expected to be received in July. Lumber is purchased on account. Approximately 60% of the purchases are paid in the current month and the balance is paid early in the next month. The balance of Accounts Payable owing at July 1, will be paid in July. Assume all other expenses (both production and administrative) are paid in the month incurred. The Board of Directors for the company plans to declare and pay a $.50 per share cash dividend during the month of July. Instructions: You are to build a spreadsheet(s) in Excel, which will enable you to prepare all the budgeted statements listed under the Required section below. Reference Assumption Tables for your budget values. The budgets should be linked, using cell references, etc. For instance, total sales revenue on the Income Statement should be referenced from the Sales Budget so that if the projected units sold in July changed, total sales revenue on the Income Statement would change. Round all values to the nearest dollar on all budgets and statements. The project is due March 6 at 11 pm. Submit your project in Moodle. The instructor will assist you by email and zoom until March 3 at 11 pm. Late projects will be accepted with a 5-point deduction per day, until March 9 at 11 pm. Grades will be based on completion of the project requirements, linking the master budget and financial statements, appropriate format and following directions. Required: 1. Sales budget for July. 2. Production budget for July in units. 3. Direct Materials budget for July. 4. Direct Labor budget for July. 5. Manufacturing Overhead budget for July. 6. Cost of Goods Sold budget for July. 7. Cash Receipts & Cash Disbursements budget for July. 8. Pro-forma financial statements for July: a. Multi-step Income Statement (link the COGS to the production budget (average cost per unit) b. Statement of Retained Earnings c. Comparative Classified Balance Sheet with Change column 9. Management is unsure of the projected sales figure and would like to know how many units need to be sold for the company to break-even. Prepare the appropriate calculations to so determine. 10. Round all values to the nearest dollar on all budgets and statements?