MS Excel and Access in Financial Calculations of Business Operations - Coursework Example

 Flyguys Project Report Part 1 Flyguys has several requirements that can be met through proper usage of Access and Excel both. However, each of those software has its own pros and cons. Flyguys is interested in implementing a system that enables them to achieve three major goals; record keeping, reward system and reporting. This report takes a look at each of these requirements separately and identifies and analyzes the ways in which MS Office can come to Flyguys’ rescue. MS Office is replete with options and ways to facilitate record keeping (Couch, 2013). Flyguys wants to keep records in a system which allows them to update, append and delete the records when necessary (FLIGHT MANAGEMENT COMPUTER, 2015). These records are mainly for three categories namely customers, flights and bookings. As for the customer records, the basic information is necessary which includes Unique Customer ID Name Address contact number. For the flights record, the following related information is necessary Flight ID Flight Departure Date and Time (as per the schedule mentioned in details) Flight Arrival Date and Time (as per the schedule mentioned in details) Route (FLIGHT MANAGEMENT COMPUTER, 2015) For the bookings record, these are the necessary requirements as identified Booking ID Customer’s information Flight’s information Departure and Arrival station for the booking Assertion for whether it’s a return flight or not Fair that needs to be calculated as per policy Mechanism to book next flight back when a return flight booking is made (FLIGHT MANAGEMENT COMPUTER, 2015) Excel and Access both provide the facility of keeping tables to achieve this with perfect harmony and ease. Reward system is a marketing method devised by Flyguys to facilitate and attract the customer. The system that is proposed is required to cater to its requirements. The system should keep track of how many partial journeys or full journey a particular customer has made and whether they are eligible for a reward, 50% discount on their next respective flight. The system is required to implement this discount on the next booking which means a connection with bookings record is necessary (FLIGHT MANAGEMENT COMPUTER, 2015). Moreover, the system should reset the count as well or another method could be use the mod function to find how many flights shall be counted for discount for a certain customer. The third and quite important requirement by Flyguys is reporting mechanism. This is an extremely important aspect of any business so that it can study, analyze and tune its working (Couch, 2013). This is also important in terms of bureaucratic and policy matter within the company and to keep at par with the external environment (Couch, 2013). The reports feature, combined with carefully constructed queries, in MS Access is of special importance here. This also leaves the Excel’s filters and reporting methods far behind in terms of efficiency and ease of use. The findings and implementation strategies used to achieve these results for Flyguys have been summarized in the table below. Requirement Implementation using MS Excel Implementation using MS Access Verdict and Reason Keep details of passengers Table of records Unique Customer ID Possibility of modifying, adding and deleting records Facility to create reports A workbook titled “Customers” is used to hold records and lookup functions are used to access and modify the contents where necessary. Filters can be used to create reports of specific records (McFedries, 2013). A table name “Customers” is used to hold records. It contains several columns regarding relevant information. Each customer is assigned a Unique Customer ID. This ID is used to refer to customer’s details at every point of relevant operation. Records are managed through Forms. MS Access is the better choice because i Records are not directly accessed by user and are modified using forms Easier to lookup and manage record details through forms Reports generated using wizards that make it easier Faster working for large number of records Keep details of all bookings made by passengers Record of bookings Linking booking record with customer record Generating reports using specific criteria when necessary A “Bookings” workbook is used to hold records. It is linked to customer’s record using VLOOKUP function and reports can be generated using filters and formulae (McFedries, 2013) A “Bookings” table is used to hold records and relationships are used to link data. Moreover, this enables creation of relevant queries and reports without redundancy or copying of data. MS Access is better option because Table relationships reduce the redundant amount of data (Couch, 2013) and also help consume less processing power and time Easier to implement one to many and many to many relationships Reports are generated using wizards Keep details of all passengers booked on a given flight Record of flights Linking with relevant booking record and customer record No more than 80 seats on a plane but overbooking allowed Generating reports when necessary A “Flights” workbook is created and formulae are used to link records to relevant booking and customer records. Reports generated using filters and formulae A “Flights” table is created that is linked to customer and booking table through relationships. Reports generated using wizards and queries. MS Access is better option because Relationships reduce redundancy, save time and power Faster working when large number of records are kept Discount mechanism 25% discount when return flight is booked 10 partial or 5 full journeys lead to half price for the next journey Updating of revenue record as per discounts offered VLOOKUP is used to check if the customer making a certain booking has enough partial or full journey streak and discounts are implemented using percentage formulae. Revenue records are updating through VLOOKUP as well. Queries are automatically launched through macros when a certain criteria are met and so records of fare and revenue are automatically updated and the streaks are reset. Both software are equally useful but MS Excel is easier for the user to debug and make calculations. Otherwise, MS Access offers better control for users familiar with query design, macros and a little VisualBasic. Reports for CEO Reports are generated using Lookup, filters and formulae and have to be created each time CEO requires a certain report (McFedries, 2013). Special macros can be designed to increase the speed of this process. Reports have already been designed to cater to needs of CEO and so they are automatically updated and generated when required. MS Access is better option since it provides better functionality and types and layout of reports. Part 3 As discussed in Part 1, there are 3 facets of the project that Flyguys is focusing on and which need to be met. They define the objectives as has been discussed in detail earlier. Several approaches were considered to achieve each of the record keeping, Reward system and reporting objectives. For the record keeping objective, one important aspect was to identify which details are necessary. For example, adding a social security number or driving license ID in the record may seem a perfectly good option but it was noted that not many airlines require that from their customer which makes it useless to reserve memory for this in each record. It was also important to format the relevant data accordingly. This is important to keep a consistency in the whole data. For example, different operators may use different methods of entering date i.e. mm/dd/yy or dd/mm/yy or mm/dd/yyyy and other variations which may results in unnecessary changes in the reports and uneven memory requirements. In Access, this was achieved through input masks, default values and formatting of each field. In Excel, this was made possible through usage of Formatting option for each cell. The reward system represented a challenging aspect since several big changes had to be made in the system. The system had to be able to keep count of customer’s current flight count for next discount availability. This was achieved through VLOOKUP and mod function in MS Excel while the queries in MS Access were used to implement the system. The reporting mechanism was an important aspect of the system as requested by the CEO. This was a big hurdle when it came to using Excel. Although macros eased the process to some extent but they meant the user had to be trained in using them as well. Moreover, each of the report relied heavily on filters crafted to each report’s requirements. Even when the irrelevant records were hidden for a particular report, the report remained difficult to be made presentable to someone of high corporate stature such as the CEO. On the other hand, Access provided much better functionality through usage of queries and reports. For example, to generate a report of revenue of each leg of the journey, the criteria for query included the route (“A-C”,”A-B” or in the opposite direction), flight’s ID and bookings made. This allowed easy functionality and faster generation of reports. Moreover, queries are easily customizable and can be generated when required using similar methods and techniques. Objective Implementation Result Record keeping for Flights, Customers and Bookings Linking the records and updating all according to changes made Objective was successfully achieved on both Excel and Access. While VLOOKUP provided easier implementation of updating records, queries provided better functionality. Excel caused redundancy of data while Access allowed same data to be referred to using relationships and primary keys. Successfully implemented: Record keeping of all operations Provision of BookingID to customer Easy to use system for bookings and flight check Reward System and Pricing Queries, VLOOKUP and formulae were used to automatically conform the billing procedure to reward policies. Customer’s streaks were implemented to keep record of their current partial or full journey credits and discounts were automatically processed. Streaks were reset when a certain discount was achieved. Successful implementation of 50% discount on next flight when 5 full or 10 partial journeys are completed by a certain customer Pricing for each journey automatically updated and shown on the booking menu Report generation MS Access reports were used and created as per CEO’s requirements and now it is easier for him to check and analyze the running of the company. Newer reports can also be easily generated as desired. All goals successfully met As is evident from the above scenario, all the requirements can be implemented using both MS Access and MS Excel, however, a few issues shall be noted. Currently the range of Excel file is set to 5000 records. With time, this value will rise asymptotically and Excel is known to be slower with a staggering number of records. Access however, manages large data more efficiently and so even on moderately powerful computers, speed would not be compromised and thus Access would provide relatively robust service. Addition, Updating and Deletion of records is equally easy on both the software packages but MS Access provides better layout and functionality using Menus and Forms. Moreover, it allows searching process much user friendly. In Excel, each record can be searched through the inherent search function (ctrl+F) and can be appended there and then but Access provides other graphically appealing methods such as buttons to achieve the same results. Moreover, the form layout allowed a more ergonomically arranged fields and more aesthetically pleasing presentation of the records which is valuable in any system. Report generation on Excel is an arduous task that is time consuming and labor intensive when done frequently. Although Macros can be used to speed up the process. Access, on the other hand, provides better functionality and more powerful reports that may contain virtually any combination of data in the system and can be generated much easily. Report design however, is equally difficult on both system and Wizards are used to ease the process. Discount and Reward system is easily implemented on both the software packages and there is not much contrast in terms of efficiency and ease of use. Final Recommendation: As per the experience and the aforementioned concerns, it is recommended that a DBMS be used for business operation vis-à-vis customer and booking process and other record keeping and reporting requirements however, for financial calculation and analysis, spreadsheets provides much better functionality, control and possibilities are endless. References Couch, A. (2013). Microsoft Access 2013. Sebastopol, Calif.: O'Reilly. FLIGHT MANAGEMENT COMPUTER. (2015). 1st ed. [ebook] Rays Aviation. Available at: http://www.raysaviation.mono.net/upl/11061/PMDG747XFMCUSERSGUIDE.pdf [Accessed 1 Jun. 2015]. McFedries, P. (2013). Excel 2013. Indianapolis, IN: Wiley. Read More