What if analysis for determining work start dates

[MrCameronExcel]

Hi,

Can I use what-if-analysis function to come up with work start dates?

I have 100 flats, and need to schedule start dates for them, but 90% of them are going into hotels for the duration of their work. I only have 10 hotels available. Their work will take 10 days.

These are the variables I am working with.

Is it possible to use what-if-analysis function to come up with work start dates?

Thanks
Cameron

 

[pjmorris]

In short I'd say that WIA would be just a part of the puzzle. It most certainly isn't going to be anything like your full answer. You have many more variables than you've identified, for example how many people can be accommodated in each hotel, do any residents have constraints that preclude certain hotels, what about holidays and weekends, when can work start, how do you want to manage the 10% who aren't in hotels, does your contractor have the resource to work constantly delivering 50 man-days a week, and i'm sure there's more.

I think I'd be looking at proper project management tools rather than Excel, though it absolutely could provide considerable help, but as I said What-if-analysis would possibly fulfill a tiny part of the solution.

HTH

 

[MrCameronExcel]

Thanks for your reply Peter.

I'm doing an exercise where every variable doesn't have to be perfect. Answers to your questions below.

How many people can be accommodated in each hotel? I will be assuming each hotel can take all the residents of a single property.
Do any residents have constraints that preclude certain hotels? I will be assuming no.
What about holidays and weekends? I want to exclude weekends and the week of Easter. This might mean using the WORKDAY formula as part of the solution.
When can work start? For this exercise I will just say 03/07/23 (although the start date is just another variable that should be able to be changed, and the rest of the information update accordingly).
How do you want to manage the 10% who aren't in hotels? Their works will proceed uninterrupted by any need for a hotel - can start anytime. Does your contractor have the resource to work constantly delivering 50 man-days a week? Yes.

I realise this is a big exercise.
I have been searching the web because I would have thought someone has done something similar before.

 

[pjmorris]

Given your assumptions its probably pretty straightforward:

Select your start date, add 10 days (use 'WORKDAY' function), repeat 9 times. The others can presumably be done in parallel so no difficulty.

I suspect the tutor wanted you to discover 'WORKDAY' and then see how you can also use it to include holidays.

Have fun.

 

[MrCameronExcel]

Given your assumptions its probably pretty straightforward:

Select your start date, add 10 days (use 'WORKDAY' function), repeat 9 times. The others can presumably be done in parallel so no difficulty.

I suspect the tutor wanted you to discover 'WORKDAY' and then see how you can also use it to include holidays.

Have fun.

Ahh yes, I see what you are saying. Can I change the work duration to 15 days then? This makes this significantly more challenging by forcing you to consider the hotel availability, and is what I am after.