9 Mock Assignment

e-Quals – Spreadsheet - Level 1 Mock Assignment 2

Candidate Instructions

Candidates are advised to read all instructions carefully before starting work and to check with your assessor, if necessary, to ensure that you have fully understood what is required. You must, at all times, observe all relevant Health and Safety precautions. Time allowed 2 hours Introduction This assignment is broken down into 3 parts: 1. 2. 3.

A brief scenario is provided for candidates Task A requires candidates to create a spreadsheet from a user description and create appropriate formulas. Task B requires candidates to edit the spreadsheet produced for Task A.

Scenario You have to create a spreadsheet to be used to calculate discounts for large quantity paper. When customers come into the shop this spreadsheet can be used to estimate the costs for fulfilling their requirements. Task A You are required to create a spreadsheet based on Figure 1. The following changes must be made:

• • •

all column headings to be in bold main heading to be centred and font changed to Times New Roman, 14 pt, italics all remaining data should be Arial, 12pt

BALHAM PAPER SERVICES Quantity discount on orders of 20 or over DESCRIPTION PAPER SIZE Photo Paper Glossy Photo Paper Matt Photo Paper 360 dpi Inkjet Paper

A4 A4

PER SHEET 1.00 1.20

A4

1.20

A4

0.30

20

2%

50

Figure 1 1.

Start up the Spreadsheet software and start a new spreadsheet.

2.

Create the spreadsheet as Figure 1, taking account of the guidance notes in the scenario.

3.

Format the spreadsheet cells which will contain currency (£) to 2 decimal places.

4.

Add the formulas which calculate costs for printing for 20, 50 and 100.

From Mike Allen (adapted)

1

5%

100

10%

e-Quals – Spreadsheet - Level 1 Mock Assignment 2

Candidate Instructions

5.

Add the formulas that calculate the amount of discount eg cost for 20 multiplied by 2%.

6.

Save your spreadsheet as BALHAM1 with the appropriate file extension.

7.

Print out the spreadsheet as A4 landscape and label it BALHAM1.

Task B 1.

Add the data and use formulas to calculate the costs and discounts shown in Figure 2 - check that the format is still 2 decimal places.

30

3%

40

4%

Figure 2 2.

Insert a new row after Photo Paper: Description - Photo Quality Inkjet paper Size - A4 Price per sheet - 30p

3.

Insert a new row after Glossy Photo Paper Description - Glossy Photo Paper Size - A5 Price per sheet – 70p

4.

Insert a new row after Matt Photo Paper: Description Matt Photo Paper Size - A5 Price per sheet - 70p

5.

Create the formulas to calculate these new costs and discounts - check the format is still 2 decimal places.

6.

Save your spreadsheet as BALHAM2 with the appropriate file extension. Print out a copy of this revised spreadsheet in A4 landscape and label it BALHAM2.

7

Swap the row ‘Photo Paper’ with ‘360 dpi Inkjet Paper’ using the move, insert and delete functions. Check that the calculations are still correct.

8.

Save the spreadsheet as BALHAM3 with the appropriate file extension. Print out a copy of this revised spreadsheet in landscape and label it BALHAM3.

9.

Set the spreadsheet to print formulas, row numbers, column letters and print a copy in landscape. Label this printout BALHAM4

10. Close down the spreadsheet software. Note • • •

At the conclusion of this assignment, hand all paperwork and disks to the test supervisor. Ensure that your name is on the disk and all documentation. If the assignment is taken over more than one period, all floppy disks and paperwork must be returned to the test supervisor at the end of each sitting.

From Mike Allen (adapted)

2