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. The 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 an 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\mathrm{.}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

$10$

th 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

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 Receivable owing at July

$1($

which represents

$40$

$\%$

of June sales

$)$

are 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 on 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 $

$\mathrm{.}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

$29$

th at

$11$

pm

$.$

Submit your project in Moodle. The instructor will assist you by email and zoom until May

$25$

th 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:

Sales budget for July.

Production budget for July in units.

Direct Materials budget for July.

Direct Labor budget for July.

Manufacturing Overhead budget for July.

Cost of Goods Sold budget for July.

Cash Receipts & Cash Disbursements budget for July.

Pro

$-$

forma financial statements for July:

Multi

$-$

step Income Statement

$($

link the COGS to the production budget

$($

average cost per unit

$)$

Statement of Retained Earnings

Comparative Classified Balance Sheet with Change column

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.

Round all values to the nearest dollar on all budgets and statements