Overview Assignment 2: Excel Simulation Decision Modeling ✓ Solved

Overviewassignment 2 Excel Simulationdecision Modelingelevator Ass

Overview Assignment (2): Excel Simulation/Decision Modeling Elevator Assignment for Fairview Tower In this assignment, you are asked to create an Excel model that automatically works out Fairview Tower’s elevator assignment when a mission is requested based on the elevators’ location, heading, and destinations. The detailed requirements are listed below. It is due 5/21/2020 (Thursday) before class. Your work must be a single Excel file named IS300FairviewFirstNameLastName.xlsx (or .xls) , submitted via Canvas by the due date. Late submission will not be accepted.

This assignment counts 10% of your final grade. You are encouraged to discuss it with each other, but all submitted work should be individual effort. Background Fairview Tower1 is a new residential and office building located in downtown Seattle. It has 46 floors – 43 above the ground (numbered 0 to 42) and 3 underground (numbered -1 to -3) – that are serviced by four elevator units (labeled A through D). Unit A, B, C are regular passenger elevators while Unit D is a service elevator that can also be used to move passengers if needed – more on that later.

Each time a ride is requested, an algorithm will decide which elevator unit is assigned to pick up the ride following a set of rules. You are asked to work out an automatic assignment application for Fairview’s management team. The Excel file IS300FairviewTemplate.xlsx (available for download on Canvas) simulates the building and the elevator units. Each unit has a location number, meaning the floor it is currently at, and a destination number, meaning the floor it is going to. A unit is moving when the destination number is different from its current location, and a unit is idling if the two numbers are the same.

In the Excel file, you can see how they move floor-by-floor by pressing the F9 key to force recalculation of the functions (or Fn+F9 on a Mac). A ride (called a mission) can be requested by a code consisting of a number and the letter “U” or “D” (e.g., 23D). The number means the floor where the mission is requested, and the letter means the direction of the mission: “U” means going up and “D” means going down. For example, mission 23D means a ride of going down is requested on the 23rd floor. Grading Download and rename the IS300FairviewTemplate.xlsx file, then enable iterative calculation on the file and set the max iteration to 1 (the option can be found by going to File – Options – Formulas on a PC, or Excel – Preferences – Calculation on a Mac).

Create additional worksheets to build your model but on the Fairview worksheet you must preserve its structure and only edit the green cells. The INITIATE row is used to initialize the elevators to a starting state for testing purpose. Your file should automatically fill the ASSIGNMENT cell (Cell B12) with the letters “A”, “B”, “C”, “D”, or “N” (meaning none) when a mission is entered in cell B10 (the floor) and D10 (the direction). The assignment rules are:

• No Mission (2 points): If no mission or an incomplete mission is entered (i.e., missing floor number or direction), your model should display an empty cell (i.e., “”) in B12.
• First Priority (2 points): If an elevator is idling at the request floor, assign the mission to it. If multiple elevators are idling at the request floor, randomize which one to assign except that Unit D (the service elevator) should get the last priority, i.e., D gets the mission if it is the only one idling.
• Second Priority (4 points): If no elevator is assigned after 2), find and assign the unit that is closest to the request floor. Only consider units that are either a) idling, or b) moving towards the request floor in the same direction as the mission’s direction. Do not assign units moving away or moving against the mission direction. If multiple units qualify, randomize the selection but prioritize choosing from A, B, or C before D if applicable.
• Otherwise (2 points): If still no assignment, display “N” in cell B12; the mission is not picked up yet. It will be assigned later as units become available.

Start with no mission and work your way through the rules to develop your model. Use functions like IF, ABS, MAX, SUM, and RAND, and other functions as needed. Test your model in different scenarios to ensure accuracy and adherence to the rules. Follow the provided hints to create indicator labels for each elevator’s status, direction, and movement to help with logic. Use the steps below for testing:

1. Input an incomplete mission without floor number or direction, confirm assignment cell remains blank.
2. Set all elevators to the first floor using INITIATE, then request a 1D mission; check assignment randomness among A, B, or C.
3. Set all elevators to the 20th floor, then move select units up or down, and request a 10D mission; verify assignment of C.
4. Set all elevators to the 25th floor, then move units to different floors, request 25D; verify that assignment is “N”.