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 or 4th GL,
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

Citycode	Cityname	Partno.	Quantity	Unitprice
10	Stockton	001	45	3.50
10	Stockton	002	50	4.00
10	Stockton	003	30	2.87
10	Stockton	004	100	0.67
10	Stockton	005	180	0.78
10	Stockton	006	25	5.00
10	Stockton	007	300	0.45
10	Stockton	008	78	3.78
10	Stockton	009	54	2.67
10	Stockton	010	32	1.67
20	Modesto	001	40	3.50
20	Modesto	002	40	4.00
20	Modesto	003	20	2.87
20	Modesto	004	80	0.67
20	Modesto	005	150	0.78
20	Modesto	006	20	5.00
20	Modesto	007	220	0.45
20	Modesto	008	70	3.78
20	Modesto	009	43	2.67
20	Modesto	010	22	1.67
30	Turlock	001	22	3.50
30	Turlock	002	28	4.00
30	Turlock	003	11	2.87
30	Turlock	004	50	0.67
30	Turlock	005	76	0.78
30	Turlock	006	11	5.00
30	Turlock	007	147	0.45
30	Turlock	008	27	3.78
30	Turlock	009	24	2.67
30	Turlock	010	15	1.67
40	Merced	001	9	3.50
40	Merced	002	12	4.00
40	Merced	003	7	2.87
40	Merced	004	29	0.67
40	Merced	005	45	0.78
40	Merced	006	5	5.00
40	Merced	007	60	0.45
40	Merced	008	10	3.78
40	Merced	009	16	2.67
40	Merced	010	10	1.67
50	Denair	001	2	3.50
50	Denair	002	5	4.00
50	Denair	003	1	2.87
50	Denair	004	15	0.67
50	Denair	005	10	0.78
50	Denair	006	3	5.00
50	Denair	007	15	0.45
50	Denair	008	2	3.78
50	Denair	009	3	2.67
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 15, 2005. Although we will attempt to keep this information accurate, we can not guarantee the accuracy of the information provided.