Please prepare answers/solution using an excel spreadsheet if possible.

Data Scenario:

You have just been hired into a management position which requires the application of your budgeting skills. You find out that budgeting has not been a priority of the company. You have contacted various areas on the organization and have accumulated the information below to assist you in preparing a comprehensive budget.

Manufacturing Inc. produces a part used in the production of engines. Actual Sales and Projected sales in units:

March (Actual) 38,000

April44,000

May 45,000

June 50,000

July 52,000

Sales are the following type:

56%Cash sales collected in month of sale

44%Credit sales collected in the following month of sale

The following data pertains to the manufacturing process.

1.Finished goods inventoryMarch 31st is 35,200 units

$148.71 budgeted cost to make a unit

Desired ending finished goods for each month80% of next month's sales volume

2.Direct materials used:

Direct MaterialPer-Unit UsageCost per Pound

Metal8 pounds$6

The beginning balance of each month needs to be able to produce 50% of that month's estimated sales volume

Beginning material in pounds as of April 1st 176,000

Direct materials paid in month purchased.

3.The direct labor used per unit 4 hours, $12.00 per hour Direct labor paid in month incurred.

4.Overhead each month is estimated based on direct labor hours per variable cost.All costs that use cash are paid in month incurred.

Fixed costVariable cost

Supplies$1.00

Power0.60

Maintenance $27,000 0.40

Supervision 15,000

Depreciation 19,000

Taxes 11,000

Other 60,000 1.10

Total $132,000 $3.10

5.Monthly selling and administrative expenses are based on units sold per variable cost.All costs that use cash are paid in month incurred.

Fixed costVariable cost

Salaries$40,000

Commissions $1

Depreciation 15,000

Shipping0.7

Other 10,0000.4

Total$65,000 $2.10

6.Unit selling price is $124 per unit

7.Cash balance as of April 1^st is $150,000

Required:

#1 Sales Budget

Manufacturing Inc.

Sales Budget

For quarter ended June 30, 20XX

AprilMayJuneQuarter

Units44,000

Selling Price$124

Sales$5,456,000

#2Production Budget

Manufacturing Inc.

Production Budget

For quarter ended June 30, 20XX

AprilMayJuneQuarter

Sales Budget (Reference Budget 1)

Plus desired ending inventory

Total Inventory requirements

Less:Beginning Inventory

Units to be produced

#3Direct Material Purchases Budget

Manufacturing Inc.

Direct Material Purchases Budget

For quarter ended June 30, 20XX

AprilMayJuneQuarter

Units to be produced (Reference Budget 2)

Direct Materials per unit (pounds)

Production needs (pounds)

Desired ending inventory (pounds)

Total needs (pounds)

Less: Beginning inventory (pounds)

Purchases needed of Direct materials (pounds)

Cost per pound

Total purchases of direct materials

#4 Manufacturing Cost Budget

Manufacturing Inc.

Manufacturing Cost Budget

For quarter ended June 30, 20XX

AprilMayJuneQuarter

Direct Materials:

Production needs(pounds)--found on Budget 3

Cost per pound

Total Cost of material issued to production

Direct Labor:

Units to be produced (Reference Budget 2)

Direct labor time per unit (hours)

Total hours needed

Cost per hour

Total cost of Direct Labor

Manufacturing Overhead:

Budgeted direct labor hours needed (Reference Row 51 above)

Variable overhead rate

Budgeted variable overhead

Budgeted Fixed overhead

Total Manufacturing Overhead

Total Manufacturing Cost

#5 Selling and Administrative Expenses Budget

Manufacturing Inc.

Selling and Administrative Expenses Budget

For quarter ended June 30, 20XX

AprilMayJuneQuarter

Budgeted Sales in units (Reference Budget 1)

Variable Selling and Administrative expenses per unit

Total variable expenses

Fixed Selling and Administrative expenses

Total selling and administrative expenses

#6Cash Budget

Manufacturing Inc.

Cash Budget

For quarter ended June 30, 20XX

AprilMayJuneQuarter

Beginning cash

Add Cash Collections of Sales:

Cash sales collected in month sale

Credit sales Collected in following month

Total Cash receipts

Cash Available

Less Cash Disbursements:

Purchases (Budget 3)

Direct Labor (Budget 4)

Overhead that uses cash

Selling and administrative expenses that use cash

Total Cash Disbursements

Ending Cash

#7Based on the quarterly cash budget you prepared, provide recommendations on cash management.Your comments should be directed at management.

#8 Budgeted Income statement for Second quarter

Manufacturing Inc.

Budgeted Income Statement

For the Secord Quarter ended June 30, 20XX

Sales (Budget #1)

Cost of Goods Sold:

Beginning Finished Goods

Total Manufacturing Costs (Budget #4)

Cost of Goods Available for Sale

Ending finished goods

Budgeted Cost of Goods Sold

Gross Profit

Less:Selling and administrative expenses (Budget 5)

Income before income taxes

#9What if the company decides to lay off one of thepart-time administrative staff.The monthly salaries will be reduced by $6,000, what budgets are effected? Why?

What is the New Net income(Loss) for the quarter?

If you have linked everything correctly, you should only have to change the monthly salary on the data sheet.

Please change the salary back to the original amount of before you submit.