Operations Management Assignment Answer

Laboratory Project : Stochastic Model of Traffic Signal Operation
Numerical Modeling & Risk Analysis

Purpose: The purpose of this project is to determine the best operation of a traffic light using the concepts of stochastic modeling including both simulation and optimization. Participants will gain experience in random number generation, use of probability distributions and calculation of probability of failure. Participants will also further their knowledge of Excel by creating custom VBA macros and a user form as part of the development of the model.

Problem Description: Suppose that you are an intern with a traffic-engineering consulting firm. The consulting firm has been hired to determine the minimum green time required to pass the traffic that approaches an intersection from a particular direction without creating queues. The direction of interest has two lanes. The firm has already conducted a field study where actual traffic volumes were observed. They measured the total volume of traffic as it approached the light including both lanes in vehicles/cycle for 30 cycles of the traffic signal. Each cycle (including the time when the light is red, amber and green) is 120 sec long and the current green time is 36 sec. They estimated that the traffic is about equally divided between the two lanes. They also measured vehicle headways in each lane. Headway is measured as the time between two consecutive vehicles when they cross the intersection. Your supervisor has asked you to create a model that finds the minimum green time not only for the observed traffic data but also for data that are generated from either a normal or gamma distribution with the same mean and standard deviation as the observed traffic data.

EXCEL Topics: This laboratory project will require the participant to use the following features in Excel:

statistical functions
data validation
logical IF tests
user controls
table lookup functions (INDEX)
dynamic graphs
the Solver tool
recorded VBA macros
custom VBA macros and
VBA user forms

Requirements:

Construct your stochastic model. Use the supplied Excel template as a starting point. Much of the basic formatting and layout has already been done for you. The template also includes the traffic volume and headway observations. Your completed model should have all the calculations and formatting shown in the companion PowerPoint presentation. The main requirements for each page are listed below:

Introduction Page. The introduction page should include your name and lab section. It should also describe the purpose of the model as well as step by step instructions for the user that describe how to implement your model.
Interface Page. The interface page should include all the controls to operate the model as shown in the PowerPoint presentation. A combo box should allow the user to determine whether the observed data or the generated data should be used and another combo box should allow the user to select the type of distribution that should be used to generate the data. Cell D11 should have a dropdown list of possible fractions of the total approach volume that is in the right lane. Use data validation to make this list. These values should be: 0.10, 0.25, 0.40, 0.50, 0.60, 0.75 and 0.90. Cell C11 should compute the fraction of traffic that is in the left lane based upon the fraction in the right lane. A check box below these cells should allow the user to specify whether queues are to be balanced in the model. The user should be able to enter the amount of green time into cell H11 and set the green time with a scroll bar that ranges from 5 to 115 sec. The maximum queues that occur in each lane should be reported in cells C20 and D20 and the probability of failure should be calculated for each lane and reported in cells C24 and D24 (you might find Excels COUNTIF function useful for this task). The Interface page should also have two dynamic graphs that are connected to a set of list boxes. The same set of list boxes controls both graphs. One graph plots the data for each light cycle (make sure the y-axis label is dynamic on this graph). The other graph plots the quartiles of the same data in a box-and-whisker format. Using one list box, the user should be able to select the lane whose data is to be plotted. For the selected lane, the user should be able to use the other list box to plot: vehicles arriving, total vehicles, headway (sec), maximum departures, vehicles departing, or queues.
VBA Macros. The interface page should include four buttons that run VBA macros. The first macro should generate random numbers and write the output into the Generated Data Calculations table on the Inputs page. These numbers will then be used to generate the traffic volume and headway numbers in each lane (see Model page description below). Your macro should generate different numbers each time it is run. The second macro should run Solver to determine the minimum green time that allows all traffic to pass without creating queues. Possible green times should be restricted to integers. Also, note that Solver can sometimes have trouble finding a solution. To avoid this problem, set the green time to 105 sec in your macro before running Solver. The third macro should save the key results to the Results page. The Results should be saved below any results that are already in the table. If the observed data is used to generate the results, then the Data Source column on the Results page should list Observed. If randomly generated data is used, then the column should list Generated Normal or Generated Gamma, depending on the distribution that was used. The fourth macro is a super macro that iterates 10 times. For each iteration, the macro should generate a new sample, calculate the optimal green time and write the results to the Results page. When the super macro is complete, it should notify the user with a message box as shown in the PowerPoint presentation.
Userform. The Interface page should also include a button or image that displays a userform. The userform should include all of the input capabilities and buttons shown in the PowerPoint presentation. Two combo boxes should be included that allow the user to change the data source (either observed data or generated data) and the type of distribution (either normal or gamma). These combo boxes should be completely linked with the spreadsheet. When they are first displayed they should show the values that are already entered into the spreadsheet. When the user uses the combo boxes to change the values, they should be updated on the spreadsheet as well. The second combo box (the distribution type) should only be displayed if generated data has been selected in the spreadsheet or in the first combo box. Also, two text boxes on the userform should allow the user to change how the traffic is divided between the two lanes. These text boxes should be completely linked with cells C11 and D11 on the Interface page. They should initially display the values from cells C11 and D11 and they should update those cells when they are changed. The user should only be able to type in the right lane text box. The left lane text box should be calculated automatically. If the user types a value below zero or greater than one in the right lane text box, a message box should be displayed that informs the user of the allowable range and discards the typed value. You might find that the macro type TextBox2_AfterUpdate works better than TextBox2_Change for this purpose because it is executed after the user completely finishes editing the textbox. The userform should also include buttons that allow the user to run the macros that generate the random numbers, find the minimum green time, save the results and run the supermacro. An exit button that closes the userform should also be included.
Inputs Page. The table at the far right already contains the available traffic and headway data. Use the table above this data to calculate the mean, standard deviation, alpha and beta, which will be used to generate additional samples from the probability distributions. The table entitled Generated Data Calculations should be used to generate news samples of traffic data. Be sure that the random numbers are generated using VBA (not Excels RAND function). The type of distribution should be determined from the users selection on the Interface page. The table entitled Data to be Used in the Model should reference either the generated data or the observations, depending on what the user has selected on the Interface page. The data in columns L and D should be rounded. Make sure that the traffic in the two lanes (columns D and E) sum exactly to the total traffic (column C).
Model Page. The model page should link to the information on the Interface and Inputs pages. The traffic flow calculations should be completed for both the left and right lanes. Some headings have comments that describe the contents of the columns and all the formulas for the model can be found in the PowerPoint presentation. Note that cars can pass through the intersection during the green and amber intervals. The maximum queue lengths for each lane should be linked to the appropriate cells on the Interface page and the queues should be used to calculate the probability of failure (if a queue occurs at the end of a given light cycle, then that light cycle failed). Conditional formatting should be used as shown in the PowerPoint presentation to highlight light cycles where queues occur. A row should be highlighted in the left lane table if the left lane has a queue at the end of that cycle. Similarly, the right lane row should be highlighted if that lane has a queue at the end of the cycle. A row in the Cycle column should be highlighted if either lane has a queue. A scrollbar should also be included on the Model page as shown in the PowerPoint presentation that allows the user to change the green time without returning to the Interface page.
Results Page. The table on this page should store the key results a given simulation. A row in this table is filled when the Save Results macro is run. You should also use freeze panes to keep the column headings visible at all times. This page should contain an icon or button with a Clear Results macro that erases the data in the table. The macro should require confirmation from the user before deleting the data as shown in the PowerPoint presentation.

Once your spreadsheet is complete and you have tested it to make sure it is working properly, use the model to determine the probability of failure in each lane if the observed data are used, the traffic is divided 0.50-0.50 between the lanes, queue balancing is on and the green time is 36 sec. Recall that this is the existing situation at the light. Repeat this same analysis, but calculate the average probability of failure in each lane for 10 simulations with a normal distribution and then 10 simulations with a gamma distribution. Save all of the model results on the Results page and copy them to the Requirement 2 section of the Conclusions page. Also, store your final answers in the small table under Requirement 2 on the Conclusions page.
Use your model to find the minimum green time required for the observed dataset and the average minimum green time for 10 simulations of both of the distributions based upon a 0.50-0.50 split of traffic between the lanes with lane balanced turned on. Save the model results on the Results page and copy them to the Requirement 3 section of the Conclusions page. Store your final answers in the small table under Requirement 3 on the Conclusions page.
Next, use your model to find the minimum green time required for the observed data and the average minimum green time for 10 simulations of each distribution based upon a 0.25(left)-0.75(right) split of traffic between the lanes with lane balanced turned on. Save these results on the Results page and copy them to the Requirement 4 section of the Conclusions page. Store your final answers in the small table under Requirement 4 on the Conclusions page.
Repeat the analysis in Requirement 4 with lane balancing turned off and save the results as described in the previous requirements.
Upload your completed spreadsheet model in .xlsm format with the results of Requirements 2-5 saved on the Conclusions page.