Section 1

  1. Open the file called Charity donations.
  2. First you will have to calculate the total attendance for the weekend. In cell G3 enter a formula in the Total column to add the ticket sales for the 5 performances.
  3. Calculate how much money was collected from the ticket sales. In the Total income (£) column, in cell I3, enter a formula to multiply the total number of tickets (shown in G3) by the cost of a ticket (shown in H3).
  4. Replicate the formula created in cell G3 to find out the Total for candy floss and icecream tubs in cells G4 and G5
  5. Replicate the formula created in cell I3 to calculate the Total income(£) for candy floss and icecream tubs in cells I4 and I5
  6. Now that all the individual incomes have been calculated, we can calculate the Gross Income for the weekend. In cell B8 enter a formula to add the 3 figures in column I.
  7. The circus has promised to give one fifth of their income to the hospital charity fund.
    To calculate the Donation to charity in cell B9, enter a formula to divide the total income in B8 by 5.
  8. To calculate the Net Income for the circus in cell B10, enter a formula that subtracts the charity donation in B9 from the total income in B8.
  9. The circus has been deeply moved by the children and dedication of the staff at the local hospital and has decided to increase their donation from one fifth to one quarter.
    Amend the formula in B9 so that it is divided by 4.
  10. Format cells G3:I5 and also B8:B10 as currency, two decimal places and the £ sign.
  11. Select the cells A8:B10 and apply borders to all the cells (All Borders)
  12. Select cell B2 and copy the formatting of this cell to cells C2:I2
  13. Save the changes as Charity-yournameand close.

Section 2

  1. Fill in the blanks using the correct works from the list below:
    Relative Absolute Mixed
    cell referencing denotes references that always refer to cells or columns in a specific location. Adding the $ sign before a row or column denotes that the particular row or column reference should not be changed in the formula.
    cell referencing denotes references to cells that are relative to the position of the formula in a worksheet. Excel automatically increments or decrements the row or column number in the formula
    cell referencing is a combination of using relative and absolute cell referencing.
  2. Open the workbook Kitchen quote.
  3. Merge and Centre the cells A6:F6 and make the cell contents bold
    Merge and Centre the cells A16:F16 and make the cell contents bold
    Merge and Centre the cells A34:D34 and make the cell contents bold
  4. Change contents of cell C3 to Price/Sqm
  5. Format cells D3 and D4 to Currency,2 decimal places.
  6. To make the data easier to view and understand:

Insert borders (All Borders) around the range A7:F13
Insert borders (All Borders) around the range A17:F31
Make the data in cells A7:F7bold
Make the date in cells A17:F17bold.

  1. Insert a formula in cell E8 to calculate the Area Price of Top A.
    The formula is calculated as explained below:
    Multiply the Width by the Depth, then multiply this figure by the Quantity, and then multiply this figure by Price/Sqm (D3)
    You must use absolute and relative cell references as this formula will need to be copied down.
  2. Replicate the formula in cell E8 down to cell E13 to find out the Area Price for the other tops.
  1. Insert a formula in cell F8 to calculate the cost of the Edge Price for Top A.
    The formula is calculated as explained below:
    Add the Width and the Depth, then multiply this figure by the Quantity, then multiply the result by Price/m (D4)
    You must use absolute and relative cell references as this formula will need to be copied down.
  2. Replicate the formula in cell F8 down to cell F13 to find out the Edge Price for the other tops.
    Format the range F8:F13 to Currency, 2 decimal places.
  3. Enter a SUM function in cell E14 to add the total AreaPrice for all tops.
    Enter a SUM function in cell F14 to add the total EdgePrice for all tops.
  4. Place the cursor in cell D18 to calculate the Cost of the Rounded corners.
    The cost is calculated by multiplying the Quantity by the Price.
    Insert this formula and replicate down to cell D31.
  5. Enter 4% in cell E18. Replicate this value down to cell E31.
  6. Place the cursor in F18 to calculate the Net Cost.
    The cost is calculated by using the following formula:
    =Cost – (Cost*Discount)
  7. Replicate this formula down to F31 to calculate the Net Cost for all the features.
  8. Insert a SUM function in F32 to calculate the TotalFeatures.
  9. Save the workbook as Quote-yourname and close.
  10. This task is now complete. Save this completed document as SS Task 3-yourname.
  11. Please send the following files to the for marking:
    SS Task 3-yourname
    Charity-yourname

Quote-yourname

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