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 requested that you craft a presentation that illustrates sales figures for the site during the past 12 months and recommend whether the business should continue to maintain their Web store. To begin your preparations, you will create an Excel spreadsheet that analyzes existing data about sales on the site.

You recently requested sales figures about the Web site from Casey, a member of the sales department and then received this email. 

Date: 2009/09/02 2:01pm
From: Casey [casey@philspills.com]
Subj: re: Web sales figures for 2008

Here is the sales information that you requested.  This is all the
information that I can provide at this time.

Medicine       Pills Per Bottle  Bottles Sold  Profit Per Bottle
Acetaminophen                20          8276              $1.81 
Acetaminophen                50          6020              $1.02 
Acetaminophen               100          3380              $1.81 
Aspirin                      10          3013              $1.85 
Aspirin                      50          7228              $1.96 
Ibuprofen                    25          8777              $1.48 
Ibuprofen                    75          5514              $1.99 
Ibuprofen                   200          2616              $1.72 
Naproxen                     10          9207              $1.18 
Naproxen                     35          1232              $1.19

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. Partially correct solutions generally receive half of the available points.
  1. (0) Download the Excel file cis-211-hw-1.xlsx. Do not change the file name prior to submission.
  2. (4) Enter the data from the email into Excel on Sheet1 in columns A to D, rows 1 to 11.
  3. (2) In cell C12, use a formula to show the total number of bottles sold.
  4. (4) In column E, use a formula to calculate the profit per pill.
  5. (4) In column F, use a formula to calculate the total number of pills sold. In cell F12, use a formula to show the total number of pills sold.
  6. (4) In column G, use a formula to calculate the total profit of each medicine and pill quantity combination (in other words, each row). In cell G12, use a formula to calculate the total overall profit.
  7. (4) In column H, use a formula to calculate the percentage of overall profit constituted by the medicine and pill quantity combination (for example, The profit from selling the 20-pill Acetaminophen bottle represents about 17% of the total overall profit). You must use an absolute cell reference to total profit.
  8. (6) Insert a column between columns C and D. In the new (empty) column D, use a formula to calculate the percentage of overall number of bottles sold by the medicine and pill quantity combination (for example, the overall percentage of 20-pill Acetaminophen bottles sold is about 15%). You must use an absolute cell reference to total bottles sold.
  9. (6) Format all data appropriately (dollar figures should be currency and percentages should be whole numbers, not decimals). Ensure all cells in rows 1 and 12 are bold.
  10. (3) (Optional extra credit work) Use the function button and Excel help to understand the functions CONCATENATE and LEFT. In column J, create a short label for the medicine and pill quantity combination that is the first three letters of the medicine followed by the quantity of pills (for example, the 20-pill bottle of Acetaminophen should have the label Ace20).
  11. (8) Create one 2-D clustered column chart that shows percentage of bottles and percentage of profit for each medicine and pill quantity combination. Ensure that the y-axis has endpoints of 0% and 100% and that the x-axis and legend have appropriate labels. If you did the extra-credit item above, use column J for labels on the x-axis. If not, use columns A and B for the x-axis labels. Do not be concerned if some of the labels overlap. Once the chart has been created, move the chart to Sheet2.
  12. (6) Use the bar chart to answer the following question: is there a relationship between sold bottle percentage and profit percentage? If so, describe the relationship. If not, say why. Insert a text box on Sheet2 and place your answer inside the text box.
  13. (4) On Sheet3, create a pie chart that shows the percentage of profits for each medicine and pill quantity combination. Ensure that the pie chart appears on its own sheet.
  14. (8) Back on Sheet1, in cell G13, enter Site Cost. In cell H13 enter $100,000 ($80,000 to build the site and $20,000 to maintain the site). In cell G14, enter ROI. In cell H14, enter a formula to calculate return on investment (ROI), which is the sales profits less the Web site development costs, all divided by the Web site development costs. A positive ROI means that the profits earned exceeded the cost of having the site, but a negative ROI means the the profits earned were not enough to overcome the cost of having the site. Insert a text box on Sheet1 and inside this text box, make an argument based on the breakdown of the Web site development costs that Phil's Pills should keep using PhilsPills.com even though ROI is negative.

Delivery

Use the following steps to upload your Excel file to Blackboard no later than the start of class on Wednesday, September 9th. We will discuss the solution to the assignment in class on Wednesday, so late work cannot be accepted. I recommend that you aim to complete and submit this assignment no later than start of class Monday, September 7th. If you would like confirmation of your submission, send an email request to the instructor.

  1. Ensure that you have not changed the original file name
  2. Open the Blackboard page for the class
  3. Click Course Tools
  4. Click Digital Dropbox
  5. Click Send File (not Add File)
  6. In the Name field, type HW1 (do not type your name)
  7. Submit the file