Copy the spreadsheet called Grade 10 Marks



Go to the Delegates Worksheet

1. Change the font of the text in cell A1 to Arial 14.

2. Widen column E by dragging it so that the numbers are all visible i.e. you don’t see ‘hash signs’ - ####

3. Add the heading SURNAME in cell B4.

4. Highlight the cells in the range A4:F4 and fill the cells using the pale blue colour.

5. Highlight the cells in the range A4:F4 and centre the contents both horizontally and vertically.

(Select the cells and choose Format | Cells and

click on the alignment tab)

6. Change the row height of row 4 to 50 points

(Right-click on the row number and choose Row Height)

7. Place a red border around cells A1:D1. (Format | Cells and click on the Border Tab)

8. Place a blue top and bottom border (only) in the cells in the range A4:F4.

9. Add black cell borders around the cells in the range A5:F75.

Wrap the text in cells A4:F4 (Select the cells and choose Format | Cells and

click on the alignment tab)

10. Change the text orientation of the heading NIGHTS in cell E4 so that the text runs vertically.

(Select the cells and choose Format | Cells and click on the alignment tab)

11. Change the colours of the tabs containing the worksheet names of Delegates and My Marks to colours that have not been used on the tabs for the other worksheets. (Right-click on the Tab name)

12. Rename the worksheet Delegates as Accommodation. (Right-click on the Tab name)

13. Locate the row containing the data for the delegate Dina Styger and change the name of this delegate you your own name and surname.

Hint: You can save a lot of time by selecting the surname column and searching for the surname Styger using the Find utility (Under the Edit Menu)

14. Move the Accommodation worksheet so that it appears just before after the Tours worksheet.

(right-click on the Accommodation tab and select the Move or copy option)

15. Insert another worksheet called Venues so it appears as the last worksheet in the spreadsheet (after the existing worksheets)

Insert | Worksheet and then rename the worksheet and move it)

16. Go back to the Accommodation worksheet and change the width of column D to 9 points. (Right-click on the column & select Column Width)

17. Use the Find and Replace function to replace all occurrences of female with f in column D.

Select the column and use the Edit | Replace menu option accordingly)

18. Swop the Name and Surname columns around without retyping the data.

Hint: Insert a blank column first perhaps? (Right-click on the column ‘letter’ and choose Insert and then do some copying/cutting and pasting perhaps?)

19. Merge and centre the contents of cells A1:D1.

20. Add the following formula in cell F5 to calculate the cost of the accommodation if the cost per night is R 170: = E5*170

21. Copy the formula down for the rest of the delegates.

22. Format the values in column F as currency (Rand) with two decimal places.

Select the column and Format | Cells and choose the Currency option- 2 decimal places.

23. Format the values in column E as numbers with no decimal places.

Select the column and Format | Cells and …...

24. Change the font colour of the districts in column A to blue.

25. Add red top and bottom borders to cell F76.

26. Add a formula in cell F76 to get the total amount spent on accommodation

27. Add a formula in cell E77 to calculate the maximum number of nights of accommodation

28. Add a formula in cell E78 to calculate the minimum number of nights of accommodation

29. Add a formula in cell E79 to calculate the average nights of accommodation

30. Copy the formulas you added in E77:E79 to the next column i.e. to cells F77:F79.

Notice how these formulas automatically ‘re-adjust’ for you.

31. A deposit of 15% of the total accommodation cost (stored in cell F76) needs to be paid in advance. Add the following formula in cell H77 to work out this amount:

=15%*F76

32. Add a formula to work out the number of delegates who have paid in cell J3.

33. Add a formula to work out the percentage of delegates who have paid in cell K3.

34. Sort the data alphabetically according to surname and name.

Select all the data including the headings i.e. A4:F75 and then select the Data | Sort menu option. Make sure the My data range has a Header row is selected and then select the Surname and name fields (in that order)

35. Inset a blank row between the current row 4 and 5 and save the spreadsheet.

Go to the Class Marks Worksheet

1. Merge and center cells A1:E1.

2. Add grid lines around the cells in the range A4:P25.

3. Change the text orientation of the heading in cell A1 to 45 degrees.

4. Wrap the cells in row 4 and fill all the headings in row 4 in Sky Blue.

5. Delete row 2 and insert a new row between row 4 and 5.

6. Enter formulas in column E to add the test marks in the previous two columns (C and D).

7. Enter formulas in column I to add the two exam marks in the previous two columns (G and H).

8. Enter formulas in column J to work out the percentage obtained in the exams (out of 250).

9. Enter formulas in column L to work out the CASS mark which is the sum of the Tests (column E), the Project (column F) and the total exam mark (column I).

10. In column M, enter formulas to convert the CASS mark to a mark out of 300

(Hint: Divide by 400 and times by 300).

11. Format your answers in column M to zero decimal places.

12. Enter formulas in column N to work out the final mark which is the total of the CASS mark (300) and the PAT (100).

13. Enter formulas in column O to work out the final mark as a percentage. Format all your answers to one decimal place.

14. Enter a formula in cell C26, to find the total of that column. Now copy this formula across for the rest of the columns.

15. Enter a formula in cell C27, to find the biggest value in that column. Now copy this formula across for the rest of the columns

16. Enter a formula in cell C28, to find the smallest value in that column. Now copy this formula across for the rest of the columns.

17. Enter a formula in cell C29, to find the average value in that column. Now copy this formula across for the rest of the columns.

18. Format all the values in row 29 to one decimal place.

19. Insert a new column between Final (column N) and the Percentage (column O).

20. Add a heading called Promotion Mark for this column.

21. Add formulas in this column to calculate the Promotion Mark which is formed by taking 10% of the CASS mark in L4 and adding 24% of the Exam Total in column I.

22. Format your answers to one decimal place.

Go to the Saturday Sales Worksheet

1. Merge and center cells A1:I4

2. Centre the heading Saturday Rugby Sales vertically within the merged sales.

3. Fill the merged cells in yellow.

4. Wrap and centre all the headings in row 5

5. Add cell borders around the range A5:I20

6. Widen column A so all the names of the items on sale are visible.

7. Format the range B6:G15 to currency (Rand) with two decimals.

8. Change the cost price of Coke to R 3.99

9. Add formulas in column C to calculate the mark-up on the items which is calculated as 10% of the Cost Price in column B.

10. Add formulas in column D to calculate the price with the mark up (in other words added to the cost price).

11. Add formulas in column E to calculate the VAT (at 14%) charged on the price in column D.

12. Add formulas in column F to calculate the price including VAT by adding the previous two columns.

13. Add formulas in column G to calculate the profit made per item by finding the difference between the price with VAT (column F) and the Cost Price in column A.

14. Add formulas in column I to calculate the total profit by multiplying the number sold by the profit per item.

15. Add a new item of Chocolates in row 16 with a Cost price of R 2.99 and a quantity sold of 30. Copy all the other formulas as needed for the other columns for this item.

16. Add formulas in row 17 to find the totals of each of the columns.

17. Add formulas in row 18 to find the average of each of the columns.

18. Add formulas in row 19 to find the largest value in each of the columns.

19. Add formulas in row 20 to find the smallest value in reach of the columns.

Go to the Tours Worksheet

1. Format cells D5:D13 to two decimal places.

2. Change the text orientation of cell A1 so that the text runs 'horizontally.

3. Wrap the headings Transport to venues and Total Cost of tour in columns J and K.

4. Add formulas in cells D8:D12 to work out the cost of the tour in Euros using the cost of the tour in Rand (column F) and the exchange rate in cell F1.

Note that an exchange rate of 9.63 means that R 9.63 is worth 1 Euro.

5. Add suitable formulas in column F to calculate the airport tax which is 4.5% of the cost of the flight in Rand.

6. Insert a column in between Column F and G (after Airport Tax) – add the following heading: Total Cost of Flight. Put in a suitable formulas to add Flight cost and Airport Tax in the new Column to give you the Total Cost of Flight (Use Column F and G to get the correct total).

7. Format the values in column F using the same format as used in column E.

8. Add suitable formulas in column I to work out the cost of the accommodation which is based on the cost per night and number of nights stayed.

9. Add suitable formulas in column K to work out the total cost of the tours which is sum of the flight cost in Rand, accommodation and transport to venues

10. Add suitable formulas in row 13 to total columns D to K.

11. Add a red border only to the upper and lower border of the cells in row 13. Do not remove any other borders.

12. Add a suitable formula in cell K15 to calculate the average cost of a tour.

13. Add a suitable formula in cell K16 to calculate the cheapest tour price.

14. Add a suitable formula in cell K17 to calculate what percentage the total cost of all flights (column G ) forms of the total tour prices (column K).

15. Make sure that all labels and data on your spreadsheet are visible.

Go to the My Marks Worksheet

1. Merge cells A1:F1 and centre the contents both horizontally and vertically.

2. Change the text direction of the text in cell A1 so that it runs vertically.

3. Add green cell borders around the cells in the range A5:K19.

4. Alter the headings in row 5 so that the text is NOT wrapped.

5. Change the background fill colour of the headings in row 5 to white.

6. Widen column E so that all the data is visible.

7. Format the data in column E to match the format in column D.

8. Add formulas in column F to calculate the difference between the Term 2 and Term 1 marks.

9. Add formulas in column G to calculate the CASS mark which is determined by taking 40% of the mark from Term 1 and 70% of the mark from Term 2.

10. Add formulas in column J to calculate the total mark out of 350 which is the sum of the CASS (column G) and the two exam papers in columns H and I.

11. Add formulas in column K to calculate the total mark (out of 350) as a PERCENTAGE (out of 100).

12. Add a red border to the bottom of the cells in row 19 only.

13. Add formulas in row 21 to find the totals of each of the columns D to K.

14. Add formulas in row 22 to find the averages of each of the columns D to K.

15. Add formulas in row 23 to find the largest value in each of the columns D to K.

16. Add formulas in row 24 to find the smallest value in each of the columns D to K.

17. Add a formula in D25 to calculate what percentage the lowest mark for Term 1 forms of the highest mark for Term 1.

Go to the My Budget Worksheet

1. Merge and centre cells A1:H1.

2. Fill the merge cells in a colour of your choice.

3. Add gridlines/borders around cells B4:C9 and F4:G18

4. Widen all the columns where the data or labels are not visible.

5. Enter a formula in cell C6 to calculate the MONTHLY salary using the data provided in the table in cells J1:K9

6. The birthday bonus in cell C7 is calculated as 25% of the Monthly salary.

7. Add a formula in cell K6 to calculate the average number of units sold in the previous year (in the table below in J11:K23)

8. The commission in cell C8 is calculated by multiplying the average number of units sold by the commission per unit sold (K7).

9. The performance bonus in cell C9 is calculated as 75c for every unit sold in the past year.

10. The tax in cell G6 is calculated as 25% of the monthly salary.

11. The medical aid in cell G7 is calculated as R 1000 plus a fixed cost per dependent. This information is available in cells K4 and K5.

12. The pension deduction in cell G11 is calculated as 8% of the monthly salary.

13. The UIF due in cell G12 is calculated as 5% of the monthly salary.

14. The school fees in G13 are charged per child. Use the data in the table in (J1:K9) to calculate the total cost of the monthly school fees. Note that the fees given are for a 10 month period.

15. The annual rates are calculated as 1.2% of the value of the house. Add a formula in cell G15 to calculate the MONTHLY rates.

16. Forma the figures in columns C and G to a currency format.

17. Enter a formula in cell K25 to calculate the largest single expense for the month.

18. Add Totals in row 23 to show the total expenses and total income.

19. Add a formula in cell K26 to work out what the savings are for this person for the month.

20. “Work out” what the annual salary of this person needs to be to save R 2000 per month. You can do this by manually ‘altering’ the annual salary in cell K2 as need be.

Go to the Employees Worksheet

1. Change the text orientation of the text in cell A1 so that the text runs horizontally.

2. Merge and centre cells A1:E2.

3. Wrap the headings in row 3.

4. Align the headings in row 3 so that they are centred both horizontally and vertically.

5. Add a yellow fill colour to the cells containing the headings in row 3.

6. Add blue borders around the cells in the range A3:L15.

7. Swop the name and surname columns around without retyping the data.

8. Format the dates in column D in the format of yyyy-mm-dd. For example 12 January 2007 becomes 2007-01-12.

9. Format the data in column E (Monthly Salary) to the exact same format as the one used in column F (Pension).

10. Add formulas in column F to calculate the Pension deductions which is 8.5% of the Monthly Salary.

11. Add formulas in column H to calculate the Tax using the Tax percentage in column G.

For example, if the monthly salary is R 10,500.00 R 12 500 and the Tax Rate percentage is 25%, then the tax is 25% of R10 500 or R 2,625.00.

12. Format your answers in column H to zero decimal places.

13. Add formulas in column J to calculate the Medical Aid deductions for the employees.

Employees pay R 750 plus R 300 per dependent for Medical Aid. For example if they have 2 dependents then the pay 750 + 2 X 300 = R 1350.

14. Add formulas in column K to determine the total employees' deductions which is the sum of the Pension, Tax and Medical deductions.

15. Add formulas in column L to calculate the nett salaries of the employees after deductions.

16. Make sure that all the data and labels in the spreadsheet are visible.

17. Add a formula in cell D19 to calculate the largest Monthly Salary.

18. Add a formula in cell D20 to calculate the average Monthly Salary.

19. Add a formula in cell D21 to calculate what percentage the total medical deductions (column J) form of the total of all deductions (column K).

20. Add a formula in cell D22 to calculate how much money the company would have to set aside for their total annual wage bill if they awarded their employees a 12.5% increase.

Go to the Finances Worksheet

1. Merge and centre the cells B2:F2 horizontally.

2. Format all monetary (money) values to Rand with 2 decimal places, making sure that all values are visible.

3. Wrap the headings in row 3.

4. Add a formula in cell D9 to determine the amount of the highest single expense in any of the terms.

5. Add a formula in cell D10 to determine the amount of the lowest sales (column G) in any of the terms.

6. Add a formula in cell D11 to determine the average telephone cost per term.

Format your answer to zero decimal places.

7. Add formulas in column F to calculate the total expenditure for each of the terms

which includes the wages, electricity and water costs, purchases and telephone costs (columns B – E).

8. Add formulas in column J to calculate the profit or loss for each term based on the Total Expenditure (column F) and the Total Income (column K) for each of the terms.

Figures that represent a profit must be indicated by a positive value and those that represent a loss must be shown as negative values.

Go to the Orders Worksheet

1. Change the text orientation of cell A1 so that the text runs 'horizontally’.

2. Add formulas in column C to work out the VAT (charged at 14%) that is due for each of the values or costs in column B.

3. Add formulas in column E to work out the Total Cost which is the quantity multiplied by the price including VAT. For example if the item costs R 10, then the VAT is R 1.40, giving a total of R 11.40. If 10 items are ordered then the Total Cost is 10 X R 11.40 or R 114.00.

4. Add a formula cell D12 to work out the total of the Quantity column.

5. Add a formula in D13 to work what percentage the quantity of the first item ordered in row 5 forms of the Total Quantity.

6. Add a red border only to the upper and lower border of the cells in row 3.

7. Insert a new line between the current rows 13 and 14.

Go to the Scores Worksheet

Ten grade 12 Art students were given the task of designing the new décor and colour scheme for the canteen. Each proposal was judged and given a score out of 100 by the same panel of seven judges. To try and make the scoring as accurate as possible, it was decided that the individual score of each proposal would be calculated as follows:

The top and bottom scores are ignored and the average of the remaining fives scored was used to calculate the final score. For example, say the seven scores for a project were:

|50 |80 |70 |65 |95 |

The average of these five scores is calculated which in this case would be 70.0.

Open the worksheet called Scores. Complete the spreadsheet by using suitable formulas to clearly indicate the two discarded values and the final score for each proposal, to one decimal place.

-----------------------

Copy the folder called Grade 10 Spreadsheet Practice Worksheet 1 and open the spreadsheet called Spreadsheet Practice 1.xls

Let’s stretch those gray cells!

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download