BUS/CPT 357

Franklin College

Summer 2003

Erich Prisner,

You are working at an institution of adult education. You re asked to prepare an Excel sheet.

It should have data entry cells for some teacher proposing a course. The data cells should be

- class hours (number of)
- fee per hour
- lump fee (due only once)
- subsidy
- maximum possible participants
- expected number of participants
- expected reduction of fee (no reduction on lump fee)
- minimum salary per hour for the teacher
- cost for learning material
- rent for the room
- other costs

Next the sheet should contain data entry cells for the responsible manager supervising the area. Here we have data entry cells as

- actual salary of teacher per hour
- minimum number of participants

These fields should pop up in red if the manager types in a value larger than the value in the "expected number of participants" into the "minimum number of participants" fields, as well as when she types a values smaller than the "minimum salary" into the "actual salary" filed. Use conditional formating.

Finally we have three columns of data output cells showing

- total teacher salary
- total fee
- degree of profitability I (=total fee divided by total teacher salary)
- total costs
- total revenue
- degree of profitability II(=total revenue divided by total costs)

computed for the minimum number, expected number, and maximum number of participants. Again the degrees smaller than 100% should pop up in color. Remember that naming cells makes the formulas more understandable.

After having finished the sheet, type in values into the teacher input cells. Possible values that make sense are

- 28 class hours
- 3.10 Sfr fee per hour
- 10 SFr lump fee (due only once)
- 500 SFr subsidy
- a maximum of 25 participants
- 15 participants expected
- 20% expected reduction of fee
- 35 Sfr minimum salary per hour for the teacher
- 120 SFr cost for learning material
- 140 Sfr rent for the room per hour
- 0 Sfr other costs

but you are free to choose your own values. Then assume you are the manager who has to fix the minimum number of participants and teacher salary. Assume the goal is to have both degrees of profitability larger than 100% (If that is too difficult, assume that we just want to raise the second degree above 100%). Use a two-dimensional data table to get an overview of the possible combinations, maybe using conditional formatting here would also be appropriate. Next fix the minimum number of participants and use goal seek to find the corresponding teacher salary necessary to achieve your goal. If there are no solutions you may have to change the teacher input data. Finally use the solver to find an appropriate combination of parameters. Write down your findings at the end of the sheet.

Up to main page Erich Prisner, June 2003