Question

Chapter 8: Applying Excel
Data	Year 2 Quarter				Year 3 Quarter
	1	2	3	4	1	2
Budgeted unit sales	40,000	60,000	100,000	50,000	70,000	80,000
• Selling price per unit	$8	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	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
Enter a formula into each of the cells marked with a ? below
Review Problem: Budget Schedules
Construct the sales budget	Year 2 Quarter				Year 3 Quarter
	1	2	3	4	1	2
Budgeted unit sales	?	?	?	?	?	?
Selling price per unit	?	?	?	?	?	?
Total sales	?	?	?	?	?	?
Construct the schedule of expected cash collections	Year 2 Quarter
	1	2	3	4	Year
Accounts receivable, beginning balance	?				?
First-quarter sales	?	?			?
Second-quarter sales		?	?		?
Third-quarter sales			?	?	?
Fourth-quarter sales				?	?
Total cash collections	?	?	?	?	?
Construct the production budget	Year 2 Quarter					Year 3 Quarter
	1	2	3	4	Year	1	2
Budgeted unit sales	?	?	?	?	?	?	?
Add desired finished goods inventory	?	?	?	?	?	?
Total needs	?	?	?	?	?	?
Less beginning inventory	?	?	?	?	?	?
Required production	?	?	?	?	?	?
Construct the raw materials purchases budget	Year 2 Quarter					Year 3 Quarter
	1	2	3	4	Year	1
Required production (units)	?	?	?	?	?	?
Raw materials required to produce one unit	?	?	?	?	?	?
Production needs (pounds)	?	?	?	?	?	?
Add desired ending inventory of raw materials (pounds)	?	?	?	?	?
Total needs (pounds)	?	?	?	?	?
Less beginning inventory of raw materials (pounds)	?	?	?	?	?
Raw materials to be purchased	?	?	?	?	?
Cost of raw materials per pound	?	?	?	?	?
Cost of raw materials to be purchased	?	?	?	?	?
Construct the schedule of expected cash payments	Year 2 Quarter
	1	2	3	4	Year
Accounts payable, beginning balance	?				?
First-quarter purchases	?	?			?
Second-quarter purchases		?	?		?
Third-quarter purchases			?	?	?
Fourth-quarter purchases				?	?
Total cash disbursements	?	?	?	?	?

Answer 1