Section 1

  1. Open the file called Amateur Dramatic Society.
  1. The first thing you need to know is how many people came to the show on the opening night. Enter a formula in cell E7 that will calculate the total Attendance on Sunday.
  1. You will also need to find the attendance for each of the other nights so copy the formula in cell E7 down to E13.
  1. You can use these figures to find the total attendance for the week. Enter a formula in cell E15 to calculate the total attendance for the 7 nights.
  1. You want to study the attendance figures to see which nights were the least well attended so that you can try to improve the figures for next year. So in cell F7, enter a formula to express Sunday’s attendance as a percentage of the total attendance. (=E7/E15)
  1. Format this value as a percentage with no decimal places to make the figure easier to understand.
  1. The other attendance figures will also need to be shown as percentages. Replicate the formula in cell F7 down to cell F13 (HINT: you may need to apply absolute referencing to one of the cells)
  1. It’s important to know how much money was taken in ticket sales each night. The price of each type of ticket is shown in row 3. In cell G7, enter a formula that will multiply the number of each ticket type sold on Sunday by the cost of each ticket and find the total for that night.
    (HINT: you may need to apply absolute referencing to one of the cells)
  2. Copy the formula in G7 down to cell G13 to find the intake for each of the other nights.
  1. Now that you have the figures for each night you can find the total income for the week. In cell G15 enter a formula to add all the figures in column G.
  1. It would be interesting to compare the ticket sales figures with the attendance figures. First you’ll need Sunday’s takings as a percentage of the weekly total, so in cell H7 enter a formula to calculate Sunday’s sales figures as a percentage of the total in cell G15. (refer to question 5 for formula guidance)
  1. Copy the formula in cell H7 down to cell H13.
  1. Save the changes as Drama-yourname and close.

Section 2

  1. Open the spreadsheet named golf.
  2. Select the costings sheet. Find column A and widen this column so you can see all the data.
  3. Go to cell B12, enter a function to calculate the sum of the cell range B7 to B11.
  4. Replicate this formula to find the total costs for November and December column in cells C12 and D12.
  5. Go to cell F7. Correct this formula with an absolute cell reference to allow accurate copying.
  6. Replicate this formula from cells F7 to F11.
    Format this cell range as a percentage with two decimal places.
  7. Look at the formula in cell E7 and compare to the formula in E8. Which formula represents good practice and why? You can enter your response here or in cell B22 -
  8. Go to cell B14. Enter a formula to subtract the total costs in cell E12 from the budget total in cell E3.
  9. Go to cell B15. Enter a function to show the maximum cost from cell range B7 to B11.
  10. Go to cell B16. Enter a function to show the minimum cost from cell range B7 to B11.
  11. Go to cell B17. Enter a function to show the average cost from cell range B7 to B11.
  12. Find cell B20. Enter a function that will:
    display the text Yes if the number in cell B14 is less than zero
    and
    display the text No if it is not.
  13. Go to the donations tab. Find cell B139. Enter a function to count how many people have made donations in the cell range A2 to A135.
  14. Save as golf-yourname and close the worksheet.

Section 3

  1. Open the spreadsheet named Giggles Party Planners.
  2. Before we carry out any calculations on this sheet we will format the data so that it is clearer to read and understand.
    Increase the width of column B so that the data in the cell range B2:B3 is visible. Highlight the cells B2:C5 and apply a light colour shading of your choice to the cells.
    Add a thick box border around this range.
  3. Select cells C2:C4 and apply a currency format with 2 decimal places and the £ sign.
    Apply a percentage format to cell C6 with no decimal places.
  4. Place the cursor in cell H2 and replicate the contents down to cells H5. Cell H5 should display the text: Party Type 4 (Hint: do NOT type in the text).
  5. Insert a blank row above row 2 so that there is some space between the title and the data. (Once you have inserted the new row; row 2 should be blank)
  6. Select the cells A1:M1 and wrap the text in these cells. Apply top vertical alignment to this range. Make this range bold so that it stands out.
  7. Change the width of column E to 16.
  8. Change the format of the date in cells B10:B15 to dd/mm/yyyy (example 03/05/2012)
  9. Columns E and F are not required on this workbook.
    Copy the data in cells E9:F15 and paste in Sheet 2 starting from cell A1. Return to Sheet 1 and delete columns E and F.
    Rename Sheet 2 as Customers, rename Sheet 1 as Parties.
  10. Every party has a basic charge and it’s stored in cell C3.
    Incell G10 enter a formula to display the basic charge and then copy it down for other parties. (Hint: you must use a cell reference and use absolute cell referencing)
  11. The charge for party food per person is shown in cell C4.
    Enter a formula in cell H10 tofind the cost of party food for Sally’s party and then copy/replicate the formula down the column.
    (The formula will be the cost of food per person multiplied by the number of children)
  1. Now enter a formula in J10 to find the total cost of Sally’s party as follows:
    If they ordered a birthday cake, the total charge will be the basic charge plus the food charge plus the cake charge (shown in cell C5)
    Otherwise the total charge will just be the basic charge plus the food charge.
    (Hint: you must use absolute cell referencing in part of the formula)
  2. Copy this formula down to the other cells in column J. Format the values in column J as currency with the £ symbol and 2 decimal places.
  3. If the cost of the party is greater than £80 the birthday child will get a free photograph.
    Enter a function in cell K10 that will look at the value in J10 to see if it is greater than £80.00. If it is, the formula will produce the comment Photo.
    Otherwise the cell will produce the comment No Photo.
  4. Replicate this function for this column.
  5. Finally centre-align all numerical data in the worksheet. Save the workbook with the filename Party-yourname.
  6. The owner of the company has increased the party charges. Change the basic cost of the party in cell C3 to £45 and then change the formulas in column K so that only parties over £90 qualify for a free photograph.
  7. Change the magnification of the Parties sheet to 120%.
    Save the workbook as Price increase-yourname. Close the worksheet.
  8. This task is now complete. Save this completed document as SS Task 4-yourname.
  9. Please send the following files to the r marking:
    SS Task 4-yourname
    Drama-yourname
    golf-yourname
    Party-yourname
    Price increase-yourname

L:\MOLU\MOLU common folder\ECDL taught sessions\Spreadsheets\ECDL SS task 4.docx1