Question

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	32,000	October	22,000
August	74,000	November	8,500
September	43,000	December	9,000

The selling price of the beach umbrellas is $13 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 $299,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	76,600	feet
September 30	?	feet

5. Gilden costs $0.80 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 $50,120.

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

Solution Requirement 1: Estimated sales, by month and in total, for the third quarter MILO COMPANY Sales Budget July September August Quarter 149000 Estimated sales (in units) 32000 74000 43000 Selling price per unit Budgeted sales $13 $13 $13 $13 $416,000 $962,000 $559,000 $1,937,000 Requirement 2: Expected cash collections, by month and in total, for the third quarter MILO COMPANY Schedule of Expected Cash Collections July September August Quarter Accounts receivable, June 30 July sales August sales September sales Total Cash collection $194,350 $194,350 $395,200 $913,900 $167,700 $1,671,150 $0 $124,800 $270,400 $0 $288,600 $625,300 $167,700 $793,000 $559,000 $319,150 Requirement 3: Estimated guantity of beach umbrellas that need to be produced in July, August, September, and October MILO COMPANY Production Budget for July October July September October August Estimated sales in units Add: Desired ending inventory Total needs Less: Likely beginning inventory Estimated production quantity 22000 1275 32000 43000 74000 11100 6450 3300 80450 46300 43100 23275 3300 11100 4800 6450 38300 19975 69350 39850

Requirement 4: Quantity of Gilden (in feet) that needs to be purchased by month and in total, for the third quarter MILO COMPANY Materials Budget Month July September Quarter August Estimated production quantity (in units) Feet of Gilden required per unit Expected Raw material usage (in feet) Add: Desired Ending inventory (in feet) Total needs (feet) Less: Likely beginning inventory (in feet) Estimated Raw material purchase (in feet) 147500 38300 69350 39850 4. 4 4. 4. 590000 153200 277400 159400 39950 138700 79700 39950 291900 629950 357100 199350 138700 76600 79700 76600 553350 215300 218400 119650 Requirement 5: cost of the raw material (Gilden) purchases by month and in total, for the third quarter MILO COMPANY July September Quarter August Estimated Raw material purchase (in feet) Cost of Raw material per foot Cost of Raw material purchases (dollars) 218400 553350 215300 119650 $0.80 $442,680 $0.80 $0.80 $0.80 $172,240 $174,720 $95,720 Requirement 6: Expected cash disbursements for raw material (Gilden) purchases, by month and in total, for the third quarter MILO COMPANY Schedule of Expected Cash Payments July $50,120 September August Quarter $50,120 $172,240 $174,720 $47,860 $444,940 Accounts Payable, June 30 July purchases August purchases September purchases Total cash payments $0 $86,120 $86,120 $0 $87,360 $87,360 $0 $47,860 $173,480 $136,240 $135,220

To see the formulas, please see the below images:

A C F Solution 2 Requirement 1: Estimated sales, by month and in total, for the third quarter 4 MILO COMPANY Sales Budget 7 July 32000 13 - C8* C9 August 74000 13 -D8*D9 September Quarter SUM(C8:E8) 13 - F8 F9 Estimated sales (in units) Selling price per unit Budgeted sales 11 8 43000 13 10 -E8 E9 Requirement 2: Expected cash collections, by month and in total, for the third quarter 12 13 14 MILO COMPANY 15 Schedule of Expected Cash Collections 16 July - 299000 65 % -C10 30 % Quarter |-SUM(C17:E17) |=SUM(C18:E18) -SUM(C19: E19) -SUM(C20:E20) -SUMIC17:C20) -SUM(D17:D20) -SUM(E17:E20) -SUM(F17:F20) August 0 September 0 17 Accounts receivable, June 30 18 July sales August sales September sales Total Cash collection -C10 65 % =D10 30 % 0 =D10 65 % 19 0 20 - E10 30 % 0 0 21 22 Requirement 3: Estimated quantity of beach umbrellas that need to be produced in July, August, September, and 23 October 24 25 MILO COMPANY 26 Production Budget for July -October 27 July -C8 -D28 15 % SUMIC28:C29) C28 15 % -C30-C31 August -D8 -E28 15 % SUMD28:D29) =D28 15 % -D30-D31 September -E8 =F28 15 % -SUM(E28:E29) -E28 15 % - E30 - E31 October Estimated sales in units Add: Desired ending inventory Total needs Less: Likely beginning inventory Estimated production quantity 33 28 22000 -8500 15 % -SUM(F28:F29) |=F28 15 % -F30-F31 29 30 31 32

A C D E F 34 Requirement 4: Quantity of Gilden lin feet that needs to be purchased by month and in total, for the third quarter. 35 36 MILO COMPANY Materials Budget 37 38 Month 39 July C32 August -D32 September Quarter Estimated production quantity (in units) Feet of Gilden required per unit Expected Raw material usage (in feet) Add: Desired Ending inventory (in feet) Total needs (feet) Less: Likely beginning inventory (in feet) Estimated Raw material purchase (in feet) |-SUM(C40: E40) 40 -E32 41 C40 C41 =D42 50 % -C42+C43 -C42 50 % C44-C45 =D40 D41 -E42 50 % -D42+D43 D42 50 % -D44-D45 -E40*E41 =F32 4*50 % -E42+E43 |=E42 50 % - E44- E45 =F40*F41 42 43 -E43 -F42+F43 C45 44 45 46 =F44-F45 47 Requirement 5: cost of the raw material (Gildenl purchases by month and in total, for the third quarter 48 49 50 MILO COMPANY 51 July C46 August September Quarter Estimated Raw material purchase (in feet) Cost of Raw material per foot Cost of Raw material purchases(dollars) -D46 0.8 -D46*D53 52 -E46 -F46 53 0.8 -E46 E53 0.8 -F46*F53 0.8 -C46*C53 54 55 Requirement 6: Expected cash disbursements for raw material Gilden) purchases, by month and in total, for the third quarter. 56 57 58 MILO COMPANY Schedule of Expected Cash Payments 59 60 July 50120 |-C54/2 August 0 September Quarter 61 Accounts Payable, June 30 July p August purchases 64 -SUM(C61 : E61 ) SUM(C62:E62) |=SUM(C63:E63) -SUM(C64 E64 ) -SUME61:E64) -SUM(F61:F64) 0 62 -C62 ses |D54/2 63 -D63 0 September purchases Total cash payments -E54/2 0 =SUMIC61:C64) -SUMID61:D64) 65 66