As Miss White needed help to keep track of monthly payments made by the 10 pupils in her group and to keep a monthly record of individual payments, a running total for each pupil, the overall total collected each month and the average amount per pupil collected each month. The overall cost of the trip is £500 per pupil between the months of October to March excluding December.
Choice of Application Used
To do this I needed to use the software Microsoft Excel 2003 as it was easier to use than other software products. As well Microsoft Excel can perform more useful functions such as Absolute Cell Reference, Functions (MIN, MAX and AVERAGE),Conditional Formatting and many more. Each of these useful in a case such as this.
The information was found for this spreadsheet by using the database used in the previous Assignment B2 by running a query in the pupil database to find Miss White’s pupils, although in the assignment descriptions it mentions that there are 10 pupils and once the query was run 11 pupils were found. As Jenny Vance was added into the previous Assignment she was removed for the following.
As the values are being inputted and the pupils aren’t allowed to give up more than £100 a month to stop this from happening I used validation to make sure that if a value was added a warning sign to stop you from inputting that value.
To do this I highlighted the cells which need this function of validation used and went to the data toolbar and validation.
After this the screenshot as below showed up and I was able to tell this tool to let any values being put in more than £100.
After this was done the warning as shown below in this screenshot showed up after I inputted £101 in a cell and validation wouldn’t allow it as no more than £100 could be put in.
To make the spreadsheet more applicable and to make it easier, with the use of conditional formatting the user could see which pupil has still yet not paid for the trip and how much they still have to pay. Both of these are under the heading, “Total,” and, “Outstanding Balance.
By choosing a suitable colour which would make it more evident for the user to see which pupils still have to pay and how much they need to pay.
After the values were inputted and the correct formulas/functions...