Chapter 8: Applying Excel (Problem 1 AE)

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
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
Beginning balance accounts receivable	?				?
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 ending finished goods inventory	?	?	?	?	?	?
Total needs	?	?	?	?	?	?
Less beginning finished goods inventory	?	?	?	?	?	?
Required production in units	?	?	?	?	?	?
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 (pounds)	?	?	?	?	?	?
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 (pounds)	?	?	?	?	?
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
Beginning balance accounts payable	?				?
First-quarter purchases	?	?			?
Second-quarter purchases		?	?		?
Third-quarter purchases			?	?	?
Fourth-quarter purchases				?	?
Total cash disbursements	?	?	?	?	?