BUSN 331 American Public Military University Programming Worksheet

P.O. #Order Date
Agent
Customer
Product
Quantity
Item Cost
% Profit
Total Cost
145248
1/3/07 Susan
Focus Ltd.
Blender
140
$ 45.00
10%
$ 6,300.00
145249
1/14/07 Karen
Tipo Inc.
Toaster
60
$ 50.00
22%
$ 3,000.00
145250
10/26/07 Connie
Focus Ltd.
Juicer
100
$ 35.00
28%
$ 3,500.00
145252
2/13/08 Bart
Raysun Inc.
Mixer
36
$ 75.00
20%
$ 2,700.00
145254
3/29/08 Karen
Delta Ind.
Juicer
300
$ 35.00
10%
$ 10,500.00
145255
2/16/07 Bart
Delta Ind.
Juicer
35
$ 35.00
40%
$ 1,225.00
145256
2/5/07 Susan
CFR inc.
Blender
20
$ 45.00
25%
$ 900.00
145257
3/9/07 Connie
Tipo Inc.
Juicer
125
$ 35.00
26%
$ 4,375.00
145258
3/20/07 Connie
Tipo Inc.
Toaster
20
$ 50.00
25%
$ 1,000.00
145259
4/11/07 Susan
Delta Ind.
Can Opener
180
$ 25.00
12%
$ 4,500.00
145260
4/22/07 Susan
Delta Ind.
Microwave
10
$ 250.00
25%
$ 2,500.00
145262
5/25/07 Connie
Tipo Inc.
Can Opener
220
$ 25.00
10%
$ 5,500.00
145263
6/5/07 Karen
Zorken
Juicer
62
$ 35.00
35%
$ 2,170.00
145265
6/16/07 Karen
CFR inc.
Toaster
200
$ 50.00
10%
$ 10,000.00
145266
7/30/07 Tony
Tipo Inc.
Microwave
40
$ 250.00
20%
$ 10,000.00
145267
8/21/07 Bart
Zorken
Juicer
145
$ 35.00
25%
$ 5,075.00
145269
9/1/07 Karen
Zorken
Toaster
140
$ 50.00
15%
$ 7,000.00
145271
7/8/07 Bart
Miller Ind.
Microwave
55
$ 250.00
18%
$ 13,750.00
145273
9/12/07 Connie
CFR inc.
Juicer
250
$ 35.00
15%
$ 8,750.00
145276
10/15/07 Connie
Tipo Inc.
Can Opener
20
$ 25.00
6%
$ 500.00
145280
12/9/07 Tony
Raysun Inc.
Mixer
55
$ 75.00
18%
$ 4,125.00
145281
12/20/07 Tony
Raysun Inc.
Mixer
120
$ 75.00
12%
$ 9,000.00
145282
12/31/07 Susan
Miller Ind.
Toaster
125
$ 50.00
16%
$ 6,250.00
145285
2/24/08 Karen
Raysun Inc.
Mixer
8
$ 75.00
25%
$ 600.00
145286
3/7/08 Tony
Raysun Inc.
Blender
200
$ 45.00
8%
$ 9,000.00
145288
3/18/08 Connie
Focus Ltd.
Can Opener
56
$ 25.00
20%
$ 1,400.00
145290
4/9/08 Susan
Focus Ltd.
Blender
120
$ 45.00
12%
$ 5,400.00
145291
4/20/08 Tony
Focus Ltd.
Juicer
10
$ 35.00
50%
$ 350.00
145292
11/28/07 Karen
Zorken
Can Opener
33
$ 25.00
25%
$ 825.00
145293
2/2/08 Karen
Zorken
Juicer
160
$ 35.00
22%
$ 5,600.00
145296
2/27/07 Karen
CFR inc.
Toaster
150
$ 50.00
14%
$ 7,500.00
145297
5/3/07 Tony
CFR inc.
Blender
80
$ 45.00
15%
$ 3,600.00
145298
6/27/07 Connie
Delta Ind.
Toaster
75
$ 50.00
20%
$ 3,750.00
145299
1/11/08 Bart
Miller Ind.
Mixer
22
$ 75.00
22%
$ 1,650.00
145300
1/22/08 Bart
Miller Ind.
Microwave
80
$ 250.00
15%
$ 20,000.00
145301
5/14/07 Bart
Tipo Inc.
Juicer
190
$ 35.00
20%
$ 6,650.00
145302
8/10/07 Bart
Tipo Inc.
Microwave
65
$ 250.00
17%
$ 16,250.00
200001
9/23/07 Tony
Delta Ind.
Microwave
100
$ 250.00
12%
$ 25,000.00
200002
10/4/07 Bart
CFR inc.
Microwave
30
$ 250.00
22%
$ 7,500.00
200003
3/31/07 Tony
Tipo Inc.
Toaster
100
$ 50.00
18%
$ 5,000.00
445687
11/6/07 Bart
Tipo Inc.
Juicer
210
$ 35.00
18%
$ 7,350.00
445688
11/17/07 Bart
Tipo Inc.
Toaster
40
$ 50.00
23%
$ 2,000.00
500000
7/19/07 Susan
Miller Ind.
Can Opener
120
$ 25.00
15%
$ 3,000.00
500001
1/25/07 Tony
Tipo Inc.
Can Opener
85
$ 25.00
17%
$ 2,125.00
Total Profit
Total Sale
$ 630.00
$ 6,930.00
$ 660.00
$ 3,660.00
$ 980.00
$ 4,480.00
$ 540.00
$ 3,240.00
$ 1,050.00
$ 11,550.00
$ 490.00
$ 1,715.00
$ 225.00
$ 1,125.00
$ 1,137.50
$ 5,512.50
$ 250.00
$ 1,250.00
$ 540.00
$ 5,040.00
$ 625.00
$ 3,125.00
$ 550.00
$ 6,050.00
$ 759.50
$ 2,929.50
$ 1,000.00
$ 11,000.00
$ 2,000.00
$ 12,000.00
$ 1,268.75
$ 6,343.75
$ 1,050.00
$ 8,050.00
$ 2,475.00
$ 16,225.00
$ 1,312.50
$ 10,062.50
$ 30.00
$ 530.00
$ 742.50
$ 4,867.50
$ 1,080.00
$ 10,080.00
$ 1,000.00
$ 7,250.00
$ 150.00
$ 750.00
$ 720.00
$ 9,720.00
$ 280.00
$ 1,680.00
$ 648.00
$ 6,048.00
$ 175.00
$ 525.00
$ 206.25
$ 1,031.25
$ 1,232.00
$ 6,832.00
$ 1,050.00
$ 8,550.00
$ 540.00
$ 4,140.00
$ 750.00
$ 4,500.00
$ 363.00
$ 2,013.00
$ 3,000.00
$ 23,000.00
$ 1,330.00
$ 7,980.00
$ 2,762.50
$ 19,012.50
$ 3,000.00
$ 28,000.00
$ 1,650.00
$ 9,150.00
$ 900.00
$ 5,900.00
$ 1,323.00
$ 8,673.00
$ 460.00
$ 2,460.00
$ 450.00
$ 3,450.00
$ 361.25
$ 2,486.25
BUSN 331
Business Data Analysis and Reporting II: Relational Database Analysis
Week 8
Final Project Scenario
Your company, Internet Kitchen Appliance, LLC has been in business since January
2007. It is now May of 2008 and you are interested in understanding the profitability picture of
your company after 16 months in business. Your company sells products to contractors that
outfit the suite kitchens in new or remodeled hotels and motels. As the owner of the company,
you are interested in understanding the sales and profits for the past 16 months that you have
been in business. Your accountant has kept an excel spreadsheet during since you started in
business. This spreadsheet contains a list of all orders placed with your company during between
January 2007 and April 2008 (through April). The database you are given is by purchase order
number and Customer. In addition, the database includes each order’s contract sales amount,
product type and quantity purchased, and profit generated.
You are to begin your analysis by importing the excel data list into a relational database
(Access) where you will create queries and then reports for each of the areas you are interested in
(Tasks listed below). You decide to create queries, that investigate the sales and profits by agent,
product, and customer.
Final Project Instructions
•
Begin by downloading the Excel file for this project (Internet Kitchen Sales and Profits)
•
Import the database into Access and Create a new file with the naming convention of
JSmith_Week8_FinalProject.
•
Create all four queries asked for in Tasks 1, 2, 3, &4 (see below). You may use either (a)
the Create → “Query Design” dropdown tab to design your queries from scratch, or (b) the
Query Wizard which will walk your way through the process. You will be creating
“Summarized Queries” that provide the data in a table form that summarizes the “fields”
you are interested in. All of the reports you generate from these queries will look like a
table that sums and averages all orders for the period. The first table you build for Job 1 in
Access should look approximately like the following table:
1
Table 1. Example of Access Generated Table for Task 1, 2, 3
Agent
Sum of Total
Average of
Sum of Total
Average of
Sales
Total Sales
Profits
Total Profits
Tony
Susan
Karen
Connie
Bart
Total
•
Each table in the subsequent Tasks will have similar columns except for the first
column. The first column heading (and rows below) will change depending on whether
you are analyzing by agent, by product, or by customer. You are not required to import
these tables in to this Word document, these tables are for your use in analyzing the data.
•
Once you have completed all four queries, create Access Reports from each query. Add
page numbers, a report title, the company name, and one other modification that you
decide to use to “dress up” your report.
•
After completing all the queries (tables) and reports, you have all the information you
need to provide a short synopsis of the findings, your conclusions, and your thoughts for
improving your business in the coming year. Make note of all of this in the space
provided under each task’s instructions in the following pages.
•
Once you have completed your work in Access and completed your analysis of the data
findings, drawn your conclusions and made recommendations in each of the tasks below,
submit both the Access and Word files in the Week 8 Assignments section.
2
Task 1: In Access, create one Query and then a report that summarizes the sales and profits by
Agent (Susan, Karen, Bart, Connie, and Tony). Insure that all the following information is
included in the Access Report:
•
Total Sales for all orders placed during the period for each agent
•
Average of all total sales for all orders placed during the period for each agent
•
Total Profit for all orders placed during the period for each agent
•
Average Profit of all orders placed during the period for each agent
You do not need to copy the query generated summary table from Access to this word file,
simply use the information to develop an analysis of the data by agent. In the area directly below
provide a one or two paragraph analysis of the report by agent. Include a summary of your
findings, conclusions, and any recommendations you may have for how to grow profits.
Task 2: In Access, create a Query and then a report that provides the following information by
Product (Juicer, Toaster, Microwave, Can opener, Mixer, Blender). Insure that all the following
information is included in the Access Report:
•
Sum of all Quantities sold by product
•
Total Sales for all orders placed during the period for each product
•
Average of all total sales for all orders placed during the period for each product
•
Total Profit for all orders placed during the period for each product
•
Average Profit of all orders placed during the period for each product
In the example table above the first column will include a title for “Products,” all other columns
will remain the same. You do not need to copy this table to this word file, simply use the
information to develop an analysis of the data by agent. In the area directly below provide one
or two paragraph analyses of the report by product. Include a summary of your findings,
conclusions, and any recommendations you may have for how to grow profits.
3
Task 3: In Access, create a Query and then a report that provides the following information by
Customer (Focus, Delata, Tipo inc., Zorken, CFR, Miller Ind., Raysun). Insure that all the
following information is included in the Access Report:
•
Total Sales for all orders placed during the period for each customer
•
Average sales value by customer for all orders placed during the period
•
Total Profit generated by customer for all orders placed during the period
•
Average Profit generated by customer for all orders placed during the period
In the example table above the first column will include a title for “Customers,” all other
columns will remain the same. Use the information from your query and report to develop an
analysis of the data by agent. In the area directly below provide one or two paragraph analysis of
the report by product. Include a summary of your findings, conclusions, and any
recommendations you may have for how to grow profits.
Task 4: In Access, create a Query and then a report that provides the following information by
Product (Juicer, Toaster, Microwave, Can opener, Mixer, Blender)
•
Total Sales quantity by product and by customer during the period include the sum of all
orders for each product by customer and the average quantities
Your table for this query will be simpler than the three previous. It should look like the
following:
Tale 2. Example Access Table – Task 4
Product
Sales Units
Sales Units
Sales Units
Sales Units
Sales Units
Sales Units
by
by
by
by
by
by
Focus Ltd.
Tipco Inc.
Zorken
Miller Ind.
Raysun Inc.
CFR Inc.
Blender
Juicer
Mixer
Can opener
Toaster
Microwave
4
Use the information from your Access Query (summary table) and Report to develop an analysis
of the unit sales by customer. Do you see any opportunities? In the area directly below provide
one or two paragraph analyses of the report by product. Include a summary of your findings,
conclusions, and any recommendations you may have for how to grow profits
5