StudentShare
Contact Us
Sign In / Sign Up for FREE
Search
Go to advanced search...
Free

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

Cite this document
Summary
The purpose of this paper is to analyze the Microsoft Excel and Acess tools in terms of business financial calculations. As is evident from the discussion presented, all the requirements can be implemented using both MS Access and MS Excel, however, a few issues shall be noted…
Download full paper File format: .doc, available for editing
GRAB THE BEST PAPER93% of users find it useful
MS Excel and Access in Financial Calculations of Business Operations
Read Text Preview

Extract of sample "MS Excel and Access in Financial Calculations of Business Operations"

 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
Cite this document
  • APA
  • MLA
  • CHICAGO
(MS Excel and Access in Financial Calculations of Business Operations Coursework - 1, n.d.)
MS Excel and Access in Financial Calculations of Business Operations Coursework - 1. Retrieved from https://studentshare.org/information-technology/1878133-fly-guys-report
(MS Excel and Access in Financial Calculations of Business Operations Coursework - 1)
MS Excel and Access in Financial Calculations of Business Operations Coursework - 1. https://studentshare.org/information-technology/1878133-fly-guys-report.
“MS Excel and Access in Financial Calculations of Business Operations Coursework - 1”, n.d. https://studentshare.org/information-technology/1878133-fly-guys-report.
  • Cited: 0 times

CHECK THESE SAMPLES OF MS Excel and Access in Financial Calculations of Business Operations

What are the tradeoffs between relational and object-oriented databases and database management systems

In view of the fact that the business clients adopt latest versions of systems, as well as develop to previous ones, their efforts to make use of RDBMS come across the "Relational Wall," where RDBMS technology is no longer capable to offer the functionality and services required by the users.... In addition, it has also offered many business applications, as well as the business sector has expended to over $4B yearly, comprising tools....
7 Pages (1750 words) Essay

General computer knowledge

Defragmentation also improves accessing files in faster times as read/write operations is faster in defragmented files in comparison to system of fragmented files.... Scam (give examples) Scam in cyber world applies manipulation for gaining access of private information to stealthily deprive money of potential target.... In the ms-DOS, format....
3 Pages (750 words) Coursework

Business Decision Making at Lambert Heating

The goal of the study "business Decision Making at Lambert Heating" is to summarize the most effective existing investment evaluation and project management techniques.... Moreover, the writer applies the described theory by analyzing the case of Lambert Heating.... hellip; Capital expenditure decisions are one of the most important decisions that firms make....
14 Pages (3500 words) Case Study

MS Office and Its Need at Educational Level

For creating a document, MS Word is used, for calculation and listings, ms excel is used, for making presentations, MS PowerPoint is used, for sending the email, MS Outlook is used and for generating a database, MS Access is used (Mendelson 2006).... The package of MS Office contains MS Word, ms excel, MS Access, MS PowerPoint and MS Outlook that are employed on an educational level generally.... MS office facilitates the users with a user-friendly package of many applications that can be employed for business as well as educational purposes....
13 Pages (3250 words) Case Study

Evaluation of Financial Position - Excel Limited

rocess costing is the normal method of capturing the cost in most manufacturing industries especially when the products are produced in large numbers using a sequence of repetitive operations.... hellip; The report identifies the shortcomings of Excel Limited and advises it regarding its financial control systems.... This report evaluates the different costing methods and inventory valuation methods, and recommends the most relevant costing model for excel Limited....
12 Pages (3000 words) Essay

Individual report of approximately 1,000 words

Moreover, no specific training is needed for using spreadsheets and employees can easily create, distribute, manipulate and access the data of spreadsheets.... preadsheets are used for countless business functions and accounting calculations.... The expense on employees, requisitions and maintenance is also given to get the calculations.... It can be used for tracking workflow, for creating accounting estimates and for key financial reporting....
4 Pages (1000 words) Coursework

Financial Analysis of Business Based on Assumed Financial Amounts

The business is supposed o start in January 2015 and she is confident that by the end of July she will achieve her full potential.... She is about to start her business The report elaborates the cash budget, budgeted income statement and budgeted statement of financial position based on the available data that is estimated by Edith.... Thus, it helps her to recognize the benefits and deficiency of her financial plan to start the new business....
9 Pages (2250 words) Assignment

Accounting and Finance will send you fquestions file

The cash flow during year 3 is £ 60,000.... Therefore calculation will be: ... ... he table above is showing present value of estimated… flows at different rates while it is clear that present value of estimate cash flows at 29 percent and 30 percent are respectively nearest greater and less to initial investment therefore the IRR will lie between 30% and 29%....
1 Pages (250 words) Essay
sponsored ads
We use cookies to create the best experience for you. Keep on browsing if you are OK with that, or find out how to manage cookies.
Contact Us