Optimization Group Project Instructions Instructions This is a group

Optimization Group Project Instructions


Instructions

This is a group assignment. In order to complete the assignment, first read the “Hawley Lighting Company” case study. Conduct necessary calculations and answer the questions listed below. Submit your Excel spreadsheet(s) with calculations to the assignment dropbox before the posted deadline.

Prepare a
properly formatted

Management Report

, which includes your answers to the assignment questions. Include cover page and appropriate references.


Grading

A total of 20 points is possible for this assignment. This includes the point values which are assigned to each question (point values are noted next to each question below). This includes the point values which are assigned to each question (point values are noted next to each question below) plus 2 points which are earned based on following the prescribed assignment format, and the proper writing style including APA format.

For full credit for Parts A and B, you must turn in your spreadsheet with dynamically coded formulas (not hard-coded numbers).

1.
Spreadsheet Model (in Excel)

Part A (3 points). Read the case study “Hawley Lighting Company”. Download Hawley Data Fall23.xlsx file and complete the model:

· Calculate Unit Profit for each product (in cells B8:I8) and Total Profit (in cell B19)

· Calculate total capacity required in Department 1 (overtime) in cell B24 and Department 2 (regular and overtime) in cells B25:B26

· Calculate total units produced of each product family in cells B28:B31.

Estimate the optimal production plan and run the sensitivity analysis.

Part B (2 points). A third-party company approached Hawley’s management offering to assemble and produce pendant lights for $27 per unit. Modify the production plan model in Part A to include the pendant light outsourcing. Assume the advertising budget with $18,000 limit. Estimate new optimal production plan and run the sensitivity analysis.

2.
Management Report (in Word)

Part C (13 points). Prepare a
management report, discussing the analysis results obtained in Part A and Part B. The report should include the following sections and must answer the following questions:

1. Executive Summary.

2. Problem Statement.

3. Analysis. This section must include the following information:

a. Describe the method of analysis used to answer the case questions and explain why it was used to address the case problems.

b. Analyze the tactical and strategic information provided by the optimal solution and sensitivity report in
Part A:

· What is an optimal output plan for the company?

· What factors could lead to even better level of performance:

· For each department, what is the
marginal value of additional overtime capacity?

· What is the
marginal value of additional advertising dollars?

· What is the
marginal value of additional sales for each product?

· What is the trade-off between advertising expenditures and increased sales for Hawley Co. If the advertising budget to be increased, how it would affect the production plan? Assume that you increased the advertising budget by the amount of the
allowable increase, how this advertising budget change would affect the optimal production plan? (1 point)

c. Analyze the optimal solution obtained in
Part B:

· 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. (0.5 points)

4. Conclusions and Recommendations.

· Summarize your conclusions and provide recommendations on optimal production strategy and on how, in your opinion, Hawley Co. should coordinate advertising and sales of different products?

5. Bibliography. Include appropriate references and follow APA style.

1

Share This Post

Email
WhatsApp
Facebook
Twitter
LinkedIn
Pinterest
Reddit

Order a Similar Paper and get 15% Discount on your First Order

Related Questions

 Effective management deals with effective managers who control and direct people according to established policies and procedures. For organizations to

 Effective management deals with effective managers who control and direct people according to established policies and procedures. For organizations to succeed, they need both functions. An effective manager pays attention to many aspects of management, leadership and learning within an organization. Dr. Sam Perkins, a graduate of the Harvard University

Regent University ECON 230 Introduction to Macro Economics Assignment 3 1. Making

Regent University ECON 230 Introduction to Macro Economics Assignment 3 1. Making consumption and savings decisions is not always about investing in businesses for retirement. People with unpredictable incomes like full commission sales people have to think about their incomes very carefully. Bob sells cars and in good years earns

Explain the macro environment in which a company operates and the strategical relevant components of the macro-environment in which the company operates

Explain the macro environment in which a company operates and the strategical relevant components of the macro-environment in which the company operates (focusing towards long-term goals). Identify the dominant and competitive driving forces in an industry. Emphasize how strong the industry dominant and competitive forces. (strategic group, key success factors