Good Evening,
I’m looking to have a calculator made for my restaurant group which functions on Google Drive. I don’t know if it should be Google Sheets or Google Forms or a combination of the two. But let me describe the purpose and functionality.
I have a burger brand with 5 locations (and adding more). My goal is to have a calculator which helps my managers understand how much bread they need to order.
Traditionally, in the restaurant inventory, we create something called a “Par” - it’s a minimum inventory level. For example, let’s say you need 10 boxes of sugar. Each day you take inventory, input the sugar you have (let’s say you had 4 boxes remaining) and it wills you how much to order (in this case the number would be 6).
The problem with the Par system is that it’s not intelligent. If your sales increase or decline, you must manually modify the minimum inventory level.
What I would like to do instead, is to have my managers input the number of burgers sold each night (in the case we use 1 piece of bread per burger) and how many pieces of bread they wasted. This must be done per-location, and we need the ability to add locations on in the future. The manager will also have to input how many unopened cases of bread they have at the end of each day (there are 84 pieces of bread per case).
Upon submitting these numbers, the system will tell the manager if the need to reorder or not, and will tell them how many cases they should reorder.
The way that this decision should be calculated is because we want to have a minimum of 2 days worth of bread at any given moment, and a maximum of 5 days worth of bread. So, if we have less than 2 days worth, a re-order prompt is triggered.
However, I don’t want the system to use an average. This is important because there are occasionally days when the restaurant is closed. So adding a 0 into the average would lower the average and possibly put us in a place where we run out of bread.
Instead, I would like the system to look at the highest bread usage of the last three like day of the week (meaning the last three Mondays, for example), and select the highest volume day of the three as the benchmark.
For example, as the manager submits the sales and current inventory on Monday, the system would do the following:
First, the system must calculate how much bread that location will need need at a minimum for the next 2 days, as well as how much it predicts it will need for 5 days. This inventory amount would be dynamic. And it would be based upon historicals.
So, if it’s Monday, we need to look at the last 3 Tuesdays and the last 3 Wednesdays, and then add together the highest grossing day of each set.
Let’s say the following three Wednesdays sold:
Wednesday 1: 450 pieces
Wednesday 2: 500 pieces
Wednesday 3: 159 pieces
In this example, we would use 500, as it’s the highest of the three previous Wednesdays. The same principal would be applied to Thursday.
Then these two numbers are added together and compared to the current inventory. If the inventory is greater, they will not need to place an order. If the current inventory is less, then the system should tell them to place an order which is the prediction of usage for the next 5 days.
This prediction of usage will be done the same way that we calculated two day of usage. We then will subtract the current inventory, divide by cases (remember 84 pieces of bread per case) and round up to the nearest case.
For the manager, the work flow should be very simple. They should visit the site, select their location, simply enter number of burgers sold, bread wasted, and unopened cases of bread and hit submit. The system will tell them to either re-order (and if so, how many cases), or not.
I understand this was a lot to read. And I appreciate you taking the time. Please let me know if this is of interest to you, how would plan on executing it, the budget and timeline.
Thank you,
Dan