11 Replies Latest reply: Jan 18, 2013 1:02 PM by Gwyn Plem
Level 2 (160 points)

Hi,

I am hoping that someone here can help! We have been directed by our education authority to calculate the cost of the additional support each child receives. I am trying to create a table for ease!

I have though, hit a problem! There are so many variables - I don't know where to start!

- We have 5 support workers at the school - each on a slightly different wage.

- The children receive different amount of support - some 30 minutes a day, 5 days a week - others 15 minutes 3 times etc.

Basically I want the table to calculate -

If little Johnny gets 15 minutes daily support 5 times a week from Mrs Brown (Annual salary £15,000), how much does little Johnny's support cost the school.

or

If Little Jenny gets 30 minutes support three times a week from Mr Evans (Annual Salary £12,000) how much does little Jenny cost the school.

Hopefully I am making myself clear. Can anyone suggest some help?  The staff work around 28 hours a week (trying to figure what info is needed!)

One area especially - seeing as I have only 5 support workers - can I enter their wages into a chart, and then call up their salaries - save me typing that in every time?

Thanks in advance if anyone is willing to give me some help!

MacBook Pro, Mac OS X (10.7.2)
• ###### 1. Re: School trying to calculate cost of support per child
Level 6 (10,815 points)

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.

• ###### 2. Re: School trying to calculate cost of support per child
Level 2 (160 points)

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!!

• ###### 3. Re: School trying to calculate cost of support per child
Level 6 (10,815 points)

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

Notes:

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.

• ###### 4. Re: School trying to calculate cost of support per child
Level 7 (29,180 points)

Hi Gwyn,

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?

Regards,

Barry

• ###### 5. Re: School trying to calculate cost of support per child
Level 2 (160 points)

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.

• ###### 6. Re: School trying to calculate cost of support per child
Level 2 (160 points)

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.

• ###### 7. Re: School trying to calculate cost of support per child
Level 2 (160 points)

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.

• ###### 8. Re: School trying to calculate cost of support per child
Level 6 (10,815 points)

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.

• ###### 9. Re: School trying to calculate cost of support per child
Level 2 (160 points)

Thank you very much. I will try my best and follow these instructions. You have been very kind. It is coming together great thanks to you - here's hoping I can SUMIF these groups!!!

• ###### 10. Re: School trying to calculate cost of support per child
Level 6 (10,815 points)

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.

• ###### 11. Re: School trying to calculate cost of support per child
Level 2 (160 points)

Fantastic - thank you so much for everything. It works perfectly. Thanks again for everything.