Spreadsheet Modelling

Spreadsheets - Tuck-shop

You are in charge of the school tuck shop for a week and have decided to keep the takings on a spreadsheet. Some of the data has been entered for you already.

Activity 1

Complete the tasks on the worksheet, answer the following questions…

1)Put your name in C12 and your form in I12.

2)Click in cell C4, what has been entered? Look at the formula bar.

3)In which cell has the label THURSDAY been entered?

4)What does £0.27 in cell H4 tell us? Explain using the column and row labels.

5)Click in cell G4, what formula has been used?

6)Click on G5. This time the formula has been entered slightly differently, but it means the same. Write this down. It is called a function.

7)Write down the formula or function for G7. Put it in the spreadsheet and then write down the total

8)Do the same for G8. What figures are seen in the spreadsheet?

9)Save your work

Activity 2 Adding to the sheet

a)Muffins cost 22p, so you will need to enter this in H7 as 0.22

b)Crisps cost 15p, so you will need to enter this in H8 as what? Write it down, then enter the details into the spreadsheet

c)What has been entered in I4? Is it text, numbers or a formula? Click on the cell then write it down.

d)Save your work.

e)I4 uses a formula. I5 needs a formula adding, write down the formula you will use, in your book. Now add this into your spreadsheet

f)Now add the appropriate formula into I6, I7 and I8. Write down what figures are displayed in each of these cells.

g)To obtain a grand total for the week’s takings you need to put a formula into I10. Write down what you think this should be. What total does it give?

h)Chris made a mistake with the figures for Biscuit sales on Wednesday, it should be 28. Please change this figure. What is the total number of biscuits sold now for the week? What are the takings for the week in biscuits?

i)Another cell has changed. Which is it, and what has it changed to?

j)Save your work.

Activity 3 - extension work

Think of all the extra things you could add to your work to make it look more user friendly

  1. Formatting – borders and colouring label cells in light grey
  2. Draw a graph of the items sold during the week.
  3. Sort the data by item (col A) take care to keep the data on the row with the labels.
  4. If cost price for each item is as follows:- Coca cola 20p, Orange 18p, Biscuits 5p, Muffin 15p and Crisps 8p, work out how much profit has been made on each item and the total profit made?

Activity 4 - Printing

  1. Save your work.
  1. Ask your teacher to check the work.
  1. Print off the spreadsheet – make sure you have put your name on the sheet.