Question

CSL is a chain of computer service stores. The number of hours of skilled repair time that CSL requires during the next 5 months is as follows:

Month 1 (January): 6000 hours
Month 2 (February): 7000 hours
Month 3 (March): 8000 hours
Month 4 (April): 9500 hours
Month 5 (May): 11000 hours
At the beginning of January, 50 skilled technicians work for CSL. Each skilled technician can work up to 160 hours per month. To meet future demands, new technicians must be trained. It takes 1 month to train a new technician. During the month of training, a trainee must be supervised for 50 hours by an experienced technician. Each experienced technician is paid $2000 a month (even if he or she does not work the full 160 hours). During the month of training, a trainee is paid $1,000 a month. At the end of each month, 5% of CSL's experienced techicians quit to join Plum Computers. Solve (minimize the labor cost incurred in meeting the service requirements for the next 5 months) this linear program problem in Excel using solver.

Answer 1

To solve this linear program problem in Excel using Solver, we need to minimize the labor cost incurred in meeting the service requirements for the next 5 months. here's a step-by-step approach to working through the problem and using Solver.

Step-by-step explanation:

To solve this linear program problem in Excel using Solver we need to minimize the labor cost incurred in meeting the service requirements for the next 5 months.

Here's how we can approach this:

1. Create a table with the months (January to May) and the number of hours required for skilled repair time in each month.
2. Calculate the total number of hours required for skilled repair time by summing the values in each month.
3. Create another table to track the number of technicians needed for each month. Initially, we have 50 skilled technicians available in January and each technician can work up to 160 hours per month.
4. Calculate the number of new technicians needed by subtracting the available skilled technician hours from the required skilled repair time for each month.
5. Calculate the number of experienced technicians needed to train the new technicians by dividing the number of new technicians by 20 (since each experienced technician can train 4 new technicians in a month).
6. Calculate the total labor cost for each month by multiplying the number of experienced technicians by $2000 and adding the cost of training the new technicians.
7. Add a column to track the number of technicians available at the beginning of each month by subtracting the number of technicians who quit (5% of experienced technicians) from the previous month's available technicians.
8. Add a column to track the cumulative labor cost for each month by summing the labor costs of the previous months.

Finally, you can use Solver in Excel to minimize the cumulative labor cost by changing the number of new technicians trained each month (decision variable) under the constraint that the number of technicians at the beginning of each month should be non-negative.