AMC Production Ltd has a ten-month production and sales plan as follows:

The planned expenses for the period are as follows: Advertising - July $2,000 and September $2,500. Selling price will be a constant $30 per unit. 50% of sales will be made on one months credit and 50% will be settled in the month of sale.

Raw materials will be held for one month before being used. Raw materials will be purchased in one month and paid for in the next. Cost of raw materials is $9 per unit. Other direct production costs will be $7 per unit paid within the month. Production overheads for April, May and June will be $2,400 per month. Production overheads for July to October will be $1,900 per month. Production overheads from November to December will be $2,700 per month. All the production overheads include a constant $700 per month for depreciation. Overheads are planned to be paid 50% in the month of production and 50% in the following month. A new item of plant cost $12,000 will be purchased and delivered in August. Payment for this will be $3,000 per month over September, October, November and December. Raw materials in stock at 1 April will be 600 units. Finished goods in stock at 1 April will be 300 units. Cash at bank on 1 April will be $11,500.

Using an Excel spreadsheet, draw up the following budgets for the nine month period to 31 December:

a) Raw materials budget showing quantities and cost. b) Cash flow forecast. c) A profit and loss account.

Note: Ten months data is needed to produce the information required because January sales units are produced in December.

Sales Month April May June July August September October November December January Finished Goods Produced Units 600 600 500 750 800 700 800 700 750 400 Units 300 400 600 800 700 700 800 750 500 500