If someone can show the formulas and or step by step on excel using solver for the questions below that would be great!

The purpose of this assignment is to apply a waiting line model to a business service operation in order to recommend the most efficient use of time and resources.

(This assignment has been adapted from Case Problem 2 in Chapter 15 of the textbook.)

Use the information in the scenario provided to prepare a managerial report for Office Equipment, Inc. (OEI).

Scenario

Office Equipment, Inc. (OEI) leases automatic mailing machines to business customers in Fort Wayne, Indiana. The company built its success on a reputation of providing timely maintenance and repair service. Each OEI service contract states that a service technician will arrive at a customers business site within an average of 3 hours from the time that the customer notifies OEI of an equipment problem.

Currently, OEI has 10 customers with service contracts. One service technician is responsible for handling all service calls. A statistical analysis of historical service records indicates that a customer requests a service call at an average rate of one call per 50 hours of operation. If the service technician is available when a customer calls for service, it takes the technician an average of 1 hour of travel time to reach the customers office and an average of 1.5 hours to complete the repair service. However, if the service technician is busy with another customer when a new customer calls for service, the technician completes the current service call and any other waiting service calls before responding to the new service call. In such cases, after the technician is free from all existing service commitments, the technician takes an average of 1 hour of travel time to reach the new customers office and an average of 1.5 hours to complete the repair service. The cost of the service technician is $80 per hour. The downtime cost (wait time and service time) for customers is $100 per hour.

OEI is planning to expand its business. Within 1 year, OEI projects that it will have 20 customers, and within 2 years, OEI projects that it will have 30 customers. Although OEI is satisfied that one service technician can handle the 10 existing customers, management is concerned about the ability of one technician to meet the average 3-hour service call guarantee when the OEI customer base expands. In a recent planning meeting, the marketing manager made a proposal to add a second service technician when OEI reaches 20 customers and to add a third service technician when OEI reaches 30 customers. Before making a final decision, management would like an analysis of OEI service capabilities. OEI is particularly interested in meeting the average 3-hour waiting time guarantee at the lowest possible total cost.

Managerial Report

Develop a managerial report summarizing your analysis of the OEI service capabilities. Make recommendations regarding the number of technicians to be used when OEI reaches 20 and then 30 customers, and justify your response. Include a discussion of the following issues in your report:

1. Do you agree with OEI management that one technician can meet the average 3-hour service call guarantee? Why or why not?
2. What is your recommendation for the number of service technicians to hire when OEI expands to 20 customers? Use the information that you developed in Question 4 (above) to justify your answer.
3. What is your recommendation for the number of service technicians to hire when OEI expands to 30 customers? Use the information that you developed in Question 4 (above) to justify your answer.
4. What are the annual savings of your recommendation in Question 6 (above) compared to the planning committee's proposal that 30 customers will require three service technicians? (Assume 250 days of operation per year.) How was this determination reached?