(a) At the end of December the management accountant has in front of him the company's budgeted profit and loss account figures for each of the months from the following January to June. The July and August budgets follow the same pattern as that for June. The budgets can be summarised as follows :

The budgeted factory fixed overhead for the year is £140,000 and this is absorbed into the factory cost of production at the rate of £3.50 per unit.

£32,000 of this overhead is depreciation of machinery and buildings, the remainder arises evenly throughout the year and is paid as it arises.
Month end balances representing budgeted under or over absorbed overhead are carried forward and for the year as a whole the budgeted overhead is fully absorbed by the budgeted production. One quarter of the variable factory cost of production is direct material cost and the balance is predominantly manufacturing labour and similar services that are paid for as they arise. The planned direct material purchases of one month are determined by the planned production of the subsequent month.
Suppliers are normally paid 30 days after the receipt of goods but it is possible to delay payment in some cases for another month. This course of action is resorted to only sparingly as the company does not wish to spoil its credit standing. 20% of the purchases attract a 5% discount for prompt (i.e., immediate) settlement. 75% of customers settle their accounts in the month following the sale, 20% take another month 's credit and the remaining 5% pay in the third month following the sale.
The company normally pays an interim dividend at the end of June and it is anticipated that the net payment to be made in the forthcoming June will be £30,000.
You are required to prepare the company's Cash Budget for the months of April. May and June assuming that the cash balance at the start of April is estimated at £5,000 and the company's policy is to maintain a month end cash balance no lower than £4,000 and no higher than £10,000.
Surplus cash can be invested on a short-term basis at 10%p.a. interest .
No recognition has been given in the budgeted profit and loss accounts to any discount receivable or short term loan interest.

(b) Discuss and describe the possible use of a spread sheet computer package in the development of an organisation's cash budgets.

Transcribed Image Text:

Jan. Feb. Mar. Apr. May Jun. Sales quantity 2,500 2,500 3,500 5,000 7,500 4,000 Production quantity 3,000 3,500 4,500 5,000 5,000 4,000 Sales revenue 30,000 30,000 42,000 60,000 90,000 48,000 Factory Cost of production 28,500 33,250 42,750 47,500 47,500 38,000 Stock adjustment (4,750) (9,500) (9,500) Factory 23,750 Cost of goods sold 23,750 23,750 33,250 47,500 71,250 38,000 Selling and distribution costs 2,250 Admin. costs Profit 2,250 2,350 2,500 2,750 2,400 3,000 3,000 3,000 3,000 3,000 3,000 1,000 1,000 3,400 7,000 13,000 4,600 30,000 30,000 42,000 60,000 90,000 48,000