The case study outlined on this page is aimed at teachers and students alike. A teacher can use this case study to generate questions of varying levels of difficulty simply by printing out the contents of the spreadsheet file that accompanies the case. Since the case is based on the generation of random numbers, teachers can use this case to set one question for each student: all questions are guaranteed to be different to each other and all questions have their fully worked solution. This is a fantastic examination, assignment, tutorial question resource for teachers!

The student can generate questions, too; and can gain enormous benefit by being able to print out a question, work through it and then check the solution which is guaranteed to be correct. This is a fantastic examination practice resource for students.

For everyone, there are two years' worth of data available for each question meaning that at least three questions can be generated for each data set: year 1 only, year 2 only or years 1 and 2.

Read the instructions that follow to see how useful and valuable this case study really is.

Buy the full case study and spreadsheet for only £5 (five GB Pounds)

Click here to go to our OnLine Financial Accounting Department eShop where you can place your order for this case study.

The Case Study: Objectives

Users of this case study will appreciate the following issues

• the recording of transactions in the books of account of a business
• the recording of adjusting transactions in the books of account of a business
• the preparation of an income statement from raw accounting data
• the preparation of a balance sheet from raw accounting data
• the closing off and opening of books of account of a business in consecutive years
• the preparation of a cash flow statement
• the preparation ratios and their interpretation

Target Audience

This case is aimed at upper first and second level students who are keen to extend their familiarity with relatively complex bookkeeping and accounting situations.

The Task

In the case of both sets of data, the student is required to prepare

• an income statement
• a balance sheet and, for year two,
• a cash flow statement

This case can be used in any of the following ways:

• year 1 only
• year 2 only
• years 1 and 2

Students could also be required to devise and analyse the key ratios relating to this business and make relevant comments on their findings.

Copy and Paste from the spreadsheet to devise the case you wish to set.

Specific Features of the Case

Given that the data are largely randomly generated using the RANDBETWEEN function, see below, and that pressing the F9 key will generate a completely new set of data each time, you can use this to your advantage by, for example

• generating a case in which both years return profits
• generating a case in which year one returns a profit but year two returns a loss

To help you to do this, go to the Transactions 2004 sheet and scroll down to row 45: this row contains the Profit and Loss Account balance b/d and c/d and when you press the F9 key you can see when profits are positive or negative and so on!

If you are confident enough to change aspects of the spreadsheet, consider changing some of the values in the =RANDBETWEEN settings. For example, if you want to force a loss for year two in all cases, then change the current setting for Sales to be found in cell E10 of

=RANDBETWEEN(160000,220000)

in Transactions 2004 to, say,

=RANDBETWEEN(130000,190000)

and see what happens. If this doesn't give you what you want, monitor what happens and make the necessary change until you are happy.

SPECIAL NOTE: RANDBETWEEN is not automatically installed when you install Excel. Consequently it may not work when you first try it: in this case you will see #NAME! instead of the value you were hoping for. To solve this problem, do the following in Excel

»Tools
»Add-Ins
»tick the Analysis ToolPak option

notice that there is also an Analysis ToolPak - VBA option but you don't need that for this case!

Cash Flow Statement

For the cash flow statement I have provided the solution to the Indirect Method of presentation and I have chosen to start the statement with Net Profit before Interest and Taxation and then add back the interest and dividends received ... this is purely my choice of how to demonstrate one possible starting point for the statement.

For the Working Capital Adjustments section I have programmed the spreadsheet to identify the situations where, for example, stocks (inventories) have increased or decreased and then to record the nature of the movement automatically.

Take a look at cell A13 in the Cash Flow 2004 sheet to see the function I have built:

=IF('Balance Sheet 2003'!C13-'Balance Sheet 2004'!C14less than0,"increase in stocks","decrease in stocks") NOTE: HTML coding does not like the less than sign in the middle of that function ... please insert that sign where it says 'less than'!

I have done the same for debtors, prepayments creditors and accruals.

Similarly in the values column for this section of the cash flow statement I have programmed them to record them as positive or negative as appropriate, as follows for stocks in cell B13 in the same sheet:

='Balance Sheet 2003'!C13-'Balance Sheet 2004'!C14

Contrast that with the function in cell B16 for creditors:

=-('Balance Sheet 2003'!C27-'Balance Sheet 2004'!C28)

Possible Extensions to the Case Study

This case study can be enhanced in a number of ways so that it becomes useful in a much wider range of settings than it is at the moment. As examples of how to enhance the case study, consider these potential extensions that could be built into it:

introduce

• a government grant and require that it be treated according to IAS 20
• the impairment of one or more assets
• a change in the depreciation basis or rate of one or more fixed assets
• the sale of a fixed asset at a profit/loss and require a full explanation of how to deal with it or them
• the requirement to produce the cash flow statement specifically by the direct and/or the indirect method
• and so on: the IASs provide a wealth of examples and ideas to help you out here

The Spreadsheet

The accompanying file Accounts from sumif.xls contains a number of worksheets that contain

• trial balance and adjustment data for two consecutive years of trading for DW plc
• income statements for the two years
• balance sheets for the two years
• an adjustment sheet showing the treatment of the adjustments in the ledger accounts for the first year of trading
• cash flow statement for the second year
• a complete set of ledger accounts for the second year of trading

The file uses the function =RANDBETWEEN(bottom,top) to generate the vast majority of numbers in the case so that simply by pressing the F9 key, the user can generate a completely new set of data and therefore solutions. Since each each set of data is unique, there is a control code at the head of each page for each of them, in the form: Control Code 4623. The number 4623 is randomly generated but will change when the F9 key is pressed. However, the code is designed to provide the information that if a full set of pages contain the same control code then they all relate to the same data set and the answers will correspond to the data in those pages. Pages with different control numbers will not correspond and will be impossible to reconcile even though their layout and appearance will be virtually identical!

WARNINGS:

• Although the recalculation setting has been set to manual, meaning that nothing will change unless the F9 key has been pressed ,once the F9 key has been pressed any previously stored data will be lost.
• Saving the file at any stage will also cause the file to recalculate and any previously held data to be lost.

Please note, subject to the contents of the next section, once you have copied and pasted from Excel to Word the random number generation feature of the spreadsheet are lost and you can safely print out the pages without the fear of them updating themselves!

Link from Spreadsheet to Word File

If you set up a case study question sheet you can use the linking function available within Office so that Word will update your files if and when you update the spreadsheet.

Specimen Question

Click here to see just one example of the many potential questions that can be developed using this case study approach.

Buy the full case study and spreadsheet for only £5 (five GB Pounds)

Click here to go to our OnLine Financial Accounting Department eShop where you can place your order for this case study.

Click here to go to our OnLine eShopWindow where you see what else we have available for sale.

Duncan Williamson
22 September 2003