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
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
Net income 1,215
Statement of Financial Position at 31 March 2019
Property, plant and equipment 2,500
Total assets 5,050
Accounts payable 400
Long term debt 2,000
Total liabilities 2,400
Share capital 500
Retained earnings 2,150
Total equity and liabilities 5,050
Note 1 Cost of sales
Note 2 Distribution costs
Note 3 Administrative expenses
Loss on disposal of fixed assets 10
Bad debts 15
Professional fees (see below) 80
Gift Aid 20
Valuation of land 10
Other (allowable) 20
Note 4 Investment income
Interest income 90
Dividends from UK companies 10
Note 5 Property, plant and equipment
Opening net book value 1,770
Disposals at net book value (60)
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.