Capstone Project 5/Classic Cookies To Go.

Capstone Project 5 Instructions 1 You have just become the new owner of a struggling bakery that only sold cookies and your final act before closing this business down is to complete any remaining standing orders and make as much profit as possible (no matter how much or little this could be) with the ingredients and fixed assets already on hand. You will not incur any additional expenses in this final act. In the pantry you have found the following items: ? sugar (cups) 100 ? butter (cups) 100 ? eggs 48 ? peanut butter (cups) 20 ? flour (cups) 100 ? baking powder (teaspoons) 179 ? baking soda (teaspoons) 48 ? vanilla extract (teaspoons) 275 ? rolled oats (cups) 75 ? honey (cups) 12 ? shortening (cups) 25 In the desk you have found a standing order for: ? orders of Honey Oatmeal Cookies (HO) ? orders of Flourless Peanut Butter Cookies (PB) ? orders of Becky’s Sugary Sugar Cookies (SS) In another drawer, you found this note: ? Honey Oatmeal Cookies (profit -- $4.75 per order) ? Flourless Peanut Butter Cookies (profit -- $2.50 per order) ? Becky’s Sugary Sugar Cookies (profit -- $5.06 per order) You also found in the desk the following Web address: http://allrecipes.com You have determined the web address contains the recipe that has been used for making the three types of cookies this bakery has been making for many years. You are to go to this website and search for the three types of cookies and copy down the ingredients used in making a batch of each type of cookie. In your research you have found there will only be one recipe found for each of the three types of cookies and the name of the recipe will match the name of the type of cookie recipe. Capstone Project 5 Classic Cookies To Go Capstone Project 5 Instructions 2 While you have been very successful in business you have no idea how to determine how many batches of each cookie you should make in order to maximize your profit. Fortunately your sister just graduated from Stevenson University and is a whiz with excel. When you described your dilemma to her, she “whipped” up a spreadsheet and e-mailed it to you and said once you have the specifics of the ingredients of each cookie type, “enter those numbers into the spreadsheet and solve for the maximum profit.” Unfortunately you did look at the spreadsheet your sister sent over for a couple of days and during that time she left the country on a business trip. After opening her e-mail you realized she forgot to enter the formulas and just e-mailed you a template and not a complete spreadsheet. So you were left to figure out how to “solve for the maximum profit” on your own. You are to complete the following: 1. Download and expand the compressed folder found in Module 12 named Capstone_Project_5_Student_Folder. 2. Open the Excel data file named Capstone_Project_5_Starter_File and save it as Classic_Cookies_To_Go_nn where nn are your first and last initials. 3. Read the definitions found in the worksheet labeled “Definitions & Color Key” and pay special attention to what the difference is between the colors blue, yellow, green and beige. 4. Enter your name in cell B2 of the Cookie Revenue worksheet. 5. Complete the Income Analysis section for Profit/Order, Profit and Total Revenue using proper formulas where applicable. Pay special attention on how you are going to figure out how to reference how many batches of cookies can be made based on the number of ingredients available. 6. In the Ingredients Inventory portion of the spreadsheet enter the amount of inventory found in the pantry. 7. Go to the website found in the desk and search for the ingredients for each of the three types of cookies. Remember, if you search correctly, there will only be one cookie recipe found for each cookie type. Write down the amount of ingredients required to make one (1) batch of each type of cookie and place these amounts in the Capstone Project 5 Classic Cookies To Go Capstone Project 5 Instructions 3 appropriate locations in the Ingredients Inventory columns for their respective cookie type. For partial ingredient amounts i.e. ¼, ½, ¾, use .25, .5 or .75. Do not worry about water if it is being used as one of the ingredients. If an ingredient is not used you can either enter a zero (0) or leave the cell empty BUT DO NOT ENTER A SPACE. 8. You now are to create the formula for cell L4 (Sugar) of the Total Ingredient column. The definition of Total Ingredients is the amount of sugar (in cups) used in the making of each cookie type. For example, if you used 2 cups of sugar to make (1) batch of Honey Oatmeal cookies, 1 cup of sugar to make (1) batch of Flourless Peanut Butter cookies and 3 cups of sugar to make (1) batch of Becky’s Sugary Sugar Cookies; you would need how many cups of sugar to make (1) batch of each type of cookie? To make 1 batch of each type of cookie you would need 2+1+3 cups of sugar or 6 cups of sugar. But if you were going to make (4) batches of Honey Oatmeal cookies, (2) batches of Flourless Peanut Butter cookies and (5) batches of Becky’s Sugary Sugar Cookies, you would need (4*2) + (2*1) + (5*3) or 8 + 2 + 15 or 25 cups of sugar. Therefore there are two keys to the Total Ingredients column you must take into account when creating this formula. First is you must include the number of batches produced and secondly you will need to use the absolute reference so you can copy your cell formula from cell L4 to cell L14. The question you must think about is what cells do you want to keep constant in this cell when you copy it from cell L4 to cell L14? Create the proper formula for cell L4 and copy it to cells L5 thru L14. 9. Now you will calculate the Balance in cell M4 which is how much of the specific ingredient you will have left after you have made your batches of cookies. You will copy this formula from cell M4 to cells M5 thru M14. 10. To test your formula do the following: a. Place the value of 1 in cells I15, J15 and K15. b. Compare your results to the PDF file titled “Classic_Cookies_To_Go_Student_Test.pdf”. c. Compare your results to the spreadsheet found in step 10.b; if your results do not match your formulas are not correct and you need to figure out which formula is incorrect and correct it. Capstone Project 5 Classic Cookies To Go Capstone Project 5 Instructions 4 d. Continue correcting your formula(s) until your results match. You have now completed the requirements portion of the spreadsheet and your next steps are to use the Excel function of Solver to "solve" for the maximum profit by increasing the number of cookie batches. In order to run the “What-If” analysis, you need to determine what you are trying to accomplish. So far you have created all the formulas required to calculate the total ingredients that would be used based on the number of batches made for a particular cookie. Additionally, you have determined how many of each of each type of ingredient will be used (i.e. the Balance column…Column M). On the Income Analysis side, you have determined how much profit will be realized for each batch of cookies sold and the Total Revenue you will receive. You are to now use Excel’s “What-If” analysis tool Solver to solve for the Maximum profit by changing how many batches of each cookie type is made based upon a number of constraints you must consider in order to allow Solver to work correctly. For example, the number of batches produced must be a whole number (integer) and cannot be less than zero, you cannot have more ingredients remaining compared to what you started with and again, the ingredients remaining cannot be less than zero. 11. Use Solver to calculate the maximum profit. In doing so, your solving method will be GRG NonLinear, all your constraints are properly entered and you are to keep your solver solution selecting the “Answer” report. 12. Move the “Answer Report 1” worksheet to the end of the active worksheets. 13. Save your workbook. 14. Save the workbook as Classic_Cookies_To_Go_nn.xlsx where nn are the initials of our first and last name and submit to Blackboard per the instructions of your instructor. Below are the definitions and the color key for Capstone Performance Exam 5 Income Analysis Table Batches: How many batches of that specific type of cookie should be made in order to generate the maximum profit Profit/Order: How much profit does that specific batch of cookie generate Profit: The profit generated for the total number of batches of that specific cookie type Total Revenue: The total profit generated from all the batches of cookies produced (maximum profit should end up as $148.08) Inventory Ingredients Table Ingredients: The various ingredients used to make each type of cookie Amount: How much of that specific ingredient is needed to make 1 batch of the specific cookie type Total Ing.: The total of the specific ingredient used in making all the batches of cookies produced Balance: The total of the specific ingredient that is remaining Total Cookie Batches: The number of batches for that specific cookie that will generagte the maximum profit Color Key: Blue means the cell is not to be used Yellow is where you place your name Green is the maximum profit being calculated (Your answer should be $148.08) Beige is the total number of batches in order to generate the maximum profit