Requirements: Using the information given below create the following in the excel 4B Workbook provided.

The budgets and reports must be created in the order shown as information from one could be used on a future budget. If there are two listed together that means that you will do both separately and information given is used for both.

Correct formatting and spelling are part of this assignment. Use your text for examples of the budgets and reports. Should you have difficulty finding examples or have questions regarding the project I will only answer questions that are entered in the discussion forum. (Remember all budgets and reports have company name, document name, & date in the header regardless of the examples shown in the text.)

Sales Budget

Independent Company is preparing budgets for the quarter ending June 30.

Budgeted sales for the next five months are:

April	20,000 units
May	50,000 units
June	30,000 units
July	25,000 units
August	15,000 units

Selling price is $10 per unit.

Expected Cash Collections

All sales are on account.

Independents collection pattern is:

• 70% collected in the month of the sale
• 25% collected in the month following the sale
• 5% uncollectable

March 31 accounts receivable balance of $30,000 will be collected in full.

Production Budget

The management at Royal Company wants ending inventory to be equal to 20% of the following months budgeted sales in units.

On March 31, 4,000 units were on hand.

Direct Materials Budget

Five pounds of material are required per unit of product.

Materials on hand at the end of each month should equal 10% of the following months production

On March 31^st, 13,000 pounds of material are on hand.

Expected Cash Disbursement for Materials

Material cost is $0.40 per pound.

March 31^st accounts payable balance is $12,000

Direct Labor Budget

Each unit of product requires .025 hours (3 minutes) of direct labor.

Company has a no layoff policy so all employees will be paid for 40 hours of work each week.

In exchange for the no layoff policy, workers agree to a wage rate of $10 per hour regardless of the hours worked (no overtime pay)

For the next three months, the direct labor workforce will be paid for a minimum of 1.500 hours per month.

Manufacturing Overhead Budget & Ending Finished Inventory Budget

Overhead is applied to units of production on the basis of direct labor hours.

Variable manufacturing overhead rate is $20 per direct labor hour.

Fixed manufacturing overhead is $50,000 per month and includes $20,000 of noncash cost (primarily depreciation of plant assets).

Selling and Administrative Budget

Selling and administrative expenses budget is divided into variable and fixed components.

Variable and selling and administrative expenses are $0.50 per unit sold.

Fixed selling and administrative expense are $70,000 per month.

The fixed selling and administrative expenses include $10,000 in cost primarily depreciation that are not cash outflows of the current month.

Cash Budget & Budgeted Income Statement

Company:

• Maintains a 16% open line of credit for $75,000.
• Maintains a minimum cash balance of $20,000
• Borrows on the first day of the month and repays loans on the last day of the quarter
• Pays a cash dividend of $49,000 in April
• Purchases $143,700 of equipment in May and $48,000 in June (both purchases paid in cash)
• Has an April 1 cash balance of $40,000

Budgeted Balance Sheet

The company reported the following account balances prior to preparing its budgeted financial statements:

• Land $50,000
• Common Stock $200,000
• Retained Earnings $246,150
• Equipment $175,000