Using Excel, please help with the following:
Mary Kelly is a scholarship soccer player at State University. During the summer she works at a youth all-sports camp that several of the university’s coaches operate. The sports camp runs for eight weeks during July and August. Campers come for a one-week period, during which time they live in the State dormitories and use the State athletic field and facilities. At the end of the week a new group of kids comes in. Mary primarily serves as one of the camp soccer instructors. However, she has also been placed in charge of arranging for sheets for the beds the campers will sleep on in the dormitories. Mary has been instructed to develop a plan for purchasing and cleaning sheets each week of camp at the lowest possible cost.
Clean sheets are needed at the beginning of each week, and the campers use the sheets all week. At the end of the week, the campers strip their beds and place the sheets in large bins. Mary must arrange either to purchase new sheets or to clean old sheets. A set of new sheets costs $10. A local laundry has indicated that it will clean a set of sheets for $4. Also, a couple of Mary’s friends have asked her to let them clean some of the sheets. They have told her they will charge only $2 for each set of sheets they clean. However, while the laundry will provide cleaned sheets in a week, Mary’s friends can only deliver cleaned sheets in two 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 campers that have registered during each of the eight weeks the camp will operate. Based on discussions with camp administrators from previous summers and on some old camp records and receipts, Mary estimates that each week about 20% of the cleaned sheets that are returned will have to be discarded and replaced. The campers spill food and drinks on the sheets, and sometimes the stain will not come out during cleaning. Also, the campers occasionally tear the sheets or 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.
Week Registered Campers
1 115
2 210
3 250
4 230
5 260
6 300
7 250
8 190
At the beginning of the summer, the camp has no sheets available, so initially sheets must be purchased. Sheets are thrown away at the end of summer.
Mary’s major at State is management science, and she wants to develop a plan for purchasing and cleaning sheets using linear programming. Help Mary formulate a linear programming model for this problem and solve it using the computer.