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	1	2	3	4		1	2
Budgeted unit sales	50,000	65,000	115,000	75,000		90,000	100,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 115,000 75,000 90,000 100,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?

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 90,000 units in any one quarter. Is this a potential problem?

• Yes

• No

Answer 1

1. Summary of cash Receipts from Customers
Description	1	2	3	4	Total
Budgeted Sales in units	50,000	65,000	1,15,000	75,000
Price p.u	$                    7	$                    7	$                    7	$                    7
Budgeted Sales	$      3,50,000	$      4,55,000	$      8,05,000	$      5,25,000	$       21,35,000
Sales Collected in same quarter (75%)	$      2,62,500	$      3,41,250	$      6,03,750	$      3,93,750	$       16,01,250
Collected in next quarter (25%)		$          87,500	$      1,13,750	$      2,01,250	$         4,02,500
Opening accounts receivable	$          65,000				$            65,000
Total Cash collection	$      3,27,500	$      4,28,750	$      7,17,500	$      5,95,000	$      20,68,750
2. Merchandise Production Budget
Description	1	2	3	4	Total
Projected sales (in units)	50,000	65,000	1,15,000	75,000	3,05,000
Desired Ending Inventory (30% of sales of next qtr)	19500	34500	22500	27000	1,03,500
Total units required	69,500	99,500	1,37,500	1,02,000	4,08,500
Opening Inventory	12000	19500	34500	22500	88,500
Total Production	57,500	80,000	1,03,000	79,500	3,20,000
3. Raw material budget
Description	1	2	3	4	Total
Units to be produced	57,500	80,000	1,03,000	79,500	3,20,000
Raw Material required per unit (in pound)	5	5	5	5	5
Total material required(in lbs)	2,87,500	4,00,000	5,15,000	3,97,500	16,00,000
Add: Desired ending raw materials ( 10%of next qtr)	8,000	10,300	7,950	9,300	35,550
Total material required	2,95,500	4,10,300	5,22,950	4,06,800	16,35,550
Less: opening inventory	23,000	8,000	10,300	7,950	49,250
Total Material to be purchased (in lbs)	2,72,500	4,02,300	5,12,650	3,98,850	15,86,300
Cost per unit	0.80	0.80	0.80	0.80	0.80
Total Cost of Material purchased	$      2,18,000	$      3,21,840	$      4,10,120	$      3,19,080	$      12,69,040
4. Summary of Cash payment to suppliers
Description	1	2	3	4	Total
Total Cost of Material purchased	$      2,18,000	$      3,21,840	$      4,10,120	$      3,19,080	$       12,69,040
Cash paid (60% in same qtr)	$      1,30,800	$      1,93,104	$      2,46,072	$      1,91,448	$         7,61,424
Cash paid (40% in next qtr)		$          87,200	$      1,28,736	$      1,64,048	$         3,79,984
Opening payables	$          81,500				$            81,500
Total Cash disbursements	$      2,12,300	$      2,80,304	$      3,74,808	$      3,55,496	$      12,22,908