Question

Requirement 2: The company has just hired a new marketing manager who insists that unit sales can be dramatically increased by dropping the selling price from $8 to $7. The marketing manager would like to use the following projections in the budget: Year 2 Quarter Year 3 Quarter Data Budgeted unit Sales Selling price per unit 50,000 70.000 115,000 5,000 85,000 100.000 D Chapter 8: Applying Excel Data Year 3 Quarter 5 Budgeted unit sales 50.000 70,000 115,000 60,000 $5,000 100,000 7 per unit 65 000 75% 25% 7 - Selling price per unit 8 - Accounts receivable beginning balance 9 - Sales collected in the quarter sales are made Sales collected in the quarter after sales are made 11 Desired ending finished goods inventory is 12 . Finished goods inventory, beginning 13. Raw materials required to produce one unit 14 Desired ending inventory of raw materials is 30% of the budgeted unit sales of the next quarter 12 000 units 5 pounds 10% of the next quarter's production needs < Prev 2 of 2 Next >

Answer 1

	Year 2 Total	Year 3 Total
a) Total Expected cash collection	$ 2,025,000.00	$ 1,225,000.00
b) Total required production units	308,500	248,000
c) Total cost of Raw material	$ 1,251,400.00	$      956,200.00
d) Total Expected cash disbursement	$ 1,221,380.00	$      839,480.00
1) Budgeted Cash Collection
		Year 2 Quarter				Year 3 Quarter		Year 2 Total	Year 3 Total
		1	2	3	4	1	2
Budgeted Sales unit	a	50,000	70,000	115,000	60,000	85,000	100,000	295,000	185,000
Sale price	b	$                   7.00	$               7.00	$               7.00	$               7.00	$               7.00	$               7.00
Budgeted Sales Revenue	c=a*b	$      350,000.00	$ 490,000.00	$ 805,000.00	$ 420,000.00	$ 595,000.00	$ 700,000.00	$ 2,065,000.00	$ 1,295,000.00
Cash receipts from customer				.
Opening account receivable	d	$        65,000.00
75% Cash receipt from sale of Current Quarter	e=c*75%	$      262,500.00	$ 367,500.00	$ 603,750.00	$ 315,000.00	$ 446,250.00	$ 525,000.00
25% Cash receipt from sale of Previous Quarter	f=c*25%	$                       -	$    87,500.00	$ 122,500.00	$ 201,250.00	$ 105,000.00	$ 148,750.00
Total Expected Cash collection	g=d+e+f	$      327,500.00	$ 455,000.00	$ 726,250.00	$ 516,250.00	$ 551,250.00	$ 673,750.00	$ 2,025,000.00	$ 1,225,000.00
2) Production and Direct Material purchase Budget
		Year 2 Quarter				Year 3 Quarter		Year 2 Total	Year 3 Total
		1	2	3	4	1	2
Budgeted Sales unit	a	50,000	70,000	115,000	60,000	85,000	100,000	295,000.00	185,000.00
Add: Closing stock of FG= 30%*Next Quarter's sales	b=a*30%	21,000	34,500	18,000	25,500	30,000	88,500
Less: Opening stock of FG	c	12,000	21,000	34,500	18,000	25,500	30,000
Budgeted Production units	d=a+b-c	59,000	83,500	98,500	67,500	89,500	158,500	308,500.00	248,000.00
Direct material consumption(pounds)	e=d*5 pound	295,000	417,500	492,500	337,500	447,500	792,500
Closing stock= 30%*Next Quarter's production	f=e*10%	41,750	49,250	33,750	44,750	79,250	-
Opening stock	g=f	23,000	41,750	49,250	33,750	44,750	79,250
Budgeted Purchase unit	h=e+f-g	313,750	425,000	477,000	348,500	482,000	713,250	1,564,250.00	1,195,250.00
Budgeted Cost of Direct material purchase@$0.8 per pound	i=h*$0.8	$      251,000.00	$ 340,000.00	$ 381,600.00	$ 278,800.00	$ 385,600.00	$ 570,600.00	$ 1,251,400.00	$      956,200.00
3) Budgeted Cash payment
		Year 2 Quarter				Year 3 Quarter		Year 2 Total	Year 3 Total
		1	2	3	4	1	2
Budgeted Cost of Direct material purchase	a	$      251,000.00	$ 340,000.00	$ 381,600.00	$ 278,800.00	$ 385,600.00	$ 570,600.00	$ 1,251,400.00	$      956,200.00
Cash payment
Opening account payable	b	$        81,500.00
60% Payment for material purchase of current Quarter	c=a*60%	$      150,600.00	$ 204,000.00	$ 228,960.00	$ 167,280.00	$ 231,360.00	$ 342,360.00	$      750,840.00	$      573,720.00
40% Payment for material purchase of Previous Quarter	D=a*40%	$                       -	$ 100,400.00	$ 136,000.00	$ 152,640.00	$ 111,520.00	$ 154,240.00	$      389,040.00	$      265,760.00
Total expected cash payment	e=b+c+d	$      232,100.00	$ 304,400.00	$ 364,960.00	$ 319,920.00	$ 342,880.00	$ 496,600.00	$ 1,221,380.00	$      839,480.00