Task 2: Design, create and use a business spreadsheet

Assessment Objective 5 is assessed in this task

In this task you will design, create and use a spreadsheet appropriate to your work for the Year 8 trip. You will need to use appropriate information that you found in Task 1 when completing this task. Your spreadsheet should present the costs involved for at least two of the different trip options and display a warning message if the price per pupil goes over £15. The final costs per pupil should be rounded up to the nearest pound.

Use the activities below to produce evidence for your assignment.

For Pass level you need to complete parts A, B, C, D, E and F of this task.

Design and create a spreadsheet to display information for at least two options for the Year 8 trip.
For example your spreadsheet could include:
    • transport costs (£1.25 per mile, plus £98.28 for the driver)
    • entry fees for visitor attractions (Theme parks/Museums)
    • cost of lunch (£1.80 per packed lunch)
    • number of people (Assume there are 120 pupils that can go on the trip)
Your spreadsheet must include column headings, row labels, a title (higher levels only), text and numeric data.

Create appropriate formulas to calculate,
for example:
    • total cost of each option
    • cost per person
    • cheapest/most expensive cost.
You must use at least two different calculations in your spreadsheet.

For Merit level you must use several different formulas including at least one function and one arithmetic operator.

For Distinction level you must use several different formulas including at least two different functions and two different arithmetic operators. .

Format your spreadsheet so that the information is displayed effectively.
You might, for example, format:
    • text (font, style, size, colour, alignment)
    • numbers (decimal places, percentage, currency, date/time)
    • cells (apply borders and/or shading).

Print out your spreadsheet:

    • clearly showing all data
    • in formula view showing all formulas in full (Higher levels only)
For the higher levels you will need to preview before printing and make sure you have chosen the options that will fit your sheet most effectively onto the paper. For Distinction level you should also set appropriate headers and footers and other appropriate print options (eg margins, gridlines, row and column headings, setting a print area).

Change some numeric data to model the expected outcome for at least two different trips, for example you might increase and/or decrease the distance to the destination to investigate what happens to the final cost or you could investigate what would happen to the total cost of the trip if the entry fees to the attractions were increased and/or decreased. Print the results of each change.

Annotate your printouts to show the changed outcomes obtained as a result of the modelling you carried out in part E.

Explain the results you have obtained as a result of the modelling you carried out in part E. (Higher levels only)

The efficiency of your spreadsheet, including the formulas you have used, as well as the appropriateness of the formatting options you have applied, will be a significant factor in determining the grade you are awarded for this Assessment Objective.

Evidence could be printouts of the original and changed spreadsheet and for the higher levels formula printouts. At Pass level you might evidence your formulas through the electronic file of your spreadsheet or through screen prints. Changes to data and setting of print options should be annotated.