# Finding average days target for teams to achieve an overall group target using excel solver?

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?

• 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. Jan 17 at 7:49
• @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. Jan 17 at 8:27
• 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? Jan 17 at 23:19