CA_AYK
| Cohesion Case | |||||||||||||||||||||||
| Input boxes in tan | |||||||||||||||||||||||
| Output boxes in yellow | |||||||||||||||||||||||
| Given data in blue | |||||||||||||||||||||||
| Answers in red |
Brazil Beans
| BRAZIL BEANS | ||||||||
| Brand | Weight (oz) | Cost | Sale Price | Number of Bags per Box | Profit per Bag | Total | Total Weight | |
| Gold Ground | 14 | 8 | 13 | |||||
| French Vanilla | 13. | 5 | 7 | 11 | ||||
| Special Dark Roast | 6 | 9 | ||||||
| Vanilla Hazelnut | 12.5 | |||||||
| Organic Special Roast | 10 | |||||||
| Constraints: | ||||||||
| Each box ordered must contain 20 bags of coffee or less | ||||||||
| Each box ordered must contain 3 different types of beans | ||||||||
| Each box ordered must contain between 4 and 8 bags of each different bean type | ||||||||
| Each box ordered must weigh equal to or less than 150 ounces |
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 need to determine which beans to buy to make the most profit possible. You can order as much coffee as you require, and this month’s options include:
|
BRAND |
WEIGHT |
COST |
SALE PRICE |
|
|
Gold Ground – A versatile, complex blend combining great Latin American and Asia Pacific coffees with a touch of Italian Roast. Great in a coffee press, drip brewer or even as espresso. |
14 oz |
$8 |
$13 |
|
|
French Vanilla – Blunt, smoky flavors are the objective in creating this, our darkest roasted coffee. The intense flavor makes it one of our most popular coffees. |
13.5 oz |
$7 |
$11 |
|
|
Special Dark Roast – A full bodied, complex blend. This medium roast features a sweet, nutty taste. |
13 oz |
$6 |
$9 |
|
|
Vanilla Hazelnut – Treat your guests with the delicious aroma and robust flavor our most intense roast and the most pronounced in terms of dark, deep roasted flavor and hazelnut. Lots of character. |
12.5 oz |
$5 |
||
|
Organic Special Roast – In the world’s best coffee producing regions, there remain a select number of farmers who have grown coffee the same way for generations. They plant and nurture each coffee tree using only environmentally friendly techniques. They harvest each crop by hand. The coffee from these farms is 100% organic and the flavor is pure…the way nature intended it to be. |
10 oz |
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 countIf function that checks the column and counts if it is over a certain number, so you can add a constraint to it that enforces “Each box order must contain 3 different types of beans”.
Countif EXAMPLE
To count cells based on one criteria (for example, greater than 9), use the following COUNTIF function.
PROJECT FOCUS 4:
· Complete the Project 1 Tutorial
· Using Excel, determine the optimal mix for a single box which maximizes your profits while adhering to Brazil Bean’s constraints.
· NOTE: A problem you will encounter is that the relationship between the Variable Cells and the Objective is often non smooth; for example, if it includes any Boolean operators (e.g., IF, OR, AND) or any VLOOKUP functions, these result in a non-smooth equation. Select the Evolutionary algorithm in Solver to find the optimal mix. It will take a few minutes to run solver. It is the slowest algorithm.
Please refer to NOTE given under
CONSTRAINTS
File:
Optimal_Mix
Worksheet in the Project1_Data.xlsx
file
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).
image1
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
