Modelling optimal number of office desks with flexible working policies

[michalklofac]

Hi everyone,

I have a challenge which is perhaps more about statistics than Alteryx per se, but since I'd like to solve it using Alteryx I'm turning to the Community for help.

A company is moving into a new office and needs to decide on the optimal number of desks. They have about 180 employees, but they know not all of them will be at the office every day in the post-covid reality.

What would be the best way in Alteryx to build a model calculating the optimal number of desks, with corresponding confidence intervals (e.g. if the number of desks is sufficient on 95% of days, the company can rent extra meeting rooms on the remaining 5% of days, however if they would have to be renting extra rooms on e.g. 20% of days it would be better to just get more desks from the beginning)?

For input assumptions, I consider two scenarios:

- Simple scenario: assuming each employee spends the same number of days at the office (e.g. 3) and the days are entirely random for each employee

- Advanced scenario: assuming the company runs a survey and finds out for each employee: (1) how many days on average will they spend at the office, (2) whether some days are more or less likely (i.e. for each weekday, whether they will "never be at the office", "always be at the office", "sometimes be at the office")

I saw https://community.alteryx.com/t5/Engine-Works/COVID-19-Return-to-Work-Scheduling-with-Alteryx-MBA-Pr... , which is cool but a little high-level, not showing the actual modelling part.

Many thanks to everyone for thinking along and/or tips!

Cheers,

Michal

[BenMoss]

Hi!

This is a great question, I think the 'number of desks' can be solved with a bit of maths (though I could be wrong).

If you want all employees to be in the office 3 days a week, and you have 180 employees then you need each employee to be in the office 60% of the time.

60% of 180 is 108, this (I believe!) is the minimum number of desks that will allow you to achieve this level of attendance.

Creating a 'timetable' is the interesting piece here and I'm just working on something that I can share with you later in the day using the optimization tool to do this.

Ben

[michalklofac]

Thanks Ben! I agree 108 would be the minimum, and did some additional thinking around the 'confidence intervals'

I think for the 'simple' scenario, the confidence interval can be solved using simple statistics, where the 95% confidence interval is the mean (108) plus 1.98 * standard deviation (6.6), so about 121 desks. This should be sufficient on 95% of the days. Of course the issue is that it assumes every employee's probability of showing up on a given day is random at 60% (when in fact some days may be more or less popular).

For the 'advanced' scenario, I think once we have the employee responses, we could either pick the day which seems the busiest, or do the calculation for each workday. We'd calculate each person' probability of showing up on that day - 1 for those who said "I'll always work that day", 0 for "never on that day", and for all the other ones who said "sometimes" calculate it based on their other responses (e.g. if we're looking at Wednesday, and an employee said they'd go to the office 3 days a week, always on Monday and never Friday, that means for Wednesday there's a 2 in 3 probability for this specific person). Based on this we'd get the mean (the 108 from the 'simple' scenario), and could use the combined individual probabilities to also calculate the 95% margin of error (although I'm not entirely sure how that calculation would work).

If you manage to work something out with the Optimization tool, please let me know! In the meantime I'm trying to brush up my statistics knowledge for the calculations 🙂

Michal

[BenMoss · Accepted solution]

@michalklofac I've taken this on a bit further and have attached a workflow.

Based on the '180 employees, 3 days a week, 108 desks' we can build an optimization model which provides a timetable for when employees can come into the office.

You could use this timetable to line people up based on their given availability in the forms, or alternatively, you could adjust the workflow so that it has true employee availability (rather than the current view which is all employees can come in at any time).

If the workflow errors 'no solution found' once you add in your employee avaibility, that is indicating that with the availability specified by your users, 108 desks simply won't work, so you could bump this figure up until a solution is identified, perhaps you could start with your 121 desk figure. To do this you would change the formula given in tool 29, where the 'RHS' specifies the capacity.

I hope this is somewhat useful, i'll probably blog this at some point in the next week as it's certainly an interesting usecase!

Ben

[michalklofac]

@BenMoss thanks for sharing, this is great! I haven't really worked with the Optimization tool much in the past, so this was very instructive, and definitely helpful for the actual challenge.

As a matter of curiosity, how far can the Optimization tool be stretched in terms of more complex constraints? E.g. if we wanted several people (a certain team) to be at the office all together for at least one day in the week, would it be possible to include that in the analysis?

I can imagine you could always do a workaround, e.g. specifying upfront that everyone from Finance must be at the office on day1, and only letting the Optimization optimize the other variables, but am wondering if there is a more dynamic approach (e.g. if you have multiple teams who each want to have their members at the office together, but don't care if it's the same day as the other teams or not - so you'd actually want the Optimization to consider different combinations, together with all the other constraints).

Michal

[FBradbury_dup_106]

I believe the Alteryx optimization tool leverages linear programming logic such as PuLP, in Python; see Optimization Modelling in Python: SciPy, PuLP, and Pyomo | by Igor Shvab | Analytics Vidhya | Medium

[BenMoss]

No problem at all, I've written a blog on it now and it's with Alteryx to publish, so look out for that in the near future. In the blog I've used a usecase whereby employees specify the minumum and maximum number of days they would like to be in the office each week, so that complicates the example I shared above a bit further.

Good question, as you say, I think if you specify a specific day then it's quite simple as you could just inform the model which employees belong to which team, and then add a constraint for a specific weekday which indicates that each members of that team must be in; I'm trying to rattle my brain on how we could adjust the data structure to essentially do an 'or' constraint, I'm sure it's possible but not on a Friday afternoon brain!

Regards optimization, it can definitely get way complex, this is a simple linear example, but the tool also supports mixed interger and quadratic optimization too. The tool itself uses R under the hood, and makes reference to a package called 'ROI', linked below..

https://cran.r-project.org/web/packages/ROI/index.html

Ben