Joshua Hall Assignment 2 – Learning Aims B & C Unit 5 – 2022
Table of Contents
Unit 5 – Data Modelling.........................................................................................................................5
Introduction...........................................................................................................................................5
Nature of the Problem...........................................................................................................................5
Functions the Model must Perform.......................................................................................................6
Function 1..........................................................................................................................................6
Function 2..........................................................................................................................................7
Function 3..........................................................................................................................................7
Function 4..........................................................................................................................................8
Function 5..........................................................................................................................................8
Function 6..........................................................................................................................................8
Function 7..........................................................................................................................................9
User Interface........................................................................................................................................9
Constraints...........................................................................................................................................11
Success Criteria....................................................................................................................................12
User Interface......................................................................................................................................13
Home Page – Design 1.....................................................................................................................13
Home Page – Design 2.....................................................................................................................14
Members Page – Design..................................................................................................................14
Food Page – Design..........................................................................................................................15
Activities Page – Design...................................................................................................................16
1|Page
,Joshua Hall Assignment 2 – Learning Aims B & C Unit 5 – 2022
Progress Page – Design 1.................................................................................................................16
Progress Page – Design 2.................................................................................................................17
Report Page – Design 1....................................................................................................................18
Report Page – Design 2....................................................................................................................18
Chart Page – Design.........................................................................................................................19
Test Plan..............................................................................................................................................20
Feedback Report..................................................................................................................................28
Data Model Evidence...........................................................................................................................35
Implementation Report...................................................................................................................37
Use of Formulas...............................................................................................................................38
Use of Functions..............................................................................................................................38
Use of Logical Functions..................................................................................................................39
Use of a Nested IF Function.............................................................................................................40
Data Manipulation...........................................................................................................................40
Auto Fill............................................................................................................................................40
Relative and Absolute Cell Referencing...........................................................................................41
Design of Database..........................................................................................................................41
Merged Cells....................................................................................................................................42
Colours, Borders & Shading.............................................................................................................42
Conditional Formatting....................................................................................................................42
Headers and Footers........................................................................................................................43
Graphics...........................................................................................................................................43
2|Page
,Joshua Hall Assignment 2 – Learning Aims B & C Unit 5 – 2022
Output Requirements:.....................................................................................................................43
Worksheet Layout............................................................................................................................43
Colours, Borders & Shading.............................................................................................................44
Graphs.............................................................................................................................................45
Using Macros & Button to Initiate Procedures................................................................................45
Extended Feedback Report..................................................................................................................45
Home Page.......................................................................................................................................46
Members Page.................................................................................................................................47
Food Page........................................................................................................................................48
Activities Page..................................................................................................................................49
Progress Page..................................................................................................................................49
Report Page.....................................................................................................................................50
Chart Page.......................................................................................................................................51
Justification..........................................................................................................................................52
Home Page.......................................................................................................................................52
Members Page.................................................................................................................................53
Food Page........................................................................................................................................54
Activities Page..................................................................................................................................55
Progress Page..................................................................................................................................56
Report Page.....................................................................................................................................57
Optimising Data Model to Meet Client Requirements.........................................................................58
Improvement/Change 1 – Headings................................................................................................58
3|Page
,Joshua Hall Assignment 2 – Learning Aims B & C Unit 5 – 2022
Improvement/Change 2 – Font Sizes...............................................................................................60
Improvement/Change 3 – Font Styles.............................................................................................61
Evaluation Report................................................................................................................................63
Meeting Client/User Requirements.................................................................................................63
Issues Encountered..........................................................................................................................66
Evaluation............................................................................................................................................68
Time Management & Meeting Targets............................................................................................68
Achieving Aims/Solutions................................................................................................................69
Identifying & Resolving Potential Issues, Reviewing & Making Improvements................................70
Response of Outcomes from Previous Feedback.............................................................................71
Witness Statement..........................................................................................................................71
Evaluated Outcomes – Including Justified Recommendation & Decisions.......................................71
Use of Effective Communication Methods......................................................................................72
Backup of Work...............................................................................................................................72
Creativeness in Layout & Research..................................................................................................73
4|Page
, Joshua Hall Assignment 2 – Learning Aims B & C Unit 5 – 2022
Unit 5 – Data Modelling
Introduction
In this assignment, I have recently been asked to design and create a spreadsheet model which can
predict the potential impact of changes in exercise and diet on a person’s BMI. Firstly, I will be willing
to produce an efficient spreadsheet model which can show a person how their BMI would change
over a period of at least 10 weeks, based on the activities they do and the foods that they eat. From
this, I will create designs for a data model so that each of the client requirements are met
successfully which will include worksheet structure diagrams and being able to provide a test plan.
Then, I will be reviewing the design with other clients to identify and inform improvements. The
model will be designed to be used by people who are not experts in spreadsheets and should include
a user interface which allows the user to enter personal data, activity choices, the length of time a
person wishes to spend on each activity, and food menu choices.
Furthermore, I will be explaining my justification to how the requirements are fully fulfilled and then
providing a well- detailed and reasoned evaluation of the optimised data model against client
requirements that already exist.
Nature of the Problem
As part of my assignment, I am designing and developing a spreadsheet model that will meet my
client's needs and requirements. For this task, I have been asked to develop, design, and build a
spreadsheet model that will first be able to predict the actual potential impact of changes made to
their exercise and diet, and how these changes will affect their BMI. A local leisure centre wants to
start a brand-new program that will help the people who attend to become fitter and healthier by
reducing their Body Mass Index.
5|Page
Table of Contents
Unit 5 – Data Modelling.........................................................................................................................5
Introduction...........................................................................................................................................5
Nature of the Problem...........................................................................................................................5
Functions the Model must Perform.......................................................................................................6
Function 1..........................................................................................................................................6
Function 2..........................................................................................................................................7
Function 3..........................................................................................................................................7
Function 4..........................................................................................................................................8
Function 5..........................................................................................................................................8
Function 6..........................................................................................................................................8
Function 7..........................................................................................................................................9
User Interface........................................................................................................................................9
Constraints...........................................................................................................................................11
Success Criteria....................................................................................................................................12
User Interface......................................................................................................................................13
Home Page – Design 1.....................................................................................................................13
Home Page – Design 2.....................................................................................................................14
Members Page – Design..................................................................................................................14
Food Page – Design..........................................................................................................................15
Activities Page – Design...................................................................................................................16
1|Page
,Joshua Hall Assignment 2 – Learning Aims B & C Unit 5 – 2022
Progress Page – Design 1.................................................................................................................16
Progress Page – Design 2.................................................................................................................17
Report Page – Design 1....................................................................................................................18
Report Page – Design 2....................................................................................................................18
Chart Page – Design.........................................................................................................................19
Test Plan..............................................................................................................................................20
Feedback Report..................................................................................................................................28
Data Model Evidence...........................................................................................................................35
Implementation Report...................................................................................................................37
Use of Formulas...............................................................................................................................38
Use of Functions..............................................................................................................................38
Use of Logical Functions..................................................................................................................39
Use of a Nested IF Function.............................................................................................................40
Data Manipulation...........................................................................................................................40
Auto Fill............................................................................................................................................40
Relative and Absolute Cell Referencing...........................................................................................41
Design of Database..........................................................................................................................41
Merged Cells....................................................................................................................................42
Colours, Borders & Shading.............................................................................................................42
Conditional Formatting....................................................................................................................42
Headers and Footers........................................................................................................................43
Graphics...........................................................................................................................................43
2|Page
,Joshua Hall Assignment 2 – Learning Aims B & C Unit 5 – 2022
Output Requirements:.....................................................................................................................43
Worksheet Layout............................................................................................................................43
Colours, Borders & Shading.............................................................................................................44
Graphs.............................................................................................................................................45
Using Macros & Button to Initiate Procedures................................................................................45
Extended Feedback Report..................................................................................................................45
Home Page.......................................................................................................................................46
Members Page.................................................................................................................................47
Food Page........................................................................................................................................48
Activities Page..................................................................................................................................49
Progress Page..................................................................................................................................49
Report Page.....................................................................................................................................50
Chart Page.......................................................................................................................................51
Justification..........................................................................................................................................52
Home Page.......................................................................................................................................52
Members Page.................................................................................................................................53
Food Page........................................................................................................................................54
Activities Page..................................................................................................................................55
Progress Page..................................................................................................................................56
Report Page.....................................................................................................................................57
Optimising Data Model to Meet Client Requirements.........................................................................58
Improvement/Change 1 – Headings................................................................................................58
3|Page
,Joshua Hall Assignment 2 – Learning Aims B & C Unit 5 – 2022
Improvement/Change 2 – Font Sizes...............................................................................................60
Improvement/Change 3 – Font Styles.............................................................................................61
Evaluation Report................................................................................................................................63
Meeting Client/User Requirements.................................................................................................63
Issues Encountered..........................................................................................................................66
Evaluation............................................................................................................................................68
Time Management & Meeting Targets............................................................................................68
Achieving Aims/Solutions................................................................................................................69
Identifying & Resolving Potential Issues, Reviewing & Making Improvements................................70
Response of Outcomes from Previous Feedback.............................................................................71
Witness Statement..........................................................................................................................71
Evaluated Outcomes – Including Justified Recommendation & Decisions.......................................71
Use of Effective Communication Methods......................................................................................72
Backup of Work...............................................................................................................................72
Creativeness in Layout & Research..................................................................................................73
4|Page
, Joshua Hall Assignment 2 – Learning Aims B & C Unit 5 – 2022
Unit 5 – Data Modelling
Introduction
In this assignment, I have recently been asked to design and create a spreadsheet model which can
predict the potential impact of changes in exercise and diet on a person’s BMI. Firstly, I will be willing
to produce an efficient spreadsheet model which can show a person how their BMI would change
over a period of at least 10 weeks, based on the activities they do and the foods that they eat. From
this, I will create designs for a data model so that each of the client requirements are met
successfully which will include worksheet structure diagrams and being able to provide a test plan.
Then, I will be reviewing the design with other clients to identify and inform improvements. The
model will be designed to be used by people who are not experts in spreadsheets and should include
a user interface which allows the user to enter personal data, activity choices, the length of time a
person wishes to spend on each activity, and food menu choices.
Furthermore, I will be explaining my justification to how the requirements are fully fulfilled and then
providing a well- detailed and reasoned evaluation of the optimised data model against client
requirements that already exist.
Nature of the Problem
As part of my assignment, I am designing and developing a spreadsheet model that will meet my
client's needs and requirements. For this task, I have been asked to develop, design, and build a
spreadsheet model that will first be able to predict the actual potential impact of changes made to
their exercise and diet, and how these changes will affect their BMI. A local leisure centre wants to
start a brand-new program that will help the people who attend to become fitter and healthier by
reducing their Body Mass Index.
5|Page
