European Computer Driving Licence
|
|
European Computer Driving Licence |
|
 |
|
|
| Back |
Module 4 Sample Test 1 (syllabus version 4)
|
 |
| In this test you are asked to prepare a
spreadsheet to record sales of books in various categories for the first
quarter of the year. Your manager has provided you with some files with
which to prepare the sales figures, in which you are required to carry
out various formatting actions and to make calculations before
presenting the spreadsheet ready for presentation to the Board. You will
need to make sure you use the most efficient formulae and functions.
You will need to download a spreadsheet file to your candidate disk before you start the test. Right click here and save the file books.xls to your candidate disk.
|
|
Tasks |
|
|
1. Most of the tasks you undertake are performed on the file books.xls which should be opened. |
(1 Mark) |
|
2. Zoom the display on the Sales worksheet to 100%. |
(1 Mark) |
|
3. Widen the columns in the Sales worksheet so the data is fully visible. |
(1 Mark) |
|
4. Cells B3:H3 should be aligned to the right. |
(1 Mark) |
|
5. There is some data omitted from the spreadsheet and there are some errors: The paperback sales for December should be entered as 750. The figure for Gardening book sales in August has been wrongly entered. Please change the value to 85. |
(1 Mark) |
|
6. Enter the most appropriate formula / function in cell H4 to calculate the total for the cell range B4:G4. |
(1 Mark) |
|
7. Replicate the formula you have used to the cell range H5:H14. |
(1 Mark) |
|
8. Enter and replicate a formula to calculate the totals for each month in cell range B15:H15. |
(1 Mark) |
|
9. In order to calculate the percentage that each category of sales contributes to the total sales, you are required in cell I4 to divide cell H4 by cell H15. This formula should then be replicated to the cell range I5:I14. |
(2 Marks) |
|
10. You should now format the cell range I4:I14 as a percentage with no decimal places. |
(1 Mark) |
|
11. You now need to calculate the Sales less Cost of Sales for each month. Enter a formula in cell B19 and replicate this to the cells C19:H19. |
(1 Mark) |
|
12. Based on your calculations, you need to ascertain if the profit is sufficient to install a new computer system. Enter a formula/function in cell B21 that refers to cell A25 if the number in cell H19 is greater than 1000 or otherwise refers to cell A26. |
(2 Marks) |
|
13. Format cell A1 on the Sales worksheet to 14 point Bold Blue. |
(1 Mark) |
|
14. In order to improve the presentation you should add an outline border around the cells A3 to I21. |
(1 Mark) |
|
15. The cell range B3:H19 needs formatting to £ currency with no decimal places. |
(1 Mark) |
|
16. Apply settings so that the gridlines and the row and column headings would be displayed when printed. Save the spreadsheet but do not print at this stage. |
(1 Mark) |
|
17. Create a clustered column chart from the data contained in cell ranges A3:G3 and A15:G15. Add the title Monthly Book Sales to your chart. |
(1 Mark) |
|
18. Insert a new worksheet into the spreadsheet after the Sales worksheet and before the Paperbacks worksheet. Change the name of the new worksheet to Chart. |
(1 Mark) |
|
19. Return to the Sales worksheet and move the chart to the Charts worksheet. Save the spreadsheet at this point. |
(1 Mark) |
|
20. You are now required to apply various formatting actions to the other worksheets in the books spreadsheet file. On the Paperbacks worksheet apply settings so that the titles on row 3 would automatically print at the top of each page. Do not print the worksheet. |
(1 Mark) |
|
21. In cell B70, enter a formula to count the number of paperbacks sold. |
(1 Mark) |
|
22. The text Paperback sales should be inserted into the header of the Paperbacks worksheet. A footer with your own name should also be inserted. Please ensure your file is saved at this point. |
(1 Mark) |
|
23. Please copy the Paperbacks worksheet only into a new spreadsheet document. This file should be saved in template format on your candidate disk as PBSales. Close the PBSales file. |
(2 Marks) |
|
24. On the New media worksheet you should sort the cell range A4:B8 into alphabetical order of item. |
(1 Mark) |
|
25. Again, using the New media worksheet, please change the bar chart to a pie chart. |
(1 Mark) |
|
26. Add percentage labels to the segments of the pie chart. . |
(1 Mark) |
|
27. There is an error in the formula in cell B10 of the New media worksheet. Locate and correct the error. |
(1 Mark) |
|
28. Return to the Sales worksheet. Without adjusting the font size or margins, apply a setting so that the contents of the worksheet would print on 1 page. |
(1 Mark) |
|
29. Please print one copy of the Sales worksheet. Do not print any other sheets. Save all open spreadsheets and close the spreadsheet application. |
(2 Marks) |
|
Total Marks |
(32 Marks) |
Module 4 Sample Test 2 (syllabus version 4)
| In this test you are asked to create a
spreadsheet to record sales of a pizza delivery company for the second
quarter of the year. Your manager has provided you with some files with
which to prepare the sales figures, in which you are required to carry
out various formatting actions and to make calculations before
presenting the spreadsheet ready for presentation to the Board. You will
need to make sure you use the most efficient formulae and functions.
You will need to download a number of files to your candidate disk before you start the test.
|
|
Tasks |
|
|
1. Most of the tasks you undertake are performed on the file pizzas.xls which should be opened. |
(1 Mark) |
|
2. In the Income worksheet, widen the columns so the data is fully visible. |
(1 Mark) |
|
3. Cells B4:H4 should be aligned to the right. |
(1 Mark) |
|
4. There is some data omitted from the spreadsheet and there are some errors: The sales for Venetia in April should be entered as 350. The figure for sales of Margaritas in May has been wrongly entered. Please change the value to 600. |
(1 Mark) |
|
5. Enter the most appropriate formula/function in cell E5 to calculate the total for the cell range B5:D5. |
(1 Mark) |
|
6. Replicate the formula you have used to the cell range E6:E10. |
(1 Mark) |
|
7. Enter a formula in E11 to calculate the total sales in the three months. |
(1 Mark) |
|
8. In cell G5, enter a formula to calculate the total income for Margarita pizzas using the price in cell F5. |
(1 Mark) |
|
9. Replicate the formula you have used to the cell range G6:G10. |
(1 Mark) |
|
10. Enter a formula in G11 to calculate the total income for the three months. |
(1 Mark) |
|
11. The cell range G5:G11 needs formatting to £ currency with no decimal places. Widen cells as required. |
(1 Mark) |
|
12. In order to calculate the percentage that each type of pizza contributes to the total income, you are required in cell H5 to divide cell G5 by cell G11. This formula should then be replicated to the cell range H6:H10. |
(2 Marks) |
|
13. You should now format the cell range H6:H10 as a percentage with no decimal places. |
(1 Mark) |
|
14. Based on your calculations, you now need to find out if the income is sufficient to buy a new van. Managers have a target of £30,000 income for the quarter. Enter a formula/function in cell D15 that refers to cell A17 if the number in cell G11 is greater than the target income or otherwise refers to cell A18. |
(2 Marks) |
|
15. On the Income worksheet, format the heading in cell A1 to Arial 12 point Bold Italic. |
(1 Mark) |
|
16. In order to improve the presentation you should add an outline border around the cells A4 to G11. |
(1 Mark) |
|
17. The text Quarterly Pizza Sales Income should be inserted into the header of the Income worksheet. A footer with your own name should also be inserted. Please ensure your file is saved at this point. |
(1 Mark) |
|
18. Apply settings so that the gridlines and the row and column headings would not be displayed when printed. Save the spreadsheet but do not print at this stage. |
(1 Mark) |
|
19. Copy the income figures in cells A4:G11 to the Sheet3 worksheet, starting at cell C3. |
(1 Mark) |
|
20. Return to the Income worksheet. Create a pie chart from the data contained in cell ranges A5:A10 and G5:G10. Add the title Pizza Income to your chart. Display percentage labels on each segment. Move the chart so it does not hide any of the data in the worksheet. |
(2 Marks) |
|
21. Copy the chart to the Sheet3 worksheet. Place it under the Income figures. |
(1 Mark) |
|
22. Change the name on the Sheet3 worksheet tab to Pres |
(1 Mark) |
|
23. Again, using the Pres worksheet, please change the pie chart to a 3D bar chart. |
(1 Mark) |
|
24. Please open the spreadsheet Summary.xls and copy the information on the Pres worksheet only into the Quarter2 worksheet. Save and close the Summary spreadsheet. |
(1 Mark) |
|
25. On the Vans worksheet please format the titles on row 3 so they are displayed diagonally in the cells. |
(1 Mark) |
|
26. On the Vans worksheet format the heading in cell A1 so it is centred across columns A to G. |
(1 Mark) |
|
27. On the Vans worksheet please sort the cell range A4:G7 by ascending order of Date purchased. |
(1 Mark) |
|
28. Return to the Income worksheet and change the settings so the worksheet will print in Landscape format. |
(1 Mark) |
|
29. Please print one copy of the Income worksheet. Do not print any other sheets. Save all open spreadsheets and close the spreadsheet application. |
(1 Mark) |
|
Total Marks |
(32 Marks) |