Question: Formulate and solve the following problem using Excel A unit operating 24 hours a day and assigns 8 hour shifts to employees. The requirements for

Formulate and solve the following problem using Excel

A unit operating 24 hours a day and assigns 8 hour shifts to employees. The requirements for each hour during the day are shown in the Excel Workbook H2_4. Employee shifts can start at any hour of the day and go for 8 hours without a break. The normal wage is $ 10 per hour with an hourly wage of $ 15 per hour for shifts starting from 5:00 P.M. to 5:00 A.M. (both included). Formulate and solve an optimization program to develop a staffing plan using a spreadsheet, minimizing the total wage cost the meets staffing requirement.

From	To	Demand
Midnight	1 A.M.	15
1 A.M.	2 A.M.	15
2 A.M.	3 A.M.	12
3 A.M.	4 A.M.	12
4 A.M.	5 A.M.	16
5 A.M.	6 A.M.	18
6 A.M.	7 A.M.	24
7 A.M.	8 A.M.	26
8 A.M.	9 A.M.	30
9 A.M.	10 A.M.	32
10 A.M.	11 A.M.	32
11 A.M.	Noon	28
Noon	1 P.M.	30
1 P.M.	2 P.M.	26
2 P.M.	3 P.M.	28
3 P.M.	4 P.M.	34
4 P.M.	5 P.M.	32
5 P.M.	6 P.M.	40
6 P.M.	7 P.M.	42
7 P.M.	8 P.M.	36
8 P.M.	9 P.M.	32
9 P.M.	10 P.M.	28
10 P.M.	11 P.M.	24
11 P.M.	Midnight	18

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!

Krusty Burger is considering letting go of its kitchen personnel in exchange for automated machines which could prepare orders in less time, with excellent quality, and better consistency. The new...

There are three Tasks and Please Make sure Task 2 is veri important. We need actual excel file's screenshot with formulars and asnwers.Thanks Task 1. Describe the problem, then formulate the problem...

Project. Bank Workforce Planning . Mexico City Bank of Commerce and Industry is a busy bank that needs a different number of tellers at different times of working due to a different customers' needs....

create a value stream map for the mailroom categories 1-3 (page 6). Start your value stream map woth delivery of the mail to ING. . Additional information: Assume 1000 pieces of mail for your...

In healthcare delivery systems, laboratory services play a vital role in inpatient care. Studies have shown that laboratory data affects approximately 65% of the most critical decisions on admission,...

Sundar G. Bharadwaj, P. Rajan Varadarajan, & John Fahy Sustainable Competitive Advantage in Service Industries: A Conceptual Model and Research Propositions The purpose of competitive strategy is to...

Instructions 10% Deduction for Each Day Late Create a current state map for the mailroom operation for categories 1-3 (page 6). Start your value stream map with delivery of the mail to ING. ....

MIT drives a hard bargain when it comes to commercializing its patents. In this case, MIT will require an annual licensing fee of $2 million plus a royalty of 10% of the wholesale price of every...

.I SELECTED ! Schedule D:Net Long-Term Capital Gains or Losses only its a team work. I need in 3-4 just just scehdule D I. Title: Preparation of a Corporate Income Tax Return Using IRS Form 1120 II....

Changes in various accounts and gains and losses on the sale of assets during the year for Argon Company are given below: Required: Prepare an answer sheet using the following column headings: For...

Rolo Enterprises is a retailer of household appliances, operating with a mark-up of 20%. Their operations for the month of June 2023 were affected by heavy rains, which damaged a portion of their...

under the perpetual inventory system, which accounts should be debited each time a sale on account is made?

Case Greenbuilders Pte Lid ("GPL") is a local construction company which specialises in providing bespoke Design and Build construction services for private residential homes that are designed to be...