Question

Enter formulas in all cells that contain question marks. For example, in cell B26 Enter formula "=B5". Check your worksheet by changing the budgeted sales in Quarter 2 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 his answer, find the errors in the worksheet and correct them. If you find a method better then the excel method please feel free to do so.

Answer 1

Chapter 8: Applying Excel
Data	Year 2 Quarter				Year 3 Quarter
	1	2	3	4	1	2
Budgeted unit sales	40,000	60,000	1,00,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	1,00,000	50,000	70,000	80,000
Selling price per unit	$8	$8	$8	$8	$8	$8
Total sales	$3,20,000	$4,80,000	$8,00,000	$4,00,000	$5,60,000	$6,40,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	2,40,000	$      80,000			$   3,20,000
Second-quarter sales		3,60,000	$ 1,20,000		$   4,80,000
Third-quarter sales			6,00,000	$2,00,000	$   8,00,000
Fourth-quarter sales				3,00,000	$   3,00,000
Total cash collections	$   3,05,000	$   4,40,000	$ 7,20,000	$5,00,000	$ 19,65,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	1,00,000	50,000	2,50,000	70,000	80,000
Add desired ending finished goods inventory	18,000	30,000	15,000	21,000	21,000	24,000
Total needs	58,000	90,000	1,15,000	71,000	2,71,000	94,000
Less beginning finished goods inventory	12,000	18,000	30,000	15,000	12,000	21,000
Required production in units	46,000	72,000	85,000	56,000	2,59,000	73,000
Construct the raw materials purchases budget	Year 2 Quarter					Year 3 Quarter
	1	2	3	4	Year	1
Required production (units)	46,000	72,000	85,000	56,000	2,59,000	73,000
Raw materials required to produce one unit (pounds)	5	5	5	5	5	5
Production needs (pounds)	2,30,000	3,60,000	4,25,000	2,80,000	12,95,000	3,65,000
Add desired ending inventory of raw materials (pounds)	36,000	42,500	28,000	36,500	36,500
Total needs (pounds)	2,66,000	4,02,500	4,53,000	3,16,500	13,31,500
Less beginning inventory of raw materials (pounds)	23,000	36,000	42,500	28,000	23,000
Raw materials to be purchased (pounds)	2,43,000	3,66,500	4,10,500	2,88,500	13,08,500
Cost of raw materials per pound	$0.80	$0.80	$0.80	$0.80	$0.80
Cost of raw materials to be purchased	$1,94,400	$2,93,200	$3,28,400	$2,30,800	$10,46,800
Construct the schedule of expected cash payments	Year 2 Quarter
	1	2	3	4	Year
Beginning balance accounts payable	$      81,500				$      81,500
First-quarter purchases	1,16,640	$      77,760			$   1,94,400
Second-quarter purchases		1,75,920	$ 1,17,280		$   2,93,200
Third-quarter purchases			1,97,040	$1,31,360	$   3,28,400
Fourth-quarter purchases				1,38,480	$   1,38,480
Total cash disbursements	$   1,98,140	$   2,53,680	$ 3,14,320	$2,69,840	$ 10,35,980

1Chaptor B: Applying Excol 3 Data Year 2 Queter Year 3 Quarter 5 Budgeted unt salos 7Seling price per uni 8 Accounts recerable beginning balance 9Salos colocted in the quartor salos are mado 0Sales colected in the quader afler sales are made 11Deained ending finished goods inventory 2 Finished goods inventory, beginning 13Raw matorias roqured to prod uoe one unit 4 Desined ending inventory of aw materals s 15Ramaras iventary, beginning 6Raw metenal costs of the budgeted unt sales of the next quarters 0.8 per pound n tho quarter the purchasoS n the quarter following 19Aceounts payabie for raw materias, beginning balance 22 Roview Problem: Budgot Schedules 4 Construct the sales budget Year 2 Quster Year 3 Quarter 26 Budgeted unt salos 7 Selng price per unit 28 Total seies FES -G5 F26-F2 0 Construct the schedule of expected cash colections Year 2 Queter 32 Begnning balance accounts rece vabke 33 Frstquarter sales 34 Second-quarter seles 35 Thre-qualor salos 36 Fourth qualor salos 7 Total ash collections -832-C32 D32 E32 833C33 D33 E33 -834+034+034 E34 828 89 -B28 810 -D28 8 -028 810 9 Construct the production budget Yoar 2 Quater Yoar 3 Quafor 41 Budgeted unt sales -841 041 D41 E41 42 Atd deairod ending finished goods invontory 43 Total needs C41-C42 E41 E42 F41-F42 G41-G42 44 Less beginning finshed goods inventory 45 Required production in unts


47 Construct the raw matorlals purchases budget Year 2 Quartor Yoar 3 Quator F45 Yaar -F49 F5 -F51·F52 F53-F54 -E45 49 Required psoduction (unts) so Raw matenals ายqurod to produce one unit tpounds) 51 Production needs (pounds) S2 Add desred ending inventory of raw etras (pounds) 53 Totel needs (pounds) 4 Less beginning inventory of raw materas (pounds) S Raw materials to bo purchasod (pounds) 56 Cost of aw mate as per pound 57 Cost of aw materals to be purchased B49 B50 -B51+B52 B53-854 -C49*C50 -C51 C52 C53-C54 045 D49 D50 -D51 D52 D53-054 -E49'E50 -G49 G50 -E51 E52 E51 SB$14 E53-E54 -$BS16 -$B516 5·F 56 59 Construct the schedule of expected cash payments Year 2 Quarter 61 Beginning baunce sccounts payable 62 First-queder putcheses 63 Second quarter purchases 64 Thrd-quater puchase -B81 C81 D61+E81 -B62+062 D62+E62 -B63 C63 D83 E63 -B64 C64 D64 E64 865-065-DES E65 857 '818 C57 917 -B57 B17 CST B18 -057 817 D57 B18 E57 817 Total cash disbursements 67 -B61+B62-B6 3+864-B65 "C81+C62-C83-C64-Се:5 -D61+D62-D63-D64.D65-E61-E62-E63-E64-Ee-F61-F62.63-F64-F65