Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 922
  • Last Modified:

Calculate amount of ingredients depending on number of batches

Hey EE.  Here is an interesting little project I am working on and decided I needed to involve the most intelligent problem solvers on earth.  A local "mom and pop" bakery asked if I could help them figure out how many ingredients they need when they bake different sizes of batches.  Right now they get a piece of paper out and figure out how much flour, eggs, sugar, etc., they need to make various batches of cookies.  This has caused them to order too little or too much ingredients since they may calculate teaspoons, tablespoons, cups, etc., wrong.  So, here is my question:

I'm not sure if Excel or Access would work best here.  I'm thinking that it would be easiest for them if they had a simple GUI (access front-end?) that they enter in how many batches they want of cookie X.  Then the software would calculate how much of each ingredient they would need.  So, how do you calculate tea-spoons, tablespoons, cups and ounces/pounds for wet and dry ingredients?  I have the list of ingredients for one of their cookie types to work with.

Thanks in advance for your help.  I am open to your suggestions but the easier we can make it for these two older bakers and business owners, the better.
0
samiam41
Asked:
samiam41
  • 7
  • 6
  • 2
  • +3
4 Solutions
 
Kevin CrossChief Technology OfficerCommented:
samiam41,

Both should work. Excel might be easier for them to maintain as ingredients per batch changes, but if there is a large amount of variation in ingredients per batch based on type of cookie, having a database and simplified interface may be worth the trouble. Let's start with the ingredients and what you know of variation and go from there.

Kevin
0
 
samiam41Author Commented:
Thanks for the quick response.  There are seven different cookies that they cook.  I will create a spreadsheet with the ingredients and measurements for a single batch.  From that, you can tell me whether I need to use Excel or Access.  Thanks again.
0
 
Kevin CrossChief Technology OfficerCommented:
Maybe start with an Access database. You can always front-end it using Excel. Here is an example of a normalized design. You can enter your data into something like this:

Measurements => Teaspoon, Tablespoon, etc.
Ingredients => Egg, Sugar, etc.
BakedItems => Sugar Cookie, etc.
BatchRecipes => (Sugar Cookie | Sugar | Cup), 1 -- where you have a combination of baked item, ingredient, unit of measurement, and finally quantity per batch.

Hope that helps!
BakeryDBEx.accdb
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
samiam41Author Commented:
Ok, so truth be told, I've never developed an Access database before.  Excel spreadsheet or workbook?  Many times but not an Access database.  How should I enter in the ingredients?  Here is one for a chocolate cookie (not the entire recipe mind you as I will fill that in later).

2 1/4 cups all-purpose flour
1 teaspoon baking soda
1 teaspoon salt
1 cup (2 sticks) butter, softened
3/4 cup granulated sugar
3/4 cup packed brown sugar
1 teaspoon vanilla extract
2 large eggs

Now there is the list.  How do I put that into a front-end that allows them to pick the cookie (obviously the only cookie at the moment) and enter in the number of batches they want which will calculate how many cups or pounds they need (depending on the volume)?

Thanks for your help!
0
 
Kevin CrossChief Technology OfficerCommented:
From what I understood, the front-end user interface will require only quantity (number of batches) and selection of a recipe from a drop-down list. Therefore, I started with the tables. You can develop forms to manage the back-end data too, but not absolutely necessary if once loaded those won't change much.

I have input the sample ingredients above into my example structure. You can then create a form OR use Excel if you are more comfortable there. Enter a recipe like "Chocolate Cookie" and then pass that to a query against database to get the quantity per ingredient in the BatchRecipes table. Once you get back those values, you can use formulas in Excel to multiply the quantity per by number of batches entered by user.

Simple examples attached.

BakeryDBEx.accdb
BakeryDBEx.xlsx
0
 
GRayLCommented:
If that recipe makes one batch, it might he helpful to indicate how many cookies that one batch will make.
0
 
FlysterCommented:
Here's a simple spreadsheet that will calculate the ingredients needed for multiple batches. Just change the number of batches you want to make. The spreadsheet is protected to keep focus in the # of batches cell. There's no password so feel free to edit as needed.

Flyster
Recipes.xlsx
0
 
samiam41Author Commented:
Thanks for all of the suggestions.  I am currently trying to work through all of the suggestions to find the one that will be easy to implement, hand-off to these people and offer support going forward.  I appreciate your patience while I try these options out.  
0
 
samiam41Author Commented:
@GRayL: It's crazy how great minds think-alike sometimes!  Great idea.
0
 
Kevin CrossChief Technology OfficerCommented:
The spreadsheet looked promising and probably can be quickly modified to add a per batch number of cookies on the left that is extended by the number of batches on the right like the ingredients. You can have one tab per recipe or a different spreadsheet. Either way, it is easy to maintain versus getting into complexities of a database with folks that are not tech savvy.
0
 
Kevin CrossChief Technology OfficerCommented:
"with folks that are not tech savvy" ... or at least want to spend their time baking and not fiddling with technology. :)
0
 
dubadayCommented:
Hi

See attached file, and if this is what you are looking for...
Just enter the number of the recipe, and the qty batched required in the highlighted cells.

Regards
Dawie Ingredient-Calculator.xlsx
0
 
samiam41Author Commented:
Hey everyone.   Had to put this on the side as we had a problem with our back-up servers and tape drives.  I hope to get back to this question mid-week.  Thanks for your help and time with this.  
0
 
FlysterCommented:
My recommendation would be to accept mwvisa1's solution with assists going to myself and dubaday.

Paul
0
 
Kevin CrossChief Technology OfficerCommented:
Thanks, Paul! I was going to post that I thought everyone provided viable solutions, so I am good with a three way split.

http:#36479245
http:#36481223
http:#36501426

I would even include GrayL's comment:

http:#36480940
0
 
samiam41Author Commented:
Hey everyone.  If it isn't too late, I would like to award points and close out the question.  I'm just getting back from an unexpected absense.  My apologies to all.
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
samiam41Author Commented:
Sounds good to me.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 6
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now