you are required to produce spreadsheet, income statement and statement of financial position as stated in the details bellow

 

 

Order Description

 

The directors of Bennix Manufacturing plc are planning to set up a new division. Budget meetings have produced the following information:

1 Sales forecast for the first 6 months (in units):

Jan Feb Mar Apr May Jun
1500 2500 3500 5000 6000 7000

From July to December sales have been forecast at 7000 units per month.

2 An advertising campaign is planned for the first six months costing £375,000. It will be paid for in two equal instalments in January and April.

3 Customers will be offered a 1 month credit period. It is estimated that 75% of customers will use these credit terms and the remaining 25% will pay cash.

4. Production costs per unit will be:
£
Direct Labour 40
Direct Material 20
Direct Expenses 10
Overhead 35

5. Selling price per unit will be budgeted production cost (£105) plus a mark-up of 45%.

6. Direct Labour and Direct Expenses will be paid for in the month the units are produced.

7. Direct Materials will be paid for in month following purchase. Sufficient material will be bought to ensure the closing inventory in each month can meet the production requirements of the following month.

8. Production will be scheduled to ensure that 50% of the following month’s sales are available in stock at the beginning of the month.

9 The overhead cost per unit is based on an estimated annual demand of 67,500 units. Total overhead cost includes depreciation on the machinery, vehicles, and fixtures and fittings of £420,000.
Overheads are paid for in the month incurred.

10. The following equipment will be purchased:

Machinery: £500,000 paid for in 3 equal instalments from January to March inclusive

Vehicles: 320,000 paid for in 12 equal instalments from Jan to December inclusive

Fixtures and Fittings: £150,000 paid for in one instalment in February.

 

11. Monthly salaries are expected to cost £32,000 per month and will be paid in the month earned.

 

12. Administrative costs will amount to £12,000 per month and will also be paid in the month in which they occur.

13. The parent company will deposit £1,400,000 in the company’s bank account on 1 January.

Required:

(a) On a spreadsheet, produce a cash budget for the first 6 months of operation (January to June inclusive). (16 marks)

(b) Produce an Income Statement for the first 6 months of operation. All figures on the income statement must be formulae. (No figures may be directly input into this page.) (13 marks)

(c) Produce a Statement of Financial Position as at 30 June on a separate page of the same spreadsheet. All figures on the SOFP must be formulae. (No figures may be directly input into this page.) (13 marks)

 

The Board of Directors have considered the budget submitted and would like to consider some changes.

 

Increase the selling price to £160 per unit, reduce the advertising spend to £250,000, spread the cost of the machinery over 6 months and reduce the administrative costs to £10,000 per month.

After making these amendments, please submit your updated spreadsheet through the link on Moodle, with the following figures highlighted in yellow;

From the Income Statement;
a. sales revenue,
b. gross profit
c. net profit

From the Statement of Financial Position;
d. balance at bank,
e. receivables,
f. accruals for payments relating to non-current assets,
g. total assets
h. the total of liabilities plus equity.