Select Budgets that make up the Master Budget. The budgets are linked with Excel formulas.
Data	Year 1 by Quarter				Year 2, 1st 2 Quarters
	1	2	3	4	1	2
Budgeted unit sales	42,000	46,500	40,000	38,000	46,000	48,000
Selling price per unit	$27.16	per unit
Accounts receivable, beginning balance	$65,000
Sales collected in the quarter sales are made	75%
Sales collected in the quarter after sales are made	25%
Desired ending finished goods inventory is	30%	of the budgeted unit sales of the next quarter
Finished goods inventory, beginning	12,000	units
Raw materials required to produce one unit	5.00	pounds
Desired ending inventory of raw materials is	10%	of the next quarter's production needs
Raw materials inventory, beginning	23,000	pounds
Raw material costs	$0.80	per pound
Raw materials purchases are paid	60%	in the quarter the purchases are made
and	40%	in the quarter following purchase
Accounts payable for raw materials, beginning balance	$81,500
Budgeted labor hours per unit produced	6.00	hours
Labor wage rate	$16.25	per hour
Construct the sales budget	Year 1 by Quarter					Year 2, 1st 2 Quarters
	1	2	3	4	Year	1	2
Budgeted unit sales	42,000	46,500	40,000	38,000	166,500	46,000	48,000
Selling price per unit	$27.16	$27.16	$27.16	$27.16	$27.16	$27.16	$27.16
Total sales	$1,140,720	$1,262,940	$1,086,400	$1,032,080	$4,522,140	$1,249,360	$1,303,680
Construct the schedule of expected cash collections	Year 1 by Quarter
	1	2	3	4	Year
Accounts receivable, beginning balance	$ 65,000				$ 65,000
First-quarter sales	855,540	$ 285,180			$ 1,140,720
Second-quarter sales		947,205	$ 315,735		$ 1,262,940
Third-quarter sales			814,800	$ 271,600	$ 1,086,400
Fourth-quarter sales				774,060	$ 774,060
Total cash collections	$ 920,540	$ 1,232,385	$ 1,130,535	$ 1,045,660	$ 4,329,120
Construct the production budget	Year 1 by Quarter					Year 2, 1st 2 Quarters
	1	2	3	4	Year	1	2
Budgeted unit sales	42,000	46,500	40,000	38,000	166,500	46,000	48,000
Add desired finished goods inventory	13,950	12,000	11,400	13,800	13,800	14,400
Total needs	55,950	58,500	51,400	51,800	180,300	60,400
Less beginning inventory	12,000	13,950	12,000	11,400	12,000	13,800
Required production	43,950	44,550	39,400	40,400	168,300	46,600
Construct the raw materials purchases budget	Year 1 by Quarter					Year 2, 1st Quarter
	1	2	3	4	Year	1
Required production (units)	43,950	44,550	39,400	40,400	168,300	46,600
Raw materials required to produce one unit	5.00	5.00	5.00	5.00	5.00	5.00
Production needs (pounds)	219,750	222,750	197,000	202,000	841,500	233,000
Add desired ending inventory of raw materials (pounds)	22,275	19,700	20,200	23,300	23,300
Total needs (pounds)	242,025	242,450	217,200	225,300	864,800
Less beginning inventory of raw materials (pounds)	23,000	22,275	19,700	20,200	23,000
Raw materials to be purchased	219,025	220,175	197,500	205,100	841,800
Cost of raw materials per pound	$0.80	0.80	0.80	0.80	0.80	0.80
Cost of raw materials to be purchased	$175,220	$176,140	$158,000	$164,080	$673,440
Construct the schedule of expected cash payments	Year 1 by Quarter
	1	2	3	4	Year
Accounts payable, beginning balance	$ 81,500				$ 81,500
First-quarter purchases	105,132	$ 70,088			$ 175,220
Second-quarter purchases		105,684	$ 70,456		$ 176,140
Third-quarter purchases			94,800	$ 63,200	$ 158,000
Fourth-quarter purchases				98,448	$ 98,448
Total cash disbursements	$ 186,632	$ 175,772	$ 165,256	$ 161,648	$ 689,308
Construct the direct labor budget	Year 1 by Quarter
	1	2	3	4	Year
Planned Production in Units	43,950	44,550	39,400	40,400	168,300
x Direct Labor Hours per Unit	6.00	6.00	6.00	6.00	6.00
Budgeted Direct Labor Hours	263,700	267,300	236,400	242,400	1,009,800
x Cost per Direct Labor Hour	$16.25	$16.25	$16.25	$16.25	$16.25
Budgeted Direct Labor Cost	$ 4,285,125	$ 4,343,625	$ 3,841,500	$ 3,939,000	$ 16,409,250

1. Save a copy of this original budget, so you have a basis of comparison. 2. Input the projections below. All the projections should be entered before the questions are answered in H3. 3. There is insufficient info to create a complete cash budget, but the following calculations can be made BEFORE and AFTER the projections indicate increase or decrease to cash balance Change in Expected Cash Collections for the year based on the projections. -Change in Expected Cash Payments (for raw materials) for the year based on the projections. indicate increase or decrease to cash balance Change in Budgeted Labor payments for the Year based on the projections indicate increase or decrease to cash balance indicate increase or decrease to cash balance 4. Based on the changes in the above 3 budgets, what is the overall cummulative effect of the projections on the cash balance? Projections: Budgeted Sales increase 5% in Q1-3, 3% increase in Q4. In the first 2 quarters next year, there is a 1% increase each quarter. A drop in sales price of $0.97 is what is expected to drive up sales units. More aggressive collection efforts are being made to increase the to 80% in current quarter, 20% next quarter Abetter supplier has been found to decrease raw material waste in production by 18%, so less material is needed. Beginning inventory is unaffacted by this more recent change. But the cost of these better raw materials is expected to increase prices by 12%. The supplier is requestion more prompt payment so that 75% of our direct materials purchases must be paid off in the month of purchase 25% the following month. Technology has reduced labor hours required per unit of production by 15% More highly skilled employees are needed to run the high tech equipment. Avg wage rates will increase to $19.95/hour only the items stated are changing. The other components of the budgets are unchanged