Franklin College
Summer 2003
Erich Prisner,


Project 1

Part 1: Create a sheet

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

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

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

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.

Part 2: Use the sheet

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

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