analyzing server traffic

total bandwidth used for each day

bandwidth used per day, by time period

average bandwitch used in each two-hour period

PPB3113Management Information Systems

FACULTY OF MANAGEMENT AND ECONOMICS
DEPARTMENT OF BUSINESS MANAGEMENT AND ENTREPRENEURSHIP

PPB3113 Management Information Systems

Assignment 1 (Individual) – 20%

Objective

Students will have the basic understanding of Management Information Systems and explore their
knowledge in using basic software for business data analysis.

Assignment details

 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).

Assignment instructions

 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

operations manager:

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

systems together.

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

flown.”

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

options.

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.