CIS 3302 – Management Information Systems Assignment 2
University of Houston – Downtown Finance, Accounting, and CIS Department
CIS 3302 – Management Information Systems (Dr. Bose) Fall 2012
Assignment 2
Plug-in Chapter T3 – Problem Solving with Excel
IS Chapter 4 – Information Security This assignment is due at the beginning of class on Monday, October 15 or Tuesday, October 16, depending on the section you are in. It is worth 30 points. You have to turn in both of all your digital files in Blackboard and a hard copy in the classroom for your assignment to be graded. The cutoff time is set in Blackboard. So, if you miss the Blackboard deadline to submit your digital files, you receive a zero in the assignment. Technical problems are not acceptable excuses. If you have submitted all of your assignment files in Blackboard by the cutoff time, but you arrive late in class or miss class, you must turn in your hard copy in my office under the door within 3 days, after which your assignment will not be graded and you will receive a zero in the assignment. General instructions Keep your answers focused and to the point. Maintain page lengths as indicated in the questions.
Begin your answers by first typing the question at the top of a new page. Add a cover page having your name, course number, class hour, and assignment number. Begin each problem/question on a new page. Parts of the same problem/question that are numbered
(a), (b), etc can remain on the same page. Clearly mention the Problem #, for example, “Problem 2c”. Type the question before you type your answer.
Use one-inch margins and double-spaced 12-point Times New Roman font. All pages should have consistent formatting. All word-processed work should be in a single Microsoft Word file. Depending on the assignment problems, you may also be creating files in other software such as,
Microsoft Excel, Microsoft Access, and PDF files containing saved web pages. Submit all the files in Blackboard Vista. You can lose a major portion of the grade if you do not submit all the files.
Name your assignment files in the following format – “Your_Blackboard Vista_user_id Assignment xx” without the double quotes. For example, if your Blackboard Vista user ID is doej1 and you are submitting Assignment 5, then your filename will be “doej1 Assignment 5” without the double quotes. If you will be submitting multiple files in an assignment – for example, a Word file, PDF file, and a Microsoft Access file – then name each file the same.
Plug-in Chapter T3 – Problem Solving with Excel Established in 2002, t-shirts.com has rapidly become the place to find, order, and save on t-shirts. One huge selling factor is that the company manufactures its own T-shirts. However, the quality manager for the production plant, Kasey Harnish, has noticed an unacceptable number of defective T-shirts being produced. Your team has been hired to assist Kasey in understanding where the problems are concentrated. Solve problems 1 through 6 to perform various analyses using the data file, T3_TshirtProduction_Data.xls, which is provided in Blackboard. The following is a brief definition of the information within the data file: A. Batch: A unique number that identifies each batch or group of products produced. B. Product: A unique number that identifies each product. C. Machine: A unique number that identifies each machine on which products are produced. D. Employee: A unique number that identifies each employee producing products. E. Batch Size: The number of products produced in a given batch. F. Num Defect: The number of defective products produced in a given batch. For each problem, first copy the original data to a new worksheet (not a new workbook) in the same workbook. Name the worksheet as instructed in each problem. The worksheet name goes in the worksheet tab. Carry out the tasks for the problem in that worksheet.
2
1. (4 points). Find out the highest number of defective products being produced in each machine for
each product. Do this by applying multi-level sort to create a table as per the shown sample. The table gives sufficient information for you to figure out the sort levels and sorting orders. Notice the repositioned columns. Name the worksheet #1 – Sort.
PRODUCT MACHINE NUM DEFECTIVE BATCH EMPLOYEE BATCH SIZE
10 5 21 65 2222 10000 10 5 16 1 3333 500
Etc. Etc. Etc. Etc. Etc. Etc. 10 6 18 53 3333 10000
Etc. Etc. Etc. Etc. Etc. Etc. 10 8 5 13 2222 5000 20 5 19 41 2222 1000
Etc. Etc. Etc. Etc. Etc. Etc. 30 5 18 48 5555 5000
Etc. Etc. Etc. Etc. Etc. Etc.
Describe your finding in a Word document and paste the table containing the worksheet data below your description. If your table spans more than one page, you must show the heading row on every page.
2. (4 points). Find the batches of product #30 manufactured on machine #8 that had between 12 and 19 defective products. Display only the rows of the affected batches. You must use the most efficient Excel tool to do this. Name the worksheet #2 – Batches.
Describe your finding in the Word document saying which tool you used, followed by a table containing the result from the worksheet.
3. (4 points). Use the most efficient Excel tool to find the total number of defective products made by each employee and the grand total as well. Your result should display as per the shown sample table. Notice that for each employee, the rows are sorted by product and by machine within product. The column sequence should be as shown. Name the worksheet #3 – Employee.
EMPLOYEE PRODUCT MACHINE NUM DEFECTIVE BATCH BATCH SIZE
1111 10 5 ? ? ? 1111 10 6 ? ? ? 1111 10 7 ? ? ? 1111 20 6 ? ? ? 1111 20 7 ? ? ? 1111 30 5 ? ? ? 1111 30 7 ? ? ?
1111 Total ? 2222 10 5 ? ? ? Etc.
2222 Total ? Etc.
Grand Total ? Describe your finding in the Word document saying which tool you used, followed by a table containing the result from the worksheet. If your table spans more than one page, you must show the heading row on every page.
3
4. (4 points). Use the PivotTable tool to tabulate the number of batches of each product produced in the
different machines as shown in the sample table. Place the PivotTable in a new worksheet and name the worksheet #4 – PivotTable Batch. EMPLOYEE (All) Count of BATCH MACHINE PRODUCT 5 6 7 8 Grand Total
10 ? ? ? ? ? 20 ? ? ? ? ? 30 ? ? ? ? ?
Grand Total ? ? ? ? ? Write in the Word document which two machines produced the most number of batches of products. Was there a big variation in the number of batches for each product? Add the PivotTable content in the Word document.
5. (4 points). Use the PivotTable tool to determine the total number of products produced by each employee as well as the total number of defective products produced by each employee as shown in the table structure below. Place the PivotTable in a new worksheet and name the worksheet #5 – PivotTable Employee.
EMPLOYEE Data Total
1111 Sum of BATCH SIZE ? Sum of NUM DEFECTIVE ?
2222 Sum of BATCH SIZE ? Sum of NUM DEFECTIVE ?
3333 Sum of BATCH SIZE ? Sum of NUM DEFECTIVE ?
4444 Sum of BATCH SIZE ? Sum of NUM DEFECTIVE ?
5555 Sum of BATCH SIZE ? Sum of NUM DEFECTIVE ? Total Sum of BATCH SIZE ? Total Sum of NUM DEFECTIVE ? In the Word document, say what percentage of the total batch size is defective and the percentage defective products produced by each employee. Add the above table after completing it.
6. (4 points). Prepare a chart as shown that displays the number of defective products produced by employee by product. Note that before you can make a PivotChart, you must first create a PivotTable to base your chart upon. Place the PivotTable and PivotChart in new worksheets and name the worksheet #6 – PivotTable Defective Count and PivotChart Defective Count. Paste the chart in your Word report.
4
IS Chapter 4 – Information Security
7. (6 points). Answer the questions of the IT’s About Business case study titled Information Security at City National Bank and Trust on page 97. Those questions are mentioned below. Your answer to each question should be at least half-a-page long.
a) Why is it so important for organizations to establish enterprise-wide security policies?
b) Are the bank’s e-mail policies too stringent? Why or Why not? Support your answer.
Checklist of items to be submitted File submission in Blackboard. Submit all files – Word, Excel, Access, PDF, or any other files – that
you created for the assignment. You can lose up to the entire grade for missing files or files that cannot be opened.
Hard copy of your assignment making sure that all the sheets are stapled together.
0
50
100
150
200
250
1111 2222 3333 4444 5555
30 20 10
BATCH SIZE (All)
Sum of NUM DEFECTIVE
EMPLOYEE
PRODUCT
Needs help with similar assignment?
We are available 24x7 to deliver the best services and assignment ready within 3-4 hours? Order a custom-written, plagiarism-free paper
![](https://powerpointpresentationhelp.com/wp-content/uploads/2022/03/WhatsApp_icon.png)
![](https://powerpointpresentationhelp.com/wp-content/uploads/2022/03/205-2057855_essay-icon.png)