E S
S E N T I A L I T A S S I G N M E N T S
Assignment
4.1
1. Load the spreadsheet program.
2. The following spreadsheet shows the
number of cars sold by Kells Motors for the
months January to March:
A
B C
D
1 Kells
Motors
2 Car
Jan
Feb
Mar
3
Makes
4 BMW
15
10
8
5
Ford
31
22
17
6
Mercedes 12
11
9
7
Renault
20
18
14
8
Toyota
27
21
15
You are required to enter
the data as shown onto a spreadsheet.
3. Alter the width of column A to best
accommodate Kells Motors.
4. Delete the contents of cell A3.
5. Change the contents of cell D6 to 8.
6. Save the spreadsheet as Car to your
disk.
7. Close the file.
Assignment
4.2
1. Load
the Car file from your disk.
2. Enter
the label Total in cell E2.
3. Enter
the correct formula in cell E4 to calculate the sum of cells B4:D4.
4. Replicate
the formula in cell E4 to cells E5, E6, E7 and E8.
5. Enter
the label Total in cell A9.
6. Enter
the correct formula in cell B9 to calculate the sum of cells B4:B8.
7. Replicate
the formula in cell B9 to cells C9 and D9.
8. Align
the labels in cells B2:E2 to the right.
9. Save
the spreadsheet as Car2 to your disk.
10.
Close
the file.
E S S E N T I A L I
T A S S I G N M E N T S
Assignment
4.3
1.
The
following spreadsheet shows the number and type of
medals
won by children from 6 different towns at the recent county games:
A
B
C
D
1 County
Games
2 Town
Gold Silver
Bronze
3 Ashton
4
8
5
4 Bradbury
7
6
7
5 Claxton
3 5
7
6 Newbury
6
1
4
7 Roetown
8
4
6
8 Treeborn
2
6
1
You are required to enter
the data as shown onto a spreadsheet.
10.Close the file.
Assignment
4.4
1. Load the file Car2 from your disk.
2. Insert a new column titled Apr between
the Mar and Total
columns.
3. Enter the following data into this new
Apr column:
6
12
8
11
10
1. Amend the totals to take account of this
new insertion.
4.
Insert
a new row titled Honda between the Ford and Mercedes rows.
5.
Enter
the following data into this new Honda row:
a. Honda
20
17
15
12
6.
Amend
the totals, where necessary, to take account of this new insertion.
7.
Insert
the label Average in cell G2.
8.
Enter
a formula in cell G4 to calculate the average monthly sales of BMW cars.
9.
Replicate
the formula in cell G4 to cells G5:G9.
10. Format
the numbers in the average column to whole number format (no
11. decimals)
12. Save
the file as Car3 to your disk.
13. Close
the file.
E S S E N T I A L I
T A S S I G N M E N T S
Assignment
4.5
1. The spreadsheet below shows test results
for a group of students. The
maximum
mark available in each test
was 10:
A
B
C
D
E
1 Test Results
2
3 Student Test
1 Test
2 Test 3 Test 4
4
5 D Kirwan
9
9
7
8
6 A Nielsen
0
8
5
4
7 P Bradshaw
2
7
7
7
8 F Cox
9
10 8
7
9 R Saunders
9
7
8
9
10 S Quinlan
3
6
0
0
11 K Doherty
3
6
5
3
12 H Mason
9
10
8
5
1. Enter the data given onto a spreadsheet.
2.
Add
a new Overall Marks column to show the total marks obtained by
4.
each
student in all four tests.
3.
Add
a new Average Marks column to show the average mark for each
5.
student
in the four tests.
4.
Format
the numbers in the Average Marks column to one decimal place.
5.
Amend
the marks for D Kirwan, P Bradshaw and H Mason in test 3 to 9,
6.
9
and 10
7.
respectively.
6.
The
mark for A Nielsen in test 4 should be 7.
7.
Add
a new Test Average row to show the average mark for each test.
8.
Format
the numbers in the Test Average row to one decimal place.
9.
Align
the data in columns B to G centrally in the relevant cells.
10. Format
the heading Test Results to Bold type.
11. Save
the spreadsheet as Tests to your disk
12. Close
the file.
E S S E N T I A L I
T A S S I G N M E N T S
Assignment
4.6
1.
The
spreadsheet below shows book sales at a bookshop (Books Unlimited) for each of
the four seasons last year:
A
B
C
D
E
1 Books
Unlimited
2
3 Category
Spring
Summer
Autumn
Winter
4
5 Biography
101
78
109
113
6 Cookery
67
72
82
251
7 DIY
177
161
344
310
8 Gardening
312
238
102
55
9 Romance
122 315
207
402
10 Thriller
97
145
215
379
11 Travel
368
589
233
190
1.
Enter the data given onto a spreadsheet.
2.
Add
a new column (F) titled Total showing the total number of each type of book sold
for the entire year.
3.
Add
a new column (G) titled Average showing the Average number of each type of book
sold each season.
4.
Format
the numeric contents of the Average column to whole number(i.e. no decimal)
5.
Add
a new row (13) titled Total showing the total number of books sold each season.
6.
Align
the main heading - Books Unlimited - centrally over the columns.
7.
Insert
a new row - Sport - between Romance and Thriller rows and center the following
details:
Sport 103 212
176
388
8.
Amend
the total and average columns, where necessary, to
take account of this insertion.
9. Change the entries in rows 1 and 3 to
bold format.
10. Ensure that the entries in columns B
to G are aligned
centrally in the relevant cells.
11. Save the spreadsheet as Books to your
disk.
12. Close the file.
E S S E N T I A L I
T A S S I G N M E N T S
Assignment
4.7
1. The spreadsheet below shows toy sales
made by six different
salespeople during a
three-month period:
A
B
C
D
E
F
G
1 Sales
Figures
2
3 Salesperson Jan Feb Mar Total No. Toy Cost Amount
4
5 Kent P
550
580 650 £3.00
6 Woods O
600
600 600
£3.50
7 Piper R
630
630 635
£3.00
8 Roberts M 450 475 500
£4.00
9 Strange W 650 700 500
£4.00
10 Andrews N 510 480 495
£5.00
11
12 Monthly
Totals
1. Enter
the data given onto a spreadsheet. Take care with
the
data for column F.
2. Enter
values and then format the entries to currency display).
3. Widen
column A to best accommodate the label Monthly Totals.
4. Use
the appropriate formulae to calculate the:
5. Total
No. of toys sold by each person (Use the summation
a. function)
b. Amount
(Total No * Toy Cost)
c. Monthly
Totals (in cells B12, C12 and D12)
6. Format
the numbers in the Amount column to currency with no pence(i.e. no decimal
places).
7. Insert
a column with the heading Apr between the Mar and Total No columns.
8. Insert
the following figures for each salesperson: Kent : 550, Woods : 450, Piper :
570, Roberts : 350, Strange : 600 and Andrews : 490.
9. Amend
the formulae, where necessary, to take account of this insertion.
10.
Change
all the toy costs to £6.00.
11.
In
cells A1, change the main heading - Sales Figures - to bold format.
12.
Align
the main heading - Sales Figures - centrally over the columns (A to H).
13.
Underline
the label `Monthly Totals' in cell A12.
14.
Ensure
that the entries in columns B to H are aligned centrally.
15.
Save
the amended spreadsheet as Sales to your disk.
16.
Close
the file.
E S S E N T I A L I
T A S S I G N M E N T S
Assignment
4.8
4.
The
spreadsheet below shows the number of houses sold by
six different real estate companies in five
different
towns in one month:
A B C D E F G H
1
House Sales
2
3 Agent
Marbury Cantwell Denton Leeside Wickham Comm.
Amount
4
5 Smith & Jones 45 6 56 34 7 1500
6 Potterton 12 9 27 51 30 1500
7 Gallaghers 19 19 20 15 33 1500
8 Gunne Estates 27 25 45 22 18 1500
9 Sherry & Co 44 62 38 22 19 1500
10
Dream Homes
39
52
31 27 27 1500
11
12
Total per Town
4.
Enter
the data given onto a spreadsheet.
5.
There
is an error in the data. Gallaghers
sold 25 houses in
6.
Leeside.
7.
Delete
the Wickham column from the spreadsheet.
8.
The
commission should be displayed in currency format (no decimal places).
9.
Enter
formulae in the appropriate cells (B12 to E12) to show the total number of
houses sold in each Town.
10. Insert
a new Total/Agent column before the Commission column.
11. Widen
the column to best accommodate the new Total/Agent label.
12. Enter
appropriate formulae in this new column to show the total number of houses sold
by each estate agent.
13. Enter
appropriate formulae in the Amount column to show the total commission earned by
each estate agent
(Hint:
Total/Agent * Commission).
14. Save
the spreadsheet as House.
15. All
estate agents have decided to increase their commission to 1800 per house.
16. Amend
the appropriate entries.
17. Ensure
that the entries in columns B to H are aligned centrally.
18. Save
the amended spreadsheet as House2.
E S S E N T I A L I
T A S S I G N M E N T S
Assignment
4.9
1. The spreadsheet below shows the present
and previous electricity
meter readings for ten
different customers:
A
B
C
1 Electricity
Bills
2
3 Customer
Previous
Present
4 Name
Reading
Reading
5
6 Sandra
Atkins
35839
36852
7 Harold
Bradshaw
13538
14011
8 Anne
Browne
20153
20542
9 Peter
Curle
28603
29256
10 Ken Dobson
32568
33410
11 Denise Martin
42398
43630
12 Jean Paget
15644
16731
13 John Quinn
22715
23510
14 Jill Shaw
9638
10263
15 Keith Williams
17988
18346
3.
There
is a standing charge of £5.80 on each customer's account. Add a
4.
Add
an Amount Payable column (H) to show the actual amount of money payable
5.
Insert
the following details on Lisa Glover's account, preserving the
i.
Previous
Present
ii.
Reading
Reading
iii.
23488
24064
6.
Obtain
the amount payable by Lisa.
7.
Format
the columns containing money amounts to currency display.
8.
Ensure
that the entries in columns B to H are aligned to the right in the
9.
Save
the spreadsheet as Bill.
10. Print
the entire spreadsheet centred horizontally and vertically on the page.
41
E S S E N T I A L I
T A S S I G N M E N T S
Assignment
4.10
1. Quality Copiers Ltd. rent photocopying
machines to companies. The
spreadsheet
below shows the counter
readings on the photocopiers rented by ten different
companies:
A
B
C
1 Quality Copiers Ltd.
2
3 Company
Last
Current
4 Name
Reading
Reading
5
6 BC Services Ltd. 31977
33668
7 Brogan & Co.
43222
46472
8 Connolly & Sons
83661
84700
9 Energywise
Ltd.
15003
16191
10 Fastline Ltd.
19981
21130
11 Kitt Packaging
64812
66917
12 Lee Electrical
36391
38110
13 Marsden & Co.
28901
30121
14 Safeclean Ltd.
18368
21555
15 Windowland
Ltd.
51252
54213
Enter the data given onto a
spreadsheet.
2.
Add a Copies Made column to show the number of photocopies made since the
last reading of the photocopiers'
counters (Hint: Subtract the last reading
from the current reading).
3.
Add a Unit Cost column after the Copies Made column showing the cost of one
copy (0.05).
4.
Add a Copies Cost column to show the actual cost of copying for each business.
5.
Add a Service Charge column displaying the amount (30.00).
6.
Add a Bill Total Column (Hint: Copies Cost + Service Charge).
7.
Add a Tax Payable column showing the tax payable on each bill. Tax is
calculated at 20% of the total
bill.
8.
Add an Amount Payable column (Bill Total + Tax Payable).
9.
Format all columns containing money to currency format.
10.Add
your name as a header in the top left-hand corner of the spreadsheet.
11.Ensure
that the entries in columns B to J are aligned to the right in the
cells.
12.Save
the spreadsheet as Copier to your disk.
13.Print
the entire spreadsheet centred horizontally and vertically on the page.
The printout should be in
landscape orientation.
14.Copy
all the details to a new worksheet within the same spreadsheet file.
15.Save
the spreadsheet as Copier2.
42
E S S E N T I A L I
T A S S I G N M E N T S
Assignment
4.11
1. The spreadsheet below shows the numbers
and cost prices of electrical items
sold by Smith's Electrical
Store during the past week:
A
B
C
1 Smith's Electrical Store
2
3 Item
Quantity
Cost
4 Name
in Stock Price (£)
5
6 Camcorder
13
485
7 Computer
16
450
8
Dishwasher
11
215
9 Electric
Cooker
7
360
10 Electric
Heater
6
40
11 Electric
Lawnmower
4
195
12 Fridge Freezer
25
285
13 Hi-Fi System
15
170
14 Microwave Oven
19 95
15 Television
22
255
16 Video
29
220
17 Washing
Machine
23
255
Enter the data given onto a
spreadsheet.
2.
Add a Profit Column. Profit is calculated at 30% of the cost price of an item.
3.
Add a Retail Price column (Cost Price + Profit).
4.
Add a Total Retail column to show the total amounts of money made by
selling
all of the items in stock (Retail
Price * Quantity in Stock).
5.
Format all columns containing money amounts to currency display.
6.
Ensure that the entries in column B are centrally aligned in the cells.
7.
Ensure that the entries in columns C to F are aligned to the right in the
cells.
8.
Save the spreadsheet as Electric to your disk.
9.
Open a new worksheet within the Electric Spreadsheet and copy the details from
the original to the new
worksheet.
10.Mr
Smith has decided to hold a sale.
The profit will only be 20% of the cost
price. Amend the appropriate
formulae on the second worksheet to take account
of Mr.Smith's generosity.
11.
Enter your name, in a central position, as a header on the worksheet.
12.
Enter today's date, in a central position, as footer on the worksheet.
13.
Change the name on the second worksheet tab to Reduction.
14.
Re-save the spreadsheet.
15.
Print both worksheets.
43
E S S E N T I A L I
T A S S I G N M E N T S
Assignment
4.12
Task
1
1. The spreadsheet below shows the numbers
of students in a class who achieved
various grades in an
examination:
A
B
1 Examination
Results
2
3 Result
Number
4
5
Distinction
4
6 Merit
6
7 Credit
9
8 Pass
7
9 Fail
4
Enter the data given onto a
spreadsheet.
2.
Produce a pie graph of the examination results shown.
The graph should have:
· Examination Results as its title.
· A legend (colour code for the segments).
· The actual values (data labels) placed
beside each segment.
3.
Save the spreadsheet and graph as Results to your disk.
4.
Close the file.
Task
2
1. The spreadsheet below shows the numbers
of personal computers sold by a
company during the first six
months of the year:
A
B
1 Computer
Sales
2
3 Month
Sales
4
5 Jan
73
6 Feb
85
7 Mar
97
8 Apr
68
9 May
59
10 Jun 75
Enter the data given onto a
spreadsheet.
44
E S S E N T I A L I
T A S S I G N M E N T S
2. Produce a column (bar) graph of the
monthly computer sales.
The graph should have:
· A chart title - Computer Sales
· An x-axis title - Month
· A y-axis title - Unit Sales
3. Save the spreadsheet and graph as
Comgraph to your disk.
4. Print the graph only.
5. Close the file.
Assignment
4.13
Task
1
1. The spreadsheet below shows a company's
profits in millions of pounds over a
five-year period:
A
B
1
Company
Profits
2
3 Year Profit (Mil £)
4
5 1996
3.1
6 1997
4.5
7 1998
4.8
8 1999
5.0
9 2000
5.6
Enter the data given onto a
spreadsheet.
2. Produce a line graph of the annual
profits.
Add the following text to
the graph:
· Chart title - Company Profits
· X-axis - Year
· Y-axis - Profits (in £Millions)
3. Save the spreadsheet as Profits to your
disk.
4. Print the graph.
5. Copy the graph from the original to a
new worksheet.
6. Alter the y-axis scale to start at `3'
on the graph in the new worksheet
7. Re-save the spreadsheet as Profits2.
8. Print the graph centered horizontally
and vertically on the page.
9. Close the file.
45
E S S E N T I A L I
T A S S I G N M E N T S
Task
2
1. The spreadsheet below shows the numbers
of packets of three different cereals
sold at a store during the
year:
A
B
C
D
1
Cereal Sales
2
3 Month Weetabix
Cornflakes
Porridge
4
5 Jan
115
67
213
6 Feb
141
80
198
7 Mar
122
97
155
8 Apr 139
120
91
9 May
94
179
48
10 Jun
86
230
32
11 Jul
102
214
22
12 Aug
113
182
34
13 Sep
122
133
48
14 Oct
105
101
78
15 Nov
122
92
119
16 Dec
133
78
156
Enter the data given onto a
spreadsheet.
during the year.
Add the following text to
the graph:
· Main heading - Cereal Sales
·
X-axis - Months
· Y-axis - Unit Sales
The graph should include a
legend showing a different colour for each cereal
given.
3.
Save the spreadsheet and graph as Cereals to your disk.
4.
Close the file.
46
E S S E N T I A L I
T A S S I G N M E N T S
Assignment
4.14
Task
1
1. The spreadsheet below shows the numbers
of guests of differing nationalities
who stayed at the Russell
Hotel over a three-year period:
A
B
C
D
1
Russell Hotel - Guest Numbers
2
3
Nationality 1998
1999
2000
4
5 American
2500
3100
2400
6 British
3400
3000
2700
7 French
1700
1800
2100
8 German
1400
1100
900
9 Other
2000
2300
2100
Enter the data given onto a
spreadsheet.
2. Produce a column graph depicting the
numbers of foreign guests who stayed at
the Russell Hotel over the
three-year period.
Add the following text to
the graph:
· Main heading - Russell Hotel Guests
1998-2000
· X-axis - Nationality
· Y-axis - Tourist Numbers
Add a legend to the graph
showing a colour for each year. The
graph should
also have data labels over
each column.
3. Save the spreadsheet and graph as
Tourists.
4. Print the graph.
5. Close the file.
47
E S S E N T I A L I
T A S S I G N M E N T S
Task
2
1. The spreadsheet below shows the
distances travelled and time taken by twenty
delivery trucks:
A
B
C
1
Quinn Building Supplies
2
Deliveries Made
3
4 Delivery Time
Distance
5 Number
(Hours)
(Miles)
6
7
1
1.5
61
8
2
2.8
103
9
3
0.6
15
10
4
1.3
53
11
5
2.6
99
12
6
0.75
22
13
7
1.8
64
14
8
2.4
85
15
9
1.1
44
16
10
0.8
27
17
11
3.0
94
18
12
1.2
40
19
13
3.3
105
20
14
1.0
36
21
15
2.2 72
22
16
1.9
76
23
17
0.6
20
24
18
1.4
50
25
19
3.1
110
26
20
2.3
74
Enter the data given onto a
spreadsheet.
2. Produce an XY (scatter) graph to show
the relationship between the delivery
times and distances.
Add the following text to
the graph:
· Main heading - Distance Vs Time
· X-axis - Time (in hours)
· Y-axis - Distance (in miles)
3. Alter the x-axis scale to start at
`0.5'.
4. Save the spreadsheet and graph as
Deliveries.
5. Close the file.
48
E S S E N T I A L I
T A S S I G N M E N T S
Assignment
4.15
1. The spreadsheet below shows the numbers
of different main courses served last
week at Sherry's restaurant.
A
B
C
D
E
F
1 Sherry's
Restaurant
2
3 Main Course
Mon
Tue
Wed Thu
Fri
4
5 Beef
14
17
19
16
19
6 Chicken Curry
10
8
12
11
13
7 Lasagne
12
9
20
15
13
8 Salad
22
28
24
12
35
9 Shepherd's Pie
19
13
29
24
27
10 Turkey & Ham
19
21
15
17 23
Enter the data given onto a
spreadsheet.
2.
Add a Total column to show the total number of each main course served over
the week.
3.
Add an Average column to show the average number of each main course
served daily.
4.
Add a Total row to show the total number of main courses served each day.
5.
Ensure that the entries in columns B to H are aligned to the right in the
cells.
6.
Save the spreadsheet as Meals to your disk.
7.
Copy the details from the original to a new worksheet within the Meals
spreadsheet.
8.
Produce a column graph using the range of cells A5 to F10 on the new
worksheet.
Add the following text to the
graph:
· Main heading - Sherry's Restaurant
· X-axis - Main Course
· Y-axis - Numbers Sold
9. Copy the details from the original to a
new worksheet within the Meals
spreadsheet. (Your
spreadsheet file will now contain three separate
worksheets).
10.
Produce a pie graph, using the details in columns A and G only, on the new
worksheet. (Hint: You must select the cell ranges
A5:A10 and G5:G10
together).
Add Breakdown of Sales as
the main heading for the graph. The
graph should
have a legend and show the
values opposite each segment.
11.
Enter your name, in a central position, as a header on the worksheet.
12.
Enter today's date, in a central position, as footer on the worksheet.
13.
Change the names on the first, second and third worksheet tabs to Original,
Column and Pie respectively.
14.
Re-save the spreadsheet as Meals2 to your disk.