BTEC Unit 5: Data Modelling - Assignment 2 & 3 Learning Aim B & C 2023

BTEC Unit 5: Data Modelling - Assignment 2 & 3 Learning Aim B & C 2023. 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 the section, there are many functions that the model must perform. Generally, these functions require complex calculation that is built in the project named “Microsoft Excel”. The functions that currently exist in this project are: SUM – This function will be used to calculate the total cost between number1 and number2 by adding up these together. An example could be adding all numbers in the cells of B1 and F5 and then providing the equals sign to get the total cost of the product. AVERAGE – This function is used to average all the numbers in the cells from B1 to F5. Also, this function can also be entered as part of a formula in a cell of a worksheet. COUNT – This function is usually used to count the number of cells that contains numbers and then counts the numbers within the list of arguments. Additionally, this function will get the number of entries in a number field that includes range of array of numbers. An example of this could be entering the following formula to count the numbers in the range. COUNTIF – This function is generally used to count how many numbers in cells B1 to F5 are higher than 50. Also, it will also be used to count how many 70s are in the cells from B1 to F5. The counting process will be Unit 5 – Learning Aim B IF – This function can perform a logical test to check whether the conditions are met and returns one value of false and another value of true. For instance, if the exchange rate of 0.7 is greater 0.3 or 0.6, it would result with a pass but if it is less then it will result with a fail instead. LOOKUP – This function would be used to search one column and would return the corresponding value from another one-column or one-row range. In general, the function “LOOKUP” would make it useful for solving certain problems in Excel. For example, if I wanted to search for a value of the total currency (in pounds), I would select that specific column and then another column to find an overall value. User interface: The user interface will consider including a minimum of at least two or three design layouts. In addition to this, I will be using Microsoft Excel to provide all the data into an entity and from this, I will be drawing rough designs to include the display of how my data model will look like before implementing this. Furthermore, my table will contain links such as including buttons so that it navigates to another page. The other page will be my form to include the labels and fields for users to input information. Also, I will also comprise a data analyst such as graphs to track the changes being made and if the costs of sale are lower or higher predicting the results to be changed over time. Constraints: For this part, I will be considering the constraints that could occur during the process of my data modelling using the project “Microsoft Excel”. This would help me to improve and make sure that I do not encounter the same problems again in the future. One of these constraints will include: Time – This will be one of the constraint issues that can exist as I may not have enough time to finish off the project and to make it accurate and efficient as possible. Furthermore, the limit of time could affect me with calculations as it may usually take long to figure out the data to be calculated. Cost – This would be another issue of my constraint as the amount of budget I have may not be enough to meet each of the hardware requirements and which ones to buy. The limitations with these will possibly prevent me to continue with the data modelling process since the hardware that I have may not function properly after having to purchase a cheaper hardware (PC, Laptop). Skills - The skills would be another common issue as part of my constraint because I may have a lack of skills that would need to be required when dealing with the tools that have been provided on the project “Microsoft Excel”. There are some tools that I may not know about or have a clue, and this would show that I have less skills which wouldn’t meet the criteria or requirement. Hardware – The use of hardware that I use may process slower than expected and will be wasting time as I would have to wait until it loads each time I create a column and inputting the data. If the loading process takes too long, then it will buffer and result with having to exit the application and restart. Unit 5 – Learning Aim B Software – The use of software that I use will be part of my constraint as there may be compatibility issues with using this project. Success criteria: The success criteria that is needed is to show how my system is successful and is a really good solution of making users aware of what is expected of them and by encouraging them to extend themselves by looking through each bit on my table. User interface design: Here I will show my design layouts and it would usually refer to graphical user interfaces to focus on looks or style and give a quick brief of how the display will look like. This design can then be used for optimisation later. My design is shown below: Main table of data: Cheese Cost per kilo (€) Cost per kilo (£) Quantity Profit + VAT VAT included Volume Sale price (£) Camembert 28 24.94 900 £7.84 £1.57 20 £27.44 Emmental 32 28.50 850 £8.96 1.79 20 £31.36 Gruyere 35 31.17 875 £9.84 1.97 20 £34.44 Edam 29 25.83 825 £8.12 1.62 20 £28.42 Gouda 31 27.61 700 £8.76 1.75 20 £30.66 Table to change data: Export file Exit Copy data Print Settings Main Menu Graph display Data of records Unit 5 – Learning Aim B Exchange rate: Price increase (10p) Profit margin (%) 0.3 £0.10 40.00% 0.4 £0.10 40.00% 0.5 £0.10 40.00% 0.6 £0.10 40.00% 0.7 £0.10 40.00% 0.8 £0.10 40.00% 0.9 £0.10 40.00% Settings table: Exchange rate Change of price Margins 30.00% -0.9 40% 40.00% -0.8 40% 50.00% -0.7 40% 60.00% -0.6 40% 70.00% 0 40% 80.00% 0.1 40% 90.00% 0.2 40% Data validation: Input mask – This would be the string of characters that indicate the format of valid input values. This would be located from the cells menu when a cell is clicked and then from there, format cells would need to be chosen. Finally, when clicking on custom, it will show the type box and I would select the correct type for input mask and it will be “#”:”00. Range check – This would be used on data made up of numbers or dates which must fall into a range. Combo box – This is a type of dialogue containing a combination of controls such as drop-down lists which will be useful for my data model in Microsoft excel. In addition to this, it is used to select one of the options given. These validations above will be beneficial to me as it will make my tables and forms very accurate and convenient.