50.1K

Verified Solution

Link Copied!

Hi, I could really use help with setting up and the math of this problem. If you could put in an Excel work sheet format, that would be great. That way, I can understand how to set it up correctly.

Acme Ganage Door Company, Inc. Acme Garage Door makes overhead garage doons fon two local construction companies and kvants to prepare a master budget for the next month of operration. 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. managembent wants to maintain ending inventory each month equal to 10% of the estimated wnit sales in that month. The beginning inventory of finished doors is expected to be 20 as of 7ml(y1,2022, with a per-unit cost of 5630 . 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 ram materials inventory and would like to have an ending inventory of 1000 square feet. Variable overhead costs are estimated at S110 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 primcipal 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 630.22 will be paid on 710 . Monthly depreciation of the building and the equipment is 58,500 and 59.000 . respectirely (this is the salestadministrative 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's sales to be received by the end of the month and the balance to be collected in the next month. Therefore, all the Accounts Receiyable owing at July 1 (which represents 40% of June sales) are expected to be reqeived in July- Lumber is purchased on aceount. Approximately 60 f of of the purghasess are paid in the current month and the balance is paid early in the mext month. Ihe ballange of Accounts Payable owing on July t, will be paid in July. Assume all othert explenses both. production and adminis(rative) are paid in the month incurred. The Board of Dinectors 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 May 29th at 11pm. Submit your project in Moodle. The instructor will assist you by email and zoom until May 25th at 11pm. 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 Balanee 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. Acme Garage Door Company, Inc. Balance Sheet June 30,2022 Current Assets Cash Accounts Receivable Inventory--Raw Material Finished Goods Total Current Assets Land, Building and Equipment Land Building (net of depreciation) Equipment (net of depreciation) Total Property, Plant \& Equipment Other Assets Total Assets Liabilities Current Liabilities Accounts Payable Income Tax Payable Total Current Liabilities Long-term debt Total Liabilities Stockholder's Equity Common Stock, \$4 par value, 90,000 shares outstanding Additional Paid-In Capital Retained Earnings Total Stockholders' Equity Total Liabilities and Stockholders' Equity 42,00088,00018,70012,600161,300 100,000997,625746,3101,843,935125,585$2,130,820

Answer & Explanation Solved by verified expert

See Answer