PHS CyberSchool

Computer Studies SG 

Modified 30 September 2003

School telephone costs

A school would like to use a spreadsheet to analyse data in connection with using the phone. The following information is of importance:

• Calls can be made either in peak time (7h00 - 19h00), or after hours (19h00 - 7h00)
• The total call times for each day are keyed in in minutes in columns B and C.
• The cost of a call made during peak time is calculated by multiplying the number of minutes with the tariff given in cell G2.
• The cost of a call made after hours is calculated by multiplying the number of minutes with the tariff given in cell H2.
• The total cost of peak time and off peak time calls are calculated in column D.
• In column E the VAT is calculated at 14% of the corresponding value in column D.
• In column F the sum of the corresponding values of columns D and E is calculated.

Create the above spreadsheet precisely according to the cell references.

1. Type your name in cell B2 and student number in cell B3.
2. Change the width of the columns so that the data will be displayed neatly but still can be printed on an A4 page.
3. Key in a formula in D5, which will calculate the total cost of peak and after hours calls for Monday.
4. Copy the formula from D5 to D6-D9.
5. Key in a formula in E5 that will calculate the VAT (at 14%) of the sub-total in D5. (Multiply D5 with 0.14)
6. Copy the formula from E5 to E6-E9.
7. Key in a formula in F5 that will add the values of the sub-total and the VAT.
8. Copy the formula from F5 to F6-F9.
9. Key in a formula in B11 that will calculate the average number of minutes of peak-time calls.
10. Copy the formula from B11 to C11.
11. Key in a formula in F10 that will calculate the total cost of all calls made during the week.
12. Also determine the totals of the sub-total and VAT columns respectively.
13. Display the values in columns B and C with 1 decimal digit.
14. Display the values in columns D, E and F as currency with two decimal digits.
15. Create a (clustered) column chart comparing the number of minutes of calls made in peak time and after hours for the different days of the week (not weekend).

• The chart title must be your name.
• The label of the x-axis should be Days of the week.
• The label of the y-axis should be Minutes.
• Create suitable legends.

16. Print the spreadsheet and graph (chart) on ONE sheet of paper.

Do the following to change the spreadsheet to be displayed as shown above. (Xxx indicates where values are calculated by formulas):

1. Centre the column headings which appear in row 4.
2. Insert an empty row between the column headings and the first data row.
3. Insert an empty row before the row Total.
4. Insert two more rows after Friday and type in Saturday and Sunday.
5. Add an extra column after the "After hrs" column. This column must be used to indicate calls made to connect to the Internet.
6. Type the data into the new column D.
7. Adjust the formula in E6 to include the calls made to connect to the Internet in the calculation of costs. (Internet call costs are calculated at Tariff 2 rates).
8. Copy the adjusted formula from cell E6 to the range E7 - E12.
9. Complete the spreadsheet to calculate the VAT and total for Saturday and Sunday.
10. Sort the data in connection with the days as cost in descending order of column G.
11. Print the spreadsheet as formulaes.

Hand in TWO sheets of paper.

Return to  Computer Studies lesson plan index. This work may be freely copied for non-commercial use only. Copyright © Pam Miller.