Homework Exercise - CIS 211 - Management Information Systems

Independent Work Policy

On this assignment, students must work independently. Actions such as directly collaborating with students currently enrolled in the class, referring to the work students formerly enrolled in the class, and using other people to complete the assignment are all examples of violations of this policy. Students should use the instructor for any questions about completing the assignment.

Academic Integrity

Students who choose to violate the independent work policy must cite the use of other people's work by including the names of those people in a conspicuous location. By providing citations, students avoid violating the Elon Honor Code. Students who both violate the independent work policy and fail to cite the names of contributors are subject to the Elon University Academic Integrity Policy.

Format

Students will create an Excel worksheet subject to the directions below.

Motivation

You are a member of the MIS department at Phil's Pills, a business that sells various over-the-counter medicines. On September 1, 2008, your project team unveiled the PhilsPills.com Web site at which consumers can browse and purchase pain relief medicine from the larger inventory at Phil's Pills. Your manager has requested that you measure the effectiveness of the Web site as well as compute some CRM metrics (by analyzing customer purchasing behavior data on the Web site). To complete your assignment, you will modify an existing Excel spreadsheet. Download cis-211-hw-2.xlsx to begin.

Definitions

• Click-through rate: Number of clicks on an ad divided by the number of times the ad is shown
• Conversion rate: Number of sales divided by the number of ad clicks
• CPM: Total sales divided by Total Cost (i.e. sales dollars generated per dollar of advertising)

Directions

Follow the directions below to complete the assignment. Numbers in parentheses at the beginning of each direction indicate the point value for the item.

1. (17) On the sheet labeled Per ID:
   1. (2) You will make a number of calculations in rows 2 through 19. In row 1, ensure to use appropriate labels.
   2. (2) In column J, calculate the click-through rate for each Promo ID
   3. (2) In column K, calculate the conversion rate for each Promo ID
   4. (2) In column L, calculate the total cost of the ad for each Promo ID
   5. (2) In column M, calculate the total sales generated from the ad for each Promo ID
   6. (2) In column N, calculate CPM for each Promo ID
   7. (2) In cells G20 through N20, calculate the average of the column
   8. (1) Sort the data so that the best CPM is on top and the worst CPM is on the bottom
   9. (2) Use conditional formatting to highlight all ads that have a conversion rate under 1%
2. (16) On the sheet labeled Per Desc:
   1. (3) Repeat steps 1.A through 1.F
   2. (2) Sort the data by Description then by Site
   3. (6) Use subtotaling to display average click-through, conversion, and CPM per Description
   4. (1) Change the view to show only the subtotals
   5. (4) Create a clustered column chart showing average CPM per Description. Ensure axes, labels, and chart titles are appropriate.
3. (11) On the sheet labeled Per Site:
   1. (3) Repeat steps 1.A through 1.F
   2. (2) Sort the data by Site then by Ad
   3. (4) Use subtotaling to display total cost and sales per site
   4. (2) Calculate CPM per site in Column N
4. (16) On the sheet labeled CRM, answer the four questions by manipulating the spreadsheet.

Tips

1. There are comments in cells A1 through I1 that more fully describe the data contained in the column. Move the mouse cursor to hover on top of a cell to view its comment.
2. You can copy cells from one sheet to another: select the cells you want to copy, right-click and select Copy, select the sheet where you want to paste, and finally right-click in the cell where you want to start pasting and click Paste.

Delivery

Use the following steps to upload your Excel file to Blackboard no later than the start of class on the due date listed on the class schedule. We will discuss the solution to the assignment in class so late work cannot be accepted. If you would like confirmation of your submission, send an email request to the instructor.

1. Open the Blackboard page for the class
2. Click Course Tools
3. Click Digital Dropbox
4. Click Send File (not Add File)
5. In the Name field, type HW2 (do not type your name)
6. Submit the file