In: Accounting
Milo Company manufactures beach umbrellas. The company is now preparing detailed budgets for the third quarter and has assembled the following information to assist in the budget preparation: |
a. | The Marketing Department has estimated sales as follows for the remainder of the year (in units): |
July | 46,000 | October | 36,000 |
August | 86,000 | November | 26,000 |
September | 66,000 | December | 26,000 |
The selling price of the beach umbrellas is $12 per unit. Sales in June were 41,000 units. |
b. | All sales are on account. On the basis of past experience, sales are collected in the following pattern: |
30% | in the month of sale |
65% | in the month following sale |
5% | uncollectible |
c. |
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. |
d. | Each beach umbrella requires 4 metres of Gilden, a material that is sometimes hard to get. Therefore, the company requires that the inventory of Gilden on hand at the end of each month 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 as follows: |
June 30 | 104,000 | metres |
September 30 | ? | metres |
e. |
Gilden costs $0.80 per metre. One-half of a month’s purchases of Gilden are paid for in the month of purchase; the remainder are paid for in the following month. The accounts payable on July 1 for purchases of Gilden during June will be $92,000. |
Required: |
1. |
Prepare a sales budget, by month and in total, for the third quarter. Also, prepare a schedule of expected cash collections, by month and in total, for the third quarter. |
2. |
Prepare a production budget for each of the months July to October. |
3. |
Prepare a materials purchases budget for Gilden, by month and in total, for the third quarter. Also, prepare a schedule of expected cash payments for Gilden, by month and in total, for the third quarter. |
Solution 1:
Milo Company - Computation of Estimated Sales for 3rd Quarter | ||||
Particulars | July | August | September | Total |
Sales in Units | 46000 | 86000 | 66000 | 198000 |
Selling Price per unit | $12.00 | $12.00 | $12.00 | $12.00 |
Estimated Sales | $552,000.00 | $1,032,000.00 | $792,000.00 | $2,376,000.00 |
Milo Company - Computation of Estimated Cash Collection for 3rd Quarter | ||||
Particulars | July | August | September | Total |
Accounts receivables, June 30 | $319,800.00 | $319,800.00 | ||
July Sales | $165,600.00 | $358,800.00 | $524,400.00 | |
August Sales | $309,600.00 | $670,800.00 | $980,400.00 | |
September sales | $237,600.00 | $237,600.00 | ||
Total Cash collection | $485,400.00 | $668,400.00 | $908,400.00 | $2,062,200.00 |
Solution 2:
Milo Company - Computation of Estimated Production of Beach Umbrellas | ||||
Particulars | July | August | September | October |
Sales in Units | 46000 | 86000 | 66000 | 36000 |
Required ending Inventory (15% of Following month Sale) | 12900 | 9900 | 5400 | 3900 |
Total Needs | 58900 | 95900 | 71400 | 39900 |
Opening Inventory | 6900 | 12900 | 9900 | 5400 |
Estimated Production (Sales + Ending Inventory - Opening Inventory) | 52000 | 83000 | 61500 | 34500 |
Solution 3:
Milo Company - Computation of quantity of Gilden to be purchased in 3rd quarter | ||||
Particulars | July | August | September | Total |
Estimated Production | 52000 | 83000 | 61500 | 196500 |
Required qty of Gilder per unit (In meters) | 4 | 4 | 4 | 4 |
Total requirement of Gilden | 208000 | 332000 | 246000 | 786000 |
Required ending Inventory (50% of Following month Production Needs) | 166000 | 123000 | 69000 | 69000 |
Total units of raw materials needed | 374000 | 455000 | 315000 | 855000 |
Opening Inventory of Gilden | 104000 | 166000 | 123000 | 104000 |
Quantity of Gilden to be Purchased | 270000 | 289000 | 192000 | 751000 |
Milo Company - Computation of cost of Gilden to be purchased in 3rd quarter | ||||
Particulars | July | August | September | Total |
Quantity of Gilden to be Purchased | 270000 | 289000 | 192000 | 751000 |
Cost per unit | $0.80 | $0.80 | $0.80 | $0.80 |
Cost of Raw Material Purchase | $216,000.00 | $231,200.00 | $153,600.00 | $600,800.00 |
Milo Company - Computation of expected cash disbursement | ||||
Particulars | July | August | September | Total |
Accounts Payable, June 30 | $92,000.00 | $92,000.00 | ||
July Purchases | $108,000.00 | $108,000.00 | $216,000.00 | |
August Purchases | $115,600.00 | $115,600.00 | $231,200.00 | |
September Purchases | $76,800.00 | $76,800.00 | ||
Total Cash disbursements | $200,000.00 | $223,600.00 | $192,400.00 | $616,000.00 |