Copyright © by Mark Baker 1997

Copyright waiver

Return to MarkChrisSoft home page

  You are going to create your own spreadsheet. You need to follow the instructions very carefully, as they tell you exactly what is wanted. Read the introduction below carefully too, as it sets up the situation that you must work with. You will be using a spreadsheet called Microsoft Excel.

You are going to create a financial model of a school production. The model will include all the costs (money going out) and all the income (money coming in). When it is finished, the model can be used for planning, before work on the production starts. It can be used to check on progress, as the production goes ahead. It can also be used after the production, to evaluate what actually happened.

Your spreadsheet should all fit onto the screen, at one time. Before you are allowed to start entering your spreadsheet on the computer, you will have to complete the plan and have it checked.

Your spreadsheet will have four main sections. These are FIXED COSTS, VARIABLE COSTS, INCOME and SUMMARY.

Variable costs are things that will change depending on how many people come to watch the production, or how many items you decide to buy (e.g. if you have 100 programs printed it may cost £20, if you have 200 printed then it will cost £40). Fixed costs are things that have to be bought, and their cost does not depend on the number of tickets sold or anything else (e.g. it will cost a set amount to hire the seating, the costumes will cost a fixed amount and so on).

Income will come from selling tickets and programmes. Everyone will get some refreshments included with their ticket, but extra soft drinks will have to be paid for.

Enter these text headings onto your spreadsheet:

The information in the paragraph below will allow you to enter all the numbers that you need for the FIXED COSTS section.

The information in the paragraph below will allow you to enter all the numbers that you need for the INCOME section.

The information in the paragraph below will allow you to enter all the numbers that you need for the VARIABLE COSTS section.

This is where you enter the really clever part of the spreadsheet. The formulas allow the spreadsheet to do all the working out and if any of the numbers are changed, then all the formulas that use that number are recalculated automatically.

All Excel formulas have an "=" sign at the front, to tell the spreadsheet that what follows is a formula, not just text.

1. You will start by entering the formulas to work out the variable costs. The number of drinks sold is already given in cell B6. This can be copied into cell G15. Click on cell G15 and enter this formula:

2. The amount of refreshments depends on the number of tickets sold for the two nights, so you need to add together the Friday and Saturday ticket numbers. Click on cell G14 and enter this formula:

3. Click on cell I13. Enter this formula:

The star means "multiply" and this formula is multiplying the number of programs sold by the cost of printing them.

4. Click on cell I14. Enter this formula:

5. This is a similar formula, to multiply the number of refreshments, by the cost of each one. You need to enter another similar formula in cell I15:

Note that these three formulas all do the same sort of calculation, only the cell numbers change.

6. Finally in this section, you must work out the total of the fixed costs. Click on cell I16 and enter this formula:

=I13 + I14 + I15 <Enter>

You do a lot of adding up of rows or columns in spreadsheets and they have a special function which can be used instead: =SUM(I13:I15) would do the same thing.

7. You have to put in the formula that works out the total of the fixed costs. Click on the cell G10 and type this:

This will add up all the cells from G4 to G9 and it is a shorter way of entering

8. To work out the amount of money you will make from the tickets sold for Friday, you must multiply the number sold, by the price. Click on cell D4 and enter this formula:

=B4*C4 <Enter>

9. You will need very similar formulas for cells D5, D6, D7 and D8. There is a quick way of entering them:

Click on cell D4 if it is not already highlighted. Select Edit/Copy from the menu bar. Drag from cell D5 to D8. The cells D5 to D8 should turn black (they have been highlighted). Select Edit/Paste.

Look at cells D4 to D8. You will see that the formula is changed on each row, so that you have B4*C4 on the first row, then B5*C5, then B6*C6 and so on. This is not the same as a simple copy, where everything would stay the same. The formula has been replicated.

10. Click on cell D9 and put in a formula to add up all the numbers from D4 to D8.

11. You must copy the totals from the other three sections to here. Click on cell B17 and enter this formula:

12. Type in formulas into B18 and B19 to copy over the correct totals.

13. To get the final balance, click on cell B20 and enter this formula:

You have now finished entering the spreadsheet and must check that everything is working properly. Your totals should be as follows:

If you have different answers, then you have made a mistake somewhere. Check your spreadsheet and correct any mistakes.

Return to MarkChrisSoft home page