Task 3 Excel financial statements (basic version)
The purpose of this task is to remind you of the basic Excel functions that you studied in the second year. These are essential ‘employability’ skills.
The following financial statements are included in Word format:
- Statement of Financial Position
- Income statement
Your task is to present them in Excel, based on the following assumptions:
- Cost of sales is a variable cost
- Administrative expenses and distribution costs are a fixed cost
- The rate of interest is 7.5% of the long-term debt
- Tax is 19% of profit before tax
- No dividends are paid
You should maximise the use of formulas in constructing the spreadsheet.
You may make any other assumption necessary to maintain the integrity of the financial statements.
Hint: If the sales value is changed in the Statement of Profit or Loss, then the Statement of Financial Position should reflect a revised retained earnings and the SFP should still balance.
Statement of Profit or Loss for the year ended 31 March 2019
£
Turnover 2,000
Cost of sales (200)
Gross profit 1,800
Distribution costs (40)
Administrative expenses (210)
Operating profit 1,550
Investment income 100
Interest payable (150)
Earnings before tax 1,500
Tax (285)
Net income 1,215
Statement of Financial Position at 31 March 2019
Cash 100
Receivables 1,150
Inventory 500
Property, plant and equipment 2,500
Intangibles 800
Total assets 5,050
Accounts payable 400
Long term debt 2,000
Total liabilities 2,400
Share capital 500
Retained earnings 2,150
2,650
Total equity and liabilities 5,050
Note 1 Cost of sales
Wages 110
Inventory 90
200
Note 2 Distribution costs
Depreciation 5
Commission 35
40
Note 3 Administrative expenses
Depreciation 35
Loss on disposal of fixed assets 10
Bad debts 15
Professional fees (see below) 80
Entertainment 50
Gift Aid 20
210
Professional fees
Accountancy 50
Valuation of land 10
Other (allowable) 20
80
Note 4 Investment income
Interest income 90
Dividends from UK companies 10
100
Note 5 Property, plant and equipment
Opening net book value 1,770
Additions:
Computer 830
Disposals at net book value (60)
Depreciation (40)
Closing net book value 2,500
Task 4 Excel spreadsheet (expanded version)
The purpose of this task is to provide further practical insight into the use of Excel by using realistic numbers and presenting the financial statements in a form suitable for publication. It relates the basic Excel spreadsheet to the Taxation syllabus by inviting you to add more detail which has tax significance.
- Change the financial statements so that they have realistic numbers and insert new headings in the Notes so that the tax computation will reflect the module syllabus. NB. You should not add any additional main headings – focus on expanding the existing Notes in Task 3 to reflect more items requiring adjustment and presenting the financial statements in a form suitable for publication.
- In order to do this, review the seminar assignments in the handbook and identify the tax-significant numbers. For example, you could include some rental income in Note 4, gifts to customers in Note 3. Expand the additions to PPE in Note 5 to reflect more capital allowances rules. You will be able to expand on this in 2019 when we cover more aspects of corporation tax, by adding Note 6, etc.
- Bear in mind that there is no tax calculation (or calculation of capital allowances) in Task 4. The object here is to include relevant items in the Income Statement and in Additions to Plant and Machinery so that there is enough material for the tax calculation in Task 6.
- The assessment of this Task focuses on two aspects:
-
- Whether the financial statements and notes have realistic numbers and are suitable for publication.
- The extent to which the Notes cover the corporation tax syllabus. The best way to ensure this is to use seminar questions and past examination questions (Q5) as a check list of content.