Complete the following problem in your textbook:
From Chapter 13: Problems: 2, 6, 10, and 17
From Chapter 14: Problems: 3, 9 and 23
From Chapter 15: Problems: 6, 10 and 25
All work should be submitted in Excel with one (1) problem per tab in a single workbook. Formulas should be used as opposed to outside or manual calculations. Use of Excel add-ins is encouraged.
Chapter 13:
Q2. A brand manager for ColPal Products must determine how much time to allocate between radio and television advertising during the next month. Market research has provided estimates of the audience exposure for each minute of advertising in each medium, which it would like to maximize. Costs per minute of advertising are also known, and the manager has a limited budget of $25,000. The manager has decided that because television ads have been found to be much more effective than radio ads, at least 70% of the time should be allocated to television. Suppose that we have the following data: (See attach photo)
a. Identify the decision variables, objective function, and constraints in simple verbal expressions.
b. Mathematically formulate a linear optimization model.
Q6. Implement the linear optimization model that you developed for ColPal Products in Problem 2 in Excel and use Solver to find an optimal solution. Interpret the Solver Answer report and identify the binding constraints and verify the values of the slack variables by substituting the optimal solution into the model constraints.
Q10. Implement the linear optimization model that you developed for ColPal Products in Problem 2 in Excel and use Solver to find an optimal solution. Interpret the Solver Answer report and identify the binding constraints and verify the values of the slack variables by substituting the optimal solution into the model constraints.
Q17. Figure 13.33 shows the Solver sensitivity report for the ColPal Products scenario in Problem 2. Using only the information in the sensitivity report, answer the following questions.
a. Suppose that the exposure for TV advertising was incorrectly estimated and should have been 875. How would the optimal solution have been affected?
b. Radio listening has gone down, and new marketing studies have found that the exposure has dropped to 150. How will this affect the optimal solution?
c. The marketing manager has increased the budget by $2,000. How will this affect the solution and total exposure?
d. The shadow price for the mix constraint (that at least 70% of the time should be allocated to TV) is 250. The marketing manager was told that this means that if the percentage of TV advertising is increased to 71%, exposure will fall by 250. Explain why this statement is incorrect.
Chapter 14:
Q3. Korey is a business student at State U. She has just completed a course in decision models, which had a midterm exam, a final exam, individual assignments, and class participation. She earned an 86% on the midterm, 94% on the final, 93% on the individual assignments, and 85% on participation. The benevolent instructor is allowing his students to determine their own weights for each of the four grade componentsof course, with some restrictions:
1. The participation weight can be no more than 15%.
2. The midterm weight must be at least twice as much as the individual assignment weight.
3. The final exam weight must be at least three times as much as the individual assignment weight.
4. The weights of the four components must be at least 10%.
5. The weights must sum to 1.0 and be nonnegative.
a. Develop a model that will yield a valid set of weights to maximize Koreys score for the course.
b. Implement your model on a spreadsheet and find a good solution using only your intuition.
c. Find an optimal solution using Solver.
Q9. Jaycees department store chain is planning to open a new store. It needs to decide how to allocate the 100,000 square feet of available floor space among seven departments. Data on expected performance of each department per month, in terms of square feet (sf), are shown next.
The company has gathered $20 million to invest in floor stock. The risk column is a measure of risk associated with investment in floor stock based on past data from other stores and accounts for outdated inventory, pilferage, breakage, and so on. For instance, electronics loses 24% of its total investment, furniture loses 12% of its total investment, and so on. The amount of risk should be no more than 10% of the total investment.
a. Develop a linear optimization model to maximize profit.
b. If the chain obtains another $1 million of investment capital for stock, what would the new solution be?
Q23. Jason Wright is a part-time business student who would like to optimize his financial decisions. Currently, he has $16,000 in his savings account. Based on an analysis of his take-home pay, expected bonuses, and anticipated tax refund, he has estimated his income for each month over the next year. In addition, he has estimated his monthly expenses, which vary because of scheduled payments for insurance, utilities, tuition and books, and so on. The following table summarizes his estimates: (See Attach Photo)
Jason has identified several short-term investment opportunities:
1. a 3-month CD yielding 0.60% at maturity
2. a 6-month CD yielding 1.42% at maturity
3. an 11-month CD yielding 3.08% at maturity
4. a savings account yielding 0.0375% per month
To ensure enough cash for emergencies, he would like to maintain at least $2,000 in the savings account. Jasons objective is to maximize his cash balance at the end of the year. Develop a linear optimization model to find the best investment strategy.
Chapter 15:
Q6. The Gardner Theater, a community playhouse, needs to determine the lowest-cost production budget for an upcoming show. Specifically, they have to determine which set pieces to construct and which, if any, set pieces to rent from another local theater at a predetermined fee. However, the organization has only two weeks to fully construct the set before the play goes into technical rehearsals. The theater has two part-time carpenters who work up to 12 hours a week each at $10 an hour. Additionally, the theater has a part-time scenic artist who can work 15 hours per week to paint the set and props as needed at a rate of $15 per hour. The set design requires 20 flats (walls), 2 hanging drops with painted scenery, and 3 large wooden tables (props). The number of hours required for each piece for carpentry and painting is shown below: (See Attached Photo)
Flats, hanging drops, and props can also be rented at a cost of $75, $500, and $350 each, respectively. How many of each units should be built by the theater and how many should be rented to minimize total costs?
Q10. The personnel director of Hatch Financial. which recently absorbed another firm, is now downsizing and must relocate five information systems analysts from recently closed locations. Unfortunately, there are only three positions available for five people. Salaries are fairly uniform among this group (those with higher pay were already given the opportunity to begin anew). Moving expenses will be used as the means of determining who will be sent where. Estimated moving expenses are as follows: (See Attached Photo)
Model this as an integer optimization model to minimize cost and determine which analysts to relocate to the three locations.
Q25. Tunningley Services is establishing a new business to serve customers in the Ohio, Kentucky, and Indiana region around the Cincinnati Ohio area. The company has identified 15 key market areas and wants to establish regional offices to meet the goal of being able to travel to all key markets within 60 minutes. The data file Tunningley.xlsx provides travel times in minutes between each pair of cities. a. Develop and solve an optimization model to find the minimum number of locations required to meet their goal.
b. Suppose they change the goal to 90 minutes. What would be the best solution?
<
