St Roch’s Secondary

S1 Curriculum for Excellence Course

Software Applications

Spreadsheet

Tasks

Krusty Burger Sales
Name / Saturday / Sunday / TOTAL
Barney / 3 / 2
Bart / 4 / 3
Homer / 10 / 8
Moe / 2 / 1

GENERAL INSTRUCTIONS

You are now going to create some spreadsheets using the skills you have already learned.

Before you start, create a new folder in you’re my Documents. Name the folder ‘Spreadsheets’.

Each spreadsheet should be saved into your

‘Spreadsheet’ folder and have your name and task number as a footer.

Each spreadsheet should be printed:

  • Showing figures
  • Showing formulae
  • With gridlines and row and column headings

Each chart should:

Have an appropriate title

Have appropriate x and y axis

Be printed on a separate sheet

Task 1

Clothing Sales - First Quarter
Men's Wear / Jan / Feb / March / Totals
Casual / 12000 / 23000 / 22722 / ?
Evening Dress / 16830 / 12500 / 26500 / ?
Business / 20900 / 19567 / 24590 / ?
Sports / 34567 / 42000 / 17890 / ?
Swimwear / 34567 / 18734 / 12560 / ?
Totals / ? / ? / ? / ?

Task 1a

Make the main heading – Clothing Sales – First Quarter a larger font size.

Underline the sub-heading Men’s Wear.

Sales of Sports in March was actually £27,890. Change this figure.

We no longer sell swimwear. Please delete this row.

Insert a row below ‘Totals’. Label this row Average and embolden this label.

Insert formulae to calculate the Average sales for each month and the Total Average.

Task 1b

Create a column chart showing the sales of each type of clothing from Jan to March.

The title of the chart should be ‘Men’s Wear Sales – January to March’

Task 2

Clothing Sales – First Quarter
Ladies Wear
Jan / Feb / March / Totals
Casual / 30679 / 25890 / 29237 / ?
Evening Dress / 56000 / 47209 / 26500 / ?
Business / 14000 / 12345 / 14556 / ?
Sports / 20670 / 13450 / 17890 / ?
Swimwear / 12000 / 18734 / 14579 / ?
Totals / ? / ? / ? / ?

Task 2a

Make the main heading – Clothing Sales – First Quarter a larger size and a different font.

Underline the sub-heading Ladies Wear.

Centre all column headings.

Insert a new row under Swimwear entitled Footwear.

Insert the following details:

Jan / Feb / March / Totals
Shoes / 25678 / 32478 / 33000 / ?

Insert a new column next to Totals. Label this Average and embolden this label.

Insert formulae to calculate the Average sales for each type of clothing and the Total Average.

Task 2b

Create a Pie Chart showing the sales of each type of clothing for January.

The title of the chart should be ‘Ladies Wear - January Sales’.

Task 3

Pupil Marks
Maths / English / Science / Computing / Total / Average
Karen Smith / 56 / 72 / 33 / 77 / ? / ?
Paula Terrance / 34 / 36 / 43 / 56 / ? / ?
Steven Elderfield / 43 / 89 / 90 / 67 / ? / ?
Paul Stevenson / 23 / 56 / 64 / 67 / ? / ?
Total / ? / ? / ? / ? / ? / ?
Average / ? / ? / ? / ? / ? / ?

Task 3a

Insert a sub-heading below Pupil Marks entitled S3 Results. This should be bold and underlined.

Right align all column headings.

Insert a new row above Total with the following information:

Maths / English / Science / Computing / Total / Average
Steven Martin / 76 / 90 / 83 / 77 / ? / ?

Delete the column for Computing.

Insert a new row below Average. Label this row Max and embolden this label.

Insert formulae to calculate the Maximum grade for each subject.

Insert a new row below Average. Label this row Min and embolden this label.

Insert formulae to calculate the Minimum grade for each subject.

Task 3b

Create a column chart comparing Steven’s grades to the Total grades for each subject.

Task 4

Tuck Shop Sales
Week 1 / Week 2 / Week 3 / Week 4 / Week 5 / Total
Crisps - Ready Salted / 21 / 22 / 18.5 / 21 / 22 / ?
Crisps - Salt and Vinegar / 28 / 32 / 28 / 23 / 23 / ?
Bottle of juice - orange / 13 / 12 / 15 / 13.5 / 14 / ?
Bottle of juice - apple / 15 / 11 / 12 / 11 / 17 / ?
Chocolate bar / 8.5 / 10 / 10 / 14 / 12 / ?
Cereal bar / 5.6 / 6.5 / 10 / 8 / 11 / ?
Total / ? / ? / ? / ? / ? / ?

Task 4a

Make the main heading – Tuck Shop Sales – a bigger size and Italics.

Right align all column headings.

Insert a new column to the right of Week 5 and name it Week 6. Embolden this heading and right align.

Delete the row for Cereal bar.

Insert the information below:

Week 6
Crisps - Ready Salted / 25
Crisps - Salt and Vinegar / 33
Bottle of juice - orange / 18
Bottle of juice - apple / 15
Cereal bar / 5.6

Centre all currency cells

Insert a new row below Total. Label this row Average and embolden this label.

Insert formulae to calculate the Average sales of each product for each week.

Task 4b

Create a pie chart showing the sales for each product for Week 1.

Task 5

Salary Sheet
Name / Monthly Salary / Annual Salary
Anne Gilchrist / 1400 / ?
Patsy Willis / 2350 / ?
Andy Gardener / 1750 / ?
Nicola Bell / 1800 / ?
Peter Westerly / 1400 / ?
Hugh Morris / 1975 / ?
TOTAL / ? / ?
AVERAGE / ? / ?

Task 5a

Make the main heading – Salary Sheet – a bigger size and Centred.

Delete the row for Nicola Bell – she has left the company.

Add a column next to ‘Annual Salary’. Name this Pension Scheme. Embolden this heading. Each employee has to pay £100 towards his or her pension each month. Enter this information into this column.

Add another column next to Pension Scheme. Name this Monthly Salary After Pension. Insert a formula to calculate each person’s monthly salary after their pension has been deducted.

Calculate the Total’s for these 2 new columns.

Delete the row for Average – this is no longer required.

Task 5b

Create a column chart comparing monthly and annual salary of Anne and Patsy only.

Task 6

Income - First Quarter
Jan / Feb / Mar / TOTAL
Sales Receipts / 50500 / 54500 / 150000 / ?
Grants Received / 102242 / 102242 / 750000 / ?
Other / 52000 / 52000 / 90064 / ?
TOTAL / ? / ? / ? / ?
AVERAGE / ? / ? / ? / ?

Task 6a

Make the whole spreadsheet a different font and Size 12.

Underline all column headings and make Italic.

Insert 2 news columns after TOTAL’, Name them Max and Min. Insert formulae to calculate the maximum and minimum figure for Sales, Grants and Other.

Insert a new row below Average. Label this row Max and embolden this label.

Insert formulae to calculate the maximum income from January to March. Embolden these figures.

Insert a new row below Max. Label this row Min and embolden this label.

 Insert formulae to calculate the minimum income from January to March. Embolden these figures.

Task 6b

Create a column chart showing sales and grants from January to March.

Task 7
Concert Sales
Venue / Total Sales / Tickets Sold / Cost per ticket
Glasgow / 230000 / 6000 / ?
Edinburgh / 470000 / 12000 / ?
Perth / 120000 / 6000 / ?
Dundee / 185000 / 8000 / ?
Ayr / 120000 / 7500 / ?
Manchester / 450000 / 12000 / ?
Liverpool / 340000 / 9000 / ?
Newcastle / 290000 / 8000 / ?
Birmingham / 324560 / 10000 / ?
AVERAGE / ?
MAX / ?
MIN / ?

Task 7a

Centre all cells in the ‘Cost per ticket’ column and embolden. Shade this column.

Right align all other column headings.

Delete the rows for Ayr and Dundee. These concerts have been cancelled.

Insert a new row after Birmingham. Label this Brighton. Brighton has ticket sales of £32,000 and 7000 tickets sold. Please you update your spreadsheet with this information.

Embolden the row that has the highest cost per ticket.

Italicise the row that has the lowest cost per ticket.

Task 7b

Create a column chart showing the total sales for each venue.

Task 8

Staff Wages
Hourly Rate / Hours Worked / Wage / Monthly Savings / Wage after Savings
Belinda Smith / 7.5 / 35 / ? / 50 / ?
Carly Simmons / 10 / 20 / ? / 20 / ?
Beth McIntyre / 12.5 / 46 / ? / 30 / ?
Derek McWhirter / 12.5 / 45 / ? / 80 / ?
Lesley Thomson / 10 / 25 / ? / 25 / ?
Finn Tate / 8.75 / 12 / ? / 20 / ?
TOTAL / ? / ? / ? / ? / ?

Task 8a

Make the main heading – Staff Wages – a bigger size and different font.

Centre all column headings.

Belinda Smith’s hourly rate is changed to £10.00 and her savings increased to £75.

Delete the row for Finn Tate – he has left the company.

Insert a new column before ‘Hourly Rate’. Name this Department. Embolden and Centre this heading.

Insert the information below for each employee:

Department
Belinda Smith / Administration
Carly Simmons / Human Resources
Beth McIntyre / Human Resources
Derek McWhirter / Finance
Lesley Thomson / Sales

Insert a new row after ‘Total’. Label this Average and embolden. Insert formulae to calculate the Average of each column.

Make the row that has the highest wage after savings bold and italics.

Task 8b

Create a column chart showing the Wage after savings for each member of staff.

1

Curriculum for Excellence S1 Course

St Roch’s Secondary