Question

B58 B с D 1 40,000 2 60.000 2 80.000 100,000 50,000 70.000 $8 $65.000 75% 25% 30% of the budgeted unit sales of the next quarter 12,000 units 5 pounds 10% of the next quarters production needs 23,000 pounds $0.00 per pound 60% in the quarter the purchases are made 40% in the quarter following purchase $81,500 Budgeted unit sales 6 2 . Selling price per unit 8 Accounts receivable, beginning balance 9 - Sales collected in the quarter sales are made 10. Sales collected in the quarter after sales are made 11 Desred ending finished goods inventory is 12 Finished goods inventory beginning 13. Raw materials required to produce one unit 14 Desired ending inventory of raw materiais is 15. Raw materials inventory beginning 16 Raw material costs 17 Raw materials purchases are paid 18 and 19 Accounts payable for raw materiais, beginning balance 20 21 Enter a formula into each of the cells marked with a 7 below 22 23 Review Problem: Budget Schedules 24 25 Construct the sales budget 26 27 Budgeted unit sales 28 Selling price per unit 29 Total sales 30 31 32 Construct the schedule of expected cash collections 33 34 Beginning balance accounts receivable 35 First quarter sales 36 Second-quarter sales 37 Third quarter sales 18 Fourth quarter sales 9 Total cash collections 4 Year 2 Quarter 2 3 2 2 2 2 ? 2 ? ? 2 Year 3 Quarter 1. 2 ? ? 2 2 ? 2 ? 2 ? Year 2 Quarter 2 3 4 Year 1 ? ? 2 ? ? ? ? ? 2 ? 2 2 ?

Answer 1

The given question is based on Budgeting , Inventory, Receivable and cash management. The various criteria given have to be utilised to scehdule the sales and cash budget.

WN 1
	Particulars		Year 2	Year 3
			Qtr 1	Qtr 2	Qtr 3	Qtr 4	Qtr 1	Qtr 2
a	Sales - Given		40,000	60,000	100,000	50,000	70,000	80,000
b	Add : Closing inventory	a x 30%	18,000	30,000	15,000	21,000	24,000	-
c	Less: Opening inventory		12,000	18,000	30,000	15,000	21,000	24,000
d	Units to be produced	a+b-c	46,000	72,000	85,000	56,000	73,000	56,000
	Raw material
e	Desired Ending Inventory = 10 /100 x next quarter production x 5 pound		36,000	42,500	28,000	36,500	28,000
f	Opening Raw material inventory		23,000	36,000	42,500	28,000	36,500	28,000
h	Raw material required -pounds	d x 5 pounds	230,000	360,000	425,000	280,000	365,000	280,000
i	Raw materials to be purchased- pounds	e+h-f	243,000	366,500	410,500	288,500	356,500	252,000
j	Purchase cost @ 0.80/ pound	I x 0.80	$      194,400	$             293,200	$             328,400	$             230,800	$             285,200	$             201,600
Note :	Units to be produced = Sales +Closing inventory -Units at the beginning
	Closing Inventory of earlier quarter will be the opening invnetory of next quarter
	In absence of information regarding the Sales units of Qtr 3 of year 3, the closing inventory is assumed to be nil.
	The inventory in Qtr 2 of Year 3 is left nil as we do not have data for next qtr.

Data
Particulars		Year 2	Year 3
	Qtr 1	Qtr 2	Qtr 3	Qtr 4	Qtr 1	Qtr 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
Accounts receivable - Beginning Balance		65,000	80,000	120,000	200,000	100,000	140,000
Sales collected in Quarter sales are made	75%	240,000	360,000	600,000	300,000	420,000	480,000
Sales collected in Quarter after sales are made	25%	65,000	80,000	120,000	200,000	100,000	140,000
Desired ending Finished Inventory -Units ( budgeted unit sales of next quarter)	30%	18,000	30,000.00	15,000.00	21,000.00	24,000.00	-
Finished goods inventory beginning		12,000	18,000	30,000	15,000	21,000	24,000
Raw material required to produce one unit -Pounds	5	230,000	360,000	425,000	280,000	365,000	280,000
Desired ending inventory of raw materials -next quarter's production needs	10%	36,000	42,500	28,000	36,500	28,000	-
Raw material inventory beginning -Pounds		23,000	36,000	42,500	28,000	36,500	28,000
Raw material costs per pound	$                  0.80	$           194,400	$        293,200	$        328,400	$           230,800	$           285,200	$           201,600
Raw material purchases paid in quarter purchases are made	60%	$           116,640	$        175,920	$        197,040	$           138,480	$           171,120	$           120,960
Raw material purchases paid in quarter following , purchases are made	40%	$             81,500	$          77,760	$        117,280	$           131,360	$              92,320	$           114,080
Accounts payable for raw material beginning balance		$             81,500	$          77,760	$        117,280	$           131,360	$              92,320	$           114,080
The accounts payable will carry a closing balance of 40 percent of purchases made in Qtr 2 of year 3
Budget Schedules
Sales Budget		Year 2	Year 3
		Qtr 1	Qtr 2	Qtr 3	Qtr 4	Qtr 1	Qtr 2
Budgeted sales units		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
Schedule of Expected cash collections
Particulars		Year 2	Year 3
		Qtr 1	Qtr 2	Qtr 3	Qtr 4	Qtr 1	Qtr 2
Year 2
Beginning balance of Accounts receivables	$              65,000	$             65,000	$          80,000	$        120,000	$           200,000	$           100,000	$                       -
Receipt of opening balance of AR of Last qtr of Yr 1		$             65,000
First quarter sales ( 320000 x75/100+65000 opening receivables )	$           320,000	$           240,000	$          80,000
Second quarter sales -(320000 x 25%+480000 x 75%)	$           480,000		$        360,000	$        120,000
Third quarter sales (480000 x 25%+800000 x75%)	$           800,000			$        600,000	$           200,000
Fourth quarter sales (800000 x25%+400000 x 75%)	$           400,000				$           300,000	$           100,000
Year 3
First quarter sales ( 560000 x75/100+100000 opening receivables )	$           560,000					$           420,000	$           140,000
Second quarter sales -(560000 x 25/100+640000 x 75/100))	$           640,000						$           480,000
Closing AR ( 640000 x 25/100)							$           160,000
Total Cash Collections		$           305,000	$        440,000	$        720,000	$           500,000	$           520,000	$           620,000
The cash receipts have been calculated at 75 percent of current qtr sales and 25 percent of last qtr sales .
The closing AR balance is 25 percent of sales of Qtr 2 of Year 3.