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. Your manager has indicated that upper management is interested in sponsoring a CRM system implementation project and would like some information projecting anticipated costs and benefits of the project so that they can make an informed decision on whether to proceed with the project. To complete your assignment, you will modify an existing Excel spreadsheet. Download cis-211-hw-3.xlsx to begin.

Definitions

Directions

Follow the directions below to complete the assignment. Use formulas unless otherwise indicated. Numbers in parentheses at the beginning of each direction indicate the point value for the item. We will discuss the meaning of some of these calculations in class at a later date.

  1. (4) Throughout all of the sheets that you will create, ensure that money is formatted as currency with no decimal points showing.
  2. (2) Compute Profits in Row 8. This is the difference in benefits and costs only for the indicated year.
  3. (4) Compute the Cumulative Profits in Row 9. This is a running total. For example, in column D, year 2, you should show the sum of profits gained in years 0, 1, and 2. In columns C, D, and E, do NOT use the SUM formula for these calculations. Instead, simply add two cells together.
  4. (4) Compute Discounted Costs in Row 11. This is the costs for the year multiplied by the discount factor for the year.
  5. (4) Compute Discounted Benefits in Row 12. This is the benefits for the year multiplied by the discount factor for the year.
  6. (4) Compute Discounted Profits in Row 13. This is the discounted benefits for the year less the discounted costs for the year.
  7. (4) Compute Cumulative Discounted Profits in Row 14. This is a running total. For example, in column D, year 2, you should show the sum of discounted profits gained in years 0, 1, and 2. In columns C, D, and E, do NOT use the SUM formula for these calculations. Instead, simply add two cells together.
  8. (0) Net Present Value is the cumulative discounted profits over a period of time. The number in cell E14 is thus the net present value, or NPV.
  9. (2) Use Excel's help features to understand the NPV function, then use the function appropriately in cell B16 (use row 8 for your series of cash flows). Note: you should get a different result than what appears in cell E14! We will discuss why in class after the assignments have been submitted.
  10. (2) Copy the NPV sheet to a new sheet named IRR
  11. Update (Web Sep 16): Ignore this step and move onto the next one.
    (2) In the IRR sheet, Use Goal Seek to set cell E14 to 0 by changing cell B1 (and click OK). Note the value that appears in cell B1, then hit Cancel on the Goal Seek Status dialog box (or if you have already hit OK, click Undo in the sheet). Now in cell B19, use help to learn about the IRR formula, then apply it to the same cash flows that you used for the NPV function. Note: you should get the same number that you just noted from Goal Seek. We will discuss why in class after the assignment has been submitted.
  12. (2) Return to the NPV sheet. In row 21, copy the contents from row 3.
  13. (4) In row 22 of the NPV sheet, calculate the cumulative costs of the project (see step 2 for an explanation of cumulative). In row 23, calculate cumulative benefits.
  14. (6) In the NPV sheet, create a line chart of cumulative costs and benefits, ensuring appropriate axes labels, titles, and endpoints. Entitle the chart Payback. Place the chart below row 23.
  15. (2) Copy the NPV sheet to a new sheet named Sim
  16. (3) For all of the following changes, use help to find a formula that will insert a random number in a cell. Change cell B6 to be a random number between 200,000 and 600,000. Change cell C6 to be a random number between 80,000 and 160,000. Change cell C7 to be a random number between 250,000 and 500,000.
  17. (2) Run a simulation that uses various costs and benefits generated from the random numbers by following these steps:
    1. Copy cell E14
    2. Right-click cell G14 and select Paste Special
    3. Click Values, then click OK
    4. Repeat steps 1 through 3 but for cell G15 instead of cell G14
    5. Repeat again for cell G16, G17, and so on down to cell G23
  18. (2) Sort cells G14 to G23 from lowest value to highest value.
  19. (4) Use conditional formatting to highlight positive values in green and negative values in red for cells G14 to G23.
  20. (3) In cell F24, write the word Avg and in cell G24, compute the average of cells G14 to G23.

Delivery

Use the following steps to upload your Excel file to Blackboard no later than the date listed on the class schedule. We will discuss the solution to the assignment in class on the due date, so late work cannot be accepted.

  1. Ensure that your file name contains only letters, numbers, and dashes (-)
  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 HW3 (do not type your name)
  7. Submit the file