The senior management team have always believed that they must monitor their results carefully in order to improve performance and stay ahead of their competitors.
Each year therefore, they prepare a set of final accounts and a range of relevant financial ratios, which highlight particular strengths or weaknesses in the business.

These accounts have always been prepared manually but this has become time consuming and impractical for the business. The senior management recognise the need for the business to improve their management information systems and utilise spread sheets more effectively.

Notes:

1. The figure for the bank balance is to be calculated as the ‘balancing figure’ in order to make the balance sheets agree for both 2015 and 2016.

2. You will need to establish the figure for Capital at 21st January 2016.

3. Depreciate Computer Equipment by 20% per annum and Motor Vehicles by 25% per annum using the straight line method of depreciation.

Assignment Tasks :

1. Prepare a handwritten solution to the profit and loss account for the twelve months to 21st January 2015 and 2016 and the respective balance sheets, together with a tabular summary showing all profitability and liquidity ratios for both years.

All relevant supporting calculations should be shown for the above.

[ This is preparatory work for the main part of the assignment which is set out in Remit 2 below and is awarded marks ]

2. Produce a spreadsheet model, using Microsoft Excel only, that will :–

• Automatically produce a profit and loss account and balance sheet for both years using a data entry section, and

• Automatically produce a summary of the profitability and liquidity ratios for both years

Specification

The model should be:

i.
Self-explanatory, flexible and “user friendly.” A number of the newly appointed operational managers, who will use the system, may be unfamiliar with computerised systems.

ii.
Capable of handling the input data using a data entry system and capable of producing the appropriate financial statements and capable of accepting new data and processing this new data accurately.

Submission of Assignments

Completed assignments should contain COPIES of the following:

• (a)
Manually produced statements with supporting calculations (i.e

. hand-written),

• (b)
A printout of the model,

PLUS one version of:
• )
A CD or memory stick containing your model using your surname(s) as the filename. Be sure that your name is on the CD or memory stick submitted.

In addition you are required to note the following points for each of the three remits:

1.Supporting calculations need to be shown with the manual statements

2 (a) The model must have a clear Data Entry Section and be able to accept the data given in the assignment, process it, and generate accurate financial statements. In addition the model must be able to accept new data in the Data Entry Section and accurately generate a new set of financial statements using this new data.

The marker assessing your work will key in ‘test data’ into the data entry cells and will assess how effective your model is at producing a new set of final statements.

In order to achieve a ‘pass level’, the model needs to be able to accept the data provided, replicate the output of the manual submission and process “test data”. The model needs to have clear ‘instructions’ explaining how to use the model and an explanation of the features built into the model. The model must have appropriate protection in place to ensure security of layout and formulae.
—————————————————————————————————-
2 (b) The financial statement(s) can in fact be upgraded by providing information about the relative change in the ratios for each of the two years. In addition, a statement indicating whether any ratio has improved or deteriorated can be created and whether any improvement or deterioration in each ratio is ‘significant’ enough to be brought to the attention of all managers.

It is this flexibility in the model construction that allows the marker to consider whether work is of a high standard.

If you choose to incorporate this flexibility into your model structure, you will need to learn how to use logic functions. However do note that this is not a prerequisite to achieving a ‘pass level’ but is a prerequisite if work is to be considered for the highest grade.

4. On a sample basis you may be required to give a short informal presentation to your lecturers to demonstrate that the work submitted is your own work and that both members of a group have worked together in a professional manner.Click

here to enter text.