PART 1
Case Scenario: A problem involving the design of a spreadsheet model to prepare sales, operational and cash budgets for Blackwood Limited.
Blackwood Limited manufactures and sells two types of products crump and flake. The company is in the process of preparing its budgets for the coming six months period from 1 April 2011 to 30 September 2011. As a member of the management accounting team, you have been presented with the following information relating to the companys budgeting process:
1. Sales forecasts
The sales units and selling prices per unit of the two products for March 2011 are as follows:
Crump Flake
Sales units 80,000 50,000
Selling price per unit � 65.50 -104.00
Sales units of crump are expected to increase by 12 % from 1 April 2011, and the company believes that the increased sales level will be maintained until the end of 2011. However, a decision has been made to continue to sell each crump at the current price of 65.50 per unit.
The company is planning to reduce the selling price of flake by 6% from 1 July 2011 and it expects that the price reduction will result in a 15% increase in the volume of sales of flakes from that month until the end of 2011.
All sales are made on credit terms. Sales customers are expected to pay for 65% of the sales in the month of sale, 32% in the month following the sale and the balance is expected to be uncollectible.
2. Raw materials requirements
Both products require two types of raw materials benilax and luccine with the following unit production and costs specifications:
Crump Flake
Benilax costing �27.80 per kg 0.25 kg 0.80 kg
Luccine costing �60 per kg 0.20 kg 0.30 kg
The supplier of benilax has agreed not to introduce any price increases during the 2011. However, the purchase cost of luccine per kg is expected to increase by 5% from
1 July 2011.
All purchases are made on credit terms. Payment for 60% of the purchases of benilax is expected to be made in the month of purchase, with payment for the remainder expected to be made in the month after purchase. However, the company expects to pay for all the purchases of luccine in the month of purchase, in order for it to take advantage of 7�% prompt settlement discount.
Purchases of benilax in March 2011 are expected to amount to �1,420,000.
3. Labour requirements
Production of crump and flake will require two grades of labour as follows:
Grade Crump Flake
Skilled labour @ �16.00 per hour 1.25 hours 1.5 hours
Semi-skilled labour @ �12.50 per hour 0.8 hour 1.2 hours
The hourly rates of pay for skilled and semi-skilled labour are expected to increase by 4�% and 4% respectively from 1 April 2011.
Salaries and wages are paid for in the month in which they are incurred.
4. Overheads
Production overheads of both products are estimated to cost �762,500 per month from
1 April 2011. The monthly overheads figure includes depreciation of equipment which is charged at the rate of 25% per annum on cost. The existing equipment used for the manufacture of the products has been fully depreciated and the company plans to acquire new equipment on 1 April 2011.
Selling and administrative overheads, which currently amount to �448,000 per month, are expected to increase by 4�% from 1 April 2011. The company pays for all production, selling and administrative overheads in the month in which they are incurred.
5. Stocks of raw materials and finished goods
The following opening stock levels of raw materials and finished goods are expected at
1 April 2011, together with projected monthly closing stock requirements:
Monthly closing stock requirements
Raw materials
Benilax 7,500 kg 10% of the following month�s production;
Luccine 3,000 kg 500 kg increase in stock level per month;
Finished goods
Crump 10,000 units 15% of the following month�s sales units;
Flake 8,000 units 20% of the following month�s sales units.
6. Capital expenditure
As part of its expansion programme, the company is planning to acquire new equipment costing �5,400,000 on 1 April 2011 and it expects to pay for it in July 2011.
7. Blackwood Limited expects to have a balance of �550,000 in its bank accounts at
31 March 2011.
REQUIRED:
Assume the role of a member of the Management Accounting Team.
Design and construct a spreadsheet model to:
1. Input the relevant budget data.
2. Using the spreadsheet model, prepare the following budgets for each of the six months from 1 April 2011 to 30 September 2011:
(i) Sales budgets;
(ii) Production budgets (in units);
(iii) Raw materials purchases budgets (in units and �)
(iv) Direct labour budgets (in hours and �)
(v) Cash budget.
PART 2
�If accounting is to provide useful information for decision making,�, it should recognise the constraints under which the users of information actually operate, and provide information accordingly.�
(From Accounting for Management Decisions, 3rd edition, page 32, by Arnold & Turley)
YOU ARE REQUIRED to write an essay not exceeding 1,200 words, discussing the above statement within the context of individual and group decision-making. You are advised to include in your discussion a critical analysis of some aspects of models of decision-making.
