Question

Unemployment among high school graduates is quite high due to a recession. City Community College is considering a new program to help young people get the training they need to be more employable. The college has collaborated with the municipal hospital to build a nurse's aide program, a 1 - year program that would lead to immediate employment. Initial financial analysis indicates that the total fixed cot of the program will be $200,000, which includes the cost of a 1-year rental of the facilities plus utilities, insurance, and administrative costs The variable cost and step cost together are $10,000 per student, which pays for facility salary, student lunches, and teaching materials and textbooks. The state kicks in $5000 per student, and the tuition based on market analysis is $6000. Given that the nurse's aide program has never been offered in the region before and will be under financial pressure due to current funding cuts, the college's board of trustees would like to know how many students will need to be enrolled for the program to break even. The board also wants to know what other options it will have to mitigate these financial issues if the expected enrollment is below the breakeven point, given the high profile of the program at a time when employment and economic recovery are critical.

Consider two possibilities: (1) Funding is limited to $100,000, or (2) with the benefit of efficiencies, variable, and step costs can be reduced to $9,500. You are required to do a financial analysis of the proposed program. Please provide a spreadsheet solution and a written explanation of your approach.

Answer 1

					Formula
			Fixed Cost	$ 2,00,000.00
			Variable Cost & Step Cost	$    10,000.00
			Funding from State/student	$      5,000.00
			Tuition Fee	$      6,000.00
			Total fee amount received/student	$    11,000.00	funding/student+tutition fee
			Contribution of each student	$      1,000.00	total fee received - variable & step cost
	(A)	1	Break-even number	200	Total Fixed Cost/Contribution per student
		2	no of students enrolled	150
			Tuition fee to be collected in case of shortfall in the number of students enrolled (<200)	6333
	(B)		Funding limited to $100,000
			Fixed cost	$ 2,00,000.00
			State Funding	$ 1,00,000.00
			Tuition fee received per student	$      6,000.00
			no of students required for $100,000 state funding	20
			Total Fee received for 20 students	$ 2,20,000.00
			Total Cost for 20 students	$ 4,00,000.00
			Since the total fee received for 20 students is less than the cost, the programme will not be financially viable even with an increase in the number of students as the incremental cost would be higher than incremental fee collected
			To make the program successful, the tuition fee to be collected is to be increased and will depend on the number of students enrolled
			No of students	50
			The fee to be collected	12000
	(C )		Variable and step cost reduced to $9,500
			Fixed Cost	$ 2,00,000.00
			Variable Cost & Step Cost	$      9,500.00
			Funding from State/student	$      5,000.00
			Tuition Fee	$      6,000.00
			Total fee amount received/student	$    11,000.00	funding/student+tutition fee
			Contribution of each student	$      1,500.00	total fee received - variable & step cost
			Break-even number	133	Total Fixed Cost/Contribution per student

The above solution has all the scenarios in the question.

Part A.Break even is the number of students for which fixed costs are covered using the contribution of each student. Contribution = total fee received per student - variable and step costs = 11000-10000=1000

In case the number of students is less than the break-even number, the fee collected from each student should be raised and the appropriate formula is given.

Part B. If state funding is limited to $100,000 it means only 20 students will be funded with $5000 each. For 20 students, the total cost will be $400,000 but the total fee received will be $220,000. SO in this case fee is to be increased and the appropriate formula is given in the above table.  

Part C. Same as Part A with only change in the value of Variable and Step costs.