Required: Prepare using EXCEL:

a. Prepare a sales budget, by month and in total, for quarter three. (3 marks)

b. Prepare a schedule of expected cash collections, by month and in total, for quarterthree.(15 marks)

c. Prepare a production budget, by month and in total, for quarter three (10 marks)

d. Prepare a direct materials budget, by month and in total, for quarter three.(12 marks)

e. Prepare a schedule of expected direct material cash disbursements, by month and in total, for quarter three.(15 marks)

Total of Part I(55 marks)

The data below pertains to the third quarter of Al AinCompany which manufactures toys.

Below is the estimated sales (in units):

July	30,000
August	60,000
September	80,000
October	40,000
November	10,000
December	20,000

b. The selling price of the toys is $10 per unit.

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

Month of sale	25%
Month following sale	70%
Uncollectible	5%

d. Sales for June totaled $400,000.

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

f. Each toy requires 3 pounds of raw materials.

g. The company requires that the ending inventory of raw materials be equal to 10% of the following month's production needs.

h. The raw material costs $1.50 per pound.

i. 40% of a month's purchases of raw materials is paid for in the month of purchase; the remainder is paid for in the following month.

j. The accounts payable on June 30 will be $80,000.