Formulate a linear programming model for the Summer Training Program for Teachers Case Problem, develop the spreadsheet model and solve it using Excel Solver. Show complete solution. Ensure that the formulas and Solver parameters of your spreadsheet model are properly set.

The Education Department announces the conduct of the Summer Training Program for Teachers, with the aim of enhancing the knowledge, skills and attitude of teachers in terms of learning area content and pedagogies. The program will run for 8 weeks during July and August and will be conducted in the State University. Training participants come in batches for a 1- week training, during which time they live in the University Apartelle. At the end of a week, a new batch of teachers comes in.

Lisa works as the Laundry Supervisor in the University Apartelle. She is in charge of arranging for sheets for the beds the participants will sleep on in the apartelle. Lisa has been instructed to develop a plan for purchasing and cleaning sheets each week of training at the lowest possible cost.

Clean sheets are needed at the beginning of each week, and the participants use the sheets all week. At the end of the week, the sheets are stripped off the beds and placed in large bins. Lisa must arrange either to purchase new sheets or to clean old sheets. A set of new sheets costs P600. A local laundry has indicated that it will clean a set of sheets for P240.

A couple of student friends of Lisa have offered to clean some of the sheets. They offer to charge only P120 for each set of sheets they clean. However, while the laundry will provide cleaned sheets in a week, Lisa's friends can deliver cleaned sheets only in 2 weeks. They are going to summer school and plan to launder the sheets at night at a neighborhood Laundromat.

The accompanying table lists the number of participants who have registered during each of the 8 weeks the training program will operate. Based on discussions with housekeeping administrators that handled previous training programs and on some old records and receipts, Lisa estimates that each week about 20% of the cleaned sheets that are returned will have to be discarded and replaced. Food and drinks get spilt on the sheets, and sometimes the stains do not come out during cleaning. Occasionally the sheets get torn at the cleaners. In either case, when the sheets come back from the cleaners and are put on the beds, 20% are taken off and thrown away.

At the beginning of the summer, the apartelle has no sheets available, so initially sheets must be purchased.

Lisa wants to develop a plan for purchasing and cleaning sheets by using linear programming. Help Lisa formulate a linear programming model for this problem and solve it by using the computer.

OPTIONAL: Some spreadsheet modeling needs to be done to see how to do this.

a. Visualize where you want to finish. What numbers are needed by Lisa? What are the decisions that need to be made? What is the objective?

b. Assume some values for new sheets purchased at the start of Weeks 1 and 2 and sent to the cleaner and to Lisa's friends at the end of Weeks 1 and 2. Calculate by hand the cost for the new sheets and cleaning. Also, calculate the number of sheets returned from the cleaner and Lisa's friends and when these would be available.

c. Formulate the above problem mathematically as a linear programming model. Define your decision variables clearly; define the notation you use. Using your decision variables, formulate the objective function and constraints.

d. Make a rough sketch of a spreadsheet model, with blocks laid out for the data cells, changing cells, output cells, and objective cell.

e. Build a spreadsheet model for Weeks 1 and 3, and then thoroughly test the model.

f. Expand the model to consider all weeks up to Week 8, and then solve it using Excel Solver

\begin{tabular}{|c|c|} \hline Week & Registered Training Participants \\ \hline \hline 1 & 58 \\ \hline 2 & 105 \\ \hline 3 & 125 \\ \hline 4 & 115 \\ \hline 5 & 130 \\ \hline 6 & 150 \\ \hline 7 & 125 \\ \hline 8 & 95 \\ \hline \end{tabular} \begin{tabular}{|c|c|} \hline Week & Registered Training Participants \\ \hline \hline 1 & 58 \\ \hline 2 & 105 \\ \hline 3 & 125 \\ \hline 4 & 115 \\ \hline 5 & 130 \\ \hline 6 & 150 \\ \hline 7 & 125 \\ \hline 8 & 95 \\ \hline \end{tabular}