Optimization of Weekly Productivity Goals

Question

I need to find optimum weekly targets for an 8-week period for a group of employees. The end result expected is maximum of sum of percentage of times the employees met the goal in each week. I am doing the "percentage met" calculation on a dataset containing past performance of employees on the tasks.

My decision variable is the weekly improvement percentage (weekly_share). This will reduce the existing goals by the said percentage in each week, starting from 10% in Week 1 and ending at 100% in Week 8.

For each week, percentage of meeting revised goal calculated using

weekly improvement= 100 x (count of Tasks Completed x 3600) / (Total work time in week x existing goal x weekly_share)

I wanted to maximize the sum of these percentages for all 08 weeks

I have set the following constraints: a) for week 1, weekly_share>= 10% b) for all weeks from 2-8, weekly_share >= weekly_share in previous week (week 2>= week 2, etc) c) weekly_share for 8th week = 100%

When using Excel Solver, I do not get a solution in Simplex LP (error in either objective or constraint cell). With GRG Nonlinear and Evolutionary, the weekly_share are set to the lower limit (since it is in the denominator, the lowest value gives the highest ratio).

How can I solve this?

Answer 1

Assuming weekly_share = x[d] where d is the day of the week you can add constraints: y[d] * x[d] = 1. Then replace weekly_share with y.

As for other constraints, solver will simply choose 10% for weeks 2-7. You are looking for cumulative, right, so last constraint would be?
So $\sum_{d=2}^8 x_d = 0.9$
The above constraint for y can be written as:
$y_2*(0.1+x_2) = 1$
$y_d * (\sum_{day=2}^dx_{day}) = 1$ for d = [3...8]
Bound constraint
$x_d E \{0.1,1\}$ for $d = \{2...8\}$

Objective:
Total = $(3600)[\frac{(NTasks)}{(0.1)*workTime_Wk[1]*Goal[1]}+\sum_{d=2}^8\frac{(NTasks)(y_d)}{workTime_Wk[d]*Goal[d]}]$ Basically am trying to reduce variable for week1, since you know it's 0.1

Answer 2

One possible way to achieve what you want is by modeling the problem as a Resource-Constrained project scheduling problem. In which the project plan is around $8$ weeks, and in each week you can define the desired tasks that should be completed by employees. In this case, there are many free tools, like excel based templates, that you can easily use to calculate the compilation percentage of tasks in each week and in the whole horizon.