UNIT 2: Data and Spreadsheet Modelling Pearson Set Assignment Brief Single Part Assessment

Turn over

 

*W77583A*

 

Pearson BTEC International Level 2 Award, Certificate, Extended Certificate, Diploma

 

June 2023 – Version 1

 

Recommended time 20 hours Paper 60110P reference

 

Information Technology

 

UNIT 2: Data and Spreadsheet Modelling Pearson Set Assignment Brief Single Part Assessment

 

You must have:

 

gardening2023.csv

 

For use with:

 

Pearson BTEC International Level 2 qualifications in Information Technology

 

W77583A

 

©2023 Pearson Education Ltd. 1/1/

 

2 W77583A

 

Instructions to Teachers/Tutors

 

The Pearson Set Assignment will be assessed internally by the centre using the unit Assessment Criteria detailed in the qualification specification. The assignment will be sampled by the Standards Verifier as part of the standards verification annual centre visit.

 

Conditions of supervision

 

The Pearson Set Assignment should be undertaken in conditions that assure the authenticity of outcome. This may require supervision.

 

We advise that the Pearson Set Assignment be completed in sessions that come to a total of 20 hours. The Pearson Set Assignment should not be shared with learners prior to the start of the assessment period. Teachers/tutors are responsible for security of the Pearson Set Assignment and materials.

 

Outcomes for submission

 

Learners must complete this Set Assignment on a computer using spreadsheet software to create the dashboard and word processing software for the reports. Learners must save their work regularly and ensure that all materials can be identified as their work.

 

Learners must import the data set from the provided data file, which is in CSV format.

 

Learners must submit their own, independent work as detailed in the Set Assignment. Each learner must complete an authentication sheet.

 

3

 

Turn over

 

W77583A

 

Instructions to Learners

 

Read the Set Assignment Information carefully then complete the activities detailed in the Set Assignment.

 

You will be asked to carry out specific activities using the information provided. You will be given a specific time period to complete the assignment.

 

At all times you must work independently and must not share your work with other learners. You must complete an authentication sheet and submit this along with your work.

 

Set Assignment Information

 

Srags is a company based in the United Kingdom that provides lawn care services.

 

The data provided in the data file is for lawn care services carried out between 2020 and 2023.

 

In this assignment you will consider how data is collected and used by organisations. You will analyse data and use data manipulation and other processing methods to create a dashboard to display a summary of the data. You will then judge the capacity of the dashboard to enable clear conclusions to be drawn.

 

Your dashboard should identify patterns and trends and possible anomalies in the data.

 

4 W77583A

 

Set Assignment

 

There are three activities in this assignment. You must complete ALL activities.

 

ACTIVITY 1 – suggested time 4 hours Data and information – the big picture

 

For the first activity, Srags would like you to produce a report that examines the different ways data is collected and used by organisations.

 

Your report should consider the:

 

• characteristics of data and the structure and context of information

 

• characteristics, reliability and value of data collection methods

 

• quality of data collected and data modelling and how these impact on

 

decision-making.

 

You should provide a range of examples in your report. This activity covers learning aim A.

 

A.P1, A.M1, A.D1

 

5

 

Turn over

 

W77583A

 

ACTIVITY 2 – suggested time 8 hours The dashboard

 

You have been given a data file gardening2023 with information on clients, different types of services including the treatments available, costs, payment methods and client review rating.

 

Import the data into a spreadsheet.

 

The spreadsheet should be easy to navigate and have a worksheet for a main menu with links to: 1) the data, 2) the analysis, and 3) the dashboard.

 

Imported data worksheet

 

• Format the worksheet to merge and centre Row 1 from A to J.

 

• Sort the data into alphabetical order of Client ID and ensure all data is visible.

 

• In the Cost of treatment column use conditional formatting to identify costs between

 

£13.50 and £25.00

 

• In the 2024 Discount column use a FUNCTION to display the amount of discount for

 

each client. Use the data from the ‘Discount table’. The cells with the 15% discount will

 

need to stand out.

 

Analysis worksheet

 

Use this worksheet to carry out your data analysis for the following information:

 

• the total income from all clients

 

• the total income from all Spring treatments

 

• the number of each Package type

 

• the highest and lowest cost of each package

 

• the average review rating given

 

• the percentage of clients that requested advice

 

• any other information you feel may be useful.

 

Dashboard worksheet

 

Create the dashboard from the analysed data in the worksheet to display a summary of the data.

 

The dashboard should be easy to read, show summaries from the data set using different presentation methods and each graph or chart should provide a clear summary.

 

This activity covers learning aim B. B.P2, B.P3, B.M2, B.M3, B.D2

 

You are provided with this file for use during this task:

 

gardening2023.csv

 

6 W77583A

 

ACTIVITY 3 – suggested time 8 hours The review

 

Srags found the dashboard useful and has asked for a report that reviews the data manipulation and presentation methods you have used.

 

Your review should include:

 

• judgements about the capacity of the dashboard to present information that enables

 

a range of specific, relevant, and clear conclusions to be drawn, such as trends,

 

patterns and possible anomalies

 

• judgements about the effectiveness of the data manipulation and presentation

 

methods used to enable accurate conclusions to be drawn.

 

Use a range of relevant examples to support your conclusions. This activity covers learning aims C.

 

C.P4, C.M4, C.D3

 

W77583A 7

 

Assessment criteria

 

Pass Merit Distinction
Learning aim A: Understand the role of data and information in organisations A.D1 Analyse different ways data is collected and used by organisations and its impact on decisionmaking.
A.P1 Identify different ways data is collected and used by organisations and its impact on decision-making.A.M1 Describe different ways data is collected and used by organisations and its impact on decisionmaking.
Learning aim B: Create a dashboard using data manipulation methods B.D2 Demonstrate the use of complex data manipulation methods and data presentation methods to provide a clear and accurate summary of data on the dashboard.
B.P2 Demonstrate the use of some basic data manipulation methods to present data.B.P3 Demonstrate the use of basic data presentation methods to develop a summary of data on the dashboard.B.M2 Demonstrate the use of detailed data manipulation methods to present data.B.M3 Demonstrate the use of a range of data presentation methods to provide a clear summary of data on the dashboard.
Learning aim C: Review the effectiveness of the dashboard to provide information C.D3 Evaluate the effectiveness of the dashboard to present information.
C.P4 Explain how the dashboard presents information. C.M4 Assess the effectiveness of the dashboard to present information.

W77583_International_BTEC_IT_60110P_Unit_2_V1_v2