A variation on the Celebrity Chef Spreadsheet Challenge. Inspired by Alan Murray PowerQuery Challenge.
11/01/2020
Part 1
This restaurant can have up to 100 diners. The waiters write up the orders on an Excel spreadsheet at the desk of the Maitre D (the manager’s spreadsheet) in this layout.
But the kitchen would like it in this layout.
How will you transform the data from one to the other?
Columns A to C are the Starters, D to F the Main, G to I the Dessert.
Part 2
The customers often change their order. When they do, the waiters update the manager’s spreadsheet.
How do you update the kitchen’s spreadsheet?
Your solution will need to take into account that the kitchen has marked off those dishes they have begun to make, or finished making. They will have done this on the kitchen spreadsheet. So, your update to the kitchen should retain any flags (Cell Comments? Cell background fill) they have put on their spreadsheet already. (ie. not overwrite it)
Part 3
The manager thinks it gets a bit chaotic when customers change their minds. Often the kitchen has already started to make the dish. Customers should not be allowed to change their order after the kitchen has started to make the dish.
How will your solution tell the waiters (and/or the diners) that it’s too late to change their order?
Part 4
Now the manager really wants to use the power of technology to make his restaurant work more efficiently. And to rival his competitors by being up with the tech!
He wants the diners to be able to order the dishes themselves. He has made it a requirement that all customers must bring their own laptop, with Excel on it, and serve themselves. The restaurant has WiFi and internet connection.
How will you set this up?
Part 5
Now the manager wants to do away with the paper menus. After all, there are only three choices of each course to choose from.
So, he intends to email the menu to the customers. But, that would need getting their email addresses and sending emails. He would like to avoid that.
Can you suggest a better way?
Part 6
So far, the menu is three choices for each course. From time to time there will be more or less than three choices for each course. Could be 2, could be 4 or more.
How would you modify your solution to accommodate this flexibility?
Part 7
Now for something spectacular.
The manager has been on a business course and has just learnt about Just-In-Time (JUT). He now wants to reduce the stocks of ingredients he holds, and has come up with the following ingenious idea to achieve that.
From now on, no stocks will be held.
All the ingredients will be ordered and instantly received from the grocery store in zero-time. (How that’s physically done is not our concern) but his is what we need to do.
We have been given a list of ingredients, per person, for each dish on an Excel spreadsheet.
When a dish is about to be started to be made, our spreadsheet is required to produce a shopping list of the ingredients that are required for it. We will send it to the grocery store (you have to decide how) and the courier service will delivery it (even quicker than getting it from the store!).
So, your spreadsheet solution needs to produce a shopping list, when a dish is about to be started. And it needs to be conveyed to the grocery store in some way (you to decide how). This needs to happen JIT because (remember?) the customers are allowed to change their minds.
(And, now that he has made his restaurant so efficient he is thinking of extending his restaurant to seat 200 … when that happens he will double your pay. Should you encourage him?)
There are a lot of requirements here. But here’s the last requirement.
Your solution must be SIMPLE 😊
TBA – sample Excel workbook to work with.
Add comment