5L4Z0002 Digital Skills Assessment Brief 2026 | Manchester Metropolitan University

5L4Z0002 Assessment Brief

Key task and word count (or equivalent):

For this assignment, you need to produce a Portfolio including the following items:

1. A single Excel spreadsheet file containing a separate sheet for each of the functions (listed in the ‘Additional Information’ section below) that you have applied to a given dataset.

2. A PowerPoint presentation with choice of a design template of no more than 750 words in which you explain two of the key findings from your portfolio item 1 dataset analysis.

Module Learning Outcomes

LO1 Use presentation and spreadsheet applications to present information and data

LO2 Develop document templates according to specified requirements.

LO3 Produce written communications appropriate to the nature of the exchange (formal and informal)

This assessment will contribute to the achievement of the following Programme Learning Outcomes:

PLO2 Our graduates will be effective communicators using a range of media

Trait 2.1.3 Demonstrate digital skills appropriate to level and to discipline

Assignment Details and Instructions

Assessment type: Individual Work

Assessment format: Portfolio submission online only, two files to upload (1 Excel and 1 Powerpoint)

Supplementary details: Maximum of 1000 words excluding References for the Powerpoint element. No maximum word limit for the Excel file. Pass mark is 40%.

There will be opportunities for formative feedback and support from your module tutor in your timetabled tutorials by using designated activities and by scheduling time for questions and answers.

Key starting points/thinking points:

The portfolio comprises two items:

A. An Excel spreadsheet containing the results of your dataset analysis using the following Excel functions:

• Filter
• IF
• MIN and MAX
• Bar chart
• Average
• VLOOKUP
• Pivot Table

Detailed instructions for each function are:

Filter

1. Open Assignment Excel file ‘2023 SMMT car registration data’

2. Create a new worksheet and name it ‘Filter’

3. Copy and paste both ‘month’ and ‘new_vehicle_reg’ column to the newly created ‘Filter’ worksheet and change the column name ‘month’ to ‘date’

4. Select the ‘date’ column and add filter function on this column

5. Use the filter function to select the car registration data of 2018 and 2019

6. Save all changes

MIN and MAX

1. Open saved Excel file previously used

2. Create new worksheet and name it ‘Min&Max’

3. Copy and paste both ‘month’ and ‘new_vehicle_reg’ column to the newly created worksheet

4. Create new columns ‘yearly min’ and ‘yearly max’ on column C and D

5. Use the ‘=min()’ function to identify the minimal value in a set of twelve months for each year and record the min value on the same row as January for each year (for example on the same row of Jan-10, then on the same row of Feb-10 and so on for all years, there will be 12 individual min values in total)

6. Use the ‘=max()’ function to identify the maximum value in a set of twelve months for each year, record as above

7. Save all changes

Bar chart

1. Open saved Excel file previously used

2. Create new worksheet and name it ‘bar chart’, copy the columns ‘month’ and ‘new_vehicle_reg’ to this newly created worksheet

3. Insert a bar chart with monthly new car registration number as vertical axis and months as horizontal axis for year 2011

4. Add bar chart title ‘Monthly Car Registration 2011’

5. Insert another bar chart with 2011 and 2012 data on the same chart and for each month the values, i.e. the two bars, are compared (note that to accomplish this you might need to organise a new dataset). Name the chart ‘2011 and 2012 Car Reg Comparison’.

6. Save all changes

AVERAGE

1. Open saved Excel file previously used

2. Create new worksheet and name it ‘Average’, copy the columns ‘month’ and ‘new_vehicle_reg’ to this newly created worksheet

3. In a new column ‘monthly average’, generate new car registration monthly average data on a yearly basis (for 2021 use the existing months data), record this average value on the same row as each January

4. Round up the record of the monthly average values with 0 decimal places (integers).

5. Save all changes

IF

1. Open saved Excel file previously used

2. Create new worksheet and name it ‘IF’, copy the columns ‘month’ and ‘new_vehicle_reg’ to this newly created worksheet

3. Create new performance index column with heading ‘performance’

4. Use the ‘IF’ function to populate the newly created column:

monthly registration above (and including) 150,000 return the value “High”;

monthly registration between (and including) 100,000 and 149,999 return the value “Medium”;

and monthly registration below (and including) 99,999 return the value “Low”

5. Save all changes

VLOOKUP

1. Open saved Excel file previously used

2. Create new worksheet ‘vlookup’ and copy the columns ‘month’, ‘new_vehicle_reg’, and ‘Performance’ columns from previous ‘IF’ worksheet to this newly created worksheet

3. Create and insert a ‘id’ column at the front of all three existing columns, fill the new ‘id’ column with number in the format of ‘201001’(four digits year + two digits month), according to the ‘month’ column information, e.g. 201001, 201002,…, 201101, 201102,…, for all existing rows

4. Create new column heading ‘selected_months’ in column F, and fill in the IDs for August and September for all years, e.g.:

5. Create new column heading ‘perf_lookup’ in column G and and use vlookup function to feedback the performance data (high, medium, and low) to the corresponding rows from column ‘id’. Note that you will need to use vlookup functions to complete this, eyeballing the data and fill in manually will not be considered correct answer.

6. Save all changes

Pivot Table

1. Open saved Excel file previously used

2. Create new worksheet ‘pivot_table’;

3. Copy and paste the first four columns ‘id’, ‘month’, ‘new_vehicle_reg’, and ‘performance’ from previous ‘vlookup’ worksheet.,

4. In column E, create new index titled ‘year’ and fill the column with the years for each role accordingly, e.g. for the first 12 rows, it is ‘2010’ for each row and the next 12 rows ‘2011’.

5. Create another new column ‘perf_score’ in column F by IF function: monthly registration above (and including) 150,000 return the value 3; monthly registration between (and including) 100,000 and 149,999 return the value 2; and monthly registration below (and including) 99,999 return the value 1

6. Generate annual performance pivot table on the same worksheet with annual aggregated performance score and find out the best and least performing years, mark the highest and least by the side of the pivot table

7. Insert another pivot table below the first one and this time calculate the average of new_vehicle_reg for each year and find out the highest and least performing year and mark by the table.

8. Save all changes

B. A PowerPoint Presentation with a design template that is no more than 750 words focussed on two key finding of the dataset analysis undertaken in portfolio item 1. Guidance on presentation format and content will be provided in class and on the unit Moodle Page.

Answer

Request Answer of this Assignment