Reference no: EM133016909

Q1. SOP in Service Sector
"PPS Airline is doing aggregate planning of flight attendant staffing for the next 6 months. Demand is shown in Row 8 as the ""Attendant Hours Needed"".

PPS Airline wishes to come up with a staffing plan for the months of November to April - specifically, they want to determine how many new attendants to hire each month.

Each trained attendant supplies 150 hours per month. A newly hired attendant is called a trainee during the first month, and each trainee's net contribution is negative (-100 hours) as they require supervision, which detracts from the productivity of other attendants.

Each trained attendant costs $1,500 in salary and benefits per month, and each trainee costs $700. Normal attrition (resignations and dismissals) in this occupation is high - 10% per month - so PPS Airline never has any planned layoffs. Trainees are hired on the first day of each month and become attendants on the first day of the next month (with no attrition for these). As of November 1, PPS Airline has 60 trained attendants."
"Requirements: Please use Solver to find the best plan which results in the lowest total staffing cost. You need to (1) input formulas to the yellow cells in Rows 9, 11, 12, and 14, and (2) take a screen shot of your Solver window and paste it to cell J6.
Hints for setting Solver parameters: (1) What is the objective function?
(2) What is/are the decision variable(s)?
(3) What are the constraints?
(4) Please guarantee that ""Trainees Hired"" must be integers. For ""Trained Attendants"", do NOT round the numbers to the nearest integers. "
Requirements: Fill in the following Master Production Schedule with the projected available balance and the available-to-promise sections, then answer the questions below. Note that the MPS is provided as given information here. In other words, you do NOT need to determine the MPS schedule in Row 10.
a) Is there anything on the master schedule that should be of great concern? Why, and what should you do?

b) A customer wants 8 of the units in period 4. What should you tell them and why?

c) After you satisfy the customer who wants the 8 units in period 4, another customer wants 16 units in period 5. What do you tell them and why?

d) Suppose the forecast for period 12 increases from 30 up to 75 units because of a possible marketing campaign. What should you do and why?

Attachment:- Assignment.rar