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	45,000	70,000	105,000	75,000		85,000	90,000
Selling price per unit	$7
A B C D E F G 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 45,000 70,000 105,000 75,000 85,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?

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?

No

Yes

Thank you!

Answer 1

	Year 2
Expected cash collection	1	2	3	4	Total
Budgeted unit sales	45000	70000	105000	75000	295000
Selling price	$7	$7	$7	$7	$7
Budgeted sales value	$315,000	$490,000	$735,000	$525,000	$2,065,000
Cash collection
From Accounts receivable	$65,000				$65,000
From Qtr 1 sales (315000*75% and 25%)	$236,250	$78,750			$315,000
For Qtr 2 sales (490000*75% and 25%)		$367,500	$122,500		$490,000
For Qtr 3 sales (735000*75% and 25%)			$551,250	$183,750	$735,000
For Qtr 4 sales				$393,750	$393,750
Total Cash collection	$301,250	$446,250	$673,750	$577,500	$1,998,750
ans a
Expected cash collection for the year	$1,998,750
b) Total required production for the year	308500
Production Budget	Year 2				Total	Year 3
	1	2	3	4		1	2
expected sales in units	45000	70000	105000	75000	295000	85000	90000
Add Desired Ending Inventory (30%*next month sales)	21000	31500	22500	25500	25500	27000
Total Needs	66000	101500	127500	100500	320500	112000
Less Beginning Inventory	12000	21000	31500	22500	12000	25500
Required Purcahses in units	54000	80500	96000	78000	308500	86500
ans c						Year 3
	1	2	3	4	Total	1
Budgeted cost of raw material purchases
Budgeted production in units	54000	80500	96000	78000	308500	86500
No. of pounds required	5	5	5	5	5	5
Total required	270000	402500	480000	390000	1542500	432500
Add Desired Ending Inventory (10%*next month production)	40250	48000	39000	43250	43250
Total Needs	310250	450500	519000	433250	1585750
Less Beginning Inventory	23000	40250	48000	39000	23000
Budgeted raw material purchases	287250	410250	471000	394250	1562750
Raw material cost per pound	$0.80	$0.80	$0.80	$0.80	$0.80
Budgeted cost of raw material purchases	$229,800	$328,200	$376,800	$315,400	$1,250,200
Total Budgeted cost of raw material purchases	$1,250,200
ans d
total expected cash disbursements for raw materials	$1,205,540
	1	2	3	4	Total
Accounts payable	$81,500				$81,500
1st Qtr Purchases (229800*60%, 40%)	$137,880.0	$91,920.0			$229,800
2nd Qtr Purchases		$196,920.0	$131,280.0		$328,200
3rd qtr purchases			$226,080.0	$150,720.0	$376,800
4th Qtr purchases				$189,240.0	$189,240
Total	$219,380	$288,840	$357,360	$339,960	$1,205,540
ans 5
Yes it is a potential problem because there is need of production is more than 80000 required