I JUST NEED HELP WITH HOW TO SET UP MY EXCEL WORKSHEET. PLEASE ONLY RESPOND BACK WITH HOW YOU SOLVED IN EXCEL (SCREENSHOT OF EXCEL SHEET IS FINE), SOLVER FORMULAS AND STEPS TO VIEW SENSITIVITY REPORT OR I WILL GIVE THUMBS DOWN. THANK YOU.

Current Issues One of the biggest issues facing this company is the rise in prices of raw materials, especially the iconic almond, which due to drought in California has seen a price rise in recent years. In order to diversify their product line, Brown & Haley has started to expand its repertoire to including other nuts, such as cashews and macadamia nuts. They also have a project underway to test a new product line of packaged mixed nuts. Note that this scenario is fictional and the details do not represent actual operations of Brown & Haley. The Scenario The company is considering three nut mixes for inclusion in the new product line: Regular Mix, Deluxe Mix, and Holiday Mix. Each mix is made from 5 nuts in different combinations: . The Regular Mix consists of 15% almonds, 25% Brazil nuts, 25% filberts, 10% pecans, and 25% walnuts The Deluxe Mix consists of 20% of each type of nut The Holiday Mix consists of 25% almonds, 15% Brazil nuts, 15% filberts, 25% pecans, and 20% walnuts . An accountant at Brown & Haley completed a cost analysis and determined that the profit contribution per pound is $1.65 for the Regular Mix, $1.90 for the Deluxe Mix, and $2.35 for the Holiday Mix. Different nuts come from different suppliers. They are shipped in bulk containers and ordering a partial container is not possible. The currently available container sizes and costs are as follows: Type of Nut Almond Brazil Container Size (pounds) 6000 7500 Cost per Container $7800 $7350 $7150 $7200 $7450 Filbert 7500 6000 Pecan Walnut 7500 One container of each of the types of nuts has been ordered and is on the way. The sales and marketing teams have projected that initial demand for the different types of mixes will be as follows: Type of Mix Regular Deluxe Holiday Orders (pounds) 10,000 5,000 3,000 The president of Brown & Haley wants to commit to producing enough of the various mixes to meet the projected initial demand, even if not immediately profitable, in order to introduce these new mixes to the market. The Analysis Required The President would like to see a PowerPoint presentation of no more than 10 slides that answers the following questions: 1. Based on current cost information, what is the cost per pound of the nuts included in the Regular, Deluxe, and Holiday mixes? 2. How much of each type of mix should be made using only the nuts already ordered and keeping in mind the President's requirement to meet the initial demand for each type of mix? What is the resulting optimized profit? 3. Sometimes small amounts of certain types of nuts become available in secondary markets. Which types of nuts should be pursued in order to increase profit? 4. A supplier has offered us 1000 pounds of almonds for $1000. Should these almonds be purchased? If yes, how much would profits increase? 5. A supplier has offered us 1200 pounds of filberts for $950. Should these filberts be purchased? If yes, how much would profits increase? 6. The marketing department is proposing an upgrade to the packaging of the Holiday Mix that would decrease the profit contribution from $2.35 to $2.29 per pound. Would the number of pounds of each type of mix be changed in the optimal solution? (Note that the President would be impressed if you did not need to rerun Solver to answer this question) 7. If the President's requirement to meet the initial demand for each type of mix were eliminated would profitability be impacted? If so, by how much? A B D E F H J K L M N 0 Q Almond 2 3 Product Ingredient Almond Brazil nut Filbert Pecan Walnut Brazil 4 15% 25% 25% 10% 25% Product Profit $1.65 $1.90 $2.35 Filbert Regular mix Deluxe mix Holiday mix Demand (lb) 10,000 5,000 3,000 $1.30 $0.98 $0.95 $1.20 $0.99 5 20% 20% 20% 20% 20% Pecan 6 25% 15% 15% 25% 20% Walnut 7 8 Ingredient 9 Availability (lb) 7.500 6,000 $7,800 7,500 $7,350 6,000 $7,200 7,500 $7,450 10 Cost $7,150 11 12 13 Decision variables Production quantity in Ibs 15 Regular mix Deluxe mix $1.05 $1.09 Holiday mix $1.11 16 17 18 Objective function 19 Regular mix Deluxe mix Holiday mix 20 Maximize profits 16500 9500 21 7050 = 33050 22 23 Constraints 24 Availability of ingredients 25 26 Consumption Availability - 6,000 27 Almond 28 Brazil nut 29 Filbert A A A A 7,500 7,500 30 Pecan 31 Walnut 6,000 7,500 32 33 Demand to satisfy 34 Produced Demand 10,000 35 Regular mix 36 Deluxe mix 37 Holiday mix 5,000 XX 3,000 "aw Page Layout Formulas Data Review View Tell me 19. Connections y Clear V 21 22 Y , E B > + Sho OV E Properties & Reapply 7 Filter Refresh All Consolidate All Sort Group Ungroup Subtotal = Hide Edit Links What-If Analysis Advanced Text to Flash Remove Data Columns Fill Duplicates Validation Solver Parameters fx D E F H Set Objective: $A$21] P Q R S T Ingredient Almond To: o Max Min Value of: 0 Brazil nut Filbert Pecan Walnut 15% 25% 10% 25% 25% % 20% By Changing Variable Cells: $1.30 $0.98 $0.95 $1.20 $0.99 20% 20% 20% 20% 25% 15% 15% 25% 20% $A$16:$C$16 Subject to the Constraints: b) 6,000 $7,800 Add 7,500 $7,350 7,500 $7,150 6,000 $ 7,200 7,500 $ 7,450 $B$27:$B$31 = $D$35:$D$37 Change Delete Holiday mix $1.11 Reset All 19 Load/Save Holiday mix Make Unconstrained Variables Non-Negative 00 7050 33050 Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non- smooth. . 7 Availability 6,000 7,500 7,500 6,000 7,500 Close Solve Demand 10,000 Current Issues One of the biggest issues facing this company is the rise in prices of raw materials, especially the iconic almond, which due to drought in California has seen a price rise in recent years. In order to diversify their product line, Brown & Haley has started to expand its repertoire to including other nuts, such as cashews and macadamia nuts. They also have a project underway to test a new product line of packaged mixed nuts. Note that this scenario is fictional and the details do not represent actual operations of Brown & Haley. The Scenario The company is considering three nut mixes for inclusion in the new product line: Regular Mix, Deluxe Mix, and Holiday Mix. Each mix is made from 5 nuts in different combinations: . The Regular Mix consists of 15% almonds, 25% Brazil nuts, 25% filberts, 10% pecans, and 25% walnuts The Deluxe Mix consists of 20% of each type of nut The Holiday Mix consists of 25% almonds, 15% Brazil nuts, 15% filberts, 25% pecans, and 20% walnuts . An accountant at Brown & Haley completed a cost analysis and determined that the profit contribution per pound is $1.65 for the Regular Mix, $1.90 for the Deluxe Mix, and $2.35 for the Holiday Mix. Different nuts come from different suppliers. They are shipped in bulk containers and ordering a partial container is not possible. The currently available container sizes and costs are as follows: Type of Nut Almond Brazil Container Size (pounds) 6000 7500 Cost per Container $7800 $7350 $7150 $7200 $7450 Filbert 7500 6000 Pecan Walnut 7500 One container of each of the types of nuts has been ordered and is on the way. The sales and marketing teams have projected that initial demand for the different types of mixes will be as follows: Type of Mix Regular Deluxe Holiday Orders (pounds) 10,000 5,000 3,000 The president of Brown & Haley wants to commit to producing enough of the various mixes to meet the projected initial demand, even if not immediately profitable, in order to introduce these new mixes to the market. The Analysis Required The President would like to see a PowerPoint presentation of no more than 10 slides that answers the following questions: 1. Based on current cost information, what is the cost per pound of the nuts included in the Regular, Deluxe, and Holiday mixes? 2. How much of each type of mix should be made using only the nuts already ordered and keeping in mind the President's requirement to meet the initial demand for each type of mix? What is the resulting optimized profit? 3. Sometimes small amounts of certain types of nuts become available in secondary markets. Which types of nuts should be pursued in order to increase profit? 4. A supplier has offered us 1000 pounds of almonds for $1000. Should these almonds be purchased? If yes, how much would profits increase? 5. A supplier has offered us 1200 pounds of filberts for $950. Should these filberts be purchased? If yes, how much would profits increase? 6. The marketing department is proposing an upgrade to the packaging of the Holiday Mix that would decrease the profit contribution from $2.35 to $2.29 per pound. Would the number of pounds of each type of mix be changed in the optimal solution? (Note that the President would be impressed if you did not need to rerun Solver to answer this question) 7. If the President's requirement to meet the initial demand for each type of mix were eliminated would profitability be impacted? If so, by how much? A B D E F H J K L M N 0 Q Almond 2 3 Product Ingredient Almond Brazil nut Filbert Pecan Walnut Brazil 4 15% 25% 25% 10% 25% Product Profit $1.65 $1.90 $2.35 Filbert Regular mix Deluxe mix Holiday mix Demand (lb) 10,000 5,000 3,000 $1.30 $0.98 $0.95 $1.20 $0.99 5 20% 20% 20% 20% 20% Pecan 6 25% 15% 15% 25% 20% Walnut 7 8 Ingredient 9 Availability (lb) 7.500 6,000 $7,800 7,500 $7,350 6,000 $7,200 7,500 $7,450 10 Cost $7,150 11 12 13 Decision variables Production quantity in Ibs 15 Regular mix Deluxe mix $1.05 $1.09 Holiday mix $1.11 16 17 18 Objective function 19 Regular mix Deluxe mix Holiday mix 20 Maximize profits 16500 9500 21 7050 = 33050 22 23 Constraints 24 Availability of ingredients 25 26 Consumption Availability - 6,000 27 Almond 28 Brazil nut 29 Filbert A A A A 7,500 7,500 30 Pecan 31 Walnut 6,000 7,500 32 33 Demand to satisfy 34 Produced Demand 10,000 35 Regular mix 36 Deluxe mix 37 Holiday mix 5,000 XX 3,000 "aw Page Layout Formulas Data Review View Tell me 19. Connections y Clear V 21 22 Y , E B > + Sho OV E Properties & Reapply 7 Filter Refresh All Consolidate All Sort Group Ungroup Subtotal = Hide Edit Links What-If Analysis Advanced Text to Flash Remove Data Columns Fill Duplicates Validation Solver Parameters fx D E F H Set Objective: $A$21] P Q R S T Ingredient Almond To: o Max Min Value of: 0 Brazil nut Filbert Pecan Walnut 15% 25% 10% 25% 25% % 20% By Changing Variable Cells: $1.30 $0.98 $0.95 $1.20 $0.99 20% 20% 20% 20% 25% 15% 15% 25% 20% $A$16:$C$16 Subject to the Constraints: b) 6,000 $7,800 Add 7,500 $7,350 7,500 $7,150 6,000 $ 7,200 7,500 $ 7,450 $B$27:$B$31 = $D$35:$D$37 Change Delete Holiday mix $1.11 Reset All 19 Load/Save Holiday mix Make Unconstrained Variables Non-Negative 00 7050 33050 Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non- smooth. . 7 Availability 6,000 7,500 7,500 6,000 7,500 Close Solve Demand 10,000