Welcome to the ITGS homepage

Osaka International School

Spreadsheets

Advanced

These activities are for those who have some skill in the use of Excel.  There are five activities on this page, each one is a little more complicated than the one before it.  If you want to really test yourself, you should complete activities 4 & 5.

• Activity 1

• Activity 2

• Activity 3

• Activity 4

• Activity 5
  

Activity 1: The Corner Convenience Store

The corner shop sells a variety of goods which the proprietor groups into five categories. The sales figures for the year to date are set out below:

1. Design a spreadsheet which will allow you to complete the requirements set out below.  

Click for an example

2. Create a spreadsheet to record the sales figures of the store.  The sheet should show total sales for each category as well as the overall total sales for the year so far.  It should also indicate the totals for each month.  All calculations should be performed using formulae.

3. Add a column to show the average monthly sales for each category and the average monthly total sales.  Insert a column next to April for the May sales figures.  Add your own data to this column.

4. Create a bar graph showing a comparison of the sales figures for each of the five categories for the five months so far.

5. Create a pie graph showing how each of the categories contributes to the total income for the year so far.

6. Print one copy of your spreadsheet showing the figures and one copy showing the formulae used.  Submit the two printouts and the design.

Back to the top of the page

Activity 2: Gold Mart Jewellery Inc.

Gold Mart Jewellery Inc. sells wholesale jewellery by mail order to retail jewellers.  The minimum order is one box of any item.

Gold mart has a price list showing the cost of a box (delivered) and the cost per item ( so that the customer can appreciate the value being offered)

Part of the current price list looks like this:

1. Design a spreadsheet which will allow you to complete the requirements set out below.  

Click for an example

2. Create a spreadsheet to record the price list. The list should show all data given above and columns to show the Total Cost and the Cost per Item.  All calculations should be performed using formulae.

3. Add another five items of your own choice and their price details. Use appropriate validation and testing techniques to check your spreadsheet

4. Sort the list so that the items appear in alphabetical order, use appropriate validation and testing techniques to check your spreadsheet and print it out. 

5. Sort the list so that the items appear in decreasing order of cost per box, use appropriate validation and testing techniques to check your spreadsheet and print it out.

6. Print one copy of your spreadsheet showing the formulae used and one copy with annotations of the testing and validation techniques used.  Submit the four printouts and the design.

Activity 3: Small Business Invoice

1. Design a spreadsheet which will allow you to complete the requirements set out below.  

Click for an example

2. Create a spreadsheet to produce an invoice for a small business, like a service station, a plumber, an electrician etc.  The invoice should contain details of the business; name address etc as well as the customer's details.  It should list the items for which the customer is being charged, the cost of these items and totals showing how much has to be paid.

3. For this spreadsheet, you must supply your own data, make it as realistic as possible.

4. Make use of as many formulae and functions as you can.  Obviously all calculations should be completed using formulae, but try using the date functions for example, to add the date to your invoice rather than just typing it in.

5. Excel offers a range of tools which can be used to decorate a sheet.  There are boxes, lines and borders, text colours and background shading.  Experiment with these to make your invoice more appealing, easier to read and realistic.  Graphics can even be inserted from the various clipart directories at school for that professional touch.

6. Print one copy of your spreadsheet showing the figures and one copy showing the formulae used.  Submit the two printouts and the design.

Activity 4: Fund Raising Stall at the International Fair

Your homeroom group is going to hold a food stall at the International Fair to raise funds for Habitat for Humanity. Your homeroom advisor has asked you to prepare a spread sheet to record the items for sale on the stall. Your spread sheet should contain the following data:

Gr 11 Fund Raising Stall

Price listing as at Friday, 5 December 2003

1. Design a spreadsheet which will allow you to complete the requirements set out below.  

Click for an example

2. Create a spreadsheet for the stall to use. The sheet should show all data given above and a column to show the Item Price. The price is made up of the cost price plus the markup. The markup is based on the markup code. If the code is 1, the markup is 100%, if it is 2 then the markup is 150%. To calculate this, you should use a formulae with an "IF STATEMENT". If the markup code is 1, then the price equals the cost plus the cost multiplied by two or else it equals the cost plus the cost multiplied by two and a half. Try something like this in the price column:

=IF(Markup Code = 1, Cost + Cost*2, Cost + Cost*2.5)

3. Add another column to contain the number of items sold at the end of the fair. Add the figures below:

4. Add more columns to calculate Income earned, Expenses incurred and Profit made. Use formulae for all these calculations.

5. Excel offers a range of tools which can be used to decorate a sheet. There are lines and borders, text colours and background shading. Experiment with these to make your sheet more appealing, easier to read and realistic. Graphics can even be inserted for that professional touch.

6. Print one copy of your spreadsheet in normal view, one showing the formulae used and one copy with annotations of the testing and validation techniques used. Submit the printouts and the design.

Activity 5: Fund Raising Stall at the International Fair (with improvements)

For this activity, you can modify the spreadsheet you created for the homeroom group stall or if you want you can start a new sheet.

Your advisor has been able to buy some more items cheaply to sell on the stall. A manufacturer has supplied a quantity of sports clothing and other items. In addition to this, your advisor has changed the system used to calculate the markup of the items, instead of using a code system; it has been decided to simply have a markup percentage. You are presented with the updated information below:

1. Re-design the existing spreadsheet (or design a new one) which will allow you to complete the requirements set out below.  

Click for an example

2. Modify or create a spreadsheet for the stall to use. The sheet should show all data given above. The Item price is still made up of the cost price plus the markup, however the markup is based on the markup percentage. To calculate this, you are to use a formula with lookup table.

You will need to delete the column with the Markup Code in it, this is now useless data. You will have to create a lookup table with the items and their associated markup percentages; probably a vertical table is best suited for this purpose. This table has to be sorted in alphabetical order (A - Z) according to Item description. The table will also need a name. For the example formulae below, I've used the name "markup" for my VLookup table, you can use another name if you want.

Try something like this in the price column, after you create and name your lookup table: =VLOOKUP(Item type, markup,2) *Cost + Cost)              ,

3. The number of items sold at the end of the fair has to be altered. Add your own made up data to the additional items to show the numbers sold.

4. Print one copy of your spreadsheet in normal view, one showing the formulae used and one copy with annotations of the testing and validation techniques used. Submit the three printouts and the design.  

Back to the top of the page