I'm a restaurant manager with intermediate Excel skills. I'm trying to use Excel to find the optimal daily server floor charts with certain parameters: the tables need to be somewhat close together, each server should have a maximum of three tables and a minimum of two, and the number of customers at a table needs to be somewhat balanced. I put together a crude blueprint of the restaurant (first screenshot) that provides the table names and it shows the basic distance between tables. The second screenshot is in a different sheet with the table names replaced by the number of customers that can fit at the table. The third sheet is the daily roster with servers' names on there--in column B there are skill levels for each of them (ranging from 1 to 7), which could theoretically affect how much they can handle (I'd like someone with a higher skill level to get priority treatment when it comes to having three tables vs. two, for instance). Since the number of servers varies each day (and they have different skill levels), I'd like to be able to paste my roster into one sheet and then have a list of those servers appear in another sheet with the optimal section next to their names.
Basically, I don't know where to start this project. I've done a little bit of reading and I think I'll need to use Solver. But if anyone can point me in the direction of what I should start reading in order to accomplish this (if it's even possible!), I'd be really grateful.
Basically, I don't know where to start this project. I've done a little bit of reading and I think I'll need to use Solver. But if anyone can point me in the direction of what I should start reading in order to accomplish this (if it's even possible!), I'd be really grateful.