Question 11.E.2: C Ltd makes two products, Alpha and Beta. The following data...
C Ltd makes two products, Alpha and Beta. The following data is relevant for year 3:
Material prices: Material M £2 per unit
Material N £3 per unit
Direct labour is paid £10 per hour.
Production overhead cost is estimated to be £200,000, which includes £25,000 for depreciation of property and equipment. Production overhead cost is absorbed into product costs using a direct labour hour absorption rate.
Each unit of finished product requires
Alpha Beta
Material M 12 units 12 units
Material N 6 units 8 units
Direct labour 7 hours 10 hours
The sales director has forecast that sales of Alpha and Beta will be 5,000 and 1,000 units, respectively, during year 3. The selling prices will be
Alpha £182 per unit
Beta £161 per unit
She estimates that the inventory at 1 January, year 3, will be 100 units of Alpha and 200 units of Beta. At the end of year 3 she requires the inventory level to be 150 units of each product.
The production director estimates that the raw material inventories on 1 January, year 3, will be 3,000 units of material M and 4,000 units of material N. At the end of year 3 the inventories of these raw materials are to be:
M: 4,000 units
N: 2,000 units
The fi nance director advises that the rate of tax to be paid on profi ts during year 3 is likely to be 30 per cent. Selling and administration overhead is budgeted to be £75,000 in year 3, which includes £5,000 for depreciation of equipment.
A quarterly cash-flow forecast has already been completed and is set out below
| 1 | 2 | 3 | 4 | |
| Quarter, year 3 | £ | £ | £ | £ |
| Receipts | 196,000 | 224,000 | 238,000 | 336,000 |
| Payments: | ||||
| Materials | 22,000 | 37,000 | 40,000 | 60,000 |
| Direct wages | 100,000 | 110,500 | 121,000 | 117,000 |
| Overhead | 45,000 | 50,000 | 70,000 | 65,000 |
| Taxation | 5,000 | |||
| Machinery purchase | 120,000 |
The company’s balance sheet at 1 January, year 3, is expected to be as follows:
| £ | £ | £ | ||
| Cost | Depreciation | Net | ||
| Non current assets | ||||
| Land | 50,000 | __ | 50,000 | |
| Buildings and equipment | 400,000 | 75,000 | 325,000 | |
| 450,000 | 75,000 | 375,000 | ||
| Current assets | ||||
| Inventories | ||||
| – raw materials | 20,000 | |||
| – finished goods | 15,000 | |||
| 35,000 | ||||
| Receivables | 25,000 | |||
| Cash at bank | 10,000 | |||
| 70,000 | ||||
| Current liabilities | ||||
| Payables | 9,000 | |||
| Taxation | 5,000 | |||
| 14,000 | ||||
| 56,000 | ||||
| 431,000 | ||||
| Financed by | ||||
| Share capital | 350,000 | |||
| Retained earnings | 81,000 | |||
| 431,000 |
You are required to prepare the company’s budgets for year 3 including a budgeted income statement for the year and a balance sheet at 31 December, year 3.
Note the order in which the budgets are prepared. The sales budget determines production requirements, which in turn determines materials usage, which in turn determines materials purchases and then payments to suppliers. Since the sales budget is prepared first, sales are termed the principal (key) budget factor.
| Sales budget for the year ended 31 December, year 3 | |||
| Alpha | Beta | Total | |
| Sales volume | 5,000 | 1,000 | |
| Selling price | £182 | £161 | |
| Sales revenue | £910,000 | £161,000 | £1,071,000 |
Production budget for the year ended 31 December, year 3
\begin{array}{lcc}&\text{Alpha}&\text{Beta}\\&\text{units}&\text{units}\\\text{Required by sales} &5,000&1,000\\\text{Required closing inventory}&\underline{150}&\underline{150}\\&5,150&1,150\\\text{Less expected opening inventory }&\underline{100}&\underline{200}\\\text{Production required }&\underline{5,050}&\underline{950}\end{array}
Raw materials usage budget for the year ended 31 December, year 3
\begin{array}{lcc}&\text{Materital M}&\text{Material N}\\&\text{units}&\text{units}\\\text{Required by production of Alpha}¹ & 60,600&30,300\\\text{Required by production of Beta}&\underline{11,400}&\underline{7,600}\\\text{Total raw material usage}&\underline{72,000}&\underline{ 37,900}\end{array}
Note 1 : The material usage for Alpha is determined as follows:
Units
Material M: 5,050 × 12 60,600
Material N: 5,050 × 6 30,300
The material requirements for Beta are calculated in the same way.
Raw materials purchases budget for the year ended 31 December, year 3
\begin{array}{lcc}&\text{Materital M}&\text{Material N}&\\&\text{units}&\text{units}&\text{Total}\\\text{Raw materials required by production}&72,000&37,900&\\\text{Required closing inventory }&\underline{4,000}&\underline{2,000}&\\&76,000&39,900&\\\text{Less expected opening inventory }&\underline{3,000}&\underline{4,000}\\\text{Quantity to be purchased}&\underline{ 73,000}&\underline{35,900}&\\\text{Price per unit}&£2&£3&\\\text{Value of purchases}&\underline{£146,000}&\underline{£107,700}&\underline{£253,700}\end{array}
Direct labour budget for the year ended 31 December, year 3
\begin{array}{lccc}&\text{Labour}&\text{Rate}&\text{Labour}\\&\text{hours}&\text{per hour}&\text{cost}\\&&£& £\\ \text{Product Alpha – 5,050 units}&35,350&10&353,500\\\text{Product Beta – 950 units}&\underline{9,500}&10&\underline{95,000}\\&\underline{44,850}&&\underline{448,500}\end{array}
Production cost budget: preliminary workings
Production overhead bsorption rate = \frac{£200,000}{44,850} = £4.459 per labour hour
Overhead absorbed by Alpha = 35,350 hours × £4.459 = £157,626
Overhead absorbed by Beta = 9,500 hours × £4.459 = £42,361
Production cost budget for the year ended 31 December, year 3
\begin{array}{lcc}&\text{Alpha}&\text{Beta}\\&£&£\\\text{Direct materials}&&\\– M² &121,200&22,800\\– N&90,900&22,800\\\text{Direct wages}&353,500&95,000\\\text{Production overhead }&\underline{157,626}&\underline{42,361}\\&\underline{723,226}&\underline{182,961}\\\text{Cost per unit (used for closing inventory valuation)}&\underline{ £143.21}&\underline{£192.59}\end{array}
Note 2 : The direct material cost for Alpha is determined as follows:
Material Usage(units) £
M 60,600 @ £2 121,200
N 30,300 @ £3 90,900
The material cost for Beta is calculated in the same way.
| Quarter | 1 | 2 | 3 | 4 |
| £ | £ | £ | £ | |
| Receipts | 196,000 | 224,000 | 238,000 | 336,000 |
| Payments: | ||||
| Materials | 22,000 | 37,000 | 40,000 | 60,000 |
| Direct wages | 100,000 | 110,500 | 121,000 | 117,000 |
| Overhead | 45,000 | 50,000 | 70,000 | 65,000 |
| Taxation | 5,000 | |||
| Machinery purchase | \underline{\hspace{10 pt}} | \underline{\hspace{10 pt}} | 120,000 | \underline{\hspace{10 pt}} |
| Total payments | 172,000 | 197,500 | 351,000 | 242,000 |
| Net cash inflow/(outflow) | 24,000 | 26,500 | 113,000 | 94,000 |
| Balance b/fwd³ | 10,000 | 34,000 | 60,500 | 52,500 |
| Balance c/fwd | 34,000 | 60,500 | 52,500 | 41,500 |
Note 3: The balance b/fwd in quarter 1 is the cash at bank on the forecast balance sheet for 1 January, year 3
Budgeted income statement for the year ended 31 December, year 3
\begin{array}{lcc}&£&£\\\text{Revenue}&&1,071,000\\\text{Opening inventory of raw materials}^{4}&20,000&\\\text{Purchases of raw materials}&\underline{253,700}&\\&273,700&\\\text{Closing inventory of raw materials}^{5}&\underline{14,000}&\\&259,700&\\<br /> \text{Direct wages}&448,500&\\\text{Production overhead}&\underline{200,000}&\\\text{Production cost of goods completed}&908,200&\\\text{Opening inventory of finished goods}^{4}&\underline{15,000}\\&923,200&\\\text{Closing inventory of finished goods}^{5}&\underline{50,370}&\\\text{Production cost of goods sold}&&\underline{872,830}\\\text{Gross profit}&&198,170\\\text{Selling and administration overhead}&&\underline{75,000}\\\text{Net profit before taxation}&&123,170\\\text{Taxation}&&\underline{36,951}\\&&86,219\\\text{Retained earnings b/f}&&\underline{81,000}\\\text{Retained earnings c/f}&&\underline{167,219}\end{array}Note 4 : The opening inventory figures for raw materials and finished goods are taken from the opening balance sheet.
Note 5 : The closing inventories are calculated as follows:
£
Raw materials:
M: 4,000 × £2 8,000
N: 2,000 × £3 6,000
14,000
Finished goods:
Alpha: 150 × £143.21 21,481.50
Beta: 150 × £192.59 28,888.50
50,370.00
| £ | £ | £ | ||
| Cost | Depreciation | Net | ||
| Non current assets | ||||
| Land | 50,000 | __ | 50,000 | |
| Buildings and equipment | 520,000 | 105,000 | 415,000 | |
| 570,000 | 105,000 | 465,000 | ||
| Current assets | ||||
| Inventories | ||||
| – raw materials | 14,000 | |||
| – finished goods | 50,370 | |||
| 64,370 | ||||
| Receivables | 102,000 | |||
| Cash at bank | 41,500 | |||
| 207,870 | ||||
| Current liabilities | ||||
| Payables | 118,700 | |||
| Taxation | 36,951 | |||
| 155,651 | ||||
| 52,219 | ||||
| 517,219 | ||||
| Financed by | ||||
| Share capital | 350,000 | |||
| Retained earnings | 167,219 | |||
| 517,219 |
\begin{array}{lcc}&£&£\\\text{Note 8 : Closing payables balance}&&\\\text{Opening balance of payables}&&9,000\\\text{Material purchases from budget}&&253,700\\\text{Overhead, excluding depreciation: *}&&\\\text{Production}&& 175,000\\\text{Selling and administration}&&\underline{70,000}\\&&507,700\\\text{Less payments (from cash budget):}&&\\\text{Materials }&159,000&\\\text{Overhead }&\underline{230,000}\\&&\underline{389,000}\\\text{Closing balance of payables}&&\underline{118,700}\end{array}
* The depreciation must be excluded from the overhead because it is not a cash item, i.e. it is not a payment which must be made to suppliers.