ECDL Module AM 4 Version 2.0 (Spreadsheets - Advanced)
AM4.8 - Sample Exercise
Your tasks are based on analysing a spreadsheet for a company Vroom Europe that sells
car engine parts all over Europe. Amongst other things you are asked to review sales data
using a pivot table, to modify chart layout, to link data and to perform calculations on the
data using various functions, before presenting the spreadsheet to your Managing Director.
There are 20 tasks in this sample exercise with 5 marks available
available for each question.
1.
Tasks
Marks
Open the template called vroom profit.xlt from your Learner Drive.
Drive
[3 Marks]
Edit the template by changing the content of cell A1 from vroom Plc
to Vroom Europe and correct the formula in cell F18 so that cell F17
is subtracted from cell F9.
F9
Save and close the revised template to your Learner Drive using the
same filename vroom profit.xlt.
2.
3.
Open the file called yearly forecast.xlt from your Learner Drive.
Drive On
the year # worksheet change the content of cell B10 to Eire, also
lso enter
the year 2010 into cell C1. Save as 2010 forcast.xls on your Learner
Drive and close.
[2 Marks]
Open the file called vroom from your Learner Drive. Find the sales
worksheet. Apply conditional formatting to the cell range C2:C18 as
follows:
[5 Marks]
Cell value (€M)
Cell Shading Colour
Less than 30
Red
30 and over
Any light blue colour
Find the payroll worksheet.
[3 Marks]
Simultaneously sort the cell range A5:E41 by Department in
ascending order, then by Salary € in descending order.
[2 Marks]
Apply an autofilter so that only the Sales or Personnel Departments
are shown.
4.
Continue using the payroll worksheet.
[5 Marks]
Enter
nter an appropriate criterion in cell range A3:E3 and a database
function in cell C43, that will calculate the average salary for the Sales
department,, format the result to 0 decimal places as € (Euros).
(Euros) Save
file keeping the same filename vroom.
ECDL ADVANCED SAMPLE TEST SYLLABUS 2
KATHY ROSSER Updated by D Russell October 2010
20
PAGE 1 OF 4
ECDL Module AM 4 Version 2.0 (Spreadsheets - Advanced)
5.
Find the special worksheet.
[1 Mark]]
Remove the vertical split.
[4 Marks]
Hide row 112 containing the text beginning Amended by Kathy
Rosser. Unhide any hidden worksheets.
6.
Find the special worksheet.
[2 Marks]
Add a function to cell C108 that will count the number of No Sales
figures in cell range C3:C102 that are greater than 5 (€ m).
7.
Add a function to cell A110 that will count the number of blank cells in
the cell range C3:C102
[3 Marks]
Continue using the special worksheet
[5 Marks]
Protect the cell range A104:C108 using the password safe.
Ensure that all other cells are available for data entry.
8.
Find the Report worksheet.
[2 Marks]
Add a function in cell C1 that displays today’s date and the time so
that it automatically updates,
updates widen column to show all information.
Add a function in cell B3 to show the month number from A3.. Copy to
cell range B4:B102.. Save the workbook with the same filename to
your Learner Drive.
9.
Find the total salaries worksheet.
[3 Marks]
[2 Marks]
Insert a link in cell C5 from cell C10 on the directors workbook to show
the total directors’ salaries. Ensure the chart in E5 is updated to reflect
this change.
Amend the 3-D
D exploded pie chart starting in cell E5 to show a pie
explosion of 30%
10.
Continue using the total salaries worksheet.
[3 Marks]
[3 Marks]
Add the data in cells B11:C11
B11:
to the existing chart beginning at B24.
Change the data labels on the Y axis so that they display the data units
in thousands.. Change the
t value axis title so that it displays vertically.
vertically
Format the data labels so that they appear at the outside end of the
bars. Format the bars to display the image Car Parts from your
Learner Drive.
ECDL ADVANCED SAMPLE TEST SYLLABUS 2
[2 Marks]
KATHY ROSSER Updated by D Russell October 2010
20
PAGE 2 OF 4
ECDL Module AM 4 Version 2.0 (Spreadsheets - Advanced)
11.
12.
Find the charting worksheet and use menu commands to transpose
the cell range A2:G4 to begin at cell A6.
[4 Marks]
In cell A14 type the menu commands you used to transpose the range.
[1 Mark]
Find the payroll statistics worksheet.
[3 Marks]
In the cell range A5:E41 use a menu command to subtotal by sum the
Salary € column after each change in Department.
Collapse the subtotalled data so that only the Department subtotals
and Grand Total are displayed. Save the workbook keeping the same
filename.
13.
Find the salespersons
s worksheet.
[2 Marks]
[5 Marks]
Use
se a lookup function in cell B4 to display the Chief Salesperson for
each country. Copy down to the range B5:B20.
14.
Find the pivot worksheet
[5 Marks]
Create a pivot table from the cell range A2:D102 starting at cell F6.
Show Country as the page field, Car Part no ID as the row fields and
Sales (€ M) as the data items to sum. Save and close the workbook
keeping the same filename.
15.
Find the France worksheet
[5 Marks]
Enter a 3-D Sum function in cell
c C2 to total the cells C2 on the jul, aug,
sep, oct, nov, dec worksheets.
16.
Find the sales worksheet
[4 Marks]
Insert a function in cell D2 that displays the appropriate comment about
the sales in cell C2 using the information provided in cells F1:G3
G3, using
the named cells low and acc and the named cell range data.
Copy the formula down to cover the range D3:D18.
[1 Mark]
Hide columns F and G.
17.
Find the regional scenarios worksheet
[2 Marks]
Show the best scenario.
Create a summary report.
[3 Marks]
ECDL ADVANCED SAMPLE TEST SYLLABUS 2
KATHY ROSSER Updated by D Russell October 2010
20
PAGE 3 OF 4
ECDL Module AM 4 Version 2.0 (Spreadsheets - Advanced)
18.
Continue using the regional scenarios worksheet
[5 Marks]
Record a macro named footer that inserts the file path and filename
in the left of the footer.. Accept the default settings.
Save and close the spreadsheet, keeping the same filename.
19.
Open the forecast 2006.xls workbook from your Learner Drive without
updating the links.
[2 Marks]
Break all links.
In cell E4 insert a hyperlink to the salespersons worksheet in the
vroom.xlsx workbook..
[3 M\arks]
Save and close the forecast 2006 file with the same filename.
20.
Open the file called Directors salary 2009.xlss
2009.xls from your Learner
Drive.
[5 Marks]
Compare and merge the copy of Directors salary 2009 file into the
Directors salary 2009 file.
Save and close ALL files
file and close the spreadsheet application.
Total Marks.
Marks
100
This is the end of the practice test.
If you have time, check the work you have done.
ECDL ADVANCED SAMPLE TEST SYLLABUS 2
KATHY ROSSER Updated by D Russell October 2010
20
PAGE 4 OF 4