Data Scenario:

You have just been hired into a manaqement position which requires the application of your budqetinq skills.

You find out that budqeting has not been a priority of the company.

You have contacted various areas on the orqanization and have acoumulated the information below to assist you

in preparinq a comprehensive budqet.

Required: You must use cell references on the BudgetSolution worksheet, by referencing this work sheet that contains the data.

Prepare the following second quarter budqets and answer the questions listed on the template provided on the Budqet Solution Worksheet. I have adapted the budqet model to meet the needs of this company.

If I bolded a line item, that is a header and does not need computation on that row.

Sales Budqet per month and quarter.

Production Budqet per month and quarter.

Direct materials purchase budqet per month and quarter.

Manufacturinq Cost budqet per month and quarter.

Sellinq and administrative expenses budqet per month and quarter.

Cash budqet per month and quarter.

Budqeted income statement $($iqnore income tax$)$ for the quarter.

What if the company decides to lay off one of the part$-$time administrative staff. The monthly salaries will be reduced by $$2,000,$ list all budgets that will change? Whg$?$ Part $1$ Solution

#$1$ Sales Budget

Units

Selling Price

Sales

#$2$ Production Budget

Sales Budget $($Reference Budget $1)$

Plus desired ending inventory

Total Inventory requirements

Less: Beginning Inventory

Units to be produced

#$3$ Direct Material Purchases Budget

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

Manufacturing Inc.

Sales Budget

For quarter ended June $30,20$XX

Manufacturing Inc.

Production Budget

For quarter ended June $30,20$XX

Total purchases of direct materials Manufacturing Inc.

Manufacturing Cost Budget

For quarter ended June $30,20$XX

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$

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 Manufacturing Inc.

Cash Budget

For quarter ended June $30,20xx$

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

#$7$ Budgeted Income statement for Second quarter

Sales $($Budget #$1)$

Cost of Goods Sold:

Beginning Finished Goods

Total Manufacturing Costs $($Budget #$4)$

Cost of Goods Available for Sale

Less: Ending finished goods

Budgeted Cost of Goods Sold

Gross Profit

Less: Selling and administrative expenses $($Budget $5)$

Income before income taxes

Manufacturing Inc.

Budgeted Income Statement

For the Secord Quarter ended June $30,20xx$

#$8$ What if the company decides to lay off one of the part$-$time administrative staff. The monthly salaries will be reduced by $$2,000,$ list all budgets that will change? 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.