Pearson BTEC Unit 5: Data Modelling Learning aim – B & C | Level 3 qualifications in Information Technology

Pearson BTEC Unit 5: Data Modelling Learning aim – B & C | Level 3 qualifications in Information Technology. Data entry and Validation...........................................................................................................................3 Test data................................................................................................................................................3 Test data...............................................................................................................................................3 Reviewing and refining data model designs...............................................................................................3 Communicating with Clients.................................................................................................................3 Gathering feedback from Clients..........................................................................................................3 Scheduling and documenting meetings................................................................................................3 Adjusting timescales.............................................................................................................................3 Refining ideas and solutions.................................................................................................................3 Updating design specification documentation.....................................................................................3 Conclusion................................................................................................................................................... 3 Learning aim - C - Developing a data model solution.................................................................................3 Processing features and requirements.......................................................................................................3 Formulae..............................................................................................................................................3 Functions..............................................................................................................................................3 Logical functions...................................................................................................................................3 Data manipulation (sorting, grouping, filtering, Pivoting data..............................................................3 Importing and exporting data...............................................................................................................3 Cell referencing and autofill..................................................................................................................3 Using macros........................................................................................................................................3 Using buttons to initiate procedures....................................................................................................3 Use of data entry forms........................................................................................................................3 Protecting cells (locking and password protection)..............................................................................3 Automated data transfer......................................................................................................................3 User prompts and messages................................................................................................................3 Output requirements..................................................................................................................................3 Testing functional requirements specification........................................................................................3 Test data...............................................................................................................................................3 Test data...............................................................................................................................................3 Test data...............................................................................................................................................3 Other factors to consider........................................................................................................................3 Reviewing and refining data model solution..........................................................................................3 Skills and behaviour...............................................................................................................................3 Media & communication skills...............................................................................................................3 References and ibliography ........................................................................................................................3 edit Abstract Learning aim: B & C: B: Design a data model to meet client requirements C: Develop a data model to meet client requirements Assignment title: Design, create and evaluate a data model Vocational Scenario or Context You have recently joined a new company as a trainee data analyst. The company import a range of different speciality Cheeses from France and the Netherlands. They supply the cheese to a number of different restaurants and specialist delicatessens throughout the UK. The company face a number of challenges due to currency exchange rate fluctuations (between the Euro, that they buy the cheese in, and the pound that they sell it in) and the variations in economic growth. Their product is relatively expensive and sells better when economic growth is good and less well when it is poor. You have been asked to design and create a spreadsheet model which can predict the potential impact of exchange rate fluctuations on the company profit margins. The model should use the data collected by the company on average monthly sales of the top five best-selling cheeses in their range: Cheese Cost per Kilo in Euros Average monthly sales based on a Euro to Pound exchange rate of 0.7 Camembert 28 900 Emmental 32 850 Gruyere 35 875 Edam 29 825 Gouda 31 700 The typical margin applied to calculate the UK sales price is 40%. Profit is calculated by subtracting the cost per kilo in pounds from the sales price. The company have found that on average for each 10p rise in the sales price (based on the 0.7 exchange rate) the average monthly sales drops by 20 units. If the exchange rate drops below 0.7 then the sales increase by a similar amount. When the exchange rate increases the company can do one of two things:  decrease the margin so prices stay the same  increase the prices keeping the margin the same, but this will reduce sales volume Your model should show the impact of doing each of these on the overall profit the company makes. The model you develop should 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 the exchange rate and then choose to either change the margin or the prices. Task 1 Following on from the presentation you created for the manager of the company you now need to: • Produce a design for the data model that meets the requirements described above including worksheet structure diagrams and a test plan. Your design should include a number of alternatives, for example to the way the user interface is designed. • Review your design for the model with two other people and record their feedback and use it to improve your design. • Write a justification of the design decisions you made and how you arrived at the final design. • Develop the model based on your final design.  Test the model using the test plan you created, checking that it functions as it should and that it produces the correct results. • Demonstrate your completed model to a user and collect their feedback, using their feedback and results of your testing to refine and optimise your model. • Write an evaluation of the design and the final optimised version of your data model considering how well it meets the client’s requirements. Present evidence that you have shown individual responsibility, effective time-management in your design and development of a data model making high-quality justified recommendations and decisions. For example, you need to show how you have: • Planned and managed your time and met targets. • Reviewed and responded to outcomes including the use of feedback from others • Behaved appropriately while completing the assignment – including professionalism, etiquette, supportive of others, timely and appropriate leadership, accountability and individual responsibility • Evaluated outcomes to help inform high-quality justified recommendations and decisions • Used appropriate methods of communication effectively Checklist of evidence required  Feedback from the design review from at least two people and evidence of how you used this to improve the design, e.g. before and after designs  Written justification of your design decisions  Evidence of developing the model, e.g. annotated screen shots showing the development process  Feedback for the review of the model by a user  Evidence of how you used the feedback to refine and improve the model, e.g. before and after screen shots  An evaluation of the design and the completed model  Evidence that you have shown individual responsibility, effective time-management and made high-quality justified recommendations and decisions Criteria covered by this task: Unit/Criteria reference To achieve the criteria you must show that you are able to: 5/BC.D2 Evaluate the design and optimised data model against client requirements. 5/BC.D3 Demonstrate individual responsibility, creativity, and effective self-management in the design, development and review of a data model. 5/B.M2 Justify decisions made, showing how the design will fulfil its purpose and client requirements. 5/C.M3 Optimise the data model to meet client requirements. 5/B.P3 Produce designs for a data model which meet client requirements. 5/B.P4 Review the designs with others, to identify and inform improvements. 5/C.P5 Develop a data model to meet client requirements. 5/C.P6 Test the data model for correctness, functionality and acceptance. 5/C.P7 Review the extent to which the data model meets client requirements. Learning aim - B - Functional Specification Introduction In this assignment, I have recently been chosen to join a new company as a trainee analyst, Firstly, I will be willing to produce an efficient spreadsheet model which can predict the potential impact of exchange rate fluctuation on the company profit margins. 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. 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 The nature of the problem is that number of challenges that the user has previously been faced and this would be the currency change issue that resulted with inaccuracy with both pounds and euros Functions the model must perform For this section, there are many functions that the model must perform, generally these functions require complex calculation that is built in Microsoft Excel application. The functions that currently exist in this application are: a) SUM function – this function will be used to calculate the total cost between number one and number two by adding them up together. For example, adding up all the numbers in the cells B12 to B16 and providing the equal sign to get the total of the Cost per Kilo in Euros(€) b) AVERAGE function – c) COUNT – d) COUNTIF – e) IF f) LOOKUP g) TIME – h) DATE – i) Use interface and navigation