Solved

Calculate amount of ingredients depending on number of batches

Posted on 2011-09-03
20
781 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +3
20 Comments
 
LVL 60

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 60

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
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!

 
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 60

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
 
LVL 60

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 60

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 60

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

732 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