Call/WhatsApp: +1 332 209 4094

Relational Database Analysis

Relational Database Analysis

 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.

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 Week8Project.
  • 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:

 

Table 1. Example of Access Generated Table for Task 1, 2, 3

Agent Sum of Total Sales Average of Total Sales Sum of Total Profits Average of 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 into 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.

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).  Ensure 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).  Ensure 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.

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 by

Focus Ltd.

Sales Units by

Tipco Inc.

Sales Units by

Zorken

Sales Units

by

Miller Ind.

Sales Units by

Raysun Inc.

Sales Units by

CFR Inc.

Blender            
Juicer            
Mixer            
Can opener            
Toaster            
Microwave            

 

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