Can you please help me in finding the optimal mix using What-If and Solver features. If you could provide excel formulas that would be great. Thank you!
Project 1 - The Broadway Cafe You have recently inherited your grandfather's coffee shop, The Broadway Cafe, which is conveniently located in downtown Jacksonville Florida. The cafe offers many different kinds of specialized coffees, teas, a full service bakery, and homemade sandwiches, soups, and salads. Your grandfather first opened The Broadway Cafe in 1952 and it was a local hotspot for many years. Unfortunately, business has been steadily declining over the last five years. Although your grandfather was an expert at running the coffee shop, it is outdated. There are no computers in the store and all ordering takes place manually. Your grandfather had a terrific memory and knew all of his customers by name, but unfortunately. none of this information is located anywhere in the store. The family recipes for the baked goods and soups are also stored in your grandfather's memory. Inventory is tracked in a note pad, along with employee payroll, and marketing coupons. The Cafe does not have a Web site, uses very little marketing except word-of-mouth, and essentially still operates the same as it did in 1952. Throughout this course you will own and operate The Broadway Cafe is taking advantage of business practices discussed in this text to increase profits, keep the business running, and bring the cafe into the 21 st century. You will perform a vast array of activities including: - Use technology to gain a competitive advantage - Build an e-business strategy - Create an online community for collaboration - Deploy a wireless network for customers - Use CRM to implement marketing campaigns and sales strategies - Deal with different ethical and information security dilemmas - Use Excel to perform business intelligence projects - Project management activities for implementing solutions - Understand how technology is used throughout a business by all of the different functional areas Business Dilemma The Broadway Cafe has been in business since 1952 and has never had a single competitor in the neighborhood. One of your employees has heard a rumor that Starbucks might be opening a store a few blocks away. Your staff is worried and is looking to you to provide reassurance that the competition will not affect your DETERMINE THE OPTIMAL MIX Brazil Beans, a wholesale distributor of coffee beans, buys overstocked beans and sells them for a discount of over 50 percent to small and privately owned coffee shops. Each month Brazil Beans has a variety of beans available for sale and you Brazil Beans has a few constraints when placing orders including: 1. Each box ordered must contain 12 bags of coffee or less 2. Each box ordered must contain 3 different types of beans 3. Each box ordered must contain between 0 and 5 full bags of each different bean type 4. Each box ordered must weigh equal to or less than 150 ounces NOTE: - Please use the constraints from the PDF file and not from the Excel file (especially \#1 and \#3) - do not use 20 bags and between 4 and 8 bags Suggested Steps: You use the attached spreadsheets that I supplied with the project. 1. Complete the project 1 tutorial first. Each part of the tutorial should be on a separate worksheet and a separate worksheet used for the Optimal Mix. All parts will be in the same workbook. 2. For the Optimal Mix worksheet, Setup your formulas correctly to get the totals across and down each column 3. What are you changing cells? 4. What cell is where you max you profit? 5. Now you are ready to enter the constraints. Do them one at a time so you can see the effect that each one has and if you have a problem with it. Then you know what to work on. HINT: How to handle this constraint: - Each box ordered must contain 3 different types of beans In general,....one way to do maximize profit on the project using solver that has this constraint is to.... You will need to add a countlf function that checks the column and counts if it is over Focus 4 The Broadway Caf Optimal Mix (Submit Excel Files using What-If and Solver features to find the solution) (Summarize and explain findings in your Report. Include both numerical and graphical form). Project 1 - The Broadway Cafe You have recently inherited your grandfather's coffee shop, The Broadway Cafe, which is conveniently located in downtown Jacksonville Florida. The cafe offers many different kinds of specialized coffees, teas, a full service bakery, and homemade sandwiches, soups, and salads. Your grandfather first opened The Broadway Cafe in 1952 and it was a local hotspot for many years. Unfortunately, business has been steadily declining over the last five years. Although your grandfather was an expert at running the coffee shop, it is outdated. There are no computers in the store and all ordering takes place manually. Your grandfather had a terrific memory and knew all of his customers by name, but unfortunately. none of this information is located anywhere in the store. The family recipes for the baked goods and soups are also stored in your grandfather's memory. Inventory is tracked in a note pad, along with employee payroll, and marketing coupons. The Cafe does not have a Web site, uses very little marketing except word-of-mouth, and essentially still operates the same as it did in 1952. Throughout this course you will own and operate The Broadway Cafe is taking advantage of business practices discussed in this text to increase profits, keep the business running, and bring the cafe into the 21 st century. You will perform a vast array of activities including: - Use technology to gain a competitive advantage - Build an e-business strategy - Create an online community for collaboration - Deploy a wireless network for customers - Use CRM to implement marketing campaigns and sales strategies - Deal with different ethical and information security dilemmas - Use Excel to perform business intelligence projects - Project management activities for implementing solutions - Understand how technology is used throughout a business by all of the different functional areas Business Dilemma The Broadway Cafe has been in business since 1952 and has never had a single competitor in the neighborhood. One of your employees has heard a rumor that Starbucks might be opening a store a few blocks away. Your staff is worried and is looking to you to provide reassurance that the competition will not affect your DETERMINE THE OPTIMAL MIX Brazil Beans, a wholesale distributor of coffee beans, buys overstocked beans and sells them for a discount of over 50 percent to small and privately owned coffee shops. Each month Brazil Beans has a variety of beans available for sale and you Brazil Beans has a few constraints when placing orders including: 1. Each box ordered must contain 12 bags of coffee or less 2. Each box ordered must contain 3 different types of beans 3. Each box ordered must contain between 0 and 5 full bags of each different bean type 4. Each box ordered must weigh equal to or less than 150 ounces NOTE: - Please use the constraints from the PDF file and not from the Excel file (especially \#1 and \#3) - do not use 20 bags and between 4 and 8 bags Suggested Steps: You use the attached spreadsheets that I supplied with the project. 1. Complete the project 1 tutorial first. Each part of the tutorial should be on a separate worksheet and a separate worksheet used for the Optimal Mix. All parts will be in the same workbook. 2. For the Optimal Mix worksheet, Setup your formulas correctly to get the totals across and down each column 3. What are you changing cells? 4. What cell is where you max you profit? 5. Now you are ready to enter the constraints. Do them one at a time so you can see the effect that each one has and if you have a problem with it. Then you know what to work on. HINT: How to handle this constraint: - Each box ordered must contain 3 different types of beans In general,....one way to do maximize profit on the project using solver that has this constraint is to.... You will need to add a countlf function that checks the column and counts if it is over Focus 4 The Broadway Caf Optimal Mix (Submit Excel Files using What-If and Solver features to find the solution) (Summarize and explain findings in your Report. Include both numerical and graphical form)
