F202 Step-by-Step Guide

OCR sets the assignment. It gives you a scenario: a client, a problem they need a spreadsheet model to solve, a list of requirements, some fixed information (rules the calculations must follow) and data files you must use to plan, create and test the model. Everything you produce must be about that client and that data.

The specification says the set assignment has four practical tasks and takes about 15 hours of supervised time. OCR's sample assignment splits the work like this:

• Task 1, planning and designing the model (Topic Areas 1 and 2): success criteria, constraints, structure, formulae, formatting and validation, HCI, test plans and a test strategy. Nine criteria.
• Task 2, creating and testing the model (Topic Areas 2 and 3): build the model from your plans and record end testing. Seven criteria.
• Task 3, delivering the outcomes (Topic Areas 3 and 4): outputs, analysis and recommendations, and the parts of the technical and user documentation the task names. Five criteria.
• Task 4, evaluation (Topic Area 5): compare the model with the requirements, assess the HCI, evaluate the model and suggest improvements. Three criteria.

The five topic areas give you the tools for the four tasks:

• Topic Area 1, principles of spreadsheet modelling: purposes (store, manipulate and analyse data, share outcomes), model types (financial, statistical, business future planning, presentation of data and outcomes), the development cycle (gather business requirements, identify entities, conceptual model, logical data model, physical data model), the decision-making process, and the challenges (version control, complexity, security, hidden errors, integration, limited user knowledge).
• Topic Area 2, planning the design: design tools (flow charts, mind maps, storyboards, visualisation diagrams, wireframes, data dictionaries), design parameters and constraints, SMART success criteria, structure, inputs, calculations, test planning, outputs and HCI. This is Task 1.
• Topic Area 3, creating the model: cell formatting and referencing (absolute, relative, named ranges, multi-sheet), linked worksheets, data manipulation tools, data validation, protection and security, navigation, formulae and functions, importing and cleansing data, outputs, and iterative and end testing. This is Task 2 and the outputs in Task 3.
• Topic Area 4, delivering the outcomes: trends and patterns, chart indicators, dashboards, pivot tables and pivot charts, conclusions, and technical and user documentation. This is Task 3.
• Topic Area 5, evaluation: meeting the requirements, effective HCI, ease of use and accessibility, appropriate formatting, strengths and improvements. This is Task 4.

F202 has synoptic links to F200 (Fundamentals of data analytics): data types, data formats such as CSV, data assurance and validation, presenting and visualising data, and permissions all come from that unit. The F200 + F201 revision notes cover them.

Give everything a reference from the start: each requirement in the scenario (R1, R2, R3), each success criterion (SC1, SC2), each test (T1, T2) and each worksheet. Several criteria ask you to compare, test or evaluate against the requirements, and Task 4 is far easier when every requirement can be traced to a success criterion, a design, a test and a finished feature.

OCR's student guide says your centre number, candidate number and the unit number must be on every page, as a header or footer, and digital files need sensible names. Keep your plans, drafts and early versions of the spreadsheet: your teacher may ask to see them, and saved versions are useful when you evaluate.

P1: Identify appropriate SMART success criteria against the purpose and user requirements. The assessment guidance says success criteria must be presented as SMART objectives and must be linked to the purpose and user requirements from the scenario.

SMART means each criterion is Specific, Measurable, Achievable, Relevant and Time-bound. Work through the requirements list one item at a time and write at least one criterion for each, showing which requirement it comes from.

M1: Explain the constraints for the project. The specification's project constraints are time, cost, scope, quality, resources, and risks and risk analysis. Explain means giving reasons: say what each constraint is in this project, and how it affects the design of the model.

Use the scenario: the time you have, the software available, what the client has and has not asked for (scope), and the risks, such as errors in the supplied data or users with limited spreadsheet knowledge, with how you will reduce them.

P2: Identify the spreadsheet data model structure using appropriate planning and design tools. Structure in the specification means the model title, worksheets, column headings and row headings.

Choose design tools that suit the job. The specification lists flow charts, mind maps, storyboards, visualisation diagrams, wireframes and data dictionaries. A mind map can show the worksheets and how they link; a wireframe shows the layout of each worksheet; a data dictionary records each item's variable name, unit of measurement, acceptable values and definition.

M2: Identify the formulae for the spreadsheet data model using appropriate planning and design tools. The guidance is specific: calculations must be expressed as headings, not cell references. The specification also says identifying cell references when planning calculations is not required.

Cover every calculation the requirements need, using the rules in the scenario's information section. The specification suggests flowcharts to plan the logic of calculations, which works well for decisions such as whether an item needs reordering.

D1: Identify the formatting and validation for the spreadsheet data model using appropriate planning and design tools. Include user comments and appropriate protection. All four parts must be there: formatting, validation, user comments and protection. The guidance adds that the model must include at least one advanced function.

• Formatting: font, font size, alignment, number and currency formats, conditional formatting.
• Validation: the specification's methods are range check, text length, lookup and limited choice. Say which input each rule applies to and the error message the user will see.
• Comments and notes: where they will appear and what they tell the user.
• Protection: passwords, limiting access to named individuals, and which cells are locked or left open for input.

P3: Identify and explain your choice of HCI features for the spreadsheet using appropriate planning and design tools. The guidance says you must explain why the HCI features you have identified are appropriate for the solution. Listing them is not enough.

The specification's HCI features are purpose, navigation, accessibility, colour, layout, learnability, memorability, messages, user perceptions and use of macros. Link each choice to the client's users: who they are, how confident they are with spreadsheets, and what the requirements ask the opening screen to do.

P4: Create a test plan to test the useability of the spreadsheet data model. M3: Create a test plan to test the technical aspects of the spreadsheet data model. For both, the guidance says the plan must include normal, extreme and erroneous testing. (The specification's names for these are valid, extreme and invalid (erroneous) test data.)

• Useability tests check the model is easy to use: navigation buttons go to the right worksheet, messages are clear, the layout and colours work for the user.
• Technical tests check the model works correctly: formulae give the right results, validation rejects bad data, protection stops changes, lookups find the right records.

Each test needs the test data, its purpose and the expected result, so it can be carried out in Task 2. Choose test data from the supplied data files and the scenario's rules, including values on the boundaries of each validation rule.

D2: Justify the selection of tests to be used in the test plan through a test strategy. The guidance says you must write a test strategy statement, not a lengthy document.

Justify means giving valid reasons. Say what you will test and why those tests, when you will test (iterative testing during development and end testing after it), and why normal, extreme and erroneous data together give confidence that the model meets the requirements.

P5: Produce a spreadsheet data model based on the design documentation. M4: Produce a functioning spreadsheet data model based on the design documentation. The step from P5 to M4 is that the model works: every calculation gives the correct result and every requirement is met.

Use your Task 1 plans as the starting point and the data files provided. If you need to change something from the plan as you build, you can: the guidance says you must not be penalised for deviating from your plans in Task 2, and you do not need to update the plans. Note the change and the reason, because it is worth reflecting on in your evaluation.

P6: Use formatting in the spreadsheet data model. The specification's cell formatting is font, font size and alignment (centre, left, right). Format consistently, so titles and headings make the purpose and the meaning of each cell clear.

P7: Use simple formulae in the spreadsheet data model. Simple formulae use mathematical operators (+, -, *, /, =, ≤, ≥) and simple functions such as SUM, AVERAGE, COUNT, MIN and MAX. Use relative and absolute cell references correctly so formulae copy down without errors.

M5: Use linked worksheets and functions in the spreadsheet data model. Split the model across worksheets that make each component easier to manage (for example, data, calculations and outputs) and link them with multi-sheet references and named ranges, so a change in one worksheet updates the others.

Use functions beyond the simple ones where the requirements need them: logical (IF, nested IF, AND, OR, NOT, SUMIF), lookup and reference (LOOKUP, VLOOKUP, HLOOKUP, INDEX), financial (PMT, PV, ISPMT), text (UPPER, LOWER, CONCATENATE), date and time (TODAY, DAY, HOUR) and maths (ROUND, CEILING, RAND). The guidance says you can use pivot tables or similar to produce outputs for the model.

D3: Use validation, protection and data manipulation tools in the spreadsheet data model. All three are named, so the model needs all three:

• Validation: range check, text length, lookup and limited choice, each with a meaningful error message.
• Protection: worksheet protection, passwords and limiting access, so users can enter data only where they should. If the scenario gives a password, use it.
• Data manipulation tools: drop-down lists, spin buttons, scroll bars, sorting and filtering, to make the model easier to use.

P8: Carry out end testing of the spreadsheet data model and record the outcomes in a test plan. The guidance says you must use screenshots in your test documentation. OCR's sample task asks for the completed test plan with screenshots of the full results and evidence of re-tests where needed.

The sample assignment's test plan template has these columns: Test number, Test data, Test purpose, Test procedure, Expected result, Actual result, Remedial action required, Retest result. OCR recommends using the template provided, but a different, appropriate test plan document must not be penalised.

P9: Develop outputs to be used to aid the analysis of the results of the spreadsheet model. The guidance says the evidence can be the model and/or the outputs.

Produce every output the requirements ask for. The specification's outputs are charts, graphs, tables, reports and dashboards. Choose the chart type that suits the data, and label it fully: titles, legends, axis labels, colour, shading or pattern, and data labels.

M6: Produce an analysis of the trends and/or patterns indicated by the outputs. The guidance says you can present it in a report or a dashboard, and can use pivot tables or similar to produce the analysis.

Analyse means breaking the results into parts and explaining what each shows. Refer to specific outputs and figures: which items, days or periods stand out, what is rising or falling, and what the patterns mean for the client's question.

D4: Evaluate the outcomes of the analysis and make recommendations. The guidance says recommendations must focus on the question or questions raised in the scenario.

Evaluate means making a reasoned judgement that weighs different factors. Reach a clear answer to the client's question, give the evidence from your analysis for it, consider what could make it wrong (for example, how much data the model covers), and recommend specific actions that follow from it.

P10: Create the required content of the technical documentation for the spreadsheet data model. The guidance says the task specifies which parts you need to create; create those parts only. You do not need full documentation, but what you produce must be fully appropriate for your model.

The specification's technical documentation contents are hardware and software requirements, instructions on use, formulae and functions used, data validation and error messages, and a fault log. OCR's sample task asked only for the data validation and error messages section.

P11: Create the required content of the user documentation for the spreadsheet data model. The same rule applies: create only the parts the task specifies, fully appropriate for the model you built.

The specification's user documentation contents are a description of the purpose of the system, the functions of the system, hardware and/or software requirements, and a step-by-step guide covering installation, access, use and the model. OCR's sample task asked for the purpose and a step-by-step guide to using the password, navigating between worksheets and changing the profit margin. Write for the client, not for another developer.

P12: Compare the user requirement with the spreadsheet data model created. Compare means giving the similarities and differences. Go through the requirements one at a time (your R1, R2 references) and say whether the model meets each one fully, partly or not at all, with the evidence: a worksheet, an output or a test result.

M7: Assess the effectiveness of the HCI features in the spreadsheet data model. The guidance says the focus is specifically on the HCI features only.

Assess means a reasoned judgement of quality, informed by facts. Take the HCI features you chose for P3 and judge how well each one worked, using evidence such as your useability test results: navigation, accessibility, colour, layout, messages, learnability and memorability.

D5: Evaluate the effectiveness of the spreadsheet data model and suggest improvements that could be made. The guidance sets three conditions: the focus is on the model as a whole; the evaluation must include justification for the improvements suggested; and the improvements must relate to the evaluation.

The specification's evaluation points are meeting the user and client requirements, effective use of HCI, ease of use and accessibility, appropriate data formatting, strengths of the model, and improvements. Each improvement should come from a weakness you identified, with a reason it would make the model more effective.

• OCR sets the assignment. A new one is released each year and each stays live for two years. The front cover shows the cohort it is for. The sample assignment is for practice only and cannot be used for assessment.
• Supervised and individual. The work is completed under teacher supervision, over as many sessions as needed, and stored securely between sessions. Group work is not allowed for any task.
• What your teacher can and cannot do. They can read the assignment to you, explain the criteria, the guidance and the command words, and remind you what you were taught. They cannot tell you how to complete a task, give you answers, or mark your work in stages.
• Feedback. Your teacher assesses the work only when the whole assignment is complete. Feedback can say which task could be improved, or that a criterion's command word has not been met, but not how to improve it.
• Reattempt and resubmission. After your teacher has marked the complete assignment, you can reattempt it before the marks go to OCR. A reattempt is not a cycle of small changes made through ongoing feedback, and no work can be changed once the final mark is submitted. After moderation there is one resubmission per assignment, with your teacher's agreement, while the assignment is still live.
• Plagiarism and AI misuse. Version 6 of the specification covers AI misuse and requires that you are taught how to reference material copied from the internet or other sources. Reference everything you use, including AI tools, and use your own words when asked to explain or evaluate. Plagiarism can lead to zero criteria for the unit.
• Authentication. You sign a declaration that the work is your own, and your teacher confirms it.
• Grading. 24 criteria: 10 for a Pass, 15 for a Merit, 20 for a Distinction. The unit is worth 60 uniform marks, and criteria achieved still count towards the qualification if the unit is not passed.