Spreadsheets and Modelling

Introduction

Learning Objectives:

• to learn keywords used with spreadsheets
• to find out about spreadsheet basics such as data, labels and formulae

Task 1: Check out some spreadsheet key words

Task 2: Produce a simple spreadsheet

Follow these instructions to produce a simple spreadsheet.  Our spreadsheet is called 'Shop'.

1. Start Excel by clicking its icon or using [Start] then Programs .
2. When Excel loads, click in the 'cell' in column B and row 2.
3. Type the title Shop and it should appear in the cell. Use the icons at the top of the window to make it bold and underline it.
4. Move down 2 rows to cell B4.  Either click into the cell or use the arrow keys. Type in the heading Item. Use the icon to underline it.
5. Move to cell B5.  Type in the label Chocolate .
6. Move to cell B6.  Type in the label Crisps .
7. Move to cell B7.  Type in the label Pop .

So far, your spreadsheet should look like this-

Now we must put in the costs of these items.

1. Move to cell C4.  Type in the heading Cost .   Use the icon to underline it.
2. Move to cell C5.  Type in the data 0.45 .
3. Move to cell C6.  Type in the data 0.24 .
4. Move to cell C7.  Type in the data 0.32 .

After these steps, your spreadsheet should look like this-

Now we must put in how many of these items have been sold.

1. Move to cell D4.  Type in the heading Sold .
2. Move to cell D5.  Type in the data 22 .
3. Move to cell D6.  Type in the data 47 .
4. Move to cell D7.  Type in the data 17 .

By now, your spreadsheet should look like this-

Finally, let's work out how much money has been made selling the items.  Formulae will work things out automatically.

1. Move to cell E4.  Type in the heading Revenue .
2. Move to cell E5.  Type in the formula =C5*D5 .
3. Move to cell E6.  Type in the formula =C6*D6 .
4. Move to cell E7.  Type in the formula =C7*D7 .

Now let's add these amounts up.

5. Move to cell E8.  Click the Autosum icon.  Press [Enter].

Your finished spreadsheet should look like this-

Print out a copy of your spreadsheet for your folder.

Extension

1. Try changing some of the data items (the numbers) on the spreadsheet and see what happens.  Each time you make a change, print out a new copy of the spreadsheet and write on it details of the change that you made and the effect that it had.
2. Click on row 7 then click Insert from the menu and Row.  Put a new item in.  Repeat this a few times.  Make sure that you insert the new items in the middle of the table.  Otherwise the total won't work.
3. Try tidying up the spreadsheet using formatting commands.

Homework