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:


  1. Cost of sales is a variable cost
  2. Administrative expenses and distribution costs are a fixed cost
  3. The rate of interest is 7.5% of the long-term debt
  4. Tax is 19% of profit before tax
  5. 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



Total equity and liabilities                           5,050





Note 1 Cost of sales


Wages                                                          110

Inventory                                                        90




Note 2 Distribution costs


Depreciation                                                     5

Commission                                                   35



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




Professional fees


Accountancy                                                  50

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


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.


  1. 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.


  1. 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.


  1. 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.


  1. The assessment of this Task focuses on two aspects:


    1. Whether the financial statements and notes have realistic numbers and are suitable for publication.
    2. 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.