Question

Problem 8-30 Integration of the Sales, Production, and Direct Materials Budgets [LO8-2, LO8-3, LO8-4]

Milo Company manufactures beach umbrellas. The company is preparing detailed budgets for the third quarter and has assembled the following information to assist in the budget preparation:

1. The Marketing Department has estimated sales as follows for the remainder of the year (in units):

July	36,000	October	26,000
August	82,000	November	12,500
September	51,000	December	13,000

The selling price of the beach umbrellas is $15 per unit.

2. All sales are on account. Based on past experience, sales are collected in the following pattern:

30%	in the month of sale
65%	in the month following sale
5%	uncollectible

Sales for June totaled $465,000.

3. The company maintains finished goods inventories equal to 15% of the following month’s sales. This requirement will be met at the end of June.

4. Each beach umbrella requires 4 feet of Gilden, a material that is sometimes hard to acquire. Therefore, the company requires that the ending inventory of Gilden be equal to 50% of the following month’s production needs. The inventory of Gilden on hand at the beginning and end of the quarter will be:

June 30	85,800	feet
September 30	?	feet

5. Gilden costs $0.60 per foot. One-half of a month’s purchases of Gilden is paid for in the month of purchase; the remainder is paid for in the following month. The accounts payable on July 1 for purchases of Gilden during June will be $44,790.

Required:

1. Calculate the estimated sales, by month and in total, for the third quarter.

2. Calculate the expected cash collections, by month and in total, for the third quarter.

3. Calculate the estimated quantity of beach umbrellas that need to be produced in July, August, September, and October.

4. Calculate the quantity of Gilden (in feet) that needs to be purchased by month and in total, for the third quarter.

5. Calculate the cost of the raw material (Gilden) purchases by month and in total, for the third quarter.

6. Calculate the expected cash disbursements for raw material (Gilden) purchases, by month and in total, for the third quarter.

Answer 1

1. Estimated sales for third quarter (by month and in total):

	July	August	September	Total
a. Sales (units)	36,000	82000	51000	1,69,000
b. Selling price per unit ($)	15	15	15	15
c. Sales ($) (a * b)	5,40,000	12,30,000	7,65,000	25,35,000

2. Expected cash collections for third quarter (by month and in total):

Particulars	July ($)	August ($)	September ($)	Total ($)
a. Sales	5,40,000	12,30,000	7,65,000
b. Cash sales (30% of month sales)	1,62,000	3,69,000	2,29,500	7,60,500
	($540,000 * 30%)	($12,30,000 * 30%)	($765,000 * 30%)
c. Credit sales (70% of sales)	3,78,000	8,61,000	5,35,500
	($540,000 * 70%)	($12,30,000 * 70%)	($765,000 * 70%)
d. June month collections	3,02,250	-	-	3,02,250
	($465,000 * 65%)
e. July month collections	-	3,51,000	-	3,51,000
		($540,000 * 65%)
f. August month collections	-	-	7,99,500	7,99,500
			($12,30,000 * 65%)
g. Total (b+d+e+f)	4,64,250	7,20,000	10,29,000	22,13,250

3. Production budget for July, August, September and October:

Particulars	July	August	September	October	November
a. Sales (units)	36,000	82,000	51,000	26,000	12,500
b. Add: Ending finished good inventory (units)	12,300	7,650	3,900	1,875
(15% of following month's sale)	(82,000 * 15%)	(51,000 * 15%)	(26,000 * 15%)	(12,500 * 15%)
c. Less: Beginning finished good inventory (units)	5,400	12,300	7,650	3,900
	(36,000 *15%)
d. Production (units) (a+b-c)	42,900	77,350	47,250	23,975

4. Raw material (Gilden) budget for the third quarter (by month and in total):

Particulars	July	August	September	October
a. Production (units)	42,900	77,350	47,250	23,975
b. Raw material required for beach umbrella (feet)	1,71,600	3,09,400	1,89,000	95,900
(4 feet of Gilden per umbrella)	(42,900 * 4)	(77,350 * 4)	(47,250 * 4)	(23,975 * 4)
c. Add: Ending raw material inventory (feet)	1,54,700	94,500	47,950
(50% of next month's production needs)	(309,400 * 50%)	(189,000 * 50%)	(95,900 * 50%)
d. Less: Beginning raw material inventory (feet)	85,800	1,54,700	94,500
e. Estimated qauntity of Gilden (feet) (b+c-d)	2,40,500	2,49,200	1,42,450
f. Total Gilden (feet) required for third quarter			6,32,150

5. Raw material purchase budget for third quarter (by month and in total):

Particulars	July	August	September	Total
a. Raw material required (feet)	2,40,500	2,49,200	1,42,450
b. Cost of Gilden per foot ($)	0.6	0.6	0.6
c. Total cost of Gilden ($) (a * b)	1,44,300	1,49,520	85,470	3,79,290

6. Expected cash disbursement for raw material purchase for third quarter (by month and in total):

Particulars	July	August	September	Total
a. Gilden purchase cost ($)	1,44,300	1,49,520	85,470
b. 50% of purchases paid in same month ($)	72,150	74,760	42,735	1,89,645
	($144,300 * 50%)	($149,520 * 50%)	($85,470 * 50%)
c. June month's purchase paid ($)	44,790	-	-	44,790
d. July month's purchase paid ($)	-	72,150	-	72,150
		($144,300 * 50%)
e. August month's purchase paid ($)	-	-	74,760	74,760
			($149,520 * 50%)
f. Total paid ($) (b+c+d+e)	1,16,940	1,46,910	1,17,495	3,81,345