Case Study- 1: Showcase Scheduling at Fred Astaire East Side Dance Studio The Fred Astaire East Side Dance Studio in New York City presents two ballroom showcases a year. The studio wanted a cheap, user-friendly, and quick computer program to create schedules for its showcases that involved heats lasting around 75 seconds and solos lasting around 3 minutes. The program was created using an integer programming optimization model in Visual Basic and Excel. The employees just have to enter the students' names, the types of dances the students want to participate in, the teachers the students want to dance with, how many times the students want to do each type of dance, what times the students are unavailable, and what times the teachers are unavailable. This is entered into an Excel spreadsheet. The program then uses guidelines provided by the business to design the schedule. The guidelines include a dance type not being performed twice in a row if possible, a student participating in each quarter of the showcase in order to keep him/ her active throughout, all participants in each heat performing the same type of dance (with a maximum of seven couples per heat), eliminating as many one-couple heats as possible, each student and teacher only being scheduled once per heat, and allowing students and teachers to dance multiple times per dance type if desired. A two-step heuristic method was used to help minimize the number of one-couple heats. In the end, the program cut down the time the employees spent creating the schedule and allowed for changes to be calculated and made quickly as compared to when made manually. For the summer 2007 showcase, the system scheduled 583 heat entries, 19 dance types, 18 solo entries, 28 students, and 8 teachers. This combination of Microsoft Excel and Visual Basic enabled the studio to use a model-based decision support system for a problem that could be time-consuming to solve. Case Study- 2: Spreadsheet Model Helps Assign Medical Residents Fletcher Allen Health Care (FAHC) is a teaching hospital that works with the University of Vermont's College of Medicine. In this particular case, FAHC employs 15 residents with hopes of adding 5 more in the diagnostic radiology program. Each year the chief radiology resident is required to make a yearlong schedule for all of the residents in radiology. This is a time-consuming process to do manually because there are many limitations on when each resident is and is not allowed to work. During the weekday working hours, the residents work with certified radiologists, but nights, weekends, and holidays are all staffed by residents only. The residents are also required to take the "emergency rotations," which involve taking care of the radiology needs of the emergency room, which is often the busiest on weekends. The radiology program is a 4-year program, and there are different rules for the work schedules of the residents for each year they are there. For example, first- and fourth year residents cannot be on call on holidays, second-year residents cannot be on call or assigned ER shifts during 13-week blocks when they are assigned to work in Boston, and third-year residents must work one ER rotation during only one of the major winter holidays (Thanksgiving or Christmas/ New Year's). Also, first-year residents cannot be on call until after January 1, and fourth-year residents cannot be on call after December 31, and so on. The goal that the various chief residents have each year is to give each person the maximum number of days between on-call days as is possible. Manually, only 3 days between on-call days was the most a chief resident had been able to accomplish. In order to create a more efficient method of creating a schedule, the chief resident worked with an MS class of MBA students to develop a spreadsheet model to create the schedule. To solve this multiple-objective decision-making problem, the class used a constraint method made up of two stages. The first stage was to use the spreadsheet created in Excel as a calculator and to not use it for optimizing. This allowed the creators "to measure the key metrics of the residents ' assignments, such as the number of days worked in each category. " The second stage was an optimization model, which was layered on the calculator spreadsheet. Assignment constraints and the objective were added. The Solver engine in Excel was then invoked to find a feasible solution. The developers used Premium Solver by Frontline and the Xpress MP Solver engine by Dash Optimization to solve the yearlong model. Finally, using Excel functions, the developers converted the solution for a yearlong schedule from zeros and ones to an easy-to-read format for the residents. In the end, the program could solve the problem of a schedule with 3 to 4 days in between on calls instantly and with 5 days in between on calls (which was never accomplished manually).

Based on the above case studies discussion complete the following tasks in class or home given exercise:

1. Using Microsoft excel design a car loan calculator based on the following conditions:
   1. Define at least 5 different cars with different prices
   2. User may select car and related price should appear automatically in the next cell.
   3. Financing amount can be 50% to 100%
   4. Interest rate is fixed 5% on the financing amount.
   5. Use can opt the installment duration from 12 months to 60 months.

Calculate and display the monthly installment based on the number of months opted by the user and financing amount + interest rate