Marks Collectibles is a newly established online reseller of the very popular Tree Point Babies stuffed animals. Periodically, Mark Allan, the owner of the small, online business, downloads his Web sites traffic statistics from his Web hosting service. As the downloaded data are not in a useful format, Mr. Allan hires you to prepare a Site Statistics Analysis worksheet. The Site Statistics Analysis worksheet provides Mr. Allan with information about his Web sites visitors, including the number of visited pages, types of operating systems, browsers, visit durations, and used bandwidth. Preparing the worksheet requires you to properly format and design the Site Statistics Analysis worksheet, determine the visit duration for each visitor, the most popular operating systems, most popular browsers, most popular site connections, and amount of used bandwidth.

Case Scenario

A few years ago, small, stuffed animals called Tree Point Babies became very popular with collectors, and demand for retired Tree Point Babies has continued to grow. Each month Tree Point Babies issues several new stuffed animals. Collectors, like Mark Allan, purchase many of these stuffed animals in the hopes that the stuffed animals can be resold at a later date for a profit.

Marks collectibles is a newly formed, small, online reseller of the popular Tree Point Babies. In an effort to widen his market, Mr. Allan has built a small Web site, showing high-quality images of his available Tree Point Babies. Mr.Allan pays a nominal fee to a Web hosting service to host his Web site. As part of its service, the Web hosting service provides Mr. Allan with a text file containing traffic statistics for his Web site. Figure 1 shows a sample of this data. (The bandwidth is shown in kilobytes.) Currently, Mr. Allan purchases 10 GB of bandwidth each month from the Web hosting service. If he uses more than his allotted amount, he is charged for the extra bandwidth.

		Browser			Entry	Exit	Bandwidth
999.010 .210 .133	Windows		HotBot	Yes	39731.47638	839731.49334	4400	4
999.250 .150 .140	Linux	Mozilla	Yahoo	No	39724.47352	239724.48189	91	3
999.111 .233 .190	Windows	Netscape	Lycos	Yes	39724.58203	339724.60697	7250	2
999.140 .152 .160	Windows		AltaVista	No	39726.4299	39726.44541	1750	15
999.180 .007 .222	Macintosh		Dogpile	No	39726.61228	839726.62361	1255	4

Figure 1: sample data from web site statistics text file

Design Specifications

As previously mentioned, Mr. Allan periodically downloads a text file that contains the Web sites traffic data. As Figure 1 shows, the text file is not in a useful format, and the data must be formatted in order to provide useful information to Mr. Allan.

Mr.Allan gives you the most recent text file containing his Web sites traffic data and asks you to import the data into a worksheet. To improve the appearance of the data, Mr. Allan asks you to properly format the data. For instance, you can apply a time format that will more easily display the dates and times of visitor entries and exits. He also asks you to include a header in the worksheet. He specifically requests that a worksheet title and date range indicating the time series for the data be placed at the top of the worksheet.

Mr. Allan wants each row of data to be assigned a visitor number. The visitor number is intended to help him quickly determine the number of visits his Web site has received and is not intended to uniquely identify each visitor. Including a visitor number for each record requires the addition of a new column. Mr. Allan wants the Visitor Number column to be the leftmost column in the worksheet.

For each visit, Mr. Allan wants to know the visit duration. As you study the imported data, you notice that the entry and exit times for each visit are included. You decide to insert a Visit Duration column. You determine the visit duration values for this column by taking the difference between the exit and entry times.

Information Specifications

For the Visit Duration, Bandwidth, and Viewed Pages columns, Mr. Allan wants the worksheet to display the median and mean for each of these columns. Mr. Allan wants the worksheet to display the mode for the Bandwidth and View Pages columns. The median identifies the middle numbers from the visit duration, bandwidth, and viewed pages values. The mode indicates the most frequently occurring bandwidth and number of viewed pages values. The mean provides averages for the visit duration, bandwidth, and viewed pages columns. Mr. Allan also wants the worksheet to show the maximum and minimum values for the Visit Duration, Bandwidth, and Viewed Pages colu mns.

Mr. Allan needs answers to the following questions. Using your newly designed Site Statistics Analysis worksheet, provide Mr. Allan with answers to his questions.

2. What is the average daily bandwidth used?174.9462366
4. How many pages were viewed in October? November? December? Assume Mr. Allan wants a chart that compares the number of viewed pages for each month. What type of chart would be appropriate? Prepare the chart.
6. What is the average stay for each visitor?0:21:39What is the longest time a visitor stayed at the Web site?0:40:52What is the shortest visit?0:00:42
8. Mr. Allan wants a chart that compares the site connections used to link to his Web site and the number of times the site connection was used. The chart should display the site connection data by month.
10. What percentage of visitors added the Web site to their list of favorite links? The chart should compare the visitor percentages by month.
12. Mr. Allan wants to know the average stay by operating system, connection, and browser. Mr. Allan wants this information displayed in pivot tables. Based on the results, what conclusions might you draw?

Implementation Concerns

Although you are free to work with the design of your worksheet, the worksheet should have a consistent, professional appearance. Also, you should use appropriate formatting for the cells and worksheet.

Test your Design

After creating the Site Statistics Analysis worksheet, you should test your design. Perform the following steps.

2. Which operating system is most popular? Prepare a chart comparing the operating systems and their percentages. The chart should display the operating system percentages by month.
4. Which browser is most popular? Prepare a chart comparing the browsers and their percentages. Prepare a chart comparing the browsers and their overall counts by month.
6. How much bandwidth was used for each month? Prepare a chart that compares the bandwidth usages by month. Should Mr. Allan consider raising or lowering his bandwidth?

Case Deliverables

In order to satisfactorily complete this case, you should build the worksheet as described in the case scenario and then prepare both written and oral presentations. Unless otherwise specified, submit the following deliverables to your professor.

2. An electronic, working copy of your workbook that meets the criteria mentioned in the case scenario and specifications sections.
4. Results for each question posed above.
6. As mentioned above, you should prepare an oral presentation. You should discuss the key features of your workbook. Also, discuss how the workbook is beneficial for Mr. Francisco. What additional information should be included in the workbook to make it more useful?