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:

  

Data	Year 2 Quarter				Year 3 Quarter
	1	2	3	4	1	2
Budgeted unit sales	50,000	65,000	105,000	60,000	85,000	95,000
Selling price per unit	$7	per unit

   

1	Chapter 7: Applying Excel
2
3	Data	Year 2 Quarter				Year 3 Quarter
4		1	2	3	4	1	2
5	Budgeted unit sales	50,000	65,000	105,000	60,000	85,000	95,000
6
7	• Selling price per unit	$8	per unit
8	• Accounts receivable, beginning balance	$65,000
9	• Sales collected in the quarter sales are made	75%
10	• Sales collected in the quarter after sales are made	25%
11	• Desired ending finished goods inventory is	30%	of the budgeted unit sales of the next quarter
12	• Finished goods inventory, beginning	12,000	units
13	• Raw materials required to produce one unit	5	pounds
14	• Desired ending inventory of raw materials is	10%	of the next quarter's production needs
15	• Raw materials inventory, beginning	23,000	pounds
16	• Raw material costs	$0.80	per pound
17	• Raw materials purchases are paid	60%	in the quarter the purchases are made
18	and	40%	in the quarter following purchase
19	• Accounts payable for raw materials, beginning balance	$81,500
20

   

a.	What are the total expected cash collections for the year under this revised budget?
	2,185,000selected answer incorrect ( I got this wrong)

      

b.	What is the total required production for the year under this revised budget?

      

c.	What is the total cost of raw materials to be purchased for the year under this revised budget?

251,600selected answer incorrect (i got this wrong)      

d.	What are the total expected cash disbursements for raw materials for the year under this revised budget?

	Total expected cash disbursements for raw materials for the year $308,220selected answer incorrect

(I got this wrong)

      

e.	After seeing this revised budget, the production manager cautioned that due to the current production constraint, a complex milling machine, the plant can produce no more than 80,000 units in any one quarter. Is this a potential problem?
	Yes No

Answer 1

a	Particulars	Q1	Q2	Q3	Q4
	Budgeted Units	50000	65000	105000	60000
	Budgeted Sales	350000	455000	735000	420000
	Collections:
	In the Quarter(75%)	262500	341250	551250	315000
	After Sales Quarter(25%)	65000	87500	113750	183750
	Total	327500	428750	665000	498750
	Total Collections	1920000
						Year 3
b	Particulars	Q1	Q2	Q3	Q4	Q1
	Quarter's Sales	50000	65000	105000	60000	85000
	Add: Closing Stock Desired	19500	31500	18000	25500	28500
	Total Requirement	69500	96500	123000	85500	113500
	Less: Opening Stock	12000	19500	31500	18000	25500
	Production	57500	77000	91500	67500	88000
	Total Production	293500
						Year 3
c	Particulars	Q1	Q2	Q3	Q4	Q1
	Production Requirement for Quarter	287500	385000	457500	337500	440000	(Production * 5)
	Add: Closing Stock Desired	38500	45750	33750	44000
	Total Requirement	326000	430750	491250	381500
	Less: Opening Stock	23000	38500	45750	33750
	Net Purchases	303000	392250	445500	347750
	Total Purchases	1488500
d	Particulars	Q1	Q2	Q3	Q4
	Raw Material Purchase Cost	242400	313800	356400	278200		(Purchase * 0.8)
	Payments:
	In the Quarter(60%)	145440	188280	213840	166920
	After Purchase Quarter(40%)	81500	96960	125520	142560
	Net Purchases	226940	285240	339360	309480
	Total Purchases	1161020
e	Particulars	Q1	Q2	Q3	Q4
	Production	57500	77000	91500	67500
	Max Capacity	80000	80000	80000	80000
	Production Possible	Yes	Yes	No	Yes
	Yes it is a problem for Q3