The spreadsheet below is used by each week by Mrs Barratt (the newsagent) to calculate how much money she has taken in the shop. It shows how much money was taken for each type of item she sells, and she could like to calculate the total amount taken for each day (in column E), and how much money was taken for the whole week for each type of item (row 12). She can then use this information to decide whether to rearrange her shop to allow more, or less space, for each type of product.
Your task is to help the newsagent by using formulae to do the calculations for her. You are creating a spreadsheet model that Mrs Barratt could use to investigate how her business works and how it could be improved.
To create the model, copy and paste the information shown above into Excel or Open Office - you don't need the bits with the grey background so you can delete them from your spreadsheet (you'll probably have to copy and paste them to start with, though). Then use a formula to calculate:
The total takings for each day of the week
The total takings for each type of product
Don't forget that you won't necessarily need to create a new formula for each cell - you can use replication to copy some of the calculations.
When you have completed your spreadsheet model, attach it below and answer the questions.
There are also some short questions about your spreadsheet model. Click this link to open a Word document containing the questions below. Use your spreadsheet to answer the questions.
Does Mrs Barratt make more money from selling magazines, or from selling sweets? (1 mark)
Mrs Barratt needs to close the shop for a day for stock-taking and staff training. Which would be the best day for her to close the shop? Explain your answer. (2 marks)
The word variable means a number that can be changed to affect a spreadsheet model. What are the variables in this spreadsheet (i.e. what could you sensibly change in the spreadsheet and have it still make sense?) (2 marks)
Why would it be better for Mrs Barratt to use a spreadsheet for this job, rather than adding up the figures on paper? (2 marks)