OFFICE PRODUCTS COMPANY
(created by Prof. Al Bento)

The Office Product Company distributes ten office products in five cities in Northern California. Each product has the same selling price and unit cost in all of the five cities. The cost data is shown below in Table I, and the sales data for the month of January is shown in the next page in Table II.

Table I: Product File

Part Number	Part Description	Unit Cost
001	Note pad - white	2.50
002	Note pad - yellow	3.00
003	Box of staples	1.37
004	Paper clips (box)	0.51
005	Pencils (3)	0.36
006	Pen - type A	3.00
007	Rubber bands (20)	0.30
008	Pen - type B	1.50
009	Glue	1.93
010	Eraser	0.95

OPC is experiencing declining profitability and Mr. John Klein, VP Finance, is trying to figure out what is the problem. He is not sure if OPC is having a product mix problem -- in which case OPC should drop the unprofitable products -- or a location problem -- in which case OPC should close a branch office.

Mr. Klein hired your consulting company (BNT) to analyze the situation and make recommendations. A senior partner of BNT assigned this job to you, and suggested the following actions:

create three tables (Product, City and Sales) using a data-base management system, such as Microsoft Access,or MySQL.
load the data shown in Tables I and II into the tables,
print a report of the tables to be sure that there are no errors in data entry and for documentation purposes,
join the tables using part number and city number as the common fields, and
prepare a summary report by city and another by product, including details of the sales and sub-totals and totals for costs, sales and profit (include profit as percentage of sales) . Hint: use queries to create tables and then prepare reports.

Please, document all stages of your solution and include a management summary in the beginning. The management summary should define the objectives of the system, represent (in graphical and relational notations) and explain the data base design, and explain the outputs obtained and how they can be used to help Mr. Klein analysis of the OPC problems.

Table II: Sales File

Invoiceno.	Citycode	Cityname	Partno.	Quantity	Unitprice
101	10	Stockton	001	45	3.50
102	10	Stockton	002	50	4.00
103	10	Stockton	003	30	2.87
104	10	Stockton	004	100	0.67
105	10	Stockton	005	180	0.78
106	10	Stockton	006	25	5.00
107	10	Stockton	007	300	0.45
108	10	Stockton	008	78	3.78
109	10	Stockton	009	54	2.67
110	10	Stockton	010	32	1.67
111	20	Modesto	001	40	3.50
112	20	Modesto	002	40	4.00
113	20	Modesto	003	20	2.87
114	20	Modesto	004	80	0.67
115	20	Modesto	005	150	0.78
116	20	Modesto	006	20	5.00
117	20	Modesto	007	220	0.45
118	20	Modesto	008	70	3.78
119	20	Modesto	009	43	2.67
120	20	Modesto	010	22	1.67
121	30	Turlock	001	22	3.50
122	30	Turlock	002	28	4.00
123	30	Turlock	003	11	2.87
124	30	Turlock	004	50	0.67
125	30	Turlock	005	76	0.78
126	30	Turlock	006	11	5.00
127	30	Turlock	007	147	0.45
128	30	Turlock	008	27	3.78
129	30	Turlock	009	24	2.67
130	30	Turlock	010	15	1.67
131	40	Merced	001	9	3.50
132	40	Merced	002	12	4.00
133	40	Merced	003	7	2.87
134	40	Merced	004	29	0.67
135	40	Merced	005	45	0.78
136	40	Merced	006	5	5.00
137	40	Merced	007	60	0.45
138	40	Merced	008	10	3.78
139	40	Merced	009	16	2.67
140	40	Merced	010	10	1.67
141	50	Denair	001	2	3.50
142	50	Denair	002	5	4.00
143	50	Denair	003	1	2.87
144	50	Denair	004	15	0.67
145	50	Denair	005	10	0.78
146	50	Denair	006	3	5.00
147	50	Denair	007	15	0.45
148	50	Denair	008	2	3.78
149	50	Denair	009	3	2.67
150	50	Denair	010	5	1.67

This page is maintained by Al Bento who can be reached at abento@ubalt.edu. This page was last updated on August 9, 2016. Although we will attempt to keep this information accurate, we can not guarantee the accuracy of the information provided.