Computer Warehouse Application

Download the ComputerWarehouseProject.accdb file from the web board and review the tables and relationships contained within.

Computer Warehouse is a retailer specializing in technology products. You have been asked by the Director of Operations and Finance to perform a complete analysis of the company's products, sales personnel, customers, service level (timeliness of shipping) and profitability and then report your findings in a concise, graphical and easily understood format.

The organization maintains a database to store its sales information.

The database contains 5 tables:

Customers - Current and past customers of Computer Warehouse.

Products - All products available for sale.

Salesman - A list of company sales personnel.

OrderTbl - A list of all Orders placed since 1/1/2013 up to the current date that identifies the Customer, the order date, the promised ship date and actual ship date.

OrderLine - the products ordered, the qty ordered, the unit price and the amount invoiced.

All primary keys and table relationships have been set.

The Director also mentioned that he read something recently about a Digital Dashboard on which all important corporate key indicators are presented that allows top management to see how the company is doing at a single glance. He is interested in knowing more about it - but for now he has asked you to create a mock up of a digital dashboard from your analysis and then write a report to introduce it to the company's key executives.

Part 1: Analysis

Use the tools you have learned this semester to aid you in your analysis and presentation. Begin your analysis by asking some basic questions:

Which products sell the best?

Who are our best customers (those that order most frequently and who spend the most)?

What are our most profitable products?

Are there any products being held in inventory that are not selling?

How much working capital do we have tied up in inventory?

Who are our best sales men/women?

Are there any customers who have not made any purchases this year?

Are company sales growing or shrinking (graph sales along a trend line???)

Create any queries and/or reports that will help you answer the questions above. Export data as needed to Excel to perform additional operations to complete your analysis. You may decide to do all the work in Excel -with pivot tables - or a combination of Access Queries and Excel charting/graphing and tables.

Part 2: Reporting

Create your digital dashboard using Excel that will allow you to place all pertinent information on a single document. Use Charts, graphs, tables, and/or lists to illustrate, as appropriate. Be sure to clearly identify your information with titles, legends and headings.

Perform some research on the concept of a digital dashboard to better understand it.

There are many, many sites where you can learn about dashboards, but a good place to begin would be to go to Youtube and search for "Excel Campus" and "Dashboard". There is a series of videos that explain the concept.

For instructions on how to link an Excel Dashboard to Access Data, please follow the lecture closely. Video if the lecture is available.

Components that you may want to include in your digital dashboard are:

Top 5 Products in Sales

Top 5 Customers in Sales / Frequency

Top 5 Products in profitability

Top 5 Salesmen

Total Sales by Month

Value of Inventory

Consider which components should be presented based upon current month sales and which components would be better represented using YTD values.

Part 3: Recommendations & Conclusions

Prepare a report (using Word) that introduces your dashboard elements and summarizes your analysis. Pull out the graphical components from your dashboard to discuss one by one in your report. Also present any concerns or problems uncovered by your review (for example - inventory levels for products not selling - or customers not buying). Identify the issues and make recommendation for solutions.

Write a clear, concise report that uses graphical illustrations to introduce, discuss, and address business operations. Organize the report logically and categorically. Use a Memo format to create a professional looking document and pay attention to sentence structure, grammar and spelling.

To Be Submitted:

1) Repost your database back to the Blackboard showing any queries or reports generated. (Be sure to use descriptive names to your objects to help me understand the methods of your analysis).

2) Post your Excel document to the Blackboard. Your digital dashboard and all other worksheets created in your analysis should be included. Clearly identify your worksheets and operations - by placing comments and titles where appropriate. The first worksheet in the workbook should be named "Dashboard" and be a printable version of your completed dashboard (meaning - if I hit "Print Preview" your dashboard should fit and be visible all on one page).

3) Post your Word document to the Blackboard.

Attachment:- computerwarehouseproject.rar