## School trying to calculate cost of support per child

348 Views 11 Replies Latest reply: Jan 18, 2013 1:02 PM by Gwyn Plem
Calculating status...
Currently Being Moderated
Jan 17, 2013 10:59 AM

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)
• Level 6 (10,515 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.

• Level 6 (10,515 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.

• Level 7 (28,825 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

• Level 6 (10,515 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.

• Level 6 (10,515 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.

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.