Question

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 below

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?

Answer 1

Answer 1		April	May	June	2nd Qtr
	Sales in units	40000	50000	60000	150000
	Sales price	25	25	25	25
	The Budgeted sales Revenue	1,000,000	1,250,000	1,500,000	3,750,000
Answer 2	Production Budget
		April	May	June	2nd Qtr	July
	The expected sales in units	40000	50000	60000	150000	60000
	Add Desired Ending Inventory (80%*next month sales)	40000	48000	48000	48000	26000
	The Total Needs	80000	98000	108000	198000	86000
	Less Beginning Inventory	32000	40000	48000	32000	48000
	The Budgeted production in units	48000	58000	60000	166000	38000
Answer 3		April	May	June	Qtr
	The Budgeted cost of raw material purchases
	The Budgeted sales volume	40000	50000	60000	150000
	No. of pounds required	10	10	10	10
	The Total required	400000	500000	600000	1500000
	Add Desired Ending Inventory (50% of same month sales volume)	200000	250000	300000	300000
	The Total Needs	600000	750000	900000	1800000
	Less Beginning Inventory	200000	200000	250000	200000
	The Budgeted raw material purchases in pounds A	400000	550000	650000	1600000
	Cost per pound B	8	8	8	8
	Cost of direct material purchases A*B	3,200,000	4,400,000	5,200,000	12,800,000
	The working
	Direct labour rate   not clear	April	May	June	Qtr
	Budgeted production in units	40000	50000	60000	150000
	Time required	4	4	4	4
	Direct labour hours required D	160000	200000	240000	600000
	Labour rate   L	9.25	9.25	9.25	9.25
	Direct labour cost D*L	1480000	1850000	2220000	5550000
Answer 4	The Manufacturing cost Budget per month
	Budgeted production	48000	58000	60000	166000
	variable manufacturing cost	129	129	129	129
	Manufacturing variable cost C	6,192,000	7,482,000	7,740,000	21,414,000
	Add: fixed manufacturing overhead D	336000	336000	336000	1008000
	Total manufacturing cost C+D	6,528,000	7,818,000	8,076,000	22,422,000
	working
	Direct material (10*8)	80
	Direct labour	37
	Variable manufacturing overhead ($3*4)	12
	Total variable manufacturing cost	129
Answer 5		April	May	June	Qtr
	The Budgeted Selling & administrative expenses
	Budgeted sales in units	40000	50000	60000	150000
	Variable selling and adm exp per unit	2	2	2	2
	Total variable overhead	80000	100000	120000	300000
	Add: Fixed selling & adm exp	110000	110000	110000	330000
	The Budgeted Selling and administrative expenses	190000	210000	230000	630000
	Dear student I have done first five subparts.