Part 1

I have a template set up on the Budgetsolution worksheet that you should use to complete the required budgets and requirements stated below.

You need to use cell references in the development of your budgets.

You must use this worksheet to reference the data that is being inputted onto the budgets on the budget worksheet.

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

April

40,000

May

50,000

June

60,000

July

65,000

Sales are the following type:

60%

Cash sales collected in month of sale

40%

Credit sales collected in the following month of sale

The following data pertains to the manufacturing process.

1. Finished goods inventory

March 31st

32,000

units

$148.71

budgeted cost to make a unit

Desired ending finished goods for each month

80%

of next month's sales volume

2. Direct materials used:

Direct Material

Per-Unit Usage

Cost per Pound

Metal

10

pounds

$8

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

200,000

3. The direct labor used per unit

4

hours

$9.25

per hour

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 cost

Variable cost

Supplies

$1.00

Power

0.50

Maintenance

$28,000

0.40

Supervision

16,000

Depreciation

200,000

Taxes

12,000

Other

80,000

1.10

Total

$336,000

$3.00

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 cost

Variable cost

Salaries

$50,000

Commissions

$1

Depreciation

40,000

Shipping

0.6

Other

20,000

0.4

Total

$110,000

$2.00

6. Unit selling price

$174

per unit

7. Cash balance as of April 1st

$160,000

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

Prepare the following second quarter budgets and answer the questions listed on the template provided on the BudgetSolution Worksheet. I have adapted the budget 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.

Please note the quarter column is for the quarter so not all lines should be added across in the quarter column. When you have beginning and ending inventory or cash balances this is for the quarter and should be brought over to the quarter column.

1. Sales Budget per month and quarter.

2. Production Budget per month and quarter.

3. Direct materials purchase budget per month and quarter.

4. Manufacturing Cost budget per month and quarter.

5. Selling and administrative expenses budget per month and quarter.

6. Cash budget per month and quarter.

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

8. Budgeted income statement (ignore income tax) for the quarter.

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

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

Part 1 Solution
#1 Sales Budget
Manufacturing Inc.
Sales Budget
For quarter ended June 30, 20XX
		April	May	June	Quarter
Units		40,000	50,000	60,000	150,000
Selling Price		$174
Sales		$6,960,000
#2 Production Budget
Manufacturing Inc.
Production Budget
For quarter ended June 30, 20XX
		April	May	June	Quarter
Sales Budget (Reference Budget 1)		40,000
Plus desired ending inventory
Total Inventory requirements
Less: Beginning Inventory
Units to be produced
#3 Direct Material Purchases Budget
Manufacturing Inc.
Direct Material Purchases Budget
For quarter ended June 30, 20XX
		April	May	June	Quarter
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
		April	May	June	Quarter
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 50 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
		April	May	June	Quarter
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
#6 Cash Budget
Manufacturing Inc.
Cash Budget
For quarter ended June 30, 20XX
		April	May	June	Quarter
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 Based 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
#9 What if the company decides to lay off one of the administrative staff. The monthly salaries will be reduced by $5,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.