QNT 5160 Team Optimization Project
Read the case study “Hawley Lighting Company”. Download the Hawley Data Models V5.xlsx file and complete the following embedding your answers in one final report and submitting the Excel file with your calculations.
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?
- What is the marginal value of additional advertising dollars?
- 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?
Part B. Advertising Budget Trade-Off (4 points). Complete and analyze the model in the second Excel tab titled Advertising Trade-Off:
- 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.