Blog

Develop a model that will yield a valid set of weights to maximize Korey’s score for the course.

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 components—of 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 Korey’s 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. Jaycee’s 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. Jason’s 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?

The post Develop a model that will yield a valid set of weights to maximize Korey’s score for the course. appeared first on superioressaypapers.

determine which analysts to relocate to the three locations.

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 components—of 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 Korey’s 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. Jaycee’s 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. Jason’s 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?

The post determine which analysts to relocate to the three locations. appeared first on superioressaypapers.

identify the binding constraints and verify the values of the slack variables by substituting the optimal solution into the model constraints.

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 components—of 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 Korey’s 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. Jaycee’s 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. Jason’s 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?

The post identify the binding constraints and verify the values of the slack variables by substituting the optimal solution into the model constraints. appeared first on superioressaypapers.

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.”

The post Use of Excel add-ins is encouraged. appeared first on superioressaypapers.

Week 2 Assignment

The Assignment: Barry Computer Company

Prepare a performance report on Barry Computer Company. (Problem 4-23 on pages 140-141 of the course text provides a balance sheet and an income statement for the company.)

Prepare your performance report to show calculations for the 14 ratios listed on page 141, as well as a comparison of your computed ratios with the listed industry averages.
Write a short memo to your supervisor explaining your findings and your recommendations for improvement.
Suggest some ways in which the company can plan to improve below industry average ratio performance.
Explain why your recommendations would be effective.
Be sure to list your computations in an appendix to your report.

General Guidance on Application Length:

The memo portion of this assignment will typically be 2 pages in length as a general expectation/estimate. You can show your calculations of financial ratios in a supplemental appendix to your memo.

 

APA format

 

 

Course text attached

The post Week 2 Assignment appeared first on superioressaypapers.

Week 6 Excel Problems

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 components—of 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 Korey’s 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. Jaycee’s 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. Jason’s 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?

The post Week 6 Excel Problems appeared first on superioressaypapers.

Develop and solve an optimization model

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 components—of 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 Korey’s 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. Jaycee’s 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. Jason’s 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?

The post Develop and solve an optimization model appeared first on superioressaypapers.

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 components—of 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 Korey’s 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. Jaycee’s 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. Jason’s 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?

 

The post Complete the following problem in your textbook: appeared first on superioressaypapers.

determine how much time to allocate between radio and television advertising

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 components—of 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 Korey’s 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. Jaycee’s 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. Jason’s 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?

The post determine how much time to allocate between radio and television advertising appeared first on superioressaypapers.