After taking CIS 125 you all have worked with Excel and have the requisite skills to complete this project; however, this project involves your spreadsheet skills to complete a business analysis.
This is an individual project that you will need to approach in the order of the tasks listed below. You should move through the tasks carefully, making sure that you have been successful with a task before beginning the next one: this will minimize your frustration level with this project!
Background of the Project:
A home improvement store needs your help in analyzing a year’s work of sales by department and by departmental sales managers in order to support informed managerial decision making for the coming year. You are to analyze the past year’s sales data in Microsoft Excel for this hypothetical business analysis scenario.
I. Retrieving your data – In this Microsoft Access database (), you will find a single table that contains a year’s worth of sales data by department and by the manager who was managing that department during the indicated time periods. You should note that, during this year, department managers were managing different departments during different time periods on a rotating basis.
- Import the table in the Microsoft Access database into a blank Microsoft Excel workbook (Hint: you will find the process that you need by clicking on the on the Data tab while you are in Excel).
- The imported data should appear as a table in Excel. Use the various Excel table features such as filtering to get a feeling for how you can manipulate and analyze the data.
II. Analyzing the data:
- Your task here is to carefully analyze the data to identify “findings” that management can use to support decisions related to departments and managers (all managers identified in the data are still working for the company).
- Now you are to select five factors within the dataset on which to base and support your recommendations to management. Once you have selected your five factors, you should then use the Pivot Table/Pivot Chart features in Excel to perform your analysis.a. Place your analysis and recommendation of each of the five factors in individual worksheets in your Excel workbook containing the table imported from Access. Name each worksheet with a clear and concise name to indicate its contents. E.g., A Worksheet could be named “Analysis #1” and contain data organized of Sales by department. b. In each worksheet, show the results of your analysis and, based on your analysis and finding(s), clearly state your recommendation to management by typing it in to a cell adjacent to your data analysis. You could also choose to use a “Shape” to type in your recommendation.
III. Charting Task: Create two charts that “tell part of the story” about your analysis. Place each chart in one of your analysis worksheets. You may use any chart type that you feel would be effective in depicting a particular aspect of your data analysis work. You can experiment with different chart types to select two that will clearly depict some of your findings.
Your completed assignment will be contained in a single Microsoft Excel workbook that contains eight (6) worksheets: the yearly sales data, the five analysis and recommendation worksheets (two of which include charts). Many of you will find this to be a cognitively challenging assignment: be determined to succeed with your best effort! Start Early and Ask Questions!
Submit your completed Excel workbook as an attachment to the assignment submission.