2
$\begingroup$

I have 3 teams within a group with each team working on different tasks assigned. I was given data of how each team performed in terms of the number of task completed each month and the average days taken to complete these task (cells O3,O5,O6). From this data, an overall group performance was generated, again based on the total tasks completed by the group and the total days used to arrive at an average days per task to complete (Cells N10).

We were given an improvement target to reduce the overall group average days per task by 30% in H1 of 2021 and then a further 30% by H2 based on the performance from last year. So a 30% reduction will result in a new target in cell "N11". My challenge is each team within the group works on different type of task with different degree of difficulty so i need to translate this new target (Cell N11) into 3 different new average days to complete for each different team.

Is there a way to use excel solver to help to calculate what will be the new targets of average days to complete task for each team in order to achieve an overall goal for the group? Average days to complete task

$\endgroup$
3
  • $\begingroup$ I suggest to consider dual formulation and lagrangian multipliers (shadow prices) in order to evaluate variation in optimal solution by means of a variation in constraint. $\endgroup$ Jan 17 at 7:49
  • $\begingroup$ @Aik Koon Wee, welcome to OR.SE. If you are interested to use excel solver, you should define your problem as the mathematical formulation variant (e.g. LP, MIP, NLP, etc) then you would be able to solve it by a standard solver. The problem you mentioned is more about a trade-off between the working days and performing a task. One possible way is to use the goal-seek option in excel. $\endgroup$
    – A.Omidi
    Jan 17 at 8:27
  • $\begingroup$ First, do you know what the work volumes (# of tasks) will be for the three teams next year? Will it be about the same? Second, do you know what degree of acceleration is possible for each group? Third, the trivial answer is to tell all three groups to cut 30% off their time per task. Is that not reasonable? $\endgroup$
    – prubin
    Jan 17 at 23:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.