1) Excel school ordered computer accessories and the following suppliers provided
the following As illustrated below.
A B C D
1 Name Item Sold Amount Date
2 Joseph Mouse 200.00 2/11/2011
3 Peter System unit 5,000.00 3/11/2011
4 Tony Keyboard 200.00 4/11/2011
5 Mike CD Writer 2,000.00 5/11/2011
6 Joseph Computer1 2,000.00 6/11/2011
System
7 Peter Mouse 200.00 7/11/2011
8 Tony Mouse 200.00 8/11/2011
9 Mike System Unit 2,500.00 9/11/2011
10 Joseph Keyboard 200.00 10/11/2011
11 Peter CD writer 3,000.00 11/11/2011
12 Tony Computer 5,400.00 12/11/2011
System
13 Mike Mouse 200.00 13/11/2011
14 Joseph System Unit 3,000.00 14/11/2011
15 Peter Keyboard 200.00 15/11/2011
16 Tony CD Writer 2,500.00 16/11/2011
17 Mike Computer 6,000.00 17/11/2011
system
(a) Enter the data shown into a spreadsheet and save it the workbook as Excel. (11mks)
(b)Copy the content of Sheet 1 to Sheet 2 into the exact position and rename it as New
price.
i. Insert a new row after the Amount row and label it “New price”. The
suppliers of the items decided to, increase all their items by 20%. Enter
the percentage into cell A18.
ii. Using absolute referencing, calculate the New price of each of the
items in the “New price” column. (7mks)
(c)Copy the content of Sheet 1 to Sheet 3 and rename it as Subtotals. Using subtotals sheet
Find subtotals for each supplier and display the Grand Total. (6mks)
(d)Using the subtotals sheet, Create a column graph (bar graph) to compare the total cost
of
all items bought from each supplier. The x-axis should be labeled as “Names” and the y-axis
“cost items’. Each bar should display a total value it represents on top of it and the
supplier’s Name below it. The title of the graph should read, SUPPLIERS COMPARISON
TOTALS. Place the graph on a new sheet and rename the sheet as BAR GRAPH (8mks)
1 | P a g e FOR MS CALL US ON 0724351706 OR VISIT OUR WEB PAGE goldlitekcserevision.co.ke
(e)Insert a new sheet into the workbook. Rename this sheet as “Filtered”.
Open the subtotals sheet. Filter the records of all suppliers whose New price
is greater than or equal to 6000 or less than 250. Copy the results onto the
“Filtered” sheet. (6mks)
(f)Open sheet 1. Restrict all the cells in the Amount column to allow
entry of amounts between 0 and 6000. A message, “Input amount <= 6000”
should be displayed whenever a cell is selected. In case of an invalid entry, the
message, “Amount >6000”, should be displayed. Put an inside and outside
border on data on sheet 1.(5mks)
(g)Put the sheet name as the header and your name. School and index number
as footer for every sheet in your workbook. Save your work on a removable
storage media and print ALL the worksheets
2 | P a g e FOR MS CALL US ON 0724351706 OR VISIT OUR WEB PAGE goldlitekcserevision.co.ke
