PHS CyberSchool

Computer Studies SG - Grade 12 - 2007

Modified 20 March 2007

Excel test - 1 period - School bookshop

Rename the file as YourSurnameExcel. Save your file in CSSG Projects Term 1
All questions are based on the spreadsheet OutstandingBooks.
Use Excel spreadsheet features to answer the questions/complete the instructions.

Some learners work in the bookshop. To get into the system they need a login. The login is made up of the 1st letter of their First name, the last 3 letters of their Surname, and 5th and 6th letters of their ID number. Create the logins of the learners using Excel features such as functions and formulas. Create the formula in one cell on the worksheet Logins in the Logins column. Display all logins as capital letters. Complete the worksheet for all the listed employees. (6)
Learners that do not return their text books at the end of the year, must first pay for them before they may receive their reports. The book codes, subject names and costs are saved in the worksheet Costs. In the Slip worksheet insert Excel functions for the subject and cost to calculate the total amount owing in currency for the
Learner: Isaac Monde Book codes: TT779, WI077, LW322.
Complete his ‘Textbook outstanding’ slip. Rename the worksheet Monde. (5)
The school’s bookshop is normally very busy. Things must be done quickly and efficiently. Create a way in which the manager can add the towns in which the staff live using a drop down box. Use the worksheet Addresses. They all live in Bonteheuwel, Brooklyn, Goodwood, Kensington, Manenberg, Pinelands and Ruyterwacht. (5)
Use a pivot table in the worksheet Costs to find the average costs of the different textbooks based on subject. Place the information on a worksheet renamed Average Costs of Subjects. Display currency in currency format. (3)
Use a pivot table in the worksheet Costs to find the total costs of just the Afrikaans, Computer Applications Technology, English and Information Technology books. Place the information on a worksheet renamed Costs NB Subjects. Display currency in currency format. (3)
Use the worksheet For subtotals and display the total number of the books in the different subjects using the Sub Totals feature. (3)
On a new sheet create a switchboard to get to the worksheet showing the (3)
1. average costs of the different textbooks. (Q 4)
2. total costs of the Afrikaans, Computer Applications Technology, English and Information Technology books (Q 5).
Save your work at the end so that it opens on the switchboard sheet. In the spreadsheet’s properties add the following comment: Useful skills for a small shop. (2)

[30]