Sorry, but copying text is forbidden on this website!
Another worksheet I want to create is a mark sheet that shows a student marks in a particular subject. This will contain at least three test marks, the total of these marks and then the term average. The functions I will use here are SUM and AVERAGE. I will also create a worksheet that shows the records of the students’ marks in all subjects. The total will be calculated and finally the overall average. I will also create a worksheet that will show a report card, back and front. The marks present here will be the marks taken from the worksheet containing all the student’s marks by using the function VLOOKUP.
A worksheet containing the salary information of the schools employees will also be created. This will show the basic wage the employee gets, the main salary (which is more than the basic) and the allowance (which is the difference between the basic and the main salary). I will also highlight the employees, which have more than 2350 pounds so that they get bonus and a tax payment, using CONDITIONAL FORMATTING function.
I will also generate a worksheet which shows the salary statement. This will show the total expense made by the salaries. I will include an ADDRESS field, which will show the address of the employees. I will use the SUBTOTALS function so that the calculations of the total expense made will be easier. And finally, I will also create an income statement worksheet that will show the total income and statement made by the school. I will also include a pie chart to show the income and expense.
First of all I want to tackle the problems using Ms Excel. Let us tackle the calculation problems first. Using simple functions like SUM and AVERAGE, we would be able to get a fast and simple answer and to make sure that the answer is correct, we can re-check to see if the values that we input is correct. When it comes to mathematical calculations in income statements, using simple functions as shown above will do.
Now let us see the problems in mark registers. We can create a lot of worksheets [and name it ‘mark registry 1’ or ‘2’ and so on] where we can input all the marks of students, from different subjects [see sample 2 or 3] in the mark register into the computer and for the calculations for Total and Average and Percentage, it is here we can apply the use of SUM and AVERAGE and for percentage we can make a formula like the one shown below:
(G8 – an example of a cell address) =G8/200*100 Now we can create another sheet [named ‘attendance’] where we can input the regularity of attendance of each student. Those who are present can be marked ‘P’ and the absentees ‘A’. We use the function, COUNTIF, where we can count the no. of the absentees or those who were present by using a formula like the one shown below:
(B21:Q28 – range of cells) =COUNTIF (B21:Q28,”A”) And to create a report card, we can use a function VLOOKUP where we can take the data from the worksheets named ‘mark register 1’ or ‘mark register 2’ and so on. The formula would look something like the one shown below: =VLOOKUP (102,CUSTOMER! A2:H3, 1,TRUE)
And now before printing out the report card, we must check and see if the data on the report cards are correct and if it is, we can print out a neat and nice report card. In case, I want to see the top ten in the mark registers, I can use the AUTOFILTER option. This function can display all rows that fall within the upper or lower limits I’ve specify. This makes the data look organized. The above is just an example of what I’m planning to do. Using certain function shown above and some more functions I haven’t mentioned, I can create pay slips, income statements etc.
To produce the pay slips, I plan to make a worksheet [and name in ‘salaries’] that will show the teachers’ names, basic salary, housing allowance, gross salary, deductions such as tax, pension plan etc. and the net salary. To achieve this, I plan to use functions such as SUM and to create the pay slip; I will use VLOOKUP to put all the necessary details. Concerning my worksheets, I would also like to validate my data. If I use the VALIDATION option in my worksheets, it can somehow, prevent the wrong input of values because I have specified a range where, if I input a number, I can only input a number between that specified range. If I input a wrong value or a value more or less than the specified range, an error message would appear stating that I have input a wrong value. I plan to use this VALIDATION option in my ‘mark registries’ worksheets in my ‘salaries’ worksheet.
As for my outputs, I want a clear, neat report card that shows the student’s grades for each subject and comments and the average percentage of the student. I want a pay slip that shows the basic and gross and net salaries along with the deductions and name of the person who is to receive the pay slip. I want the income statements for every 3 months to show all the income the school gets from the tuitions, services, bookshop, canteen and the expenses such as the salaries, electricity, water, and I want to see the total income and the total expense of the school. And also I want to generate a graph that would be used a presentation of the school’s income and expense report.
The basic outputs I will produce are the attendance sheet, the marks sheet, the salary information sheet, the salary statement sheet, the income statement sheet and the pie charts. Attendance sheets are very essential to the teacher since it contains the information whether a student was present or absent. Mark sheets are very important also because without these marks, there would be no report card.