Question

The Barberton Municipal division of Road Maintenance is charged with road repair in the city of Barberton and the sumounding area. Cindy Kramer, road maintenance director, must submit a staffing plan for Kramer estimates that the labor hours required for the next four quarters are 6,000, 12.000. 20,000, and 9.000 the 11 workers on the workforce can contribute 500 hours per quarter. Payroll costs are $6,000 in wages per worker for regular time worked up to 500 hours, with an overtime pay rate of $17 for each overtime hour. Overtime is limited to 20 percent of the regular-time capacity in any quarter. Although unused overtime capacity has no cost, unused regular time is paid at $12 per hour. The cost of hiring a worker is $3,200, and the cost of laying off a worker is $1,400. Subcontracting is not permitted. (Hint: When calculating the number of workers, make sure lo round up to the next whole number before proceeding with any furthor calculations.) a. Find a level workforce plan that relies just on overtime and the minimum amount of undertime possible. Overtime can be used to its limits in any quarter. What is the total cost of the plan? S(Enter your response as an integer.)

Answer 1

Below is the screenshot of the LP Table

Book1 - Excel Abhishek Rai -O × File Home Insert Page Layout Formulas Review View Help Tell me what you want to do (.DFrom Quenes DDCornections Rem ve Duplicates-consolide. 相GroupupA 2↓TE T ', RIepp y AdvancedComnsData Vaidation - 면 Data Analysis 2. DheAnalys s Ib E What-If ForecastUngroup Solver Analysis . Sheet Subtotal From Table - Remove DuplicatesRelationships Get External Nevw zI Sort Filter Recent SourcesEdit Links DataQuery Get & Transform Connections Sort & Filter Data Tools D5 SUM(D3:D4) Total Unit cost Total Cost 47000 1 Quarter 6000 12000 20000 9000 5500 55005500 3 Regular Hours 4 Overtime Hours 5 Total Available Hours 6 Undertime 7 Overtime C 5500 17 5500 5500 5500 5500 1100 1100 1100 1100 9 Hired 10 Fired 6000 264000 3200 1400 Total Cost 12 13 15 17 19 20 21 Sheet1 Ready Display Settings ®凹 + 100% Type here to search ENG US 12:52 PM 07-Apr-19 ^ x

Below is the screenshot of the formula applied.

Book1 - Excel Abhishek Rai -O × File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do Flash Fill - Remove DuplicatesRelationships Show Queries Consolidate &Group . +-| |-Data Analysis What-If ForecastUngroup Solver Analysis . Sheet Subtotal From Table Get External Nevw DataQuery zI Sort Filter Recent SourcesEdit Links Advanced Tex to Columns co Data Validation Get & Transform Connections Sort & Filter Data Tools D16 1 Quarter Total Unit cost Total Cost 6000 12000 9000 SUM(C2:F2 3 Regular Hours 4 Overtime Hours 5 Total Available Hours 6 Undertime 7 Overtime C -C8*500 -E8 500 F8 500 SUM(C4:F4 17 -H4 G4 SUM(C3:C4 SUM(D3:D4 -SUM(F3:F4 MAX(0,(C3-C2)) MAX(0,(D3-D2) MAX(0,(E3-E2)) MAX(0,(F3-F2 C3 0.2 SUM(C6:F6 F3 0.2 E8+F9+-F10 D3 0.2 -E3*0.2 11 -B8+C94-C10 =C8+D9+, D 10 9 Hired 10 Fired =SUM(C9:F9 -SUM(C10:F10 6000 3200 1400 2H10-G10 Total Cost UM< 14:110 12 13 15 16 17 19 20 Sheet1 Ready Display Settings ®凹 + 100% Type here to search ENG US 12:52 PM 07-Apr-19 ^ x

Below is the screenshot of the LP solver -

Book1 Excel Abhishek Rai Review View Help Tell me what you want to do ! 7 Flash Fill File Home Insert Page Layout Formulas olver Parameters how Queries へ 8-l Remove Du | Columns Data Validat From T Get External Ne Ted to Set Objective SIS11 Querye Recent Sources AlEdrt LinlsortFiterppy Advanced Data Get & Transform Connections Sort & Filter SUM(D3:D4) By Changing Variable Cells: SCSA:SFSA,SCS9 ScS10 Total Unit cost Total Cost 47000 1 Quarter Subject to the Constraints: 6000 12000 20000 9000 5500 55005500 3 Regular Hours 4 Overtime Hours 5 Total Available Hours 6 Undertime 7 Overtime C 5500 17 SCS9 SCS10 integer 5500 5500 5500 5500 1100 1100 1100 1100 6000 264000 3200 1400 Beset All 9 Hired 10 Fired Total Cost i 264000 Make Unconstrained Variables Non-Negative 12 13 Select a Solving Simplex LP Ogtions 15 Soving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. 17 19 20 21 Sheet1 Display Settings ®凹 Type here to search ENG 1251 PM US 07-Apr-19 ^

Below is the result -

Book1 - Excel Abhishek Rai -O × File Home Insert Page Layout Formulas Review View Help Tell me what you want to do (.DFrom Quenes DDCornections Rem ve Duplicates-consolide. Consolidate Relationships 相GroupupA 2↓TE T ', RIepp y AdvancedComnsData Vaidation - 면 &Group . +-| |-Data Analysis 2. DheAnalys s Ib E What-If ForecastUngroup Solver Analysis . Sheet Subtotal From Table H Remove Duplicates Get External Nevw zI Sort Filter Recent SourcesEdit Links DataQuery Get & Transform Connections Sort & Filter Data Tools Total Unit cost Total Cost 47000 1 Quarter 6000 12000 20000 9000 1700017000 17000 17000 3 Regular Hours 4 Overtime Hours 5 Total Available Hours 6 Undertime 7 Overtime C 3000 17 51000 1700017000 20000 17000 11000 5000 3400 34003400 24000 136 9 Hired 10 Fired 6000 816000 3200 73600 1400 23 23 Total Cost 12 13 15 17 19 20 21 Sheet1 Ready Display Settings - + 100% Type here to search ENG 1251 PM US 07-Apr-19 ^

Total Cost without Undertime = 940600

Total Under time hours = 24000

Total Undertime Cost = 24000*12 = 288000

Total Cost = 940600 + 288000 = 1228600