The following data have been developed for the purpose of developing the aggregate sales and operations...

 

Transcribed Image Text:

The following data have been developed for the purpose of developing the aggregate sales and operations plan for a company that manufactures a single product line, using a single 8-hour shift per day. The aggregate demand forecasts for the upcoming four quarters are, respectively, 100, 80, 210 and 180 units. (Assume that an average quarter consists of 500 hours.) Regular time wage rate = $20 per hour; Material & overhead costs = $3000/unit; Inventory holding cost = $2400/unit/year; Hiring cost $3500/worker; Beginning workforce size = 60 (qr.1); Maximum overtime = 100 hours/worker/ quarter; Average number of regular time hours available = 500 hours per quarter per worker; Sales revenue = $20000/unit; 30% of the shortages turn into last sales. Desired end of year inventory is zero, with no outstanding backorders. The following linear programming model has been formulated for obtaining the optimal aggregate SOP for the company: Let X₁ = units of planned sales during quarter t, t = 1, 2, 3, 4 P₁ = (C "total production 26 I₁ = S₁ = O hours" overtime scheduled in H₁=# of workers hired at the beginning of quarter t, t = 1, 2, 3, 4 L₁ = " " (6 66 66 C (6 laid off" " W₁ = " << 66 Subject to 66 66 ending inventory in 66 (6 shortage 66 employed (6 66 during (6 66 Overtime wage rate = $30 per hour; Backorder cost = $1000/unit/quarter; Direct labor content = 250 hours/unit; Layoff cost $3000/worker; Beginning inventory = 10 units (qr.1); Maximum workforce size = 100 66 66 66 (6 66 Max. Z = = Et-1(20000X - 10000W - 3000Pt - 300t - 700St-601t - 3500Ht-3000Lt) (1) It - St = It-1-0.7St-1 + Pt - Dt, for t = 1, 2, 3, 4 (2) Wt = Wt-1 + H - Lt, for t = 1, 2, 3, 4 (3) Pt ≤ 2Wt+ Ot for t = 1, 2, 3, 4 " 250 (4) Ot ≤ 100Wt, for t = 1,2, 3, 4 (5) Xt = Dt +0.7St-1 - St, for t=1, 2, 3, 4 (6) W≤ 100, for t = 1, 2, 3, 4 (7) lo= 10,14 0, So = 0, S4 = 0, Wo = 60, D₁ = 100, D₂ = 80, D3 = 210, D4 = 180 (8) Xt, Pt, Ot, It, St, Ht, Lt, Wt ≥ 0, for t = 1, 2, 3, 4 Create an Excel spreadsheet in a workbook, embedding the above LP model for solution via Solver. Then, using Solver, find and outline the optimal aggregate sales & operations plan for the company, showing all the relevant decision variable values and the resulting estimated total operating profit for the upcoming four quarters. The following data have been developed for the purpose of developing the aggregate sales and operations plan for a company that manufactures a single product line, using a single 8-hour shift per day. The aggregate demand forecasts for the upcoming four quarters are, respectively, 100, 80, 210 and 180 units. (Assume that an average quarter consists of 500 hours.) Regular time wage rate = $20 per hour; Material & overhead costs = $3000/unit; Inventory holding cost = $2400/unit/year; Hiring cost $3500/worker; Beginning workforce size = 60 (qr.1); Maximum overtime = 100 hours/worker/ quarter; Average number of regular time hours available = 500 hours per quarter per worker; Sales revenue = $20000/unit; 30% of the shortages turn into last sales. Desired end of year inventory is zero, with no outstanding backorders. The following linear programming model has been formulated for obtaining the optimal aggregate SOP for the company: Let X₁ = units of planned sales during quarter t, t = 1, 2, 3, 4 P₁ = (C "total production 26 I₁ = S₁ = O hours" overtime scheduled in H₁=# of workers hired at the beginning of quarter t, t = 1, 2, 3, 4 L₁ = " " (6 66 66 C (6 laid off" " W₁ = " << 66 Subject to 66 66 ending inventory in 66 (6 shortage 66 employed (6 66 during (6 66 Overtime wage rate = $30 per hour; Backorder cost = $1000/unit/quarter; Direct labor content = 250 hours/unit; Layoff cost $3000/worker; Beginning inventory = 10 units (qr.1); Maximum workforce size = 100 66 66 66 (6 66 Max. Z = = Et-1(20000X - 10000W - 3000Pt - 300t - 700St-601t - 3500Ht-3000Lt) (1) It - St = It-1-0.7St-1 + Pt - Dt, for t = 1, 2, 3, 4 (2) Wt = Wt-1 + H - Lt, for t = 1, 2, 3, 4 (3) Pt ≤ 2Wt+ Ot for t = 1, 2, 3, 4 " 250 (4) Ot ≤ 100Wt, for t = 1,2, 3, 4 (5) Xt = Dt +0.7St-1 - St, for t=1, 2, 3, 4 (6) W≤ 100, for t = 1, 2, 3, 4 (7) lo= 10,14 0, So = 0, S4 = 0, Wo = 60, D₁ = 100, D₂ = 80, D3 = 210, D4 = 180 (8) Xt, Pt, Ot, It, St, Ht, Lt, Wt ≥ 0, for t = 1, 2, 3, 4 Create an Excel spreadsheet in a workbook, embedding the above LP model for solution via Solver. Then, using Solver, find and outline the optimal aggregate sales & operations plan for the company, showing all the relevant decision variable values and the resulting estimated total operating profit for the upcoming four quarters.

Answer rating: 100% (QA)

1 The objective function is to maximize total profit Profits come from sales revenue minus costs of ... View the full answer