I. 341. Most large cities have extensive public transportation networks. Many vehicles have been converted to use electricity because of environmental protection. However, a vehicle operating on a dedicated track can sometimes be disadvantageous because, for example, a malfunctioning tram can disrupt the traffic, or one vehicle cannot overtake the other.
Your task is to simulate a tram transportation system.
Since there can be large fluctuations in the traffic or in the number of travellers even during one day, our model is restricted to a simpler homogeneous time period.
The file megallo.txt obtained from the city transportation company contains data for each station such as
the travel time between consecutive stations , provided that the traffic is smooth;
the maximal extra time needed to cover the distance between two consecutive stations due to traffic lights or heavy traffic, during the corresponding time period in the last year (tplusz);
the average number of passengers arriving at the actual station in every minute to get on the vehicles (fel);
the percentage of the passengers getting off at the actual station (lesz).
Besides the above, we know the starting time of the first vehicle (for example, 8:00), the time difference between consecutive vehicles (for example, 0:10), finally, the time for a passenger to get on or off a vehicle (for example, 0:00:01). Other factors that would modify the schedule are neglected in this task.
Create a sheet to investigate 5 consecutive vehicles in the following way:
a station sheet should contain the data from the file megallo.txt;
a test sheet should compute the following data for each vehicle and each station: the arrival time, the number of people getting off the vehicle, the number of people getting on the vehicle, the total time to get off and on, and the departure time;
o the number of people getting on the first vehicle should be based on the time difference between consecutive vehicles, while the number of people getting on the later vehicles should be based on the time elapsed since the arrival of the last vehicle;
o the extra time between the stations can be any random number between 0 and the given maximal extra time;
o the number of people getting off and on can randomly deviate from the given values by at most 20 percent (calculations with non-integer number of passengers are also allowed);
o take into consideration (when dealing with the arrival times) that a vehicle cannot overtake another vehicle;
create a diagram to display the time difference between consecutive vehicles for each station with respect to the initial time difference;
your solution should recompute the appropriate data if changes are made in the starting times, in the time differences, in the passenger exchange rates, or in other station properties.
After creating your sheet, try and determine the modification of which parameter (or parameters) the system is the most sensitive to.
Your sheet (i341.xls, i341.ods, ...) containing the solution should be submitted containing a short documentation (i341.txt, i341.pdf, ...) together with a description about parameter dependence and sensitivity, and the name and version number of the spreadsheet application used.
Downloadable file: megallo.txt
Deadline expired on 10 March 2014.
Google Translation (Sorry, the solution is published in Hungarian only.)
A feladat megoldását 6 fő készítette MS Excel (2010 vagy 2013), 4 fő LibreOffice (4.1 vagy 4.2) programmal, egyikük pedig Kingsoft Office-t használt.
A feladat nem volt nagyon nehéz, de nagy figyelmet igényelt. Sokan egy-egy részletnél voltak figyelmetlenek, mások pedig az alkalmazott függvényeket nem ismerték kellőképpen (pl. perc(), randbetween())
Abszolút kifogástalan megoldás nem is született, minden részletében elfogadható is csak egy, Gercsó Márk munkája (i341gercso.xlsx).
A dokumentációt a legalaposabban Kovács Balázs Marcell készítette el. (i341kovacs.pdf)
Az értékelés az alábbi szempontok mentén történt: villamosertekeles.pdf