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 Fall$23.$xlsx

file and complete the model:

$$ Calculate Unit Profit for each product $($in cells B$8$:I$8)$ and Total Profit $($in cell B$19)$

$$ Calculate total capacity required in Department $1($overtime$)$ in cell B$24$ and Department $2$

$($regular and overtime$)$ in cells B$25$:B$26$

$$ Calculate total units produced of each product family in cells B$28$:B$31.$

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.