Advanced Search

Between the Sheets - Accounting

Teacher Notes to Downloadable Spreadsheets

In the series of accounting modules, you will find definitions, explanations and examples on the following topics:

1. Introduction to balance sheet and income statement
2. Markups and margins
3. Gearing
4. Depreciation
5. Cost analysis
6. Cost volume profit analysis
7. Budgeting
8. Elasticity of demand
9. Profit maximisation

These supporting notes have been written by Duncan Williamson, the author of the modules. They are split into various sections to help you you use the spreadsheets with students.

• Learning objectives of the modules
• Teaching objectives
• Solutions to problems: the example of the break even point
• Additional problems: the Master Budget
• Other help
• Finally

Learning objectives of the modules

These modules have been written with two objectives in mind

1. Helping students to understand some of the issues in each of them; and
2. Giving students practice at the numerical aspects of those issues.

I hope that once the students have worked through each of the modules they will feel satisfied than before they began looking through them! I have tried to set up these modules in such a way that they are inherently interesting. I hope this doesn't come across as patronising and suggesting that classroom work is inferior!

I have tried to give as much explanation as possible of the key features built into the modules. I have also tried to take the students step by step through any calculations they need to do.

Of course, it has not been possible in these modules to cover every last aspect of the topics they contain. That is, there is still the need to read more about depreciation, more about cost volume profit analysis and more about profit maximisation ...

Teaching objectives

In addition to the learning objectives, there are always the objectives that the teacher needs to bear in mind. The key question that start us off here is, "Why should I use these modules with my students?" Here are a few ideas that should help you to answer this question.

Firstly, you should see these modules as an additional resource that can help you out in a few ways, including. Textbooks are fine, the television and radio is fine as an additional resource. Case studies and past examination questions are also fine and so is project work. However, I believe that these modules can help both teacher and student in some or all of the following ways:

1. They are new and contain aspects of the topics that some students might find challenging and that they can go over again and again.
2. When the student works through any of the calculations, the spreadsheet has been programmed to tell them whether their answer is right or wrong.
3. The spreadsheet is tireless!
4. Teachers can use these modules not only in an accounting and economics environment; but also in a spreadsheeting environment. If you are interested, take a look at some of the formulae, functions and features of these spreadsheets and you may see a few things that both you and/or your students might like to use from a spreadsheeting point of view. For example,
   1. Setting up and defining your own default charts: I don't show you how to do this; but at least I point out that such an option exists
   2. How about the structure of the following formula that sits silently and invisibly awaiting a student response? =IF(C9="","",IF(C9=B9-B8,"Good","Read the comment in cell C8")); and the use of concatenation?: =Workings!A1&" Budgeted Income Statement"
5. Following on from this latter point, you might be interested in spreadsheet model building. Working through these modules can at least give you some ideas here. Cutting and pasting from here to there can be a boon for you and your students. The module on budgeting could be an ideal starting point for some of you.
6. You have some fully worked examples and graphics that you can print out at will and turn into handouts if you want to use the materials in a non computer setting.
7. The spreadsheets are password protected to make the student's life easier: trying to focus them on changing only what they need to change. This password is available from Biz/ed.
8. The modules give you an excuse to build in some computer time when otherwise you might not have the opportunity.
9. In the final analysis, these modules might present you with some remedial work materials: both for the weak student and for the higher flyer who simply wants something different.
10. Finally, you might find the module simply plain old riveting!

Solutions to problems: The example of the break even point

You should find that the solutions to the majority of the questions given in these modules are relatively straightforward to solve. This should be especially true if the students have attended and appreciated your own lessons and have worked carefully through these modules. However, there may be one or two examples that could prove challenging. Perhaps the prime candidate here is the following question, taken from the cost volume profit analysis module (see sheet CS MOS (3)):

Plot the following data on a graph first of all … and then calculate the values of the break even point, and the margin of safety. Create your graph and place it over the orange area below. Then fill in the yellow table with your answers to the break even, C/S ratio (use the formula to calculate this rather than typing in your answer) and margin of safety calculations.
Sales £900,000; Variable costs £525,000; Fixed costs £225,000

On the face of it there appears not to be enough information to work on. The following is the table the students should prepare and the graph they are looking to draw:

That is, since we don't have the output data, we have to use the sales data instead. There is nothing wrong with this, on average, and it does give us a decent result anyway.

The following graph comes from this table: notice, I haven't included the variable costs data: we could do, and it would effectively make this a contribution break even chart. Feel free to encourage the students to include variable costs as you wish.

The following screenshot from the same sheet confirms the break even point, the C/S ratio and the margin of safety percentage derived from these data. The word "Good" to the right of the results table tell the student that their answer is correct!

The graph perfectly demonstrates that the accountant's version of the break-even chart needs a minimum amount of data since everything stems from straight lines. Contrast that with the economist's break even chart that you will find in the profit maximisation module, sheet Rev Costs and Profit: here's a screenshot of that sheet.

Additional problems: the Master Budget

In the case of the Master Budget module, I have not only worked through a full master budgeting case, but I have provided you with a follow up question that you can set for your students. You can simply let the students take the new question and fill in the relevant data in the model built into the module; or you can print out the question (already printer ready) and get them to work on it away from the spreadsheet: that is, as a normal class based, or homework, assignment.

Please make your students work really carefully if you do want them just to overtype the new information otherwise they could become confused very easily. You could try it for yourself to see why I might be saying this.

Secondly, you can, if you wish, change some or all of the data in the second question, type the new data into the model and it will tell you the cash balance, the profit and the balance sheet values … all automatically. This means you can have an infinite supply of questions and the solutions are just a few keystrokes away. Do notice, though, that the comments in the "Workings sheet" do not directly apply to the new question.

I have worked through the new question and here are the results: note first of all that you only need to enter the new name Fransco Ltd in the workings sheet and it is updated automatically in the other sheets.

If you want to provide templates for the students to complete, simply take the following tables and delete all of the values from them then print them out in student friendly format!

Note also, for balance sheet purposes, the spreadsheet automatically determines whether the bank is overdrawn or not.

I have put the cash budget, the income statement and the balance sheet all on separate sheets within the module. Whilst this is not necessary, it does separate them all out and does avoid potential formatting problems. If you wish, once you have taken the password from Biz/ed, you can move things around as you wish.

Other help

There is some other help that you should also read that contains further discussions of these modules under the following headings:

• Module formats
• Numerical calculation
• Graphical solution
• One screen at a time
• Printing

In that file, you will find out about the general layout of the files and the numerical versus the graphical solution of problems. You will also learn how and why the modules are presented in a screen by screen format, thus largely avoiding the need for users to scroll all over the place. Finally, you will see that the modules have been designed with printing in mind: all sheets have been fully formatted for the printer.

Finally ...

I hope you find these modules useful and usable. I also hope you will give me some feedback on what you find. If there is something that doesn't work, please let me know through the feedback form.

If there are modules that I haven't published yet and that you'd like to see, let me know, too, and I'll do what I can.

Duncan Williamson
November 2000