Need to make a Chart

Dear,

Please help me to make charts on below mentioned scenarios,


1. Company has 37 Trucks, but 5 trucks always down in the workshop. So we are left with only 32 Trucks.
We have 14 drivers from Sub-Contractor continuously.
Then how many drivers are required from company to cover 32 trucks, if:

Their rotation is 20 days/10 days and 28 days AL to be taken along with field break. They can even split their AL twice (14+14) along with their field break
or
Their rotation is 06 days/03 days and 28 days AL to be taken along with field break. They can even split their AL twice (14+14) along with their field break
or
Their rotation is 36 days/24 days and NO AL.
or
50% of them want 20 days/10 days and 50% of them want 06 days/03 days with 28 days AL to be taken along with field break. They can even split their AL twice (14+14) along with their field break
or
50% of them want to take 06 days/03 days PLUS 28 days AL to be taken along with field break and AL can be split twice, and 50% of them want 36 days/24 days with AL.

Please prepare a spread sheet for 1 year with different options.
Daily 32 drivers must be available to run the trucks, need to prepare a chart including the subcontractor drivers.

· Rotation of Drivers : Run a spread sheet for 1 year for different scenarios and show in graphs their “Daily Availability” on duty in different sheets.

Dear Dilipandey,

Its a work given to one of my colleague as a test, he asked as a help, but I couldn't make it . Please see the attached excel file

Sandz