Tutorial 08 case5 expansion-instructions
ICT (FEB11013)
Erasmus Universiteit Rotterdam
Aanbevolen voor jou
Reacties
Preview tekst
Warning: TT: undefined function: 32
ICT Tutorial 8 Excel 3
Open the spreadsheet. When you are done answering the questions, save the file and check your answers in the online learning environment ese-ict and try to correct your errors. If you have questions you can ask the teaching assistant but you need to try to find your mistake first on your own. Finally, email the solution file to yourself in case you want to use it again for practice. Make sure you fill in the answers in the cell that is stated in the question because otherwise the program will not check your answers. Also make sure that you fill in a formula and not a numeric number. If you want to use the value in B2 for example in a formula in D10 then you use the reference B2 in the formula and not the value within B2. When construction a formula with references think about mixed and absolute references, this saves a lot of time. Finally, when you are asked to use a specific formula or when there are formulas in the book for the specific situation use them!
** Important** Save your answers and close the excel file before you check your file. If you want to check your answers during the tutorial make sure you finish the whole case and not partly! The program checking your answers can take a bit of time so wait 30 to 40 seconds if it does not respond immediately. The questions marked with X cannot be checked with the program so ask your TA if the answer is correct. The program could give al false negative so if you think your answer is correct ask your TA! It is possible that you come up with a different approach that is not correctly graded by the program.
Good luck and save often!
Case 5: Ringold Hospital Expansion Project
You are the chief financial officer at Ringold County Hospital. With the closure of nearby hospitals, your hospital is planning an expansion to handle the increased demand. Given a budget of $750,000, you must determine the number of private and double rooms and the number of beds to add along with the number of each product to order. All beds need an overbed table and television. The hospital needs to add at least 10 private rooms, with the rest of the new rooms for double occupancy. At least half of the bed need rails, at least a quarter of the beds need a patient lift, at least 60% of the beds need a patient monitor, and at least 20% of the beds need a vital sign monitor. The hospital needs one blood pressure device for at least 20% of the beds and one therapeutic ultrasound device for at least 5% of the beds. The hospital needs at least 50 beds to meet the increased demand. Your staff started a worksheet that contains cost inputs. Use the Ringold Hospital Expansion sheet. ** Tab the box “Make Unconstrained Variables Non-Negative” inside the solver! **
X a) Enter formulas to calculate the product totals in D10:D18 and the total private and double room costs in B5:B6. Enter other input values provided in the information above. Create the Solver model to set an objective to calculate the minimum total cost possible by changing the units to order and the number of private and double rooms.
Set the following constraints properly:
X b) Total cost;
X c) Number of beds, overbed tables, and televisions match the total beds and the appropriate unit orders for rails, patient lifts, patient monitors, vital sign monitors, blood pressure devices, and ultrasound devices based on their respective percentage of total beds;
X d) Number of private rooms;
X e) Number of total beds needed;
X f) Integer constraints as needed.
g) Run solver and keep the results.
Tutorial 08 case5 expansion-instructions
Vak: ICT (FEB11013)
Universiteit: Erasmus Universiteit Rotterdam
