QNT 5160 Team Optimization Project

Part A. Optimal Solution (4 points). Complete and analyze the model in the first Excel Tab titled Optimal Solution:

• Use Excel to calculate Unit Profit for each product (in cells B8:I8) and Total Profit (in cell B19).
• Use Excel Solver to estimate the optimal production plan and create the first sensitivity report. Analyze the tactical and strategic information provided by the optimal solution.
• What is an optimal output plan for the company and maximum profit? (2 points)
• Use the sensitivity report to answer the following questions (2 points):
• For each department, what is the marginal value (shadow price) of additional overtime capacity and what does it mean?
• Using the data in the reduce cost column, in the top portion of the sensitivity report, what factors could lead to even better levels of performance?

• Again, use Excel to calculate Unit Profit for each product (in cells B8:I8) and Total Profit (in cell B19).
• From the first sensitivity report (Optimal Solution), find the maximum allowable advertising budget and enter this number into the advertising budget RHS constraint cell C33. This is, in effect, increasing the advertising budget.
• Run Excel Solver for this change in the Advertising Trade-off Tab (only) to create another optimal production plan and create another sensitivity report. Analyze the tactical and strategic information provided by the Solver solution and answer the following:
• How is the objective (profit) impacted by the additional advertising budget as compared to the profit shown in the Optimal Solution Tab? And, what is the impact to the product demand constraints (cells B28 – B31)? (1 points)
• What is the trade-off between increased advertising expenditures and profit for Hawley Co. – is expending more advertising budget worth the additional profit? (1 points)
• How would an increase in advertising budget affect the optimal production plan? (1 points)
• Assume each department claims to require a proportional amount of advertising budget in order to attain the projected sales for each of their products. Calculate the portion of advertising budget for each product as a percentage of total Sales – determine how much advertising budget each product should be allocated. Now, allocate the new advertising budgets for each product proportionally in cells B15 thru E15. Do Not Re-Run Solver! What is the profit impact and are there any issues between the RHS and LHS demand constraints – is this a viable approach? (1 points)

Part C. Outsourcing Option (3 points). A third-party company approached Hawley’s management offering to assemble and test pendant lights for \$27 per unit.

• Use Excel to calculate Unit Profit for each product (in cells B8:J8) and Total Profit (in cell B19). Assume an advertising budget with a \$18,000 limit – you do not need to redistribute the advertising budget for this part.
• Run Excel Solver for this Tab to estimate the optimal production plan and create another sensitivity report. Analyze the tactical and strategic information provided by the optimal solution.
• Should the Hawley’s management consider the offer? If yes, how much of pendant light units should be outsourced and how many units should be produced in-house.

Part D. (4 points). Prepare a management report in APA format and use the course cover sheet with each team member’s name. In addition to the cover sheet, the report should not be longer than 4 pages (12 pt. Arial font). Show your team’s answers and discuss the questions in Part A, Part B and Part C referring to your Excel spreadsheet Tabs. Create at least one graphic or table to support your recommendations or a key point. Provide your recommendations on how, in your opinion, Hawley Co. should coordinate advertising and production of the different products to maximize profit.

Note: each of the 3 scenarios (Optimal Solution, Advertising Trade-off, Outsource Option) should have an Excel Tab for the model with your calculations and a sensitivity report. This results in 6 Tabs in one Excel file.

inciteprofessor

Inciteprofessor is a Master Holder in Actuarial Science from the World's Best Universities. He also possesses a Bachelor degree in Computer Science and Cyber Security. He has worked with many freelance companies including Freelancers, Fiverr , Studybay, Essayshark, Essaywriters, Writerbay, Edusson, and Chegg Tutor. He offers help in research paper writing & tutoring in Mathematics, Finance, and Computer Science field.