MEM407
Homework 4
Some of the problems below require you to use Excel Solver, so you must submit a spreadsheet model for each. You should also include some brief text for each problem that interprets or explains the solution. Please include these explanations in a text box within the spreadsheet.
1. A hotel has 100 rooms and two per-night fares: $150 and $250 for leisure and business customers, respectively. The probabilities of daily demands of each type of customers are available in Tables 1 and 2 (Note: both probabilities are assumed independent). The manager of the hotel wants to apply a policy of “nested bookings”. That is, a number of rooms are exclusively reserved for business customers. Once the number of leisure customers reaches a certain limit, any subsequent leisure booking is rejected. Of course, business bookings are always accepted (unless the hotel is at its full capacity). Leisure bookings are assumed to arrive first, i.e., when business bookings arrive leisure bookings have already been decided.
(a) Suppose currently the manager has reserved 15 rooms for business customers. Develop a simulation model to find the following measures of performance of this booking system:
the expected number of leisure rooms used per night. the expected number of rejected leisure bookings per night.
the expected number of business bookings.
(b) Develop a simulation model (can use the same as in part (a)) to find the optimal reservation size (i.e., the number of rooms to be reserved for business which maximizes expected profit).
Demand Probability
74 0.075
75 0.011
76 0.089
77 0.034
78 0.075
79 0.028
80 0.059
81 0.021
82 0.016
83 0.091
84 0.094
85 0.051
86 0.062
87 0.037
88 0.064
89 0.067
90 0.078
91 0.048

Demand Prob
20 0.013
21 0.076
22 0.031
23 0.062
24 0.082
25 0.011
26 0.022
27 0.102
28 0.054
29 0.023
30 0.102
31 0.082
32 0.03
33 0.094
34 0.055
35 0.05
36 0.004
37 0.107
Table 1: Leisure distribution Table 2: Business distribution
1
2. (a) Allbest Airlines planes require repairing an average of 200 times per year and a single repairman takes one week to fix. Assuming the times between breakdowns and repairs are exponential, how many repairmen are needed to ensure that there is at least a 95% chance that 2 or fewer planes are being repaired? All repairmen work in a single plane until it is fixed. (assume 1 year = 52 weeks)
(b) From the time a request for data is submitted until the request is fulfilled, a database takes an average of 3 seconds. We find that the database is idle around 20% of the time. Answer the following questions, assuming that the database can be modeled as an M/M/1 system.
i. With these two values, compute ? and µ.
ii. What is the average service time per database query? iii. What is the average number of queries in the system? iv. What is the probability that 5 or more queries are present?
3. Some universities allow an employee to put a fixed amount of money, q, into an account at the beginning of each year to be used for child-care expenses. The amount q is not subject to federal income tax. Once this amount is put into the account it cannot be accessed during the year, except for child-care purposes. Assume that all other income is taxed by the federal government at a 40% rate.
(a) If child-care expenses for the year (call them d) are less than q, the employee in effect loses q-d dollars in before-tax income. Express in terms of q and d how much money is lost in after-tax income.
(b) If child-care expenses exceed q, the employee must pay the excess out of his or her own pocket but may credit 25% of that as a savings on his or her state income tax. Express in terms of q and d how much money is lost by under-stocking (that is, setting aside too little money).
(c) Suppose a professor believes that her child-care expenses for the coming year will be according to Table 3. Provide a recommendation to the professor on how much money she should place in the child-care account, in order to minimize her expected cost. Use simulation to support this recommendation.
Expense Probability
2000 0.14
2500 0.2
3000 0.12
3500 0.02
4000 0.13
5000 0.21
6000 0.15
7000 0.03
Table 3
4. Food-for-Thought is attempting to determine how many servers (or lines) should be available during the breakfast shift. During each hour, an average of 100 customers arrive at the restaurant. Each line or server can handle an average of 50 customers per hour. A server costs $5 per hour, and the cost of a customer waiting in line for 1 hour is $20. Assuming that an M/M/s model is applicable, determine the number of lines that minimizes the sum of delay and service costs. Assume that at most 5 servers can be available. Computing these values in an Excel spreadsheet might come in handy here.
2