Functional Skills
ICT
Skills
Level 2

Spreadsheets
Learning Resource
2010 Version
Task 2

Microsoft product screen shot(s) reprinted with permission from
Microsoft Corporation.

SPREADSHEETS
LEVEL 2
TASK 2

Excellence in skills development

Edit and develop a drama group spreadsheet
As you work through, you will find help available in the Spreadsheet Information Pack, if you need to use it.

The purpose of the spreadsheet
You have decided to help to support the local drama group. You have been asked to create a spreadsheet to show new ticket prices for 2011. The prices will be based on an increase of 5% on top of last year’s prices. You will also produce a pie chart to show the projected income for each show.

Open Microsoft Excel
Open Microsoft Excel.

Open a spreadsheet/file
Open the Microsoft Excel workbook called SS L2 4.2-Mansfield Drama Group from the learner files.

Save
Save the file with the new name Mansfield Drama Group 2 to your folder.

Help if you need it!
Spreadsheet Information Pack
Page
Open a spreadsheet ................................................................................................................... 49
Open Microsoft Excel ................................................................................................................. 50
Save ........................................................................................................................................... 56

©West Nottinghamshire College

1

SPREADSHEETS
LEVEL 2
TASK 2

Excellence in skills development

Select a cell range
Select the cell range I2:J5.

Drag and drop
Use drag and drop to move the selected cell range to cells B18:C21.

Rows
Delete row 19.
Insert a row after row 1.

Columns
Insert a new column after column E.
Insert another new column after column G.

Enter information
Enter the information shown into the correct cells.

Cell

Text to be entered

F3
H3
J3
K3
B17

60+ Discount
Child Discount
Projected Attendance
Projected Income
TOTAL PROJECTED GROSS INCOME

Help if you need it!
Spreadsheet Information Pack
Page
Delete columns or rows .............................................................................................................. 17
Drag and drop ........................................................................................................................... 21
Enter information into a cell ........................................................................................................ 21
Insert columns and rows ............................................................................................................ 17
Select a cell range ..................................................................................................................... 57

©West Nottinghamshire College

2

SPREADSHEETS
LEVEL 2
TASK 2

Excellence in skills development

Column width
Widen the column widths to show the titles.

Insert a formula in cell D4
Ticket prices for 2011 are 5% more than in 2010.
Create a formula in cell D4 that calculates the 5% increase (shown in cell C19).

Absolute cell references
If you have not already done so, make the reference to cell C19 in the formula you have just entered into cell D4, an absolute cell reference (hint $).
It needs to be an absolute reference because the formula will need to be replicated to the remainder of the cells in the column later in the task.

Insert a formula in cell E4
In cell E4, create a formula or use the SUM function to calculate the Adult Ticket Price for
2011. You will need to add the 5% increase (in cell D4) to the 2010 price (in cell C4).

Autofill
Use Autofill to copy the formula in cell D4 to cells D5:D15 and the formula in E4 to cells
E5:E15.

Help if you need it!
Spreadsheet Information Pack
Page
Absolute cell references