PPB3113 Management Information Systems
FACULTY OF MANAGEMENT AND ECONOMICS DEPARTMENT OF BUSINESS MANAGEMENT AND ENTREPRENEURSHIP
PPB3113 Management Information Systems
Assignment 1 (Individual) – 20%
Students will have the basic understanding of Management Information Systems and explore their knowledge in using basic software for business data analysis.
Download all FOUR MS Excel files from MyGuru/ Google Classroom. i. TicketSales.xls ii. TCO.xls iii. frequentflier2.xls iv. ServerLogs.xls
This assignment will be mark based on:
i. ability to correctly solve the questions; (10m) ii. ability to use the suggested functions; (8m) and iii. ability to follow the assignment instructions (2m).
Make sure that you include your matrics number in the filename when you save all the files that you want to submit (example: TicketSales_D20181012345.xls, TCO_ D20181012345.xls, Task5_ D20181012345.xls).
There are five (5) tasks in this Assignment. Do your own research and try to solve all the problems.
Submit your assignment to our Google Classroom.
REMINDER : PLAGIARISM is a serious offence.
DUE DATE to submit the file to Google Classroom : 14 November 2020 (Saturday) – WEEK-6
INSTRUCTION: Please refer to the MS Excel files suggested in each Task in order to answer the questions.
Task 1 : Ticket Sales at Campus Travel
The local travel center, Campus Travel, has been losing sales. The presence of online ticketing websites, such as
Travelocity.com and Expedia.com, has lured many students away. However, given the complexity of making
international travel arrangements, Campus Travel could have a thriving and profitable business if it concentrated
its efforts in this area. You have been asked by the director of sales and marketing to help with analyzing prior
sales data in order to design better marketing strategies. Looking at these data, you realize that it is nearly
impossible to perform a detailed analysis of ticket sales given that the data are not summarized or organized in a
useful way to inform business decision making. The spreadsheet TicketSales.xls contains the ticket sales data
for a 3-month period. Your director has asked you for the following information regarding ticket sales. Modify the
TicketSales.xls spreadsheet to provide the following information for your director:
1. The total number of tickets sold.
a. Select the data from the “tickets sold” column.
b. Then select the “autosum” function.
2. The largest amount of tickets sold by a certain salesperson to any one location.
a. Select the appropriate cell.
b. Use the “MAX” function to calculate each salesperson’s highest ticket total in one transaction.
3. The least amount of tickets sold by a certain salesperson to any one location.
a. Select the appropriate cells.
b. Use the “MIN” function to calculate the “least tickets sold”.
4. The average number of tickets sold.
a. Select the cells.
b. Use the “AVERAGE” function to calculate the “average number of tickets sold” using the same
data you had selected in the previous step.
Task 2 : Valuing Information Systems
The cost of maintaining information systems is high for Campus Travel. You have been assigned to evaluate the
total cost of ownership (TCO) of a few systems that are currently in use by Campus Travel employees. Take a look
at the TCO.xls file to obtain the list of systems that are in use and the costs associated with maintaining the
software, hardware, and the associated personnel for each type of system. Calculate the following for your
1. The costs for server hardware by adding a new row to include Web Servers. This includes $4,500 for the
main campus and $2,200 for the other campuses.
2. The TCO for the entire information system used at Campus Travel. Hint: Sum all the values for all the
3. The TCO for servers and network components of the information system.
4. Make sure that you format the table, including using the currency format, in a professional manner.
Task 3 : Tracking Frequent-Flier Mileage
You have recently landed a part-time job as a business analyst tor Campus Travel. In your first meeting, the
operations manager learned that you are taking an introductory MIS class. As the manager is not very proficient
in using office software tools, he is doing all frequent-flier mileage in two separate Excel worksheets. One is the
customer’s contact information, and the second is the miles flown. Being familiar with the possibilities of
spreadsheet applications, you suggest setting up one worksheet to handle both functions. To complete this, you
must do the following:
1. Open the spreadsheet frequentflier2.xls. You will see a tab for “customers” and a tab labeled “miles
2. Use the vlookup function to enter the miles flown column by looking up the frequent-flier number.
(Hint: If done correctly with absolute cell references (using $), you should be able to enter the vlookup
formula in the first cell in the “miles flown” column and copy it down for all the cells.)
3. Use conditional formatting to highlight all frequent fliers who have less than 4,000 total miles.
4. Finally, sort the frequent fliers by total miles in descending order.
Task 4 : Analyzing Server Traffic
Campus Travel has recently found that its Internet connections between offices are becoming slow, especially
during certain periods of the day. Since all the online traffic is maintained by another company, an increase in
bandwidth requires a formal approval from the general manager. The IS manager has proposed to increase the
bandwidth of the company’s network; in a few days, he has to present the business case for this proposal at the
weekly meeting of the department heads. You are asked to prepare graphs for the presentation to support the IS
manager’s business case. In the file ServerLogs.xls, you will find information about the network traffic for a 1-
week period. Prepare the following graphs:
1. Total bandwidth used for each day (line graph).
2. Bandwidth used per day, by time period (line graph).
3. Average bandwidth used in each two-hour period (line graph).
Format the graphs in a professional manner and place each graph on a separate page. (Hint: If you are using
Microsoft Excel’s Chart Wizard, select “Place chart: As New Sheet.”)
Task 5 : Online versus Traditional Spreadsheet
Campus Travel is currently evaluating the possibility of using online spreadsheet software as opposed to the
traditional locally installed spreadsheet application. There are a variety of issues involved in this decision. The
company wants you to investigate the possibilities that are currently available while also paying special attention
to the company requirements. Campus Travel has the following requirements: (1) the ability to share
spreadsheets easily, (2) the ability to secure this information, (3) the ability to save the spreadsheets into other
formats (i.e., CSV files), and (4) the ability to work from anywhere in the world. Prepare the fol!owing information:
1. On the Internet, find different options for online and traditional spreadsheets and list the available
2. Using the company requirements, list the pros andcons for each spreadsheet option.
3. Create a brief report not more than three (3) pages summarizing the findings and provide a
recommendation to the company. Present your findings with tables and/or graphs, if available.