Close any open Excel
spreadsheets. Create a new spreadsheet as you did for Task 2.
1. This task develops
your skills on further. You are going to create a "computer
model" for a record store's accounts. Follow the instructions
carefully, but think about each step, as you need to do much more on
your own this time!
2. A record store has
the following financial information for one week. Design and
create your own spreadsheet to show this.
3. For this week, the
record store bought 1000 CDs, 1000 videos and 100 cassettes.
Add this information, together with the "purchase cost" information,
to your spreadsheet. You should show "purchase cost per unit"
and "total purchase cost" for each item. Each video costs the
shop £3, but is sold for £12. Each CD is sold for £12, but
costs the shop £5. Each cassette is sold for £8, at a profit
4. Develop your
spreadsheet further - add in additional columns to calculate
"Total sales", "Daily profits" and "Weekly profit".
Remember to use formulas wherever you can as this will allow you
to add in different numbers and automatically calculate results.
5. Experiment with the
"Chart wizard" button. Highlight the CD sales column and press
the button. You can work through and easily create charts to
show your data. Try to create one that shows the sales of CDs,
videos and cassettes all together.
6. Go back to your
main spreadsheet for the final work. Select the cell that
shows the "Weekly profit". Select "Format", "Conditional
Formatting" from the top menu bar. Set it up to show a
loss in bold red and a profit in
7. To be really
professional, you can also set up automatic comments using the "IF"
statement. To show a different message for a profit or loss,
the following formula could be used - look how brackets, commas and
speech marks are used:
Try it out yourself - make sure you use the punctuation as shown:
= IF ( formula, "true statement",
Move onto the assessment.