Question

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	1	2	3	4		1	2
Budgeted unit sales	50,000	65,000	120,000	65,000		80,000	90,000
Selling price per unit	$7

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Chapter 8: Applying Excel Data Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales 50,000 65,000 120,000 65,000 80,000 90,000 • Selling price per unit $7 per unit • 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

a. What are the total expected cash collections for the year under this revised budget?

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?

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

Answer 1

Solutions:

E F G Particulars Budgeted unit sales selling price per unit Total sales Schedule of expected cash collection | Quarter 1 | Quarter 2 Quarter 3 Quarter 4 50000 65000 120000 65000 $7.00 $7.00 $7.00 $350,000.00 $455,000.00 $840,000.00 $455,000.00 Year 3 Quarer 1 80000 $7.00 $560,000.00 Year 3 Quarer 2 90000 $7.00 $630,000.00 $7.00) 15 Schedule of expected cash collection Particulars | Quarter 1 Quarter 2 Quarter 3 Quarter 4 Accounts receivables beginning $65,000.00 Cash received for Q1 Sale (Units $7*% collection) $262,500.00 $87,500.00 Cash received for Q2 Sale $341,250.00 $113,750.00 Cash received for Q3 Sale $630,000.00 $ 210,000.00 Cash received for Q4 Sale $341,250.00 Budgeted Cash Collection $327,500.00 $428,750.00 $743,750.00 $551,250.00 Year $65,000.00 $350,000.00 $455,000.00 $840,000.00 $341,250.00 $2,051,250.00 17 19 20 23 Particulars Budgeted sales unit Add: ending inventory (30% of next quarter sales) Less: Beginning inventory Production Budget Quarter 1 Quarter 2 Quarter 3 50000 65000 120000 19500 36000 19500 12000 19500 36000 24 Quarter 4 65000 24000 19500 Year Quarter 1, next year Quarter 2, next year 300000 80000 90000 24000 27000 12000 24000 25 26 27 Estimated production unit 57500 81500 103500 69500 312000 83000 28 29 et703 ) Sheet704 Sheet157 (18) Sheet225 (2) Sheet393 (3) Sheet708 Sheet253 (2) Sheet710

H 29 30 31 32 33 Year Quarter 1, next year 312000 83000 51 34 35 36 Budgeted Cost of raw material purchases Particulars | Quarter 1 | Quarter 2 Quarter 3 Quarter 4 Budgeted Production units 57500 81500 103500 69500 Raw material per unit (In Pounds) 555 Total requirement of raw materials 287500 407500 517500 347500 |Add: Desired ending inventory (10% of next quarter production needs) 40750 51750 34750 41500 Less: Beginning inventory 23000 40750 51750 34750 1560000 415000 41500 23000 37 38 3052504 18500 418500 Budgeted purchase units of raw material (In Pounds Raw material cost per pound 40 305250 $0.80 500500 $0.80 354250 $0.80 1578500 $0.80 41 Budgeted cost of purchases $244,200.00 $334,800.00 $400,400.00 $283,400.00 42 43 $1,262,800.00 44 45 46 48 Schedule of expected cash disbursement for merchandise purchases Particulars Quarter 1 Quarter 2 Quarter 3 Quarter 4 Accounts Payable, Beginning $81,500.00 Q1 Purchases $146,520.00 $97,680.00 02 Purchases $200,880.00 $133,920.00 Q3 Purchases $240,240.00 $160,160.00 Q4 Purchases $170,040.00 Total payments $228,020.00 $298,560.00 $374,160.00 $330,200.00 49 Year $81,500.00 $244,200.00 $334,800.00 $400,400.00 $170,040.00 $1,230,940.00 51 52 53 54 55 56 et703 ) Sheet704 Sheet157 (18) Sheet225 (2) Sheet393 (3) Sheet708 Sheet253 (2) Sheet710