Answering your last question, yes you can put the support workers and their salaries and a calculation to determine their hourly wage into a table and do a LOOKUP to get the hourly wage. This would be the recommended method.
Going off on a slight tangent from the problem as stated, what about all their fringe benefits (vacation, health insurance, sick leave, etc), office/class space (if it isn't already a fixed cost), supplies and materials and equipment, employment taxes (if that applies), and anything else that has a cost associated with this support. Do they require management time, another potential cost?
Also, should their "hourly wage" be determined not by a fixed number of hours per year but by how many hours they actually spent providing support during the year? If yes, this adds some complexity to the problem.
Thanks very much for responding!
I have the worker's annual salary based on the cost to the school - not based on their pay - so the salary I have includes all 'extras', and is based of their Full Time Equivalent wage - is this enough info? They work 46 weeks a year - but this is accounted for in their FTE salary as is any insurance, health insurance etc we have to pay on their behalf. I have very little direction from the education authority - and am trying to muddle through best as I can!!
Table 1 cell E2 =IFERROR(LOOKUP(B,Table 2 :: A,Table 2 :: C)*C*D/60,"")
fill down to last row (not into the footer row at the bottom)
Table 1 footer row (not just the last row, needs to be a footer row) = SUM(E)
Table 2 cell C2 =B/(46*5*8)
I don't know if that is the correct formula. I assumed 5 days/wk and 8 hrs/day.
fill down to the end
You might want to use a popup menu for the teacher names in Table 1. It will keep you from accidentally mistyping a name.
The lookup will do a "close match" if you type a name in wrong in Table 1 or the name you typed isn't in Table 2. In other words, it might look up some other teacher's data.
You can add a column in Table 1 for total cost. I didn't know if I was supposed to multiply by 46 weeks or not so I left it off.
Here's another little spanner in the works:
Presumably, the support worker's annual salary pays for the time spent in preparation and administration/record keeping as well as the actual contact time with each child. Are you accounting for that time in your cost calculations?
Question to the EA: What advice and guidelines been given to the individual schools to ensure that calculation and reporting of "the cost of additional support each child receives" is consistent across all schools in the EA's jurisdiction?
A real spanner!!! Just prooves that the education authority do not consider these things! The directive I have had is for contact time only - so I'll stick with that - but will send an email as to what this data does not contain!
thanks for all the responses - I am working on it this morning - hopefully it will be 'close' - and at least proportionate for each child - which is what they want I think.
This has worked brilliantly! Thank you very much.
Can I ask you a follow up question - and I apologies for this! - this is beyond what the county have asked - but I thought it would make useful and interesting statistics....
Could I calculate the percentage of the support one child receives when compared to the total support? This could be percentage of time and/or cost? It would be useful to demontrate to some parents that little johnny recives 10% of the total time allocated to support or 5% of the support budget for example.
Apologies again for asking for more - as the above solution was perfect. Thanks again.
One other stumbling block - last one I promise!!! Some children are seen in a group. Their time support is therefore effectively divided by the number in the group. Is it possible to divide the hours support per week by the number in a group (but only if they are in a group) a therefore get the true level of support. I tried it but get the red triangle when they are not in a group - which is the majority of pupils.
Yes, you can calculate the percentage one child receives. How you do it depends on whether each child is listed only once in the table or might be listed multiple times. If only once, you can divide their cost by the total cost and do this on the same line their name appears. If they might be listed more than once, you will need to do a SUMIF to get the sum of the costs for that student using their name as the condition, then divide by the total cost.
To handle groups, there are also two ways you might handle this. You'll need another column. The easy way is to enter the number of students in that group and modify the cost/week formula so it divides by the number of students in the group. A better way would be to give each group a number or name or some other designation in that column, do a SUMIF to determine how many students are in that group, and divide the cost/week by that number. This second way is better in that if you make a change (add/delete someone from the group), you don't have to search out everyone else in the group and change their number too.
I can't provide any formulas for you at the moment, I have to take off in a few minutes. The SUMIF for the number of students in the group would be of the form IF(the group column = "",1, SUMIF(...)) and you would use it directly in the cost/week formula or you could add another column for that number then use that column in the cost formula. In other words, if the student is not in a group (the column is blank), the number in the "group" is 1, else sum up how many students are in the group.
I should have said COUNTIF, not SUMIF, for some of that. Here it is:
cell F2 =IF(E="",1,COUNTIF(E,E))
cell G2 =IFERROR(LOOKUP(B,Table 2 :: A,Table 2 :: C)*C*D*(1/F)/60,"")
cell H2 =IFERROR(G2/G$11,"")
cell I2 =IF(COUNTIF(A$2:A2,A2)=1,SUMIF($A,A2,G)/G$11,"")
Notice how column I adds up both rows for "Johnny" and gives a percentage for him in one row only, leaving his other row(s) blank.
The group names I used don't make much sense, but you get the idea.