show your work in Excel
Assume all questions can be solved using linear programming. You do not have to create a plot unless explicitly requested. Also, you must show you can solve the problem using Solver when applicable. For example, if you just type in the answer without showing any work, you will not get credit.
1. The Café Habana in Malibu is considering adding a new item(s) to their menu. They determined based on market research that the three possibilities are the following: Extreme Quesadilla (X1), Super Burrito (X2), and Mega Taco (X3). Each of these products is made from a different combination of beef, beans, and cheese, and each product has a specific price point. The manager at Café Habana feels he can sell X1 for $17, a X2 for $13, and a X3 for $14. You are hired by the restaurant as a consultant.
Copyright By PowCoder代写 加微信 powcoder
You formulate the following problem based on the resources required and available to make each item:
= $14X3 + $13X2 + $17X1
subject to:
Beef 2X3 + 3X2 + 4X1 ≤ 28
Cheese 9X3 + 8X2 + 11X1 ≤ 80
Beans 4X3 + 4X2 + 2X1 ≤ 68
X1, X2, X3 ≥ 0
What products and how much of each product should the restaurant offer to maximize revenue? (The time frame is irrelevant for this problem but you can assume it is daily based on the self-life of the ingredients).
a. Find the optimal solution to the problem (if a solution is possible).
b. What constraints are binding and non-binding?
c. Provide a management statement with recommendations.
2. The head brewer for Santa Monica produces two kinds of beer: Lager and IPA. The two main resources used to produce the beer are malt and hops. The manager can obtain at most 4800 oz of malt per month and at most 3200 oz of hops per month, respectively. Each bottle of Lager requires 12 oz of malt and 4 oz of hops, while a bottle of IPA uses 8 oz of malt and 8 oz of hops. The price for Lager beer is $4 per bottle, and price for IPA beer is $7 per bottle. The cost of malt is $0.15 per ounce and the cost of hops is $0.20 per ounce. Assume that the brewer can reuse leftover malt and hops and isn’t concerned about additional supply at least for this month (i.e., no storage cost). The brewery wants to produce at least 400 bottles of beer this month. How many bottles of each beer should be produced to maximize profit?
a. Formulate an LP model for this problem.
b. Plot the constraints and sketch the feasible region for this model. (You may use Excel or a Web Application to plot.)
c. Find the optimal solution to the problem (if a solution is possible).
d. Provide a management statement with recommendations.
3. The head of marketing for the Los Angeles Kings was hopeful that the print ads in the LA times and 30 second spots on the sports radio station would generate some interest in next week’s upcoming games (tickets sold at this point were lower than the Kings had hoped). The communications director for the Kings disagreed and believes that social media is the way to go; she favored a social media campaign consisting of Tweets (on Twitter) and Facebook posts.
The communications director’s market research revealed the following:
The advertising budget is $3500, but there is no requirement that all the money be spent. The newspaper has only four issues before the first game, but the radio is operating 24/7 and has two dozen 30 second slots available. After negotiating with the Kings social media manager, there is space on the Kings Facebook page for only three postings before the first game. The communications director feels she needs five tweets to convey a single message about the upcoming games, so for one message, the cost would be $25. Twitter is complicated by the 140-character cap per tweet. In other words, five tweets multiplied by 140 would be 700 characters maximum for a single “ad”. However, research shows that fans prefer shorter tweets so she figured that she should limit herself to at most 2,800 characters total before the first game. The goal is maximize total exposures.
a. Formulate an LP model for this problem.
b. Find the optimal solution to the problem (if a solution is possible).
c. Provide a management statement with recommendations.
4. The Dukes restaurant in Malibu is trying to schedule servers based on customer flow. The restaurant is open 8 AM–6 PM (everything in Malibu closes early) and needs the number of servers each hour as shown in the following table (in other words, the number of servers must be greater than or equal to the amount needed). Full-time servers can work 8 AM–5 PM (with a noon–1 PM lunch hour) or 9 AM–6 PM (with a 1 PM–2 PM lunch hour). Part-time servers work 10 AM–2 PM. Full-time servers receive $500 per day, and part-time servers receive $100 per day. At most, five part-time servers can be hired. How can Dukes minimize their daily server cost (compensation)?
While not necessary for this solution, as a hint, one way to constrain a value to an integer is to add a constraint in Solver, highlight the cells as reference cells, and specify that the cells must be “integer” or “int” (i.e., a human should be a whole number). This is an additional constraint.
a. Formulate an LP model for this problem.
b. Find the optimal solution to the problem (if a solution is possible).
c. How many part-time servers should be hired?
5. Alexa Foods is a non-profit organization that provides meals for a variety of adoptable dogs. While hoping to save on costs, the organization also seeks to provide pets with a well-balanced nutrition. The meals are made by mixing two types of dog food to obtain the nutritionally balanced dog diet. The data for the two dog foods are as follows:
Cost/Ounce
Beef+Sweet Potato
Alexa Foods wants to be sure that the dogs receive at least 4 ounces of protein and at least 3 ounces of fat per day. What is the minimizing cost and how much fat and protein do the dogs receive?
a. Formulate an LP model for this problem.
b. Find the optimal solution to the problem (if a solution is possible).
c. Provide a management statement with recommendations.
6. Your company has nine projects under consideration. As a project manager, you are in charge of justifying project selection. The NPV added by each project and the capital required by each project during the next two years is shown in the following table. (All numbers are in millions.) For example, Project 1 will add $30 million in NPV and require expenditures of $12 million during Year 1 and $3 million during Year 2. During Year 1, $50 million in capital is available for projects, and $35 million is available during Year 2. Also, your manager does not want to take on more than 4 total projects.
Year 1 Cost
Year 2 Cost
Select the project?*
*(Hint: This column corresponds to your decision cells and also contribute to the constraint cells; select the cells below, add constraint, select “bin”, constraint = binary when setting the constraint); additionally, add that this must be an integer or set the constraint so that the cells “=” “integer”; the interpretation is that 1 = Select, 0 = Don’t Select
If you can’t undertake a fraction of a project but must undertake either all or none of a project, how can you maximize the total NPV considering the project expenditures?
a. Find the optimal solution to the problem (if a solution is possible).
b. What projects should be selected?
7. Reference the problem covered earlier in the course on Wo :
Assume the same constraints related to Monthly Capacity and additional assumptions outlined as displayed in the following table:
Robotic design
3D Printing
Wa King wiring
Jamu plus wiring
However, assume that each Wa King generates $1,500 in profit and Jamu Plus generates $1,000 in profit.
a. How much of each robot should the firm now produce?
b. Plot the constraints and sketch the feasible region for this model. (You may use Excel or a Web Application to plot.). Comment on whether there is any slack or surplus associated with any of the constraints.
c. What resources are binding/non-binding?
d. What is the maximum amount of total profit?
8. A financial planner at Northwestern Mutual wants to design a portfolio of investments for a client. The client has $500,000 to invest and the planner has identified four investment options for the money. The following requirements have been placed on the planner. No more than 30% of the money in any one investment, at least one half should be invested in long-term bonds which mature in six or more years, and no more than 40% of the total money should be invested in B or C since they are riskier investments. The planner has developed the following LP model based on the data in this table and the requirements of the client. The objective is to maximize the total return of the portfolio.
Investment
Years to Maturity
1-Excellent
2-Very Good
Formulate the LP for this problem and determine the optimal total return to the portfolio.
9. You were just hired at and the firm wants to go on a business trip right away. You have no idea how much to pack for this business trip. However, you develop the following model to determine the optimal combination of socks and underwear to take. The model is as follows (S=socks, U=underwear):
Maximize 5S+7U
subject to:
3S – 2U≤ 45
7S + 3U≤ 33
2S + 8U≤ 70
a. Develop the initial tableau.
b. Solve the tableau using the Simplex Method (you may check your solution using Solver however you must show your iterations and final solution).
10. Develop a problem that can be solved using linear programming. For example, while you are in graduate school, you may have to balance your available monthly budget between clubbing and clothes.
You must have an objective function, at least two decision variables, and at least three constraints. (Remember only two variable problems can be shown on a plot.). You may cite a source as the basis for any assumptions you made. [You will be graded largely based on your logic, your consideration of the possible key constraints, and effort-not so much on how “correct” your final solution is.]
a. Describe the problem and formulate an LP model for this problem.
b. Find the optimal solution to the problem (if a solution is possible). If you can plot, plot the constraints and sketch the feasible region for this model. (You may use Excel or a Web Application to plot.)
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com