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

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.

 

  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.

Do You Know That our Professional Writers are on Stand-by to Provide you with the Most Authentic Custom Paper. Order with us Today and Enjoy an Irresistible Discount!