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.
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.
You will find the solution in the attached Excel file. The basic idea is the following. At the beginning of each week, Mary must choose how many sheets she’ll purchase, send to the laundry, or send to her friends. Let’s call ‘x’ to the number of sheets that are purchased, ‘y’ to the number of sheets that are sent to the laundry and ‘z’ to the number of sheets that are sent to her friends. We’ll use a sub index to show which week we’re talking about: for example, [pic]is the number sheets purchased at the beginning of week 1, [pic]is the number of sheets sent to the laundry at the beginning of week 4, and so on.
We must minimize total cost choosing the variables we mentioned above. The total cost function will be: [pic]
Notice that I deliberately excluded [pic] and[pic]: these must be equal to zero, as we have no dirty sheets at the beginning of the camp (beginning of week 1) to send to clean.
Now let’s see the constraints. At the beginning of each week, the number of clean sheets must be equal to or greater than the number...
Please join StudyMode to read the full document