information technology 0418/03 - ict & … technology 0418/03 paper 3 practical test ... website...

UNIVERSITY OF CAMBRIDGE INTERNATIONAL EXAMINATIONS

International General Certificate of Secondary Education

INFORMATION TECHNOLOGY 0418/03

Paper 3 Practical Test

May/June 2006

2 hours 45 minutes

Additional Materials: Candidate Source Files

READ THESE INSTRUCTIONS FIRST

Make sure that your Centre number, candidate number and name are clearly visible on each printout,

before it is sent to the printer.

Carry out every instruction in each task.

Tasks are numbered on the left hand side of the page, so that you can see what to do, step by step. On

the right hand side of the page for each task, you will find a box which you can tick (!) when you have

completed the task; this checklist will help you track your progress through the assignment.

Before each printout you should proof-read the document to make sure that you have followed all

instructions carefully.

At the end of the assignment put all your printouts into the Assessment Record Folder.

This document consists of 10 printed pages.

IB06 06_0418_03/6RP

© UCLES 2006

[Turn over

2

© UCLES 2006 0418/03/M/J/06

DATA ANALYSIS

You work for a gym equipment company called Gymnastic. Your manager has asked you to calculate

the current stock position for stock items.

All currency values should be in dollars with the $ sign visible.

!

1 Create a data model which looks like this:

12.1.1

Information Table

Mark-up 0.05

0.07

Number of stock items

Small

Large

Date Item Size Purchase Increase Sale

24/03/2006 Basic exercise bike

30/03/2006 Cross trainer

06/04/2006 Multi gym

15/04/2006 Treadmill

19/05/2006 Rowing machine

20/05/2006 Trampoline

26/05/2006 Benches

02/06/2006 Punch bags

02/06/2006 Weights

07/06/2006 Elliptical cross

trainers

17/06/2006 Step trainer

22/06/2006 Press

The cells in these columns will represent:

Date Date of item in stock (shown above in

dd/mm/yyyy format, you may use mm/dd/yyyy

if you prefer)

Item The type of gym equipment

Size Whether a large item or a small item

Purchase The price paid for each item

Increase The value added to each item based on the

Purchase price. If the Purchase price is

greater than or equal to 300, then the increase

is 7%. Otherwise the increase is 5%.

Sale The price each item is sold at including the

Increase

Information Table

Mark-up The percentage increase added to all items

Number of stock items Count of the number of items.

3

© UCLES 2006 0418/03/M/J/06 [Turn over

!

2 In the Information Table name the cell that holds the data 0.05 five

Name the cell that holds the data 0.07 seven

These named cells will be used to calculate the Increase

12.1.5

6 In the Information Table use COUNTIF to count the number of items

where the Size is Small. Place the result in the cell to the right of the

heading Small.

In the Information Table use COUNTIF to count the number of items

where the Size is Large. Place the result in the cell to the right of the

heading Large.

12.1.4

7 Format the cells in the Date column to give the month in word format, (for

example: May 19, 2006 or 19 May 2006).

14.1.1

8 Format the cells in the Purchase, Increase, and Sale columns to display

the $ sign (dollar) with 2 decimal places.

14.1.1

9 Copy down all formulae entered in steps 3 and 4 so that 12 rows of data

can be entered.

12.1.1

10 Set your page orientation to landscape.

14.3.1

11 Save the data model and print a copy of the sheet showing the formulae

used. Make sure that the contents of all cells are visible and that the

printout fits onto a single printed page.

15.1.1

14.2.1

3 In the main table in the cell under Increase, enter a formula using IF. This

formula calculates the mark-up on the first item.

If the Purchase is greater than or equal to 300 then multiply the Purchase

by the named cell seven to calculate the Increase

If the Purchase is less than 300 then multiply the Purchase by the named

cell five to calculate the Increase

12.1.4

4 In the main table in the cell under Sale enter a formula which adds the

Increase to the Purchase

12.1.3

5 In the Information Table format the cells containing the data 0.05 and

0.07 to display the % value with 0 decimal places (for example 5%).

14.1.1

4

© UCLES 2006 0418/03/M/J/06

!

12 Enter the following data into the model to test that it works correctly:

12.1.2

12.2.1

Date Item Size Purchase

March 24, 2006 Basic exercise bike Small 94

March 30, 2006 Cross trainer Large 295

April 6, 2006 Multi gym Large 449

April 15, 2006 Treadmill Large 839

May 19, 2006 Rowing machine Small 519

May 20, 2006 Trampoline Large 349

May 26, 2006 Benches Small 139

June 2, 2006 Punch bags Small 258

June 2, 2006 Weights Small 79

June 7, 2006 Elliptical cross trainers Large 444

June 17, 2006 Step trainer Small 45

June 22, 2006 Press Small 88

13 Save this data and print a copy showing the values. Make sure that the

contents of all cells are visible and that the printout fits onto a single

printed page.

15.1.1

14.2.1

14 Produce a printout showing only the rows where the Size is Large

13.1.1

15.1.1

15 Produce a printout from all the data showing only the rows where the

Date is after 1 June 2006 and the Purchase is greater than 150

13.1.1

15.1.1

5

© UCLES 2006 5197/B/01/06 [Turn Over

WEBSITE AUTHORING

Your manager has asked you to prepare web pages for a gym equipment company called

Gymnastic. These pages will give information about the company’s products.

!

1 Download the following files from

http://www.hothouse-design.co.uk/2006webb to your own work area:

SWAD6INT.HTM

SWAD6GYM.HTM

SWAD6MEN.HTM

SWAD6GYM.JPG

SWAD6ICO.JPG

SWAD6ROW.JPG

16.1.1

16.2.1

2 Using a suitable software package, prepare the following styles for use

within all pages on this website:

• h1 – dark green, sans-serif font (e.g. Arial), largest (e.g. 45 point), bold,

centered

• h2 – light green, serif (e.g. Times New Roman), bold, smaller (e.g. 18

point), left aligned

• h3 – light green, sans-serif (e.g. Arial), smallest (e.g. 14 point), left

aligned

Save the stylesheet and attach it to each web page as you create it.

17.1.1

17.1.2

17.1.3

3 Make sure that the stylesheet will work in any browser.

Print a copy of the stylesheet that has to be attached to each page as

html source code.

Make sure that your name is printed on this page.

21.1.1

4 Using a suitable software package, create a new homepage

SWAD6HM.HTM

This page will have menu options across the top and a table below the

menu. It should look like this:

18.1.1

5 Enter the heading GYMNASTIC

Set the text to style h1.

17.1.4

6 Place the contents of SWAD6MEN.HTM below the heading GYMNASTIC

to create the menu. This table must have no visible borders. Set the text

to style h2.

18.1.2

17.1.4

7 Create a hyperlink for the menu item Rowing Machine to point to the file

SWAD6GYM.HTM which should open in a new window called

EXTERNAL

Note that the web page which you have linked to is not yet complete.

18.2.1

18.2.2

6

© UCLES 2006 0418/03/M/J/06

!

8 Below the menu table, create a new table which has 5 rows and 2

columns.

19.1.1

9 Merge the cells to create the table as shown below.

19.1.3

A B

C D

E

F

G

10 Set a 4 point border size for the table.

19.1.2

11 Using the contents of the file SWAD6INT.HTM

• place the text What we do into cell A and format this as style h2

• place the text Our company sells…retail prices. into cell B and format

this as style h3

• place the text We keep the…is on-line. into cell C and format this as

style h3

• place the text You can contact… different media. into cell D and format

this as style h3

• place the text Through our… on this website. into cell E and format this

as style h3

• place the text In all cases… of our services. into cell G and format this

as style h2.

17.1.4

12 Import the image SWAD6GYM.JPG and place it in cell F.

Set the width to 500 pixels and maintain the aspect ratio.

Make sure that the whole image is visible.

Centre align the image.

20.1.1

20.1.2

20.2.1

13 Make sure that you have attached the stylesheet to this page and save

the page as SWAD6HM.HTM

Make sure your name is displayed on each page. Print this page as it is

viewed in your browser. Print a copy of the HTML source.

17.1.4

21.1.1

14 Open the file SWAD6ICO.JPG in a suitable application. Change its size

to 35 pixels wide and maintain the aspect ratio. Ensure that the colour

depth is 256 colours (8 bit colour) and save the file as SWAD6ICO.GIF

20.2.1

20.2.2

15 Open the file SWAD6GYM.HTM

Import the image SWAD6ROW.JPG into the right cell of the table.

Resize the image SWAD6ROW.JPG to 300 pixels wide and maintain the

aspect ratio.

20.1.1

20.1.2

20.2.1

20.2.2

7

© UCLES 2006 5197/B/01/06 [Turn Over

!

16 Replace the text click here (at the bottom of the page) with the image

SWAD6ICO.GIF

Make this a link (in the same window) to the file SWAD6HM.HTM

18.2.1

18.2.2

20.1.1

17 Make sure that you have attached the stylesheet to this page.

Save the page as SWAD6GYM.HTM

Make sure your name is displayed on each page. Print this page as it is

viewed in your browser. Print a copy of the HTML source.

17.1.4

21.1.1

8

© UCLES 2006 0418/03/M/J/06

PRESENTATION AUTHORING

Your manager has asked you to set up a presentation for an educational organisation, for students

who study business and academic subjects, called The College.

The medium for delivery will be a multimedia projector. Presenter notes need to be developed with

the slides.

!

1 Set up a new presentation consisting of 3 slides. The slide master must have a

light green background and your name and today’s date in small black text

placed in the top left corner. It must also contain clipart as a logo placed in the

top right corner. The logo must be resized so that it does not overlay any slide

text.

22.1.1

22.3.1

Set the following styles of text throughout the entire presentation: 2

Heading:

Subheading:

Bulleted list:

Dark blue, right aligned, large font (between 50 and 68 point)

Bright blue, centred, medium font (between 36 and 48 point)

Dark green, left aligned, small font (between 16 and 34 point)

Use a bullet of your choice.

22.1.1

On the first slide:

3 Enter the heading The College

22.2.1

4 Enter the subheading Student enrolments below the heading.

22.2.1

5 Add the following presenter notes to this slide:

Introduce yourself and your role within the college.

22.2.1

9

© UCLES 2006 5197/B/01/06 [Turn Over

On the second slide:

!

6 Enter the heading Department enrolments 2006 in the same style as the first

slide.

22.2.1

7 Enter the following list on the left side of the slide, as shown below:

" Business/IT

" Catering

" Art & Design

" Science

" Technology

22.2.1

8 Produce a vertical bar chart from the following data: Place this chart on the right

side of the slide. Make sure that you include a legend showing the subjects.

22.3.2

Product $

Business/IT 350

Catering 275

Art & Design 196

Science 371

Technology 215

9 Draw a thick dark blue vertical line between the graph and the text with an arrow

on both ends.

22.3.3

10 The bullets should be set to appear one at a time.

23.2.1


We have a wide variety of courses on offer. Call in and speak to our

enrolment department.

22.2.1

10

Permission to reproduce items where third-party owned material protected by copyright is included has been sought and cleared where possible. Every

reasonable effort has been made by the publisher (UCLES) to trace copyright holders, but if any items requiring clearance have unwittingly been included, the

publisher will be pleased to make amends at the earliest possible opportunity. University of Cambridge International Examinations is part of the Cambridge Assessment Group. Cambridge Assessment is the brand name of University of

Cambridge Local Examinations Syndicate (UCLES), which is itself a department of the University of Cambridge.

© UCLES 2006 0418/03/M/J/06

On the third slide:

!

12 Enter the heading Student activities in the same style as the first slide.

22.2.1

13 Enter the following list on the left side of the slide:

" Keep fit

" Basketball

" Football

" Gymnasium

" Reading club

22.2.1

14 Place a further clipart image showing sports e.g. football, on this page to the

right of the bullets.

22.3.1

Add the following presenter notes to this slide:

The college offers many extra curricular activities.

Why not call in and discuss your needs?

22.2.1

For the whole presentation:

15 Use the same transitional effect between each slide.

23.1.1

16 Use only one animation effect on the title of each slide.

23.2.1

17 Save the presentation using a new filename. Print the presentation in notes

format showing all presenter notes.

24.1.1

(Please note – your tutor will need to assess the transitions and animations before sending in your

work.)

After the examination time

On your html printout highlight those portions of the code which show that:

The external stylesheet is attached to each webpage

The table borders are set to 0 and 4 points

SWAD6GYM.JPG is resized to 500 pixels wide

SWAD6GYM.JPG is centre aligned

The hyperlink from Rowing Machine opens SWAD6GYM.HTM in a new window called

EXTERNAL

SWAD6ROW.JPG is resized to 300 pixels wide

SWAD6ICO.JPG has been changed to .gif format

SWAD6ICO.GIF is resized to 35 pixels

SWAD6ICO.GIF hyperlinks to SWAD6HM.HTM




Paper 3 Practical Test

October/November 2006

2 hours 45 minutes



Make sure that your Centre number, candidate number and name are clearly visible on every printout,

before it is sent to the printer. Printouts with handwritten information will not be marked.









IB06 11_0418_03/7RP

© UCLES 2006

[Turn over

2

© UCLES 2006 0418/03/O/N/06

DATA ANALYSIS

You work for a electrical goods retail company called Dygitell. Your manager has asked you to

calculate the current stock position for stock items.

All currency values should be in dollars with the $ sign visible.

!

1 Create a data model which looks like this:

12.1.1

Information Table

Manager's Discount M .06

Clearance Discount C .03

Number of sales

Kitchen

Household

Date Product Offer Type Price Discount Value

15/02/06

24/03/06

09/04/06

27/04/06

30/05/06

08/06/06

21/07/06

21/07/06

26/07/06

01/08/06

14/08/06

25/08/06

The cells in these columns will represent:

Date Date of product in stock (shown above in

dd/mm/yyyy format, you may use mm/dd/yyyy

if you prefer)

Product The type of product

Offer The type of discount to be applied to the

product

Type The type of product on sale

Price The price of the product before discount

Discount The value is taken away from the Price based

on the Offer. If the Offer is M, then the

discount is 6%. If the Offer is C then the

discount is 3%.

Value The price each item is sold at, after taking off

the discount

Information Table

Manager’s Discount The percentage discount to be taken away

from the product if the Offer equals M

Clearance Discount The percentage discount to be taken away

from the product if the Offer equals C

Number of sales Count of the number of items.

3

© UCLES 2006 0418/03/O/N/06 [Turn over

!

2 In the Information Table name the cell that holds the data 0.06 six

Name the cell that holds the data 0.03 three

These named cells will be used to calculate the Discount

12.1.5

6 In the Information Table use COUNTIF to count the number of products

where the Type is Kitchen. Place the result in the cell to the right of the

heading Kitchen.

In the Information Table use COUNTIF to count the number of products

where the Type is Household. Place the result in the cell to the right of

the heading Household.

12.1.4

7 Format the cells in the Date column to give the month in word format, (for

example February 02, 2006 or 02.Feb 2006).

14.1.1

8 Format the cells in the Price, Discount, and Value columns to display the

$ sign (dollar) with 2 decimal places.

14.1.1

9 Copy down all formulae entered in steps 3 and 4 so that 12 rows of data

can be entered.

12.1.1

10 Set your page orientation to landscape.

14.3.1

11 Save the data model and print a copy of the sheet showing the formulae

used. Make sure that the contents of all cells are visible and that the

printout fits onto a single printed page.

15.1.1

14.2.1

3 In the main table in the cell under Discount, enter a formula using IF.

This formula calculates the discount on the first item.

If the Offer equals M then multiply the Price by the named cell six to

calculate the Discount

If the Offer equals C then multiply the Price by the named cell three to

calculate the Discount

12.1.4

4 In the main table in the cell under Value enter a formula which takes

away the Discount from the Price

12.1.3

5 In the Information Table format the cells containing the data 0.06 and

0.03 to display the % value with 0 decimal places (for example 6%).

14.1.1

4

© UCLES 2006 0418/03/O/N/06

!

12 Enter the following data into the model to test that it works correctly:

12.1.2

12.2.1

Date Product Offer Type Price

15-Feb-2006 Washer M Kitchen 350

24-Mar-2006 Iron M Kitchen 25

9-Apr-2006 Television C Household 1050

27-Apr-2006 Dryer C Kitchen 150

30-May-2006 Kettle C Kitchen 15

8-Jun-2006 Radio C Household 34

21-Jul-2006 Washer M Kitchen 550

21-Jul-2006 Oven M Kitchen 650

26-Jul-2006 Washer C Kitchen 421

1-Aug-2006 Television C Household 624

14-Aug-2006 Radio C Household 28.50

25-Aug-2006 Television M Household 624

13 Save this data and print a copy showing the values. Make sure that the

contents of all cells are visible and that the printout fits onto a single

printed page.

15.1.1

14.2.1


Type is Kitchen

13.1.1

15.1.1


Date is before 1 July 2006 and the Value is less than 30

13.1.1

15.1.1

5


WEBSITE AUTHORING

Your manager has asked you to prepare web pages for an educational organisation called The

College. These pages will give information about the college and its courses.

!


http://www.hothouse-design.co.uk/2006web to your own work area:

SWAA6FLX.HTM

SWAA6INT.HTM

SWAA6MEN.TXT

SWAA6HOMJPG

SWAA6ICO.JPG

SWAA6FLX.JPG

16.1.1

16.2.1

17 Using a suitable software package, prepare the following styles for use

within all pages on this website:

• H1 – black, serif font (e.g. Times New Roman), largest (e.g. 45 point),

bold, centred

• H2 – bright blue, sans-serif (e.g. Arial), bold, italic, smaller (e.g. 18

point), left aligned

• H3 – dark blue, sans-serif (e.g. Arial), smallest (e.g. 14 point), left

aligned

Save the stylesheet and attach it to each web page as you create it.

Make sure the stylesheet will work in any web browser

17.1.1

17.1.2

17.1.3

18 Print a copy of the stylesheet that is to be attached to each page as html

source code. Make sure your name is printed on this printout.

21.1

19 Using a suitable software package, create a new homepage

COLHOME.HTM

This page will have a heading at the top, menu options on the left and a

text/graphics area on the right. It should look like this:

18.1.1

20 Enter the heading THE COLLEGE as style H1.

17.1.4

21 Place the contents of SWAA6MEN.TXT down the left side of the page to

create the menu options in style H2.

18.1.2

17.1.4

22 Create a hyperlink for the item Flexibly learning to point to the file

SWAA6FLX.HTM which should open in a new window called

EXTERNAL


18.2.1

18.2.2

6

© UCLES 2006 0418/03/O/N/06

!

23 In the text/graphics area on the right, create a table which has 6 rows and

2 columns,

19.1.1

A

B C

D E

F G

H I

J K

24 Merge the top two cells of the table as shown A.

19.1.3

25 Set a 6 point border for the table.

19.1.2

26 Use the contents of the file SWAA6INT.HTM:

• place the heading Business/IT into cell B and format this as style H2

• place the text which starts the courses offered here are:

Administration...into cell C and format this as style H3

• place the heading Catering into cell D and format this as style H2

• place the text which starts The courses offered here are:

Accommodation …into cell E and format this as style H3

• place the heading Art & Design into cell F and format this as style

H2

• place the text which starts The courses offered here are: Art &

Design…into cell G and format this as style H3

• place the heading Science into cell H and format this as style H2

• place the text which starts The courses offered here are: Animal

Care…into cell I and format this as style H3

• place the heading Technology into cell J and format this as style H2

• place the text which starts The courses offered here are:

Brickwork…into cell K and format this as style H3

17.1.4

27 Import the image SWAA6HOM.JPG and place it in the merged cell A.

Set the height to 150 pixels.

Do not maintain the aspect ratio. Make sure that the whole image is

visible.

20.1.1

20.1.2

20.2.1

28 Make sure that you have attached the stylesheet to this page and save it

as COLHOME.HTM

Print this page as it is viewed in your browser. Print a copy of the HTML

source.

17.1.4

21.1.1

29 Open the file SWAA6ICO.JPG in a suitable application. Change its size

to 35 pixels wide and maintain the aspect ratio. Ensure that the colour

depth is 256 colours (8 bit colour) and save the file as SWAA6ICO.GIF

20.2.1

20.2.2

30 Open the file SWAA6FLX.HTM

Import the image SWAA6FLX.JPG to the right cell of the table.

Place this image to the right of the text.

20.1.1

20.1.2

7


!

31 Replace the text click here (at the bottom of the page) with the image

SWAA6ICO.GIF

Make this a link (in the same window) to the file COLHOME.HTM

18.2.1

18.2.2

20.1.1

32 Make sure that you have attached the stylesheet to this page and save it

as SWAA6FLX.HTM

Print this page as it is viewed in your browser. Print a copy of the HTML

source.

17.1.4

21.1.1

8

© UCLES 2006 0418/03/O/N/06

PRESENTATION AUTHORING

Your manager has asked you to set up a presentation for an electrical goods company called Electry.

The medium for delivery will be a multimedia projector. Presenter notes need to be produced with the

slides.

!

33 Set up a new presentation consisting of 3 slides. The slide master must

have a very light green background and your name and today’s date in

small black font placed in the top right corner. It must also contain clipart

of an item of electrical equipment for the kitchen as a logo placed in the

bottom right corner.

The logo must be resized so that it will not overlay any text or graphics on

any slide.

Include the slide number in the centre of the footer.

22.1.1

22.3.1

Set the following styles of text throughout the entire presentation: 34

Heading:

Subheading:

Bulleted list:

Dark green, right aligned, large font (between 50 and 72

point)

Bright green, centre aligned, medium font (between 36

and 46 point)

Bright green, left aligned, small font (between 16 and 32

point). Use a bullet of your choice.

22.1.1

On the first slide:

35 Enter the heading Electry

22.2.1

36 Enter the subheading Suppliers of quality white goods below the

heading.

22.2.1


Introduce yourself and your role within the company.

22.2.1

9



!

38 Enter the heading Price of larger white goods in the same style as the

first slide. 22.2.1

39 Enter the following bulleted list on the right side of the slide.

! Freezer

! Fridge

! Fridge/Freezer

! Oven

! Tumble Dryer

! Washing Machine

22.2.1

40 Create a pie chart from the following data:

22.3.2

Place the chart on the left side of the slide. Make sure that you include a

legend.

Each slice must show the currency value in $ (dollars) to 0 decimal

places.

41 Add a thick dark green vertical line between the bulleted list and the

chart.

22.3.3

42 The bullets should be set to appear one at a time.

23.2.1


Our range of white goods excels. Visit our website

www.electry.com

22.2.1

White goods $

Freezer 127.99

Fridge 205.99

Fridge/Freezer 498.99

Oven 589.99

Tumble Dryer 185.99

Washing Machine 404.99

10





© UCLES 2006 0418/03/O/N/06

On the third slide:

!

44 Enter the heading Our warranty in the same style as the first slide.

22.2.1

45 Enter the following bulleted list on the left side of the slide:

! 20 days return if not completely satisfied

! Free delivery within a radius of 20 miles

! Free removal of old goods on delivery of new

22.2.1

46 Place a different clipart image showing an item of electrical equipment for

a kitchen. Place this to the right of the bulleted list.

22.3.1


Outside a radius of 20 miles we only charge a small fee, for example

$5 per 10 miles.

Visit our showroom or our website and take advantage of our low

prices.

22.2.1



23.1.1

49 Use the same animation effect on the title of each slide.

23.2.1

50 Save the presentation using a new filename. Print the presentation

showing all presenter notes.

24.1.1

(Please note – your tutor will need to assess the transitions and animations before sending in your

work.)



The external stylesheet is attached to each webpage

The table borders is set to 6 point

SWAA6HOM.JPG is resized to 150 pixels high

The hyperlink from Flexibility Learning opens SWAA6FLX.HTM in a new window called

EXTERNAL

SWAA6ICO.JPG has been changed to .gif format

SWAA6ICO.GIF is resized to 35 pixels

SWAA6ICO.GIF hyperlinks to COLHOME.HTM


IB07 06_0418_03/4RP

© UCLES 2007 [Turn over

*0030068772**0030068772*




Paper 3 Practical Test B May/June 2007

2 hours 30 minutes










instructions correctly.


2

© UCLES 2007 0418/03/M/J/07

You work for a company called Hothouse Design. You are going to perform some clerical tasks for

this company.

All currency values should be in UK pounds (sterling) with the £ sign visible.

!

1 Using a suitable software package, load the file J7STOCK2.CSV

2 In the Company column use a lookup function to show the company name. Use the

CCode column for the lookup value and the file J7CODE.CSV for the array. Make

sure that you use both absolute and relative referencing within your function.

Replicate this function so that the company name for each item is shown.

3 Enter the following data into the model.

Dcode Rate

A 0.225 B 0.18 C 0.1

4 Name the cell containing 0.225 RateA

Name the cell containing 0.18 RateB

Name the cell containing 0.1 RateC

5 Format these three cells as percentage values to 0 decimal places.

6 Use a Countif function (that includes both absolute and relative referencing) in cell

B2, to count the number of SX entries in the CCode column. Do not count any

entries in rows 1 to 4.

Replicate this function into cell B3 to count the number of BE entries and into cell

B4 to count the number of UTB entries.

7 In the Cost column (Cell G7) use a formula to multiply Unit by Quantity

Replicate this function so that the Cost price is shown for each item.

3


!

8 In the Discount column (Cell H7) use an IF function to calculate the discount to be

given.

If the Code is A then multiply the named cell RateA by Cost

If the Code is B then multiply the named cell RateB by Cost

If the Code is C then multiply the named cell RateC by Cost

Replicate this formula so that the discount is shown for each item.

9 In the Price column (Cell I7) use a formula to subtract the Discount from the Cost

Replicate this formula so that the price is shown for each item.

10 Format only the Unit, Cost, Discount and Price columns as currency in pounds

sterling to 2 decimal places.

11 Set the page orientation to landscape.

12 Save the data model and print a copy of the sheet showing the formulae used.

Make sure that the contents of all cells are visible and that the printout fits on a

single page wide and two (or three) pages tall.

Make sure that your name, candidate number and centre number are printed in the

header of each page.

13 Set the page orientation to portrait.

14 Save the data model and print a copy of the sheet showing the values. Make sure

that the contents of all cells are visible and that the printout fits on a single page

wide and two (or three) pages tall.



15 Hide rows 1 to 5 inclusive.

Hide columns A, C, E, G and H.

16 Search the data to find all the items where the Price is less than £1

Print this data. Make sure that your name, candidate number and centre number

are printed in the header of the page.

4

© UCLES 2007 0418/03/M/J/07

!

17 Select all the data.

Leave the rows 1 to 5 inclusive and columns A, C, E, G and H hidden.

Search this data to find all the items where Quantity is greater than or equal to 60

and Price is greater than £2



You are going to create and edit some web pages for this company.

18 Download the following files from http://www.hothouse-design.co.uk/2007_June

to your own work area:

J7PAGE.TXT

J7MENU.HTM

J7OFFER.HTM

J7SUPPLY.JPG

J7LOGO.JPG

J7SUPP1.JPG

J7SALES.CSV

19 Using a suitable software package, prepare the following styles for use within all

pages of this website:

• h1 – dark blue, sans-serif font, 24 point, centre aligned, bold

• h2 – dark blue, sans-serif font, 18 point, centre aligned

• h3 – blue, sans-serif font, 12 point, right aligned

• h4 – blue, sans-serif font, 12 point, centre aligned

• li – black, serif font, 10 point, bullet points, left aligned, italic

• p – blue, serif font, 10 point, left aligned.

Save the style sheet and attach it to each web page as you create it. Make sure the

style sheet that you prepare will work in any web browser.

20 Print a copy of the style sheet in css format. Make sure that your name, centre

number and candidate number are printed on this page.

21 Using a suitable software package, create a new homepage HOMEPAGE.HTM

This page will have your name, a heading and menu options across the top. It will

have a table below the menu. It should look like this:

5


!

22 Attach the external style sheet that you saved in step 19.

Ensure (by removing code if necessary) that, if you are using a WYSIWYG

package, the styles from the style sheet are not overridden by the package.

23 In the top right corner add the text Last edited by and add your name, centre

number and candidate number.


24 Enter the heading Hothouse Design


25 Place the contents of J7MENU.HTM as a table below the heading to create the

menu. Apply the style h4 to all the text within this table.

26 Set the table border to 3

27 Set the cell padding and the cell spacing for the table to 4 and the width of the table

to 100%

28 Below the menu, create a new table which has 4 rows and 2 columns.


A B C

D

E

30 Set the cell padding for the table to 4, the cell spacing for the table to 4 and the

width of the table to 100%

31 Set the table border of this table to 1

6

© UCLES 2007 0418/03/M/J/07

!

32 Using the contents of the file J7PAGE.TXT place:

• the text Hothouse Design can provide… high quality products. into cell A

and format this as style h2

• the text We offer a… and much more. into cell B and format this as follows:

We offer a range of products including: as style p

stationery as style li

computer hardware as style li

computer software as style li

computer consumables as style li

and much more. as style p

• the text You can contact us in many ways. into cell C and format this as

style h2

• the text Through our website… of our services. into cell E and format this

as style h3.

33 Import the image J7SUPPLY.JPG and place it in cell D.

Set the width of the image to 300 pixels and maintain the aspect ratio.


34 Create a hyperlink for the menu item Special offers to point to the file

J7OFFER.HTM which should open in a new window called _external


35 Save the page as HOMEPAGE.HTM

Print this page as it is viewed in your browser. Print a copy of the HTML source.

36 Open the file J7LOGO.JPG in a suitable application. Change its width to 50 pixels

and its height to 53 pixels. Ensure that the colour depth is 256 colours (8 bit colour)

and save the file as J7LOGO.GIF

37 Open the file J7OFFER.HTM

Import the image J7SUPP1.JPG into the right cell of the table.

Resize the image J7SUPP1.JPG to 250 pixels wide and maintain the aspect ratio.

38 Replace the text Candidate name (at the top of the page) with your name,

candidate number and centre number.

7


!

39 Replace the text click here (at the bottom of the page) with the image J7LOGO.GIF

Make this a link in the same window to HOMEPAGE.HTM




41 Save the page as J7OFFER.HTM


8

© UCLES 2007 0418/03/M/J/07

You are going to create and edit a presentation for this company.

The medium for delivery will be a multimedia projector. You will need to produce presenter notes and

audience notes with the slides.

!

42 Set up a new presentation consisting of 3 slides.

On the master slide:

43 The slide master must have a cream (or light yellow) background and your Centre

number, candidate number and name in a small black font placed in the bottom left

corner. It must also contain a clipart image of a pen or pencil as a logo placed in

the top right corner.

The logo must be resized so that no text or other object overlaps it.

Include the slide number in the bottom right of the footer.

44 Set the following styles of text throughout the entire presentation:

Heading: Dark blue, right aligned, large font (48 point)

Subheading: Blue (bright blue), right aligned, medium font (32 point)

Bulleted list: Black, left aligned, small font (24 point). Use a bullet of your choice

On the first slide:

45 Enter the heading Hothouse Design

46 Enter the subheading Providing all your stationery and ICT needs for now and

the future… below the heading.


Hothouse Design have now added ICT to their portfolio.


48 Enter the heading Range of products: in the same style as the first slide.

49 Add a black horizontal line, 3 points thick, below the heading.

9


!

50 Enter the following bulleted list on the left side of the slide, below the line.

• stationery

• computer hardware

• computer software

• computer consumables

51 Enter the text and much more... below the bulleted list on the left side of the slide

as a subheading.

52 Place the image J7SUPP1.JPG so that it fills most of the right half of the slide.

Make sure that it does not overlap or cover anything placed on the master slide.

53 The bulleted list should have each bullet set to appear one at a time using the

same animation effect. No other elements on the slide should be animated.

54 Add the following presenter notes to this slide: We have a wide variety of

products, ranging from pens and pencils to state of the art computers.

On the third slide:

55 Enter the heading Best sellers in the same style as the first slide.


• pens

• pencils

• rulers

57 Using a suitable software package, load the file J7SALES.CSV

Create a bar chart from this file using only the Product and Sold data. Do not

include a legend. Add the label Products to the category axis and Number Sold to

the value axis. Make sure that all of the Product names are visible.

Place this bar chart on the right side of the slide.


We will never be surpassed for quality of products or service.

10



publisher will be pleased to make amends at the earliest possible opportunity.

University of Cambridge International Examinations is part of the Cambridge Assessment Group. Cambridge Assessment is the brand name of University of


© UCLES 2007 0418/03/M/J/07


!


60 Save the presentation using a new filename. Print the presentation showing all of

the presenter notes.

61 Print the presentation showing audience notes of three slides to a page with space

for the audience to make notes.

62 Print a screen shot of slide 2 showing the animation used for the bulleted lists.

63 Print a screen shot of the presentation showing the transitions between the slides.

After the examination time – on the html printouts of your webpage

On your printout highlight those portions of the code which show that:

• your name, centre number and candidate number are present

• the external style sheet is attached to each webpage

• the table borders are set to 3 and 1 point

• the table widths are set to 100%

• the cell spacing and cell padding are both set to 4

• J7SUPPLY.JPG is resized to 300 pixels wide

• J7SUPP1.JPG is resized to 250 pixels wide

• the hyperlink from Special offers opens J7OFFER.HTM in a new window called _external

• J7LOGO.JPG has been changed to .gif format

• J7LOGO.GIF is resized to 50 pixels wide and 53 pixels high

• J7LOGO.GIF hyperlinks to HOMEPAGE.HTM


IB07 11_0418_03/7RP


*0810124082**0810124082*




Paper 3 Practical Test B October/November 2007

2 hours 30 minutes










instructions correctly.


2

© UCLES 2007 0418/03/O/N/07


this company.

All currency values should be in UK pounds (sterling) with the £ sign visible.

!

1 Using a suitable software package, load the file N7STOCK2.CSV

2 In the Unit Cost column use a lookup function to show the unit cost. Use the Code

column for the lookup value and the file N7CODE.CSV for the array. Make sure you

use both absolute and relative referencing within your function.

Replicate this function so that the Unit Cost is shown for each row.

3 Enter the following data into the model.

Dcode Rate

A 0.045

B 0.09

C 0.125

4 Name the cell containing 0.045 RateA

Name the cell containing 0.09 RateB

Name the cell containing 0.125 RateC

5 Format these three cells as percentage values to 0 decimal places.


H2, to count the number of Cartridge entries in the Type column.

Replicate this formula into cell H3 to count the number of Paper entries and into cell

H4 to count the number of Storage entries.

7 In the Cost column (Cell G7) use a formula to multiply Unit Cost by Sales

Replicate this formula so that the Cost price is shown for each item.

3


!

8 In the Discount column (Cell H7) use an IF function to calculate the discount to be

given.

If Sales are greater than or equal to 10 then multiply the named cell RateA by Cost

If Sales are greater than or equal to 20 then multiply the named cell RateB by Cost

If Sales are greater than or equal to 30 then multiply the named cell RateC by Cost

Replicate this function so that the Discount is shown for each item.

9 In the Price column (Cell I7) use a formula to subtract the Discount from the Cost

Replicate this formula so that the Discount is shown for each item.

10 Format only the Unit Cost, Cost, Discount and Price columns as currency in pounds

sterling to 2 decimal places.



Make sure that the contents of all cells are visible.





that the contents of all cells are visible.




Hide columns A, D, F, G and H.

4

© UCLES 2007 0418/03/O/N/07

!

16 Search the data to find all the items where the Supplier is Maxell



17 Select all of the data.

Leave the rows 1 to 5 inclusive and columns A, D, F, G and H hidden.

Search this data to find all the items where Sales are greater than or equal to 20

and Price is less than £100





http://www.hothouse-design.co.uk/2007_November to your own work area:

N7PAGE.TXT

N7MENU.HTM

N7OFFERS.HTM

N7CART.JPG

N7ICON.JPG

N7MEDIA.JPG

N7PRINT.JPG

N7SALES.CSV


pages of this website:

• h1 – black, serif font, 36 point, centre aligned

• h2 – black, serif font, 18 point, centre aligned

• h3 – red, sans-serif font, 12 point, centre aligned, bold

• h4 – red, sans-serif font, 12 point, left aligned

• li – red, sans-serif font, 10 point, bullet points, left aligned

• p – dark red (or maroon), sans-serif font, 10 point, left aligned

Save the style sheet and attach it to each web page as you create it. Make sure the

style sheet that you prepare will work in any web browser.

20 Print a copy of the style sheet in css format. Make sure that your name, centre


5


!

21 Using a suitable software package, create a new homepage HOMEPAGE.HTM

This page will contain your name, a heading, a table and have menu options across

the bottom. It should look like this:



package, the styles are not overridden by the package.

23 In the top left corner add the text Last edited by and add your name, centre



24 Enter the heading Peripherals and Consumables


25 Below the heading, create a table which has 4 rows and 2 columns.


A

B

C

D

E




6

© UCLES 2007 0418/03/O/N/07

!

29 Using the contents of the file N7PAGE.TXT place:

• the text We stock a wide…accessories and consumables. into cell A and

format this as style h2

• the text Peripherals include…CDs and DVDs. into cell B and format this as

follows:

Peripherals include as style p

printers as style li

scanners as style li

network and WIFI, as style li

and consumables like: as style p

cartridges as style li

specialist paper as style li

Blank CDs and DVDs. as style li

• the text You can contact us in many ways. into cell C and format this as

style h3

• the text Through our website…of our services. into cell E and format this

as style h4.

30 Import the image N7CART.JPG and place it in cell D.

Set the height of the image to 200 pixels and maintain the aspect ratio.


31 Place the contents of N7MENU.HTM as a table below the existing table to create

the menu. Apply the style h2 to all the text within this table.

32 Set the cell padding for the table to 10 and the cell spacing for the table to 2

Set the table width to 600 pixels.

33 Set the border size of this table to 2

34 Create a hyperlink for the menu item Special Offers to point to the file

N7OFFERS.HTM which should open in a new window called _external


35 Save the page as HOMEPAGE.HTM


7


!

36 Open the file N7ICON.JPG in a suitable application. Change its width to 40 pixels

and its height to 42 pixels. Ensure that the colour depth is 256 colours (8 bit colour)

and save the file as N7ICON.GIF

37 Open the file N7OFFERS.HTM

Import the image N7MEDIA.JPG into the right cell of the table.

Resize the image N7MEDIA.JPG to 300 pixels wide and maintain the aspect ratio.

38 Replace the text Candidate name (at the top of the page) with your name, centre


39 Replace the text click here (at the bottom of the page) with the image N7ICON.GIF

Make this a link in the same window to HOMEPAGE.HTM




41 Save the page as N7OFFERS.HTM


8

© UCLES 2007 0418/03/O/N/07

You are going to create and edit a presentation for this company.

The medium for delivery will be a multimedia projector. You will need to produce presenter notes and

audience notes with the slides.

!



43 The slide master must have a very light blue background and your Centre number,

candidate number and name in a small black font placed in the top right corner. It

must also contain a clipart image of a peripheral device for a computer as a logo

placed in the bottom right corner.

The logo must be resized so that no text or other object overlaps it.

Include the slide number in the bottom left of the footer.


Heading: Black, centre aligned, large font (48 point)

Subheading: Dark red (or maroon), left aligned, medium font (32 point)

Bulleted list: Red, left aligned, small font (24 point). Use a bullet of your choice

On the first slide:

45 Enter the heading Hothouse Design for Computer Peripherals

46 Enter the subheading We offer a full range of peripherals for PCs and Macs

below the heading.


I am here on behalf of the Sales department of Hothouse Design.


48 Enter the heading Printers of all types in the same style as the first slide.

49 Enter the following bulleted list on the left side of the slide.

• Ink Jet

• Laser

• Thermal

9


!

50 Enter the text and for all budgets: below the bulleted list on the left side of the

slide as a subheading.

51 Enter the following bulleted list below the subheading on the left side of the slide.

• Budget from £99

• Home office from £199

• Network from £459

52 Place the image N7PRINT.JPG so that it fills most of the right half of the slide.


53 Add a red vertical line, 5 points thick, between the bulleted lists and the image.

54 Both bulleted lists should have each bullet set to appear one at a time using the

same animation effect. No other elements on the slide should be animated.

55 Add the following presenter notes to this slide: All the latest printer technologies

are available.

On the third slide:

56 Enter the heading Storage media special offers in the same style as the first slide.

57 Enter the following bulleted list on the right side of the slide:

• CD-R

• CD-RW

• DVD-R

• DVD-RW

58 Using a suitable software package, load the file N7SALES.CSV

Add together the total sales for both makes where the Type is CD-R

Create a bar chart from the data using only the Type and the Sales. Do not include

a legend. Add the label Type to the category axis and Total Sales to the value axis.

Make sure that all of the Type labels are visible.

Place this bar chart on the left side of the slide.


Sales of these products have been spectacular, as can be seen by the graph.

10



publisher will be pleased to make amends at the earliest possible opportunity.

University of Cambridge International Examinations is part of the Cambridge Assessment Group. Cambridge Assessment is the brand name of University of


© UCLES 2007 0418/03/O/N/07


!


61 Save the presentation using a new filename. Print the presentation showing all of

the presenter notes.

62 Print the presentation showing audience notes with three slides to the page and

space for the audience to make notes.





• your Centre number, candidate number and name are included


• borders of both the tables are set to 2 point

• the cell padding of both tables is set to 10

• the cell spacing of both tables is set to 2

• the table width is set to 100%

• N7CART.JPG is resized to 200 pixels high

• the table width is set to 600 pixels

• the hyperlink from Special Offers opens N7OFFERS.HTM in a new window called _external

• N7MEDIA.JPG is resized to 300 pixels wide

• N7ICON.JPG has been changed to .gif format

• N7ICON.GIF is resized to 40 pixels wide and 42 pixels high

• N7ICON.GIF hyperlinks to HOMEPAGE.HTM


IB08 06_0418_03/5RP


*0000000000*




Paper 3 Practical Test May/June 2008

2 hours 30 minutes






Tasks are numbered on the left hand side of the page, so that you can see what to do, step by step. On the

right hand side of the page for each task, you will find a box which you can tick (!) when you have





2



this company.

!

1 Using a suitable software package, load the file J8PHONE.CSV

2 In the Department column use a lookup function to show the department name.

Use the Dcode column for the lookup value and the file J8CODE.CSV for the

array. Make sure that you use both absolute and relative referencing within your

function.

Replicate this function so that the department name for each of the 7 items is

shown.

3 Enter the following data into the model

Code Rate

C 1.1

I 3

P 1.6

4 Name the cell containing 1.1 Cheap

Name the cell containing 3 Intl

Name the cell containing 1.6 Peak

5 Format these three cells as numbers to 1 decimal place.

6 Format cells A2, A3 and A4 so that they are right aligned.


C7, to count the number of MD entries in the Dcode column. Do not count any

entries in rows 1 to 13.

Replicate this function into cells C8 to C13 to count the number of calls from other

departments.

8 Use a function (that includes both absolute and relative referencing) in cell D7, to

calculate the sum of the Duration column if the Dcode column contains MD

Do not include rows 1 to 13.

Replicate this function into cells D8 to D13 to sum the Total Duration for each

Dcode

9 In the Units column (Cell E16) use an IF function to calculate the units used.

If the Code is C then multiply the named cell Cheap by the Duration

If the Code is I then multiply the named cell Intl by the Duration

If the Code is P then multiply the named cell Peak by the Duration

If the Code is not C, I or P then the units used should be zero.

Replicate this function so that the Units for each item are shown.

10 Format all cells in rows 1, 6 and 15 to be centre aligned.

11 Format all cells in rows 1, 6 and 15 so that they are bold.

3


12 Format only the Units column to no decimal places.


14 Save the data model and print a copy of the sheet showing the formulae and

functions used. Make sure that the contents of all cells are visible and that the

printout is one page wide. It may be three or four pages tall.

Make sure that your name, Centre number and candidate number are printed in

the header of the page.



that the contents of all cells are visible and that the printout is one page wide.




Hide column D.

18 Interrogate the data, to find all the items where the Code is I

Print this data.



19 Select all of the data.

Leave the rows 1 to 14 and column D hidden.

Hide column C.

Interrogate this data, to find all the calls made on 04/01/2008 where the Dcode is

PU or SA

Print this data.



20 Select from all of the data, ONLY the data for Department and Total Duration

Use this data to create a vertical bar chart.

Label the chart with the title Total Duration By Department

Make sure that all category axis labels are fully visible and that there is no legend.

Save this chart in a format that can be imported into a presentation authoring

package. Print a copy of this chart.

Make sure that your name, Centre number and candidate number are printed with

this chart.

4



!


http://www.hothouse-design.co.uk/2008June to your own work area:

J8COHIST.HTM

J8HIST.JPG

J8IMAGE.GIF

J8MENU.HTM

J8MOVIE.JPG

J8PAGE.TXT

J8PROD.JPG

J8TEL.JPG


pages on this website:

• h1 – black, serif font, 20 pixels, centre aligned, bold

• h2 – blue, serif font, bold, 18 pixels, centre aligned

• h3 – black, serif font, 16 pixels, left aligned

• li – black, sans-serif font, 14 pixels, bullet points, left aligned

• p – dark blue, sans-serif font, 14 pixels, left aligned, italic

Save the style sheet and attach it to each web page as you create it. Make sure

that the style sheet that you prepare will work in any browser.

23 Print a copy of the style sheet in css format. Make sure that your name, Centre


24 Using a suitable software package, create a new homepage SAMPLE.HTM

This page will have a table below the heading and menu options below the table.

It should look like this:

25 Attach the external style sheet that you saved at Step 22.



26 In the top left corner add the text Last edited by and add your name, Centre


Set this text to style h3.

27 Enter the heading Sample Website for client X


28 Below the heading, create a new table which has 5 rows and 3 columns.

5


!


A

B C

D E

F G

H

J




32 Using the contents of the file J8PAGE.TXT

• place the text Our Vision is to promote … and hosting of a web presence.

into cell A and format this as style h3

• place the text Our Products into cell C and format this as style h2

• place the text Company History into cell E and format this as style h2

• place the text Contact Us into cell G and format this as style h2

• place the text Each section of this … included on the web site. into cell H.

Format this text as follows:

• Each section of this table can be used to convey information, or to provide

a link to other areas. as style p

• For example there may be: as style p

• your flagship products as style li

• special offers as style li

• price reductions as style li

• in order to entice customers … included on the web site. as style p.

33 Place the image J8PROD.JPG in cell B.

Place the image J8HIST.JPG in cell D.

Place the image J8TEL.JPG in cell F.

Make sure that the whole of each image is visible.

34 Place the image J8MOVIE.JPG in cell J.

Set the width of the image to 400 pixels and the height to 50 pixels. Horizontally

align this image in the centre of the cell.


6


!

35 Place the contents of J8MENU.HTM

• as a table

• below the table created at Step 28

• to create the menu.

Set the text within this table to style h2.



37 Set the table border of this table to 2

38 Create hyperlinks for the menu item History and the text Company History (in cell

E) to point to the file J8COHIST.HTM which should open in a new window called

_history


39 Create an anchor at the top of the page called TOP

Create a hyperlink so that the menu item Home links to the anchor TOP

40 Save the page as SAMPLE.HTM

Print this page as it is viewed in your browser.

Print a copy of the HTML source.

41 Open the file J8COHIST.HTM

Import the image J8IMAGE.GIF into the right cell of the table.

Resize this image to 300 pixels wide and maintain the aspect ratio.

42 Replace the text Candidate name (at the top of the page) with your name, Centre


43 Create a hyperlink so that the text Home links in the same window to the file

SAMPLE.HTM

44 Attach the external style sheet that you saved at Step 22.

Ensure (by removing code if necessary) that if you are using a WYSIWYG


45 Save the page as J8COHIST.HTM



7


One of the work experience trainees in the company needs to learn how to create a master slide for a

presentation. You are going to produce a sample master slide for this trainee.

!



47 The master slide must have a white background with one vertical green stripe, two

horizontal green stripes and one diagonal green stripe. Each stripe should be

about 6 points wide. It should look like this:

48 Include an automated slide number in the bottom left of the footer. Make sure that

it does not overlap any of the lines added at Step 47.

49 Place a clipart image of a pen or pencil as a logo in the top right corner.

The logo must be resized so that it will not overlap the diagonal line added at Step

47.

50 Enter your name, Centre number and candidate number, right aligned between

the two green lines at the bottom of the master slide. Use a black, 12 point, sans-

serif font. It should look like this:

8





0418/03/M/J/08

! 51 Add the text Hothouse Design at the top of the master slide. The text should

appear as a heading on every slide of your presentation in a black, 48 point sans-

serif font. This should not overlap any of the other objects on the slide. It should

look like this:

52 On slide 1 add the text Slide 1 example in a blue, 24 point, left aligned serif font.

On slide 2 add the text This is slide 2 in a blue, 24 point, left aligned serif font.

On slide 3 add the chart saved at Step 20.

Make sure that these objects do not overlap any of the master slide items.

53 Save the presentation using a new filename. Print the presentation showing only

the slides, with each slide on a separate page.


Highlight the portions of the code which show:

• your name, Centre number and candidate number



• the table width is set to 100%

• the cell spacing and cell padding are set to 6 in both tables in SAMPLE.HTM

• J8MOVIE.JPG is resized to 400 pixels wide and 50 pixels high and is centered

• the hyperlinks from History and Company History open J8COHIST.HTM in a new window

called _history

• the anchor TOP is created

• the hyperlink from Home points to the anchor TOP in SAMPLE.HTM

• J8IMAGE.GIF is resized to 300 pixels wide

• the hyperlink from Home in J8COHIST.HTM points to SAMPLE.HTM


IB08 11_0418_03/8RP


*0000000000*




Paper 3 Practical Test October/November 2008

2 hours 30 minutes






Tasks are numbered on the left hand side of the page, so that you can see what to do, step by step. On the

right hand side of the page for each task, you will find a box which you can tick (!) when you have





If you have produced rough copies of printouts, these should be neatly crossed through to indicate that they

are not the copy to be marked.

2



this company.

Your manager has asked you to set up a presentation for the board of directors.

The medium for delivery will be a multimedia projector. Presenter notes need to be produced with the

slides.

!



2 The master slide must have a light grey background on the left hand side (about

1/5 of the width) with one vertical red stripe as a border for the grey background

and two horizontal red stripes. Each stripe should be about 6 points wide. It

should look like this:

3 Include an automated slide number in the bottom right of each slide. Make sure

that it does not overlap any of the lines added at Step 2.

4 Place a clipart image of a telephone as a logo in the top left corner.

The logo must be resized so that it is within and does not overlap the three red

lines added at Step 2.

3


!

5 Enter your name, Centre number and candidate number, left aligned in the white

area, at the bottom of the master slide. Use a black, 12 point, sans-serif font. It

should look like this:

6 Add the text Telephone Analysis as a header, left aligned in a black, 44 point

sans-serif font at the top of the master slide. This should be between the red lines

and not overlap any of the other objects on the slide. It should look like this:

On each slide you are going to add text and images to the main white area of the slide. No text

or objects should overlap any items on the master slide.


Heading:

Subheading:

Bulleted list:

Red, left aligned, 60 point (large font)

Red, centre aligned, 36 point (medium font)

Black, left aligned, 24 point (small font). Use

a bullet of your choice.

On the first slide:

8 Enter the heading:

Hothouse Design

9 Below the heading, enter the subheading:

Snapshot analysis of telephone use within the company


This brief presentation will identify some of the results of our recent

telephone use analysis.

4



!

11 Enter the subheading:

Departments analysed

in the same style as the first slide.


" Accounts

" Cleaning

" Human Resources

" Information Systems

" Maintenance

" Transport

13 Enter the following bulleted list on the right side of the slide:

" Production

" Purchasing

" Sales

" Security

" Site Management

14 Both bulleted lists should have each bullet set to appear one at a time using the

same animation effect. No other elements of the slide should be animated.


Telephones were used by other departments but some have been removed

from the sample.

On the third slide:


Call type Minutes

International 1640

Peak Rate 7842

Cheap Rate 1543

Internal 16805

17 Place the chart in the centre of the slide. Enlarge the chart so that it is clearly

visible.

Each segment must show the call type and percentage value. Do not display a

legend.


As you can see the vast majority of our calls are internal. These figures are

the average values per day for all departments, using a monitoring period of

2 weeks.

5


On the fourth slide:

!

19 Enter the text:

Replace some calls with:

as a subheading.


" Video conference

" E-mail

" SMS messages

21 Place a different clipart image showing a telephone to the right of the bulleted list.


We must look at cheaper media in order to reduce our operating costs.



24 Save the presentation. Print the presentation showing all of the presenter notes.

25 Print the presentation showing 3 slides to a page with space for the audience to

make notes.



Your manager has asked you to help develop the company website.


http://www.hothouse-design.co.uk/2008November to your own work area:

N8ABOUT.GIF

N8CLIENT. GIF

N8CONTAC.GIF

N8CONTAC.HTM

N8HOTDES.HTM

N8STYLE.CSS

N8WHAT. GIF

N8WORK. GIF

6


!

29 Using a suitable software package, edit the file N8STYLE.CSS so that only the

following changes are made:

• set styles h1 and h2 to red

• set style p to dark blue

• set styles h1, h2 and h3 so that the primary font is Times New Roman, but if

this is not available the browser selects any serif font

• set styles li and p so that the primary font is Arial, but if this is not available

the browser selects any sans-serif font

• centre align styles h1 and h2

• set style h1 to bold and italic.

Make sure that the stylesheet that you have edited will work in any browser.

Save the stylesheet as N8STYLE1.CSS and attach it to each web page as you

create or amend it.

30 Print a copy of the stylesheet. Make sure that your name, Centre number and

candidate number are printed on this page.

31 Open the file N8HOTDES.HTM

32 Attach the external stylesheet that you saved at Step 29.

33 Replace the text Homepage with the heading Hothouse Design Ltd

Set the heading to style h1.

34 At the bottom of the page add the text Last updated by and add your name,

Centre number and candidate number.


35 Replace the text Image 1 here with the image N8ABOUT.GIF

Replace the text Image 2 here with the image N8WHAT.GIF

Replace the text Image 3 here with the image N8CLIENT.GIF

Replace the text Image 4 here with the image N8WORK.GIF

Replace the text Image 5 here with the image N8CONTAC.GIF

Make sure that the whole of each image is visible.

36 Centre align all cells in the top row of the table.

Right align all cells in the second row of the table.

37 Set the background colour of only the top row of the table to black.

38 Set the cell padding for the table to 20 and the cell spacing for the table to 5

Set the table width to 100% and the height to 300 pixels.


40 Create hyperlinks from the text Contact us and from the image N8CONTAC.GIF

to point to the file N8CONTA1.HTM which should open in a new window called

_contact

Note that the web page which you have linked to has not yet been created.

7


!

41 Save the page as N8HOTDE1.HTM



42 Open the file N8CONTAC.HTM

Attach the external stylesheet that you saved at Step 29.

43 At the bottom of the page add the text Last updated by and add your name,



44 Save the page as N8CONTA1.HTM



Your manager has asked you to develop a spreadsheet about telephone calls.

45 Using a suitable software package, load the file N8PHONE.CSV and save it into

your own work area as N8PHONE1

Load the file N8CODE.CSV and save it into your own work area.

46 In the Minutes column (Cell D11) use a function to calculate the whole number

(integer) of minutes for each call, by dividing the Duration by 60

Replicate this function so that the Minutes for each call are shown.

47 In the Seconds column (Cell E11) calculate the number of remaining seconds for

each call using the formula:

Duration – (Minutes multiplied by 60)

Replicate this formula so that the Seconds for each call are shown.

48 In the Length column (Cell F11) use an IF function to show:

• If the Minutes are less than 6 then show the word Short

• If the Minutes are greater than 5 and are less than 12 then show the word

Medium

• If the Minutes are greater than 11 then show the word Long

Replicate this function so that the Length of each call is shown.

49 In the Name column use a lookup function to show the staff name. Use the SCode

column for the lookup value and the external file N8CODE.CSV for the array.

Make sure that you use both absolute and relative referencing within your

function.

Replicate this function so that the names for each of the 7 members of staff are

shown.

50 In cell C2 use a function that includes both absolute and relative referencing to

calculate the sum of the Minutes column if the SCode column contains GBA. Do

not include rows 1 to 10.

Replicate this function into cells C3 to C8 to sum the total Minutes for each

SCode.

51 Format all cells in rows 1 and 10 only so that they are centre aligned.

8





© UCLES 2008 0418/03/O/N/08

!

52 Format all cells in rows 1 and 10 only so that they are bold.



Make sure that the contents of all cells are visible and that the printout is one

page wide. It may be three or four pages tall.




56 Save the data model and print a copy of the sheet showing the values.

Make sure that the contents of all cells are visible and that the printout is one

page wide. It may be two pages tall.



57 Interrogate the data to find all the calls where the Length is Long

Print this data.



58 Interrogate the data to find all the calls where the Length is Long and where the

SCode is VCR or RSP

Print this data.





• The external stylesheet is attached to both web pages

• The table border is set to 5

• The table cell padding is set to 20

• The table cell spacing is set to 5

• The table width is set to 100%

• The table height is set to 300 pixels

• Row 1 of the table is centre aligned

• Row 1 of the table has a black background

• Row 2 of the table is right aligned

• The hyperlinks from both text and image open N8CONTA1.HTM in a new window called

_contact


IB09 06_0417_03/XRP


*0000000000*



INFORMATION AND COMMUNICATION TECHNOLOGY 0417/03


2 hours 30 minutes





Carry out every instruction in each step.

Steps are numbered on the left hand side of the page. On the right hand side of the page for each step,

you will find a box which you can tick (!) when you have completed it; this will help you track your

progress through the test.



At the end of the test put all your printouts into the Assessment Record Folder.



2


You work for a company called Hothouse Design. You are going to update a website for a client

called Supa Scuba.

!


http://www.hothouse-design.co.uk/2009June to your own work area:

J9CLOWN.JPG

J9CODE.CSV

J9DIVE.HTM

J9DOLPHI.JPG

J9FISH.JPG

J9LOGO.JPG

J9PAGE.TXT

J9SCUBA.HTM

J9SHEET.CSV

J9SHOAL.JPG

J9STYLE1.CSS

J9SUNSET.JPG

2 Using a suitable software package, open the webpage J9SCUBA.HTM so that it can

be edited.

Attach the stylesheet J9STYLE1.CSS to this webpage.

3 Replace the text Centre number, candidate number, name with your Centre number,

candidate number and name.

Change this line of code from paragraph style to style h5.

4 Change the width of the table so that it fills the width of the window in any web

browser.

5 Set the cell padding for the table to 2, the cell spacing for the table to 2 and the border

of the table to 2

6 Replace the text Place anchor here with an anchor called TOP

In the text Click Here to return to the top of the page, make only the words Click Here

a hyperlink to the anchor that you have just created.

7 Replace the text Logo image with the image J9LOGO.JPG

8 Replace the text Sunset image with the image J9SUNSET.JPG

Resize this image so that it becomes 183 pixels wide and 320 pixels high. Do not

maintain the aspect ratio.

9 Replace the text Shoal image with the image J9SHOAL.JPG

Create a hyperlink from this image to point to the file J9DIVE.HTM which should open

in a new window called _scubadive

10 Replace the text Fish image with the image J9FISH.JPG

11 Replace the text Dolphin image with the image J9DOLPHI.JPG

12 Create a hyperlink from the text Amazing fish to point to the file J9DIVE.HTM which

should open in a new window called _scubadive

3


!

13 Save the page as J9SCUBA.HTM



14 Using a suitable software package, open the stylesheet J9STYLE1.CSS

15 Edit the stylesheet to change the styles so that:

• h1 is 48 point

• h5 is left aligned • h4 is black • the red and blue components of h1, h2, h3 and h5 remain the same and there

is no green component in any of these styles

• the paragraph style has no red component and the green and blue

components are set to 50%

16 Save this stylesheet as J9STYLE2.CSS

Print this stylesheet.

17 Open the webpage J9SCUBA.HTM so that it can be edited.

Attach the stylesheet J9STYLE2.CSS to this webpage in place of the current

stylesheet.

18 Save the page as J9SCUBA.HTM

Open this page in your browser and print it.


19 Using a suitable software package, open the webpage J9DIVE.HTM so that it can be

edited.

Attach the stylesheet J9STYLE2.CSS to this webpage.

20 Replace the text Centre number, candidate number, name with your Centre number,

candidate number and name.

21 Replace the text Heading here with the text Supa Scuba Diving Holidays and format

this as style h2

4


!

22 Using the contents of the file J9PAGE.TXT replace the text:

• First text here with the text As a company … bespoke holiday service.

Format this as style h4

• Second text here with the text We offer the … the following ideas:

Format this as style h5

• List here with the text Experienced divers … Family packages

Format this text with the following styles:

Text Style

Experienced divers p

Go deep li

Wreck week li

Shark experience li

Wall dives li

Cave dives li

Beginners p

Meet the fish li

Turtle week li

Learn to dive li

Snorkel with us li

Family packages p

Format the two lists as unordered lists.

23 Replace the text Image here with the image J9CLOWN.JPG

Set the width of the image to 280 pixels and maintain its aspect ratio.

24 Set the table border to 2 25 Create a hyperlink from the text Homepage to point to the file J9SCUBA.HTM which

should open in the current window.

Open J9DIVE.HTM in your browser and print it.

Print a copy of the HTML source for J9DIVE.HTM

5


You are going to create a short presentation of 3 slides about the website that you are creating for

Supa Scuba.

The presentation will be delivered using a multimedia projector. Presenter notes need to be produced

with the slides.

!

26 Create a new presentation with 3 slides.

The master slide:

27 The master slide must have:

• a pale blue background on the right hand side (about 1/4 of the width of the

slide)

• one vertical dark blue line as a border for the pale blue background

• two horizontal dark blue lines

• each line should be 4 points wide.

It should look like this:

28 Include an automated slide number, left aligned in the white area at the bottom of the

master slide. Make sure that it does not overlap any of the lines added at Step 27.

29 Place an image (from clipart, scanner, digital camera or elsewhere) of one or more fish

in the right hand area.

Crop and/or resize the image so that it fits within and will not overlay the dark blue

lines added at Step 27. Do not distort the image. Make sure that the image fills more

than 50% of the available space.

6


!

30 Enter your name, Centre number and candidate number. Centre align this text in the

white area, below the blue line, at the bottom of the master slide. Use a black, 12

point, bold, sans-serif font. It should look like this:

31 Add the text Supa Scuba, left aligned in a dark blue, 48 point sans-serif font at the top

of the master slide. This should be above the blue line and not overlap any of the other

objects on the slide. It should look like this:

32 Set the following styles for the presentation:

Heading:

Subheading:

Bulleted list:

Dark blue, left aligned, large font (60 point)

Blue, centre aligned, medium font (30 point)

Black, left aligned, small font (20 point). Use a

bullet of your choice.

On each slide add text and images to the main white area of the slide. No text or objects should

overlap any items on the master slide.

On the first slide:

33 Enter the heading New website 34 Enter the subheading Proposed web pages below the heading. 35 Add the following presenter notes to this slide:

During the development of this new website, we have realised that the proposed

design brief may need to be amended.

7


On the second slide: !

36 Enter the subheading Rationale in the same style as the first slide.


! Customer views

! Easier site location

! Faster navigation

! Compliant with new browsers

38 The bulleted list should have each bullet set to appear one at a time using the same

animation effect. No other elements of the slide should be animated.


The proposed design brief did not take into account the latest web browsers, or

recent customer satisfaction surveys.

On the third slide:


Type of trip 2008 Dives

Go deep 2512

Wreck week 12608

Shark experience 940

Wall dives 12540

Cave dives 353

Drift dives 13247

41 Label the segments with the type of trip and percentage value. Do not display a

legend.

Place the chart in the centre of the slide. Enlarge the chart so that it is clearly visible.

Make sure the chart is not distorted.


This information could be added to the trips page of the website to indicate the

number of dives made in a single year on your trips.



44 Save the presentation using a new filename.

Print the presentation showing all the presenter notes.

45 Print a screen shot of slide 2 showing the animation used for the bulleted list.


8


Your manager has asked you to manipulate a spreadsheet about the time and costs involved with

some of the webpage development.

!

47 Using a suitable software package, load the file J9SHEET.CSV

48 Place your Centre number, candidate number and name in the header. 49 In the Webpage column use a lookup function to show the company name. Use the

Code column for the lookup value and the file J9CODE.CSV for the array. This

function must include both absolute and relative referencing and must not use a

named range.

Replicate this function so that the company name is shown for each of the 7 codes.

50 In cell C2 use a SUMIF function.

If the value in the Pagecode column matches the content of cell B2, then sum the

values from the Minutes column. This function must include both absolute and relative

referencing and must not use named ranges. Do not include any entries in rows 1 to 9.

Replicate this function into cells C3 to C8 for each of the other codes.

51 In cell D2 use a formula to calculate the number of whole hours. Use the minutes from

cell C2 to help you.

Replicate this function into cells D3 to D8.

52 In cell E2 use a formula to calculate the number of minutes left when the whole hours

are removed. Use the values in cells C2 and D2 to help you.

Replicate this formula into cells E3 to E8.

53 Save the data model and print a copy of cells A1 to E23 only, showing the formulae

and functions used. Make sure that the contents of all cells in this range are visible

and that the printout fits on a single page.

Make sure that your name, Centre number and candidate number are printed in the

header.

54 Interrogate the data to show only the entries where the Pagecode is HOL or FP

Sort this data into descending order of minutes.

Hide rows 1 to 9 inclusive.

Print this data.


header.

9





0417/03/M/J/09



• your name, Centre number and candidate number are present on all printouts

• the external stylesheets are attached to the webpages

• the anchor TOP has been placed

• the table borders in the webpages are set to 2 point

• the table is set to the width of the page


• J9SUNSET.JPG is resized to 183 pixels wide by 320 pixels high

• the hyperlink from the text ‘Amazing fish’ opens J9DIVE.HTM in a new window called

_scubadive

• the hyperlink from the image J9SHOAL.JPG opens J9DIVE.HTM in a new window called

_scubadive

• the text Click Here links to the anchor TOP

• J9CLOWN.JPG is resized to 280 pixels wide

• the hyperlink from the text ‘Homepage’ opens J9SCUBA.HTM in the current window.


IB09 11_0417_03/8RP


*0000000000*




Paper 3 Practical Test October/November 2009

2 hours 30 minutes














2



this company.

!

1 Using a suitable software package, load the file N9SUPP.CSV

2 Place your Centre number, candidate number and name in the header.

3 In the Company column use a LOOKUP function to show the company name. Use the

Ccode column for the lookup value and the file N9CODE.CSV for the array. This

function must include both absolute and relative referencing and must not use a named

range.

Replicate this function so that the company name is shown for each of the 15 codes.

4 Enter the following data into the model

Code Rate

D 1.21

N 1.3

P 1.65

5 Name the cell containing 1.21 Discount

Name the cell containing 1.3 Normal

Name the cell containing 1.65 Premium

6 Format these three named cells as numbers to 2 decimal places.

7 Format cells A2, A3 and A4 so that they are right aligned.

8 Use a COUNTIF function in cell C7 to count the number of products made by the

Supplier with a Ccode that matches the contents of cell B7. This function must include

both absolute and relative referencing and must not use a named range. Do not count

any entries in rows 1 to 23.

Replicate this function into cells C8 to C21 to count the number of products in stock

made by each Supplier.


10 Save the data model and print a copy of cells A1 to C21 only, showing the formulae and

functions used.

Make sure that the row and column headings and the contents of all cells in this range

are visible.

Make sure that the printout fits on a single page.


header.


12 Save the data model and print a copy of cells A1 to C21 only, showing the values.

Make sure that the row and column headings are not visible.

Make sure that the contents of all cells in this range are visible and that the printout fits

on a single page.


header.

3


!

13 In the Unit Retail column (Cell F24) use an IF function to calculate the retail price of

each unit.

If the Code is D then multiply the named cell Discount by the Unit Purchase price.

If the Code is N then multiply the named cell Normal by the Unit Purchase price.

If the Code is P then multiply the named cell Premium by the Unit Purchase price.

If the Code is not D, N or P then return the Unit Purchase price.

Replicate this function so that the Unit Retail price for each item is shown.

14 In the Retail column (Cell H24) multiply the Unit Retail price by the Pack size to

calculate the Retail price for the item.

Replicate this formula so that the Retail price for each item is shown.

15 Format all cells in row 23 to be centre aligned.

16 Format all cells in row 23 so that they are bold.

17 Format all cells in row 23 so that they are italic.

18 Format the data in the Unit Purchase, Unit Retail and Retail columns to Euros (!) with 2

decimal places.

19 Format only the Pack Size column to 0 decimal places. 20 Centre align the Supplier and Code columns from row 24 to row 116 inclusive. 21 Set the page orientation to landscape. 22 Save the data model and print a copy of cells F23 to H116 only, showing the formulae

and functions used.

Make sure that the row and column headings and the contents of all cells in this range

are visible.


header.

23 Keep the page orientation as landscape. 24 Save the data model and print a copy of rows 23 to 54 only, showing the values. Make

sure that the contents of all cells in this range are visible and that the printout fits on a

single page.


header.

25 Interrogate the data, to find all the Red items with a Unit Purchase price between 0.51

and 0.58 Euros.

Print this data.


header.

4


You are going to edit some web pages for this company.

!


http://www.hothouse-design.co.uk/2009November to your own work area:

N9DOUBLE.JPG

N9FILE.JPG

N9FOLDER.JPG

N9GAFFER.JPG

N9GLUE.JPG

N9GLUE2.JPG

N9OFFERS.HTM

N9PAGE.TXT

N9PARCEL.JPG

N9PEN.JPG

N9SELLO.JPG

N9STYLES.CSS

N9TAPE.HTM

N9TAPE.JPG

27 Using a suitable software package, open the webpage N9OFFERS.HTM so that it can

be edited.

Attach the stylesheet N9STYLES.CSS to this webpage.

28 Replace the text Candidate name, Centre number, candidate number with your name,


Change this line of code from style h4 to paragraph style.

29 Change the width of both tables so that they each fill the width of the window in any web

browser.

30 Merge the two cells that contain the word Adhesives so that they become a single cell,

containing only the word Adhesives

Make sure that this new cell uses 44% of the table width.

31 Merge the two cells that contain the word Files so that they become a single cell,

containing only the word Files

Make sure that this new cell uses 44% of the table width.

32 Set the cell padding for both tables to 4, the cell spacing for both tables to 4

Set the table border of the top table to 0 and the table border of the bottom table to 2

33 Replace the text Place anchor here with an anchor called TOP

In the text Click Here to return to the top of the page, make only the words Click Here a

hyperlink to the anchor that you have just created.

34 Replace the text Tape image with the image N9TAPE.JPG

Create a hyperlink from this image to point to the file N9TAPE.HTM which should open

in a new window called _adhesivetape

35 Replace the text Glue image with the image N9GLUE.JPG 36 Replace the text File image with the image N9FILE.JPG 37 Replace the text Folder image with the image N9FOLDER.JPG

5


!

38 Replace the text Pen image with the image N9PEN.JPG

Resize this image so that it becomes 100 pixels wide and 300 pixels high. Do not

maintain the aspect ratio.

39 Using the contents of the file N9PAGE.TXT

Replace the text Sponsored links here with:

Select from the following sponsored links to take you to our partner companies:

Format this as style p

Align this text to the top of the table cell.

40 Using the contents of the file N9PAGE.TXT

Replace the text Place list here with:

Rootrainer Trees

Rock ICT

Kuroki Koi

Electry

International Tea and Coffee Traders

Format this as style li

41 Save the page as N9OFFERS.HTM



42 Open the file N9TAPE.HTM

Identify 2 new hyperlinks that need to be created from the text on this webpage.

Make a note of the type of hyperlinks required and the text that these hyperlinks will link

from but do not create them. You will need this information at Step 54.

6


You have been asked to prepare a short presentation for your team leader giving information about

the website.

!

43 Create a new presentation with 3 slides.

The master slide:

44 The master slide must have a white background.

45 In the top left corner of the master slide enter the text Hothouse Design Stationery

Supplies in a black, 24 point, sans-serif font.

46 In the bottom left corner of the master slide place an automated slide number. 47 In the bottom right corner of the master slide place an image (from clipart, scanner,

digital camera or elsewhere) of a pen or pencil as a logo.

48 In the top right corner of the master slide enter your name, Centre number and

candidate number in a black, 12 point, sans-serif font.

49 Set the following styles for the presentation:

Heading: Red, 48 point, right aligned serif font.

Subheading: Black, centre aligned, 24 point serif font.

Bulleted list: Black, left aligned, 18 point sans-serif font. Use a bullet of your choice.

On the first slide:

50 Enter the heading Progress on the website

Make sure that this text does not overlap any of the items on the master slide.

51 Add the subheading Current status



52 Enter the heading Webpage N9Tape.htm


53 Enter the subheading This webpage needs improving with:


54 Add a bulleted list with three bullet points.

The first bullet point will contain the text: Consistent spacing between images

The second and third bullet points should list the new hyperlinks to be added to the

webpage N9TAPE.HTM. State the types of hyperlinks required and the text that the

hyperlinks will link from. You identified these at Step 42.

Make sure that these do not overlap any of the items on the master slide.

55 The bulleted list should have each bullet set to appear one at a time using the same

animation effect.

No other elements on the slide should be animated.

7





0417/03/O/N/09

On the third slide: !

56 Enter the heading Image for the glue webpage


57 Open the image N9GLUE2.JPG in a suitable application package.

Flip the image horizontally.

Place and resize the new image so that it fills most of the slide. Ensure that the aspect

ratio is maintained.



58 Save the presentation using a new filename.

Print the presentation.

59 Print a screen shot of slide 2 showing the animation used for the bulleted list.



• your name, Centre number and candidate number are present

• the external stylesheet is attached to the homepage

• the anchor TOP has been placed


• the tables are the width of the page


• N9PEN.JPG is resized to 100 pixels wide by 300 pixels high

• the cells containing the word Adhesives have been merged

• the cells containing the word Files have been merged

• the hyperlink from the image N9TAPE.JPG opens N9TAPE.HTM in a new window called

_adhesivetape

• the text Click Here links to the anchor TOP


IB10 06_0417_03/RP


*5230304889*





2 hours 30 minutes














2

© UCLES 2010 0417/03/M/J/10

You work for a company called Hothouse Design. You are going to create a new website for a client

called The Relaxing Holiday Company.

This website must open in any browser with any screen resolution. The homepage will have three

separate tables and look like this:

!


http://www.hothouse-design.co.uk/2010XJune to your own work area:

JXBOTTOM.HTM

JXBUBBLE.JPG

JXDEST.CSV

JXFOOTER.JPG

JXHARBOUR.JPG

JXIMG1.JPG

JXIMG2.JPG

JXIMG3.JPG

JXIMG4.JPG

JXIMG5.JPG

JXIMG6.JPG

JXPRES.RTF

JXSEA.HTM

JXSHEET.CSV

JXSTYLES1.CSS

JXSTYLES2.CSS

JXTEXT.TXT

JXTITLE.JPG

JXTOP.HTM

2 Using a suitable software package, create a new webpage called JXRHC.HTM

Attach the stylesheet JXSTYLES1.CSS to this webpage.

3


!

3 Create the top table using the contents of JXTOP.HTM

4 Set the table width to 100% of the width of the window.

5 At the top of the page create an anchor called START

6 Replace the text candidate name, Centre number, candidate number with your

name, Centre number and candidate number.

7 Create the middle table with 3 rows and 2 columns as shown in the diagram on

the previous page.

8 Set the table width of the middle table to 90% of the width of the window.

9 Align the middle table with the centre of the window, as shown in the diagram.

10 Place the image JXHARBOUR.JPG in the left cell of the middle table.

11 Use the contents of the file JXTEXT.TXT to place the following text, as style h3, in

the middle table:

• Place the text We offer holidays to suit your every need. We can often

make dreams come true. in the top right cell.

• Place the text Let us arrange your holiday for you. We will collect you from

your home and offer the best service of any holiday company in the world.

in the middle right cell.

• Place the text Combine our service with superb value for money. Contact

us with your requirements and we are sure that we will not disappoint you.

in the bottom right cell.

Make sure that the styles which are in the stylesheet are not over-ridden.

12 Create the bottom table using the contents of JXBOTTOM.HTM

13 Set the table width of the bottom table to 100% of the width of the window.

14 Replace the text Place anchor here with an anchor called SNOWZONE

This anchor must not be visible on the page.

15 Create a hyperlink from the word Snow in the top table to the anchor that you

have just created.

16 Make only the words Click Here a hyperlink to the anchor called START

17 Select the most appropriate image from those obtained in step 1 and use this to

replace the word Sun in the bottom table.


replace the word Sea in the bottom table.


replace the word Sand in the bottom table.


replace the words Contact us in the bottom table.

21 Set the width of each of the images placed in steps 17 to 20 to 160 pixels wide

and maintain the aspect ratio.

22 Create a hyperlink from the image placed in step 18 to point to the file

JXSEA.HTM which should open in a new window called _sea

23 Create a hyperlink from the image placed in step 20 to send an e-mail to

[email protected]

4

© UCLES 2010 0417/03/M/J/10

!

24 Save the webpage as JXRHC1.HTM


25 Attach the stylesheet JXSTYLES2.CSS to this webpage.

26 Save the webpage as JXRHC2.HTM

Open this page in your web browser. Print screenshot evidence of this page.

You may use more than one printout to make sure that the entire page is visible.

27 Open the webpage JXSEA.HTM

28 Attach the stylesheet JXSTYLES2.CSS to this webpage.

29 Make the text Home a link to JXRHC2.HTM

30 Save the webpage.

You are going to create a short presentation for the manager of The Relaxing Holiday Company about

the website.

31 Import the file JXPRES.RTF placing the text as slides in your presentation

software.

Remove any blank slides.

(This should provide four slides, each containing a title and some bullet points.)

Save the presentation.

32 The master slide must have:

• a white background

• your Centre number, candidate number and name in a 12 point black serif

font in the top right corner

• a clipart image of the sun as a logo in the bottom right corner

• slide numbers in the bottom left corner.

33 Insert a new slide before slide 1. Use a layout with a title and subtitle for the new

slide.

34 Enter the text New website for as a subtitle for the slide.

Enter the text The Relaxing Holiday Company as the title for the slide. Move this

title so that it is below the subtitle.

35 Explain why the background image in JXSTYLES1.CSS does not work. Give your

answer by replacing the text Answer 1 here on slide 4.

36 On slide 4 replace the text Answer 2 here with only the html code in the stylesheet

JXSTYLES1.CSS that makes the background colour of the page visible.

37 The stylesheet should have set h1 and h2 to white and h3 to yellow.

Explain why the text for styles h1 and h2 is not white and h3 is not yellow. Give

your answer by replacing the text Answer 3 here.

38 Describe what cell padding in html coding is. Give your answer by replacing the

text Answer 4 here on slide 5.

39 Describe what cell spacing in html coding is. Give your answer by replacing the

text Answer 5 here on slide 5.

40 On all slides make sure that no objects overlap any of the master slide items.

41 Save the presentation using a new filename. Print the presentation with two slides

on each page.

5


Your manager has asked you to manipulate a spreadsheet about some holiday bookings.

!

42 Using a suitable software package, load the file JXSHEET.CSV

43 Place your name, candidate number and Centre number in the footer.

44 In the Destination column use a lookup function to show the destination name.

Use the Code column for the lookup value and the file JXDEST.CSV for the array.

This function must include both absolute and relative referencing and must not

use a named range.

Replicate this function so that the destination name is shown for each of the

codes.

45 In cell C2 use a function to count the number of trips to the destination where the

value in the Dcode column matches the contents of cell B2. This function must

include both absolute and relative referencing and must not use a named range.

Do not count any entries in rows 1 to 24.

Replicate this function into cells C3 to C8 to calculate the number of trips booked

to each of the other destinations.

46 In cell C11 use a function to add the total number of days (using the Duration

column) for holidays starting in the month of January. This function must include

both absolute and relative referencing and must not use a named range.

Replicate this function into cells C12 to C22 to calculate the total number of days

booked for the other months in 2010.


48 Save the data model and print a copy of the cells A1 to C22 only, showing the

formulae used. Make sure that the contents of all cells in this range are visible and

that the printout fits on a single page wide.

Make sure that your name, candidate number and Centre number are printed in

the footer of the page.

49 Format only the cells in rows 1, 10 and 24 to be centre aligned.

50 Format only the cells in rows 1, 10 and 24 so that they are bold and italic.


52 Save the data model and print all of the sheet showing the values. Make sure that

the contents of all cells are visible and that the printout is one page wide.




54 Hide columns A, B and C.

55 Interrogate the data, to find all the bookings where the country is Egypt and the

discount is yes.

Print this data.



6





© UCLES 2010 0417/03/M/J/10


Highlight those portions of the code which show that:

• your name, Centre number and candidate number are present on all printouts

• the external style sheet is attached to the webpage

• the top and bottom tables have a width of 100%

• the middle table has a width of 90%

• the anchor Start has been placed

• the anchor Snowzone has been placed

• the hyperlink from the text ‘Snow’ links to the anchor ‘Snowzone’

• the hyperlink from the text ‘Click here’ links to the anchor ‘Start’

• the hyperlink from the sea image opens JXSEA.HTM in a new window called _sea

• the hyperlink from the ‘Contact us’ image sends an e-mail to [email protected]

information technology 0418/03 - ict & … technology 0418/03 paper 3 practical test ... website...

Documents