Excel worksheetChapter 8 Form Chapter 8: Applying Excel Data Year 2 Quarter Year 3 Quarter...

Question

Excel worksheetChapter 8 Form 	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	?	?	?	?	? 	Required 	Check your worksheet by changing the budgeted unit sales in Quarter 2 of Year 2 in cell C5 to 75,000 units.  The total expected cash collections 	for the year should now be $2,085,000.  If you do not get this answer, find the errors in your worksheet and correct them.  		Units	60,000	75,000 	Fill in the following information from your worksheet 	Total cash receipts for the year 	Total cash disbursements for the year

Ayushi · Accepted Answer

Chapter 8 Form
	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	40,000	60,000	100,000	50,000	70,000	80,000
	Selling price per unit	$8	$8	$8	$8	$8	$8
	Total sales	$320,000	$480,000	$800,000	$400,000	$560,000	$640,000
	Construct the schedule of expected cash collections	Year 2 Quarter
		1	2	3	4	Year
	Beginning balance accounts receivable	$   65,000				$   65,000
	First-quarter sales	$240,000	$   80,000			$   320,000
	Second-quarter sales		360,000	$   120,000		$   480,000
	Third-quarter sales			600,000	$   200,000	$   800,000
	Fourth-quarter sales				300,000	$   300,000
	Total cash collections	$   305,000	$   440,000	$   720,000	$   500,000	$   1,965,000
	Construct the production budget	Year 2 Quarter					Year 3 Quarter
		1	2	3	4	Year	1	2
	Budgeted unit sales	40,000	60,000	100,000	50,000	250,000	70,000	80,000
	Add desired ending finished goods inventory	18,000	30,000	15,000	21,000	84,000	24,000	- 0
	Total needs	58,000	90,000	115,000	71,000	334,000	94,000
	Less beginning finished goods inventory	12,000	18,000	30,000	15,000	75,000	21,000
	Required production in units	46,000	72,000	85,000	56,000	259,000	73,

Chapter 8 Form Chapter 8: Applying Excel DataYear 2 Quarter Year 3 Quarter 123412 Budgeted unit sales40,00060,000100,00050,00070,00080,000 • Selling price per unit$8 • Accounts...

Answer To: Chapter 8 Form Chapter 8: Applying Excel DataYear 2 Quarter Year 3 Quarter 123412 Budgeted...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment