Solved

Calculate amount of ingredients depending on number of batches

Posted on 2011-09-03
20
677 Views
Last Modified: 2012-06-27
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
Comment
Question by:samiam41
  • 7
  • 6
  • 2
  • +3
20 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36478591
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
 
LVL 9

Author Comment

by:samiam41
ID: 36478657
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36478733
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
 
LVL 9

Author Comment

by:samiam41
ID: 36479186
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 125 total points
ID: 36479245
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
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 125 total points
ID: 36480940
If that recipe makes one batch, it might he helpful to indicate how many cookies that one batch will make.
0
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 125 total points
ID: 36481223
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
 
LVL 9

Author Comment

by:samiam41
ID: 36498596
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
 
LVL 9

Author Comment

by:samiam41
ID: 36498605
@GRayL: It's crazy how great minds think-alike sometimes!  Great idea.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36498646
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36498648
"with folks that are not tech savvy" ... or at least want to spend their time baking and not fiddling with technology. :)
0
 
LVL 1

Assisted Solution

by:dubaday
dubaday earned 125 total points
ID: 36501426
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
 
LVL 9

Author Comment

by:samiam41
ID: 36596180
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
 
LVL 22

Expert Comment

by:Flyster
ID: 37057554
My recommendation would be to accept mwvisa1's solution with assists going to myself and dubaday.

Paul
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 37057942
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
 
LVL 9

Author Comment

by:samiam41
ID: 37138666
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
 
LVL 24

Expert Comment

by:broomee9
ID: 37169469
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
LVL 9

Author Comment

by:samiam41
ID: 37145986
Sounds good to me.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now