Managing Business Information – Year 2014/2015
The Case Study
Fly Guys is a company that operates flights to and from the cities of Aberdeen, Birmingham and Cardiff.
Fly Guys have only one plane. This plane has eighty seats. Each day the plane goes on one journey with two stops. On Monday, Wednesdays and Fridays they take off from Aberdeen, stop at Birmingham and finish at Cardiff. On Tuesdays, Thursdays and Saturdays they fly back from Cardiff, stop at Birmingham and finish the journey at Aberdeen.
Passengers can book a flight for any part of the journey (e.g. just from Aberdeen to Birmingham) or for the complete journey (from Aberdeen to Cardiff via Birmingham). They may choose to book a return flight which would involve them returning the following day when the plane comes back.
When a passenger books a flight they are given a booking number. This booking number is used when they check-in for their flight.
Fly Guys have recently introduced a rewards scheme for their loyal customers. If a passenger completed 5 full journeys (e.g. from Aberdeen all the way to Cardiff) or 10 partial journeys (e.g. Birmingham to Cardiff or Aberdeen to Birmingham) they are entitled to get their next flight at half price.
If a passenger books a return flight at the same time as their outbound journey the whole booking is charged at 75% of the regular price.
The prices of journeys are as follows:
Aberdeen -> Birmingham – £50
Birmingham -> Cardiff – £60
Aberdeen -> Birmingham -> Cardiff (i.e. the whole journey) – £110
(The same pricing is applied in the opposite direction).
The CEO of Fly Guys would like you to use the applications available in Microsoft Office to design a system which will allow him to
• Keep details of passengers
• Keep details of all bookings made by passengers
• Calculate the price of a passenger’s ticket based on departure point and destination
o This should include any discount due as a result of the Frequent Flyer scheme or because a return journey has been booked
• Keep details of all passengers booked on a given flight
The CEO would also like to be able to produce reports detailing:
• The number of bookings for each part of the journey
• The amount of revenue generated for each leg of the flight schedule
• An up-to-date list of the number of bookings for each day of the working week (Monday-Saturday)
Using the case study of Fly Guys, evaluate which aspects of their operation might best be managed using a spreadsheet (Excel) and which would be more suited to a database (Access). Justify your assertions by drawing comparisons between the activities and lectures you have done in class to support your recommendations. This part should be presented as a written report. (25 marks)
Using Access and Excel, design solution(s) to implement your recommendations from Part 1. You will be assessed on the quality of your solution. Marks will be awarded based on whether you have used all the techniques and functions of the Access and Excel applications that have been taught. Further marks are available for going beyond the workshop activities and undertaking independent research to use further features of the applications.(50marks)
Evaluate how successful your solution has been at fulfilling the requirements of the CEO of Fly Guys. Make reference to the basic operations of the organisation and discuss whether or not your solution is able to achieve the desired outcomes of the CEO. Conclude with a final recommendation on the business use of spreadsheets and databases. This part should be presented as a written report. (25marks).