CVPSensitivity analysis spreadsheet recommended Quality Cabi

CVP-Sensitivity analysis; spreadsheet recommended. Quality Cabinet construction is considering introducing a new cabinet-production seminar with the following price and cost characteristics.

Tution…………………………………………… $200 per Student
Variable Costs (wood, supplies, etc..)……………………. $120 per Student
Fixed Costs (advertising, instructor’s safety, insurance, etc.)………………….. $400.00 per year.

What enrollment enables Quality Cabinet construction to break even?

b. How many students will enable Quality Cabinet construction to make an operating profit of $200,000 for the year?

c. Assume that the projected enrollment for the year is 8,000 students for each of the following situations:

1. What will be the operating profit for 8,000 students?

2. What would be the operating profit if the tuition per student (that is,l sales price) decreased by 10 percent? Increased by 20 percent?

3. What should be the operating profit if variable costs per student decreased by 10 percent? Increased by 20 percent?

4. Suppose that fixed costs for the year are 10 percent lower than projected whereas variable costs per student are 10 percent higher than projected. What would be the operating profit for the year?

Solution

a. Break-even No.of students to be enrolled: Fixed costs/Contribution earned per student Contribution per student= Tuition fees received-Variable costs per student ie. 200-120= 80 So, BEP= 400000/80= 5000 students b. No.of students to be enrolled=(Fixed costs +Target profit)/Per student contribution No.of students to be enfrolled=(400000 +200000)/80 7500 students c. 1...Operating profit=(8000*80)-400000=240000 2… Sales price decreased by 10% ie. 200*(1-10%)=180 So,Contribution per student=180-120=60 Operating profit for the Year=(8000*60)-400000= 80000 Sales price increased by 20% ie. 200*(1+20%)=240 So,Contribution per student=240-120=120 Operating profit for the Year=(8000*120)-400000= 560000 3… Variable cost decreased by 10% ie. 120*(1-10%)=108 So,Contribution per student=200-108=92 Operating profit for the Year=(8000*92)-400000= 336000 Variable cost increased by 20% ie. 120*(1+20%)=144 So,Contribution per student=200-144=56 Operating profit for the Year=(8000*56)-400000= 48000 4… Variable cost projected=120*(1+10%)=132 So, Contribution per student= 200-132= 68 Fixed costs projected= 400000*(1-10%)=360000 So, Operating profit for the year=(8000*68)-360000= 184000