GRADED ASSIGNMENT $2$

Directions:

$1.$ Complete the following two problems, save your work, then submit your saved spreadsheet to Canvas by the due date and time.

$2.$ Do not change the name of this file when you save your work. The file should be called "Graded Assignment $2.$xls$".$

$3.$ To facilitate grading, please do not delete or insert ROWS.

$4.$ If you have questions, see me or email me at bberkle@calstatela.edu.

Inventory holding cost $($per unit per month$)$ $$1\mathrm{.}00$

Hiring cost $($per worker hired$)$: $$5,000\mathrm{.}00$

Firing cost $($per worker fired$)$: $$5,000\mathrm{.}00$

Straight$-$time labor cost $($per worker per month$)$: $$2,000\mathrm{.}00$

Overtime cost $($per worker per month$)$ $$4,000\mathrm{.}00$

Production per worker per month $100$

Present permanent workforce: $60($The number of workers must be an INTEGER.$)$

Beginning inventory in January: $400$

a$.$ Use Microsoft Excel Solver to find the least$-$cost CHASE aggregate production plan with a CONSTANT workforce. Workers may be hired or fired only at the beginning of January. Vary production using overtime. Your Solver setup will be GRADED. $(20$ points$)$

January February March April May June July August September October November December

Shipment Forecast $500060008000100001200080004000600080001200060003000$

Aggregate Production Plan

Straight$-$Time Production

Overtime Production

Output Plan:

Beginning Inventory $400$

Shipments $500060008000100001200080004000600080001200060003000$

Ending Inventory

Input Plan:

Number of Workers

Hires

Fires

Overtime $($worker$-$months$)$

Costs:

Inventory Holding Cost

Straight$-$Time Labor Cost

Hiring Cost

Firing Cost

Overtime Cost

Total Plan Cost

b$.$ Find the least$-$cost LEVEL aggregate production plan with a CONSTANT workforce. Workers may be hired or fired only at the beginning of January. DO NOT USE SOLVER. The Solver setup for question $($a$)$ will be graded. $(20$ points$)$

January February March April May June July August September October November December

Shipment Forecast $500060008000100001200080004000600080001200060003000$

Aggregate Production Plan

Straight$-$Time Production

Overtime Production

Output Plan:

Beginning Inventory $400$

Shipments $500060008000100001200080004000600080001200060003000$

Ending Inventory

Input Plan:

Number of Workers

Hires

Fires

Overtime $($worker$-$months$)$

Costs:

Inventory Holding Cost

Straight$-$Time Labor Cost

Hiring Cost

Firing Cost

Overtime Cost

Total Plan Cost