OFFICE PRODUCTS COMPANY



[pic]

ASSIGNMENT 2 (GROUP)

OFFICE PRODUCT OFFICE (OPC)

DATABASE MANAGEMENT

INSS640

DUE: April 17, 2012

(20% weight)

Peer Evaluation: Each group member will be grading out of 100 the contributions of other team members. A student’s score will be an average of the group member’s rating times the overall assignment score.

For example if you get an average of 80/100 in peer evaluation from your group and 90 in the assignment, your INDIVIDUAL score would be 90*.8 = 72/100.

If there is a problem with your group members, please e-mail me right away!!!

PEER EVALUATION FORM

Once the project is submitted, fill it out and post it in Sakai under peereval2 (ctrl + click above underlined link to see the peer evaluation form)

If your team has three/four members, you will be submitting two/three evaluations in ONE zip file respectively.

OFFICE PRODUCTS COMPANY (OPC), is headquartered in Frankfurt, Germany with branches all over the world. OPC was started by Berger brothers in 1929. Over the years company saw tremendous growth and in 1952 became a public company. Berger brothers do not maintain day to day control of the company anymore. Most of the common shares are held by institutional investors in Europe and the USA. Lately Chinese companies have also started investing in OPC.

OPC supplies office parts all over the world. They provide a mix of products ranging from paper clips to printers and copiers. In 1996 they added PCs, notebooks, GPS, Mobile phones etc to their line of products. Not all products are sold in all location. Small locations only cater to local markets. OPC operates in a very competitive market in what Porter calls a buyer’s market. OPC relies on loyalty, quality and turnover. Their brand is recognized worldwide and synonym with quality. They have used such big international stars as Beckham, Tiger Woods, Maria Sharapova, and Federer to market their products. However, recently (2009), they lost a major contract to a little known Brazilian product supply company, called South Pacific. South Pacific offered lower costs and financing which OPS could not or did not want to provide. Management is afraid that they may lose more contracts due to deteriorating economic conditions..

Current situation: Management, though concerned about long term impact, is worried about declining profitability and declining stock price. OPC, currently, is focusing on declining profitability in the western region of the USA. A digital dashboard analysis showed sales decline in Stockton, Modesto, Turlock, Merced and Denair regions. Mr. Bialski, VP Finance, is trying to analyze the problem. He is not sure of OPC’s problem in these region.

Mr. Bialski has hired your consulting company to analyze the situation and make recommendations.

From their Chicago warehouse, OPC distributes ten office products to five cities in the U.S.. Each product has the same selling price and unit cost in all of the five cities. Selling price may change in the future based on competition in each market. The Product data is shown below in Table I, and the sales data for the month of January is shown in Table II.

Table I: Product File

|Part Number |Part Description |Unit Cost (Euro) |Unit Price (Euro) |

|001 |Note pad - white |2.50 |3.50 |

|002 |Note pad - yellow |3.00 |4.00 |

|003 |Box of staples |1.37 |2.87 |

|004 |Paper clips (box) |0.51 |0.67 |

|005 |Pencils (3) |0.36 |0.78 |

|006 |Pen - type A |3.00 |5.00 |

|007 |Rubber bands (20) |0.30 |0.45 |

|008 |Pen - type B |1.50 |3.78 |

|009 |Glue |1.93 |2.67 |

|010 |Eraser |0.95 |1.67 |

Table II: Sales File

|Citycode |Cityname |Partno. |Quantity sold |

|10 |Stockton |001 |45 |

|10 |Stockton |002 |50 |

|10 |Stockton |003 |30 |

|10 |Stockton |004 |100 |

|10 |Stockton |005 |180 |

|10 |Stockton |006 |25 |

|10 |Stockton |007 |300 |

|10 |Stockton |008 |78 |

|10 |Stockton |009 |54 |

|10 |Stockton |010 |32 |

|20 |Modesto |001 |40 |

|20 |Modesto |002 |40 |

|20 |Modesto |003 |20 |

|20 |Modesto |004 |80 |

|20 |Modesto |005 |150 |

|20 |Modesto |006 |20 |

|20 |Modesto |007 |220 |

|20 |Modesto |008 |70 |

|20 |Modesto |009 |43 |

|20 |Modesto |010 |22 |

|30 |Turlock |001 |22 |

|30 |Turlock |002 |28 |

|30 |Turlock |003 |11 |

|30 |Turlock |004 |50 |

|30 |Turlock |005 |76 |

|30 |Turlock |006 |11 |

|30 |Turlock |007 |147 |

|30 |Turlock |008 |27 |

|30 |Turlock |009 |24 |

|30 |Turlock |010 |15 |

|40 |Merced |001 |9 |

|40 |Merced |002 |12 |

|40 |Merced |003 |7 |

|40 |Merced |004 |29 |

|40 |Merced |005 |45 |

|40 |Merced |006 |5 |

|40 |Merced |007 |60 |

|40 |Merced |008 |10 |

|40 |Merced |009 |16 |

|40 |Merced |010 |10 |

|50 |Denair |001 |2 |

|50 |Denair |002 |5 |

|50 |Denair |003 |1 |

|50 |Denair |004 |15 |

|50 |Denair |005 |10 |

|50 |Denair |006 |3 |

|50 |Denair |007 |15 |

|50 |Denair |008 |2 |

|50 |Denair |009 |3 |

|50 |Denair |010 |5 |

This assignment has two different tasks (stages): (a) design and (b) implementation.

Design

1. Remove redundancy in OPC database (note this would result in a total of 3 tables, PRODUCT and two tables from SALES table)

Implementation

2. Create the three tables and load the data: (use Access or ORACLE)

3. Define the relations (ERD) among the three tables (include a print screen copy if using ACCESS).

4. Develop and provide output of following queries in ACCESS or ORACLE:

1. Provide contents of each table

2. Provide names of products and cities that have sales of less than 5 units.

3. Provide names of products and cities that have total sales of less than 150 euros.

4. Provide total sales by city

5. Provide total sales by product.

5. Reports:

1. Create a report of total sales by product

2. Create a report of total sales by city

3. Create sales by city within city by product: use Access or You can also use EXCEL for this part if you do not have extensive knowledge of ACCESS.

Ex: for report 3 (sample template)

City name Product sales

Stockton Note pad – white XX.XX

Note Pad –yellow XX.XX

Etc.

Total sales at Stockton XXX,XXX.XX

Modesto Note ad – white XX.XX

Note Pad –yellow XX.XX

Etc..

Total sales at Modesto XXX,XXX.XX

etc….

Overall total sales XXX,XXX.XX

6. Any further analysis to support your recommendations.

7. Kung-, a Chinese company, has been accumulating OPC stock and currently owns 5% of the outstanding common stocks. They started a hostile bid for the company on December 1, 2011 by offering 35% premium over May 30th closing price of 45 Euros. Figure 1 shows the closing stock prices of both companies for the last five years. Discuss how OPC should respond to hostile takeover. Make sure to relate your response to concepts in the text.

[pic]

Given the above information, you group need to analyze the situation at five cities. Please remember, your contract states that the group report is due on June 29th 2012.

Submission submit in SAKAI

• One submission per group in Sakai. Name your file as assignment1_your groupname, i.e. if your group is “A”, then name your file as assignment2_GroupA.

• Include Group NAME and names of group members who participated on the first page of WORD file.

• Discuss EACH of the submission parts as a separate question in ONE WORD file (Do not combine answers to question)

• Submit all files as ONE zip file. You will have TWO files-- one file for an executive report and discussion questions in WORD, and the other file with a ACCESS database

Note: Create a zip with following documents :

A. Executive summary:

1. Use proper format: You may want to visit the Internet to look for sample executive reports. At a minimum, it should include a brief description of the system, analysis and your final recommendations.

B. Design & Implementation

1. Create the tables and load the January data: use Access.*** or ORACLE

2. Define the PK and FK for each table and relations among the three tables (if using ACCESS include a screen shot copy).

3. Queries (design and output of each query)

4. ACCESS Reports

***include the OPC database file in ACCESS (mdb extension in ACCESS 2003 or accdt in 2007

C. Additional analysis (as determined by your group).

D. Discuss how OPC should respond to hostile takeover. Make sure to relate your response to concepts in the text.

E. You have just heard of Business intelligence (BI) and market basket analysis. Discuss how market basket analysis could be used to gain competitive advantage.

F. OPC is thinking about outsourcing their IT operations to India. Discuss pros and cons of such a decision.

G. Appendix A

Appendix A: Grading Criteria

|Requirements |Maximum Points |Group Points |

|A. Executive Report |6 | |

|(including recommendations, if any) | | |

|B. Part 1: ACCESS table: |10 | |

| | | |

|Part 2: Define PK, FK and relationship among |10 | |

|tables | | |

| | | |

|Part 3: Queries |10 | |

| | | |

|Part 4: Reports |10 | |

| | | |

|C: Additional Analysis |3 | |

| | | |

|D:: Hostile Takeover |3 | |

|E. Market basket analysis |4 | |

|F. Outsourcing |4 | |

|TOTAL |60 | |

This case was developed by Dr. Bento and updated by Dr. Aggarwal

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download