Chapter Review 1. 5-2 Units Problems - Chili
Refer to the attached Excel worksheets for Questions 1-10):
The Quarter is over and its time for the CIS 200 students to celebrate their success with a party. You are giving a party and serving “MR. T’s famous chili”. You have created an Excel workbook with three worksheets. The recipe is given on the sheet called “recipe”. On a Sheet called “units” are some unit equivalents you will need. On the third spreadsheet called “shopping” - you will create the shopping list you will need in order to make chili for 20 adults. Each recipe serves 10 adults. Sheet names appear below each table - when referring to cell addresses the sheetname!cell address format was used.(example- recipe!A1)
1. What is the formula to put in cell units!B7 to calculate the number of multiples of the recipe needed to feed all the adults at the party?
_ B6/B5.total#people/#people per recipe = # recipes
2. What is the formula to put in cell shopping!C2 to calculate the quantity of meat in pounds you will need to purchase for the party? Assume you will copying the formula into cell shopping!C3 to calculate the number of tomatoes that you will need to purchase.
units!B$7 * recipe!B2 #recipes *lbs/recipe =total lbs
3. What is the formula to put in cell shopping!C4 to calculate the ounces of chili powder you will need to purchase for the party?
units!B7 * recipe!B4/units!B4 #recipes * tbsp/recipe / tbsp/ounce
4. What is the formula to put in cell shopping!C5 to calculate the pounds of beans you will need to purchase for the party?
Units!B7 * recipe!B5/units!B3 #recipes * cups/recipe / cups/pound
5. Write a formula to put in shopping!F2 to calculate the total cost of the meat? Assume you will copy this formula into F3:F5.
C2* E2
6. Write a formula to put in shopping!F6 to calculate the total cost of your shopping list? Assume you have copied down the formula in question 5 into F3:F5.
= Sum (f2:F5)
7. Cell shopping!G2 is formatted as a percent. Write a formula to put into shopping!G2 to calculate what is the meat percentage of total cost. Assume you need to copy this formula into G3,G4 and G5.
.= F2/F$6
8. What is “goal seek” and how can it be used in this problem?
Tool that can take a given outcome and tell you a new value for one of its component variables in order to get that outcome.
9. A friend has told you about a local deli that sells two types of prepared chili in tubs (regular and extra meat). Each tub feeds approximately 4 people. The regular chili from the deli costs $12.95 per tub, the extra meat chili costs $15.95 per tub. You want to decide between cooking your own chili and these prepared chilis based on the following criteria:
· If the prepared extra meat chili costs less than cooking - buy it. (BUY – Extra Meat)
· If the extra meat chili is more expensive than your cooked chili but the regular meat chili is cheaper than your cooked chili buy the regular chili (Buy – Regular).
· If both of these prepared chilis are more expensive than your cooking then cook it (COOK)
Write an Excel formula in cell Shopping!D8 which will determine whether you will Buy-Extra Meat, Buy-Regular or Cook the Chili.
=if( 15.95/4<F6/units!B6, “BUY- extra meat”, if(12.95/4< F6/ units!B6, “Buy – regular”, “cook”))
also
=if( units!B6/4 * 15.95<F6, “BUY- extra meat”, if(units!B6/4* 12,95 < F6/20, “Buy – regular”, “cook”))
10. If you cook the Chili you will have the ingredients delivered. The Acme delivery service will pickup these ingredients from both the butcher and the grocery and deliver it at the same time. They charge 10% of the total value of the purchases, or a minimum delivery fee of $4 whichever is higher. Write a formula in cell B8 to calculate the total cost of all the ingredients including delivery charges.
=if( F6*.1< 4, 4+F6, F6*1.1) OR =f6+max(f6*.1,4)
Chili Problem:
A / B / C1 / ingredient / Quantity / unit
2 / meat / 3 / pounds
3 / tomatoes / 2 / each
4 / chili powder / 1 / tablespoon
5 / beans / 1 / cup
SHEET NAME: recipe
A / B1 / Unit / conversion
2 / ounces per pound / 16
3 / cups of beans per pound / 4
4 / tablespoon chili per ounce / 3
5 / adults served per recipe / 10
6 / total adults attending party / 20
7 / number of multiples of recipe / B7
SHEET NAME: units
A / B / C / D / E / F / G / H1 / store / ingredient / Quantity / unit / $/unit / total cost / % of total / ingredient
2 / Butcher / meat / C2 / pounds / $4.00 / F2 / G2 / meat
3 / Grocery / tomatoes / C3 / each / $0.50 / F3 / G3 / tomatoes
4 / Butcher / chili powder / C4 / ounces / $2.00 / F4 / G4 / chili powder
5 / Grocery / beans / C5 / pounds / $1.00 / F5 / G5 / beans
6 / F6
7
8 / Total w/delivery / B8 / Buy or Cook / D8
SHEET NAME: shopping
hw4a.doc