ReviseICT.co.uk homepage
Microsoft Excel
Spreadsheet software

Tasks

 
Record Store

Close any open Excel spreadsheets.  Create a new spreadsheet as you did for Task 2.

Directions

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 of 4.

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 bold black.

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", "false statement")

 

All done?
Move onto the assessment.

Index page