Macro to calculate budget required to achieve productivity target

[y3kesprit]

Hi everyone,

I run a factory team of 20 members and I am trying to put together a sheet that can calculate the budget required for each employee to hit a certain productive target. We do this exercise twice a month (mid and end of month)

I have uploaded the initial draft of the template I have created here which I will making reference to the rest of this thread.

Because I run this exercise twice a month, I have the same table laid out side by side (Column E to N is for Period 1, while O onwards is for Period 2).

How I am envisioning is that the macro can run upon the user clicking either Cell E2 or E3 depending on Period 1 or 2. Upon user clicking either button, the macro will run and calculate the hours required to allocate to each employee in order to reach the overall target of 73% (calculated in Cell C6).

There are a few rules the macro needs to account for when calculating this
1. Every employee has a different cost / hour (Column C). The macro should start from the employee with the lowest cost.
2. The macro should calculate the budget required to bump the employee with the lowest LCR (i.e. Larry) up to 100% (Column L) before moving on the 2nd lowest cost / hour employee (i.e. Amy), and so on.
3. Each employee's maximum hours that can be allocated is determined in Column I, which is essentially the difference between the maximum working days in that period and his / her actual productive hours.

The outcome I am trying to achieve, is for the macro to be able to calculate:
1) the total budget (based on each employee's cost / hour) required to achieve the 73% profitability target
2) the method of allocating this budget to each employee to achieve this 73% using the calculated budget

Appreciate if the gurus here can point me in the right direction due to my limited knowledge in VBA. And apologies for the long post...

Thanks a ton!

 

[sergioMabres]

The file is not public: You need permission

 

[y3kesprit]

Hi Sergio,

Thanks for getting back. I have uploaded the Excel file here.

What I am hoping to achieve is when user:
1. clicks cell F2 (for period 1) or F3 (for period 2) (below cells reference will be for period 1 only, but ideally this should replicate for period 2 also)
2. the macro will populate Column M with the maximum hours (listed in Column L) until the productivity hits 73% as per Cell E2.
3. the order of filling this maximum productive hours should begin with the employee having the lowest cost / hour (per Column E)
4. in the case of my template, it will start with Kayla > Wright > Jonas > Polly ... and ending with Amit (although it shouldn't reach the employees with the higher cost / hour range with the 73% target in mind)
5. In the case where two employees have same cost / hour, then we can go by order where the employee listed higher in the list gets the allocation
6. In addition, it is likely that the final employee need not receive the maximum hours to achieve the 73%, likely a portion of this maximum hours instead. In this case, it will be awesome if the macro can compute the required part thereof hours to reach specifically 73% for the final employee in the calculation threshold.
7. Also I am hoping this calculation can run for the two teams listed in Column F separately. i.e I can filter for Team 1 and the macro will run only for employees listed as Team 1, and same for Team 2.

Sorry for the long list of request, and thanks in advance again for your guidance!

 

[y3kesprit]

Anyone..? Would appreciate any help! Thanks.

 

[y3kesprit]

Bump for guidance..