Spreadsheets Task 4

Task 4 - Formulas and Functions

The adventure park is now ready to open and entry tickets can now be bought. Tickets are bought at the gate or over the phone. There are a variety of different tickets available, tickets for children, adults, one day tickets, three day tickets etc. To keep track of all of the ticket sales and work out how much income the adventure park is making, they will need a customised spreadsheet.

Some of the skills you will be learning in this task are:

  • Creating formulas using cell references
  • Understand the difference between absolute and relative cell references Use Sum, Average, Countif, VLOOKUP
  • Use the IF function
  • Open the 'Adventure Land Ticket Prices' spreadsheet. The structure of the spreadsheet has been set up for you, you will need to add formulae and functions to make the spreadsheet work.

Adventure Land Ticket Prices

Task Instructions

  1. Open the Daily Sales sheet. The ticket sales have been entered for you. You need to use a formula to work out the total sales for each day.
  2. Rename the Daily Sales worksheet to Daily Ticket. Move this worksheet so that it is now the first sheet in the workbook. Right click on this tab (Daily Ticket) and see how you can create a copy of this worksheet if required.
  3. You also need to work out the total sales of each type of ticket. Use a function to work this out.
  4. on the Daily Ticket worksheet remove row 21 as this label/row is no long required.
  5. On the Month Sales worksheet delete column C and it is not good practice to have blank columns in a table.
  6. On the Total Income worksheet insert a row below row
  7. Now open the Total Income sheet. You will notice 3 tables. The first table is for the total sales for the week beginning 01/06/2016. You need to link the cells in the table to daily ticket sale totals on the Daily Ticket Sales sheet.
  8. The earnings table is a little bit more complicated. This table will display the amount earned by the sale of tickets. This means that you will need the price of each ticket, which can be found in the Prices table. Using formula, calculate the amounts for the earnings table.
  9. Create a VLOOKUP that will find the price of each ticket from the Prices table, then work out the amount earned by multiplying it with the total sales of each ticket.
  10. We have now reached the Monthly Sales sheet. As you can see the sales forecast have been entered into the sheet, however the column 'Passed Target?' is blank. The adventure park has a target of 150 for one day pass ticket sales per month in order to remain profitable. Using an IF statement, display 'Target Achieved' when 150 or more tickets were sold, and 'Target Not Achieved' when less than 150 were sold.
  11. In the table below, use the SUM function to work out the total sales through the year.
  12. Use the AVERAGE function to work out the average sales for ticket type for each month.
  13. Use the MEDIAN function to work out the median sales month for each ticket type.
  14. Use the COUNTIF function to find the months were no sales at all were made for each ticket type
  15. Set all numbers to 2 decimal places and make sure that all prices are in accounting format.