Catherine George
Worksheet 1 description (Proposed layout: how many columns, rows, separate tables of information, named ranges, navigation, overall layout):In this worksheet there will be 17 columns and 52 rows all in 1 table of information. It will also have a hyperlinked home button and a macro button to save and exit. The overall layout is one large spreadsheet containing all of the pupil’s information.Cell Name/ labelData TypeValidationFormulaeFormatting (conditional formatting, cell layout, text format)Justification (explain why you have made these decisions )Pupil first nameTextNoneNoneThe title is in bold and all of the boxes are arranged like a tableThe name is text because this enables the user to type the students name easily. There isn’t any validation because it isn’t needed. There isn’t any formulae because there isn’t a formula to predict pupils’ names. The title is in bold to make it stand out from the data and the boxes are arranged like a table to help the user to identify which piece of data goes with which box.Pupil surnameTextNoneNoneThe title is in bold and all of the boxes are arranged like a tableThe name is text because this enables the user to type the students name easily. There isn’t any validation because it isn’t needed. There isn’t any formulae because there isn’t a formula to predict pupils’ names. The title is in bold to make it stand out from the data and the boxes are arranged like a table to help the user to identify which piece of data goes with which box.Year GroupNumberA drop down menu where you can select the relevant year group. If someone types in another year group which isn’t allowed then an error message will come up.NoneThe title is in bold and all of the boxes are arranged like a tableThis is a number because there aren’t any letters required to type in a year group. A drop down menu is used to make it easier and faster for the user to select the relevant year group. Since there is a lot of data the user will be able to select the year group much faster and complete their work faster. The title is in bold to make it stand out from the data and the boxes are arranged like a table to help the user to identify which piece of data goes with which box.Form tutorTextA drop down menu where you can select the relevant teacher. If someone types in another teacher which isn’t allowed then an error message will come up.NoneThe title is in bold and all of the boxes are arranged like a tableThis is in text because the names aren’t numbers or currency. A drop down menu is used because it is easier to choose the relevant form tutor without making any spelling mistakes and so that only a teacher from this school can be chosen.HouseTextNoneThis has the VLOOKUP function which uses the teachers name to find which house the pupil is in.The title is in bold and all of the boxes are arranged like a tableThis is text because it isn’t a number or currency and the house is a name (eg- “Inspiration”). The VLOOKUP function will be used because it is an easy way to find out which house each student is according to their form tutor. This means there is less typing for the user making faster and more user friendly.Total achievement pointsNumberNoneNoneThis will have conditional formatting which means that when the number in the box is above 100 the box and text turns green.This is number because it doesn’t contain any letters. It will have conditional formatting which makes it easier to see when a student is overachieving and which students aren’t.Total behaviour pointsNumberNoneNoneThis will have conditional formatting which means that when the number in the box is above 20 the box and text turns red, indicating concern.This will be a number because it doesn’t contain any letters so it doesn’t need to be text. This will be conditional formatted to make it easier for the user to see when a student has too many behaviour points. This makes it more user friendly.Alton TowersTextNoneAn if statement which says that if the achievement points are larger than 100 then an “X” will appear in the box.This whole column will be coloured pink like the rest of the externals trips to group relevant pieces of data together.This is text because everything in the column is either an “X” or nothing so it can only be text. The IF statement is used because it makes it easy to see which student qualifies for each trip and makes it easier for the user to see which students are going on which trips without looking at each students individual achievement points. This will make it faster for the user.Sea LifeTextNoneAn if statement which says that if the achievement points are larger than 90 then an “X” will appear in the box.This whole column will be coloured pink like the rest of the externals trips to group relevant pieces of data together.This is text because everything in the column is either an “X” or nothing so it can only be text. The IF statement is used because it makes it easy to see which student qualifies for each trip and makes it easier for the user to see which students are going on which trips without looking at each students individual achievement points. This will make it faster for the user.PaintballingTextNoneAn if statement which says that if the achievement points are larger than 80 then an “X” will appear in the box.This whole column will be coloured pink like the rest of the externals trips to group relevant pieces of data together.This is text because everything in the column is either an “X” or nothing so it can only be text. The IF statement is used because it makes it easy to see which student qualifies for each trip and makes it easier for the user to see which students are going on which trips without looking at each students individual achievement points. This will make it faster for the user.CinemaTextNoneAn if statement which says that if the achievement points are larger than 70 then an “X” will appear in the box.This whole column will be coloured pink like the rest of the externals trips to group relevant pieces of data together.This is text because everything in the column is either an “X” or nothing so it can only be text. The IF statement is used because it makes it easy to see which student qualifies for each trip and makes it easier for the user to see which students are going on which trips without looking at each students individual achievement points. This will make it faster for the user.Art DayTextNoneAn if statement which says that if the achievement points are larger than 60 then an “X” will appear in the box.This whole column will be coloured blue like the rest of the internal activities to group relevant pieces of data together.This is text because everything in the column is either an “X” or nothing so it can only be text. The IF statement is used because it makes it easy to see which student qualifies for each trip and makes it easier for the user to see which students are going on which trips without looking at each students individual achievement points. This will make it faster for the user.CheerleadingTextNoneAn if statement which says that if the achievement points are larger than 50 then an “X” will appear in the box.This whole column will be coloured blue like the rest of the internal activities to group relevant pieces of data together.This is text because everything in the column is either an “X” or nothing so it can only be text. The IF statement is used because it makes it easy to see which student qualifies for each trip and makes it easier for the user to see which students are going on which trips without looking at each students individual achievement points. This will make it faster for the user.Football skillsTextNoneAn if statement which says that if the achievement points are larger than 40 then an “X” will appear in the box.This whole column will be coloured blue like the rest of the internal activities to group relevant pieces of data together.This is text because everything in the column is either an “X” or nothing so it can only be text. The IF statement is used because it makes it easy to see which student qualifies for each trip and makes it easier for the user to see which students are going on which trips without looking at each students individual achievement points. This will make it faster for the user.T-Shirt designTextNoneAn if statement which says that if the achievement points are larger than 30 then an “X” will appear in the box.This whole column will be coloured blue like the rest of the internal activities to group relevant pieces of data together.This is text because everything in the column is either an “X” or nothing so it can only be text. The IF statement is used because it makes it easy to see which student qualifies for each trip and makes it easier for the user to see which students are going on which trips without looking at each students individual achievement points. This will make it faster for the user.X-BoxTextNoneAn if statement which says that if the achievement points are larger than 20 then an “X” will appear in the box.This whole column will be coloured blue like the rest of the internal activities to group relevant pieces of data together.This is text because everything in the column is either an “X” or nothing so it can only be text. The IF statement is used because it makes it easy to see which student qualifies for each trip and makes it easier for the user to see which students are going on which trips without looking at each students individual achievement points. This will make it faster for the user.CostCurrencyNoneThis will have an IF statement which will find out which box has the “X” in and then locate the cost for that trip.NoneThis will be currency because it is an amount of money not just a number. The IF statement will see who is going on which trip and locate how much it costs for each trip, making it faster and easier for the user to find out how much each student needs to pay.Worksheet 2 description (Proposed layout: how many columns, rows, separate tables of information, named ranges, navigation, overall layout):My second worksheet will be called “Data”. It will have 3 separate tables. The first will contain the information about form tutors, houses and form names. It will have 3 columns and 8 rows. It will have each form tutors name, with their house written next to it and their form name next to that. The second table has external trip information. This has 4 columns and 5 rows. This contains the trip name, date, cost and maximum number of students able to attend. Its named range is “External”. The third table contains internal trip information. It has 4 columns and 6 rows. This contains trip names, dates, costs and the maximum number of students able to attend. Its named range is “Internal”. It will also have a macro button to save and exit and a hyperlinked home button.Cell Name/ labelData TypeValidationFormulaeFormatting (conditional formatting, cell layout, text format)Justification (explain why you have made these decisions )Form tutor informationForm TutorTextNoneNoneAll of the information in this section will be arranged in a table. This will be text because it only involves letters so it doesn’t need to be General or Number. It will be arranged in a table because it is easier for the user to see what they are looking for and since there are multiple tables on this sheet it groups the relevant pieces of data together in a table.HouseTextNoneNoneAll of the information in this section will be arranged in a table. This will be text because it only involves letters so it doesn’t need to be General or Number. It will be arranged in a table because it is easier for the user to see what they are looking for and since there are multiple tables on this sheet it groups the relevant pieces of data together in a table.Form NameGeneralNoneNoneAll of the information in this section will be arranged in a table. This will be General because this includes numbers and letters so general is the most appropriate. It will be arranged in a table because it is easier for the user to see what they are looking for and since there are multiple tables on this sheet it groups the relevant pieces of data together in a table.Internal and external tableTripTextNoneNoneAll of the information in this section will be arranged in a table. It will have a data range which will depend on whether or not it is in the external or internal table. ( If it is external the range will be “External” and if it is internal the range will be “Internal”)This will be text because it only involves letters and doesn’t need to be General or Number. It will be arranged in a table because it is easier for the user to see what they are looking for and since there are multiple tables on this sheet it groups the relevant pieces of data together in a table. It will have a data range because it is easier for the user to find which data they want without reading the whole table. This will make it more user friendly and easier for the user to find the information they need.DateDateNoneNoneAll of the information in this section will be arranged in a table. It will have a data range which will depend on whether or not it is in the external or internal table. ( If it is external the range will be “External” and if it is internal the range will be “Internal”)This will be Date because the information in this column will all be dates so this is the easiest format to use. It will be arranged in a table because it is easier for the user to see what they are looking for and since there are multiple tables on this sheet it groups the relevant pieces of data together in a table. It will have a data range because it is easier for the user to find which data they want without reading the whole table. This will make it more user friendly and easier for the user to find the information they need.CostCurrencyNoneNoneAll of the information in this section will be arranged in a table. It will have a data range which will depend on whether or not it is in the external or internal table. ( If it is external the range will be “External” and if it is internal the range will be “Internal”)This will be Currency because all of the data in this column is money so it is easiest for the format to be currency. It will be arranged in a table because it is easier for the user to see what they are looking for and since there are multiple tables on this sheet it groups the relevant pieces of data together in a table. It will have a data range because it is easier for the user to find which data they want without reading the whole table. This will make it more user friendly and easier for the user to find the information they need.Maximum NumberNumberNoneNoneAll of the information in this section will be arranged in a table. It will have a data range which will depend on whether or not it is in the external or internal table. ( If it is external the range will be “External” and if it is internal the range will be “Internal”)This will be number because everything in this column will be numbers so it is easiest for the format to be number. It will be arranged in a table because it is easier for the user to see what they are looking for and since there are multiple tables on this sheet it groups the relevant pieces of data together in a table. It will have a data range because it is easier for the user to find which data they want without reading the whole table. This will make it more user friendly and easier for the user to find the information they need.Worksheet 3 description (Proposed layout: how many columns., rows, separate tables of information, named ranges, navigation, overall layout):The 3rd worksheet will be called Behaviour and Achievement. It will have a chart to show the difference between the behaviour and achievement points in each house. It will have a hyperlinked home button and a macro which will save and exit the spreadsheet. It will also has a table with 3 columns and 5 rows which shows the total achievement and behaviour points for each houseCell Name/ labelData TypeValidationFormulaeFormatting (conditional formatting, cell layout, text format)Justification (explain why you have made these decisions )Table to show the behaviour and achievement points for each houseHouseTextNoneNoneThis will be arranged in a table.This will be text because it only contains letters and not numbers.Achievement PointsNumberNoneA SUMIF function which will add up all of the achievement points for each house and give a total at the end.This will be arranged in a table.This will be number because it only contains numbers so this is the most relevant format. It will have a SUMIF function to easily add up all of the achievement points without doing it manually. This makes it easier for the user to see the total achievement points. This makes it more user friendly.Behaviour PointsNumberNoneA SUMIF function which will add up all of the achievement points for each house and give a total at the end.This will be arranged in a table.This will be number because it only contains numbers so this is the most relevant format. It will have a SUMIF function to easily add up all of the achievement points without doing it manually. This makes it easier for the user to see the total behaviour points. This makes it more user friendly.Type of Output (Graph, chart, pivot table)Justification for outputChartThis will be used to compare the total behaviour and achievement points easily with a visual aid. This helps the user to see which house has the highest achievement points and behaviour points. This makes it more user friendly and increases the speed at which the user can interpret the data.Pivot TableThe first Pivot Table will contain the total achievement points for each form tutors pupils. This allows the user to clearly see which form class has the highest achievement points and it is easier than manually adding up each student’s achievement points for each from class.Pivot TableThe second Pivot Table will contain the total cost of all the trips in each house. This will be used so that the administration’s office at the school can compare the money they have collected for the trips with the amount of money they should’ve collected, making it easier for the teachers to find out how much money they need to collect.-6667585725Design the main menu interface. 60293252571757019925180975TitleTitle233362547625Click a button to go to the relevant page!00Click a button to go to the relevant page!238125172085Hyperlinked button that links to the spreadsheet when clicked00Hyperlinked button that links to the spreadsheet when clicked4581525162560Data Page (Prices and Trips)00Data Page (Prices and Trips)742950010160Hyperlinked button that links to the data page when clicked00Hyperlinked button that links to the data page when clicked142875010160SpreadsheetSpreadsheet7038975673101276350673107600950172085Hyperlinked button which links to the behaviour and achievement points tables and charts when clicked0Hyperlinked button which links to the behaviour and achievement points tables and charts when clicked4829175181610Behaviour and Achievement points00Behaviour and Achievement points476250248920Hyperlinked button that links to the pivot tables when clicked00Hyperlinked button that links to the pivot tables when clicked157162586995Pivot TablesPivot Tables702944910796001447800584205600700163830Macro that saves and exits the document when clickedMacro that saves and exits the document when clicked315277511430Save and ExitSave and Exit4800600201930center000Alternative Solution (Could you design the spreadsheet in another way, different calculations, layouts, graphs and charts? Be specific to which parts you would change and why)Worksheet 1:I create a spreadsheet which contains all of the students information (Name, teacher, behaviour and achievement points) and I will then set up a VLOOKUP function to search for which trip the students can go on according to their achievement points. I would then give the price of the trip using VLOOKUP and use the sum function to add all of these up. In a separated table I would use SUMIF to calculate how many people are going on each trip. I would format each trip in its own colour by filling the box in a certain colour. This would make it easier to group the students who are going on each trip and make it obvious who is going on each trip.Worksheet 2:In this sheet I would put a graph in it which shows the number of students going on each trip and then put a checklist of who has paid and who hasn’t. I would put the students names in each row and then use a drop down menu with the options “Yes” or “No” which can be selected by an administrations officer as students pay. If a student hasn’t paid then their name would be conditional formatted in red. If they have paid there will be no formatting on their name. This would make the document more user friendly as it is evident when a student hasn’t paid and when they have.Worksheet 3:In this sheet I would use a pivot table to add up the total cost of each trip for each house. This would mean that the total amount of money owed for each trip could be calculated like in my original design easily for user friendliness. I would also add in a chart to show how many students have paid and how many haven’t.Main Menu:I would have 3 hyperlinked buttons which would link into each different worksheet in the document. This would be for ease of use for the user, very similar to my original design.DistinctionHow will your proposed spreadsheet fulfil the purpose and the user requirements?My spreadsheet will meet all of the requirements as it is a user friendly way of seeing who is going on each trip. It gives all of the required information and more. It includes charts and pivot tables which help the user to clearly and easily see the data they need. It also provides the total amount of achievement and behaviour points for each house and a chart to show this making it user friendly and easy to use. Also it fulfils all of the things that the school wants and more. According the scenario the manager wants 3 screens, I have provided 4 screens and a home screen which has more than what the manager required. This will help to fulfil all of the things that the upper school require for their trips, making it easy for them to know which student belongs to which house, which student qualifies for each trip, how much money they owe and the total number for each trip. This is everything that the scenario asks for so it fulfils the requirements.Any design constraints? (What might affect you from developing the spreadsheet as designed, Time, knowledge, technology?)The time constraint may prevent me from making this spreadsheet to my full capability. This may prevent me from doing this because I may be unable to complete the spreadsheet, making me rush and miss certain things out in a formula. Another thing that may constrain my design is the technology available. Since I don’t have Microsoft spreadsheet at home I will be unable to improve on this outside of school, making it more difficult for me to complete the task under the time constraint.Why did you reject your alternative solution?Explain why you have chosen your main design instead of your alternative solution.I rejected my alternative solution because my first design offers macros which are more user friendly. The whole document seems like it will be more user friendly than my alternative design and it also seems like it has more complex formulae which do things that fulfil the criteria better than my alternative solution. This makes it better for the school as they don’t have to input the numbers multiple times as they can use a VLOOKUP function or an IF statement to find the thing they need. This makes it better for the user and more suitable for the target audience, therefore it fits the specification better. ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.