ACME, Inc. is a small business startup located in Atlantic Canada. The CEO hired you and your team as management consultants. On your first day, you asked the IT department to provide you with a snapshot of the company sales data in the form of a .csv file (comma-separated values) exported from their ERP system. Your job is to analyze the data using analytical tools, find answers to questions asked by the CEO, and build a management report and a dashboard containing in-depth company analysis and strategy recommendations to be considered by the top management.

Required Deliverables

1) Based on the information provided below, you are required to build a comprehensive management report that addresses the concerns and questions presented by leadership and presents practical recommendations to improve the company's financial performance and strategic business direction.

2) You are required to build a "live" management dashboard that shows key performance analytics for the organization.

a. The dashboard needs to have the capability of being dynamically updated if/when the underlying data is changed. The elements of the dashboard must reflect key performance criteria of the business, and allow managers to examine data in a variety of ways and views.

b. For the sake of building an efficient dashboard, you are required to identify at least 2 performance metrics for territory and at least 3 for employees, to be reflected on the dashboard's different visual element

On your second day, you met with the top management, and conducted a series of interviews where you had a chance to understand their concerns and listen to their questions. This section summarizes the requirements gathered during the interviews. Your deliverables must address these requirements and provide clear answers supported by evidence from the company data.

6) The sales director wants to have insights into the performance of individual sales representatives and compare their performance to the workload of each representative.

7) Based on the above analysis, provide recommendations to improve the overall company sales performance. For example, do we need more (or less) reps in certain territories?

8) Based on data analysis, the CFO wants you to suggest effective areas in the business for cost reduction and optimization.

9) Suggest and Design Corporate and individual KPIs, and reflect them in your dashboard.

10) Feel free to extend your analysis beyond the above requirements from top management, as long as it provides useful insights that can help the management.

How to Prepare & Execute

To start working on the project, you need to organize the efforts within your team and familiarize yourself with the data and requirements. The following steps represent a suggested pathway from beginning to completion to ensure efficient execution:

1) Data preparation: start by formatting your data, understanding what the data represents, and adding any missing data elements that might be needed to complete the required analysis tasks. For example, you may wish to add calculated columns that complete the business picture and help you gain further insights into the sales. The next step would be to experiment with various views and structures of pivot table analyses.

2) Keep and present your Intermediate work. For example, you can use multiple sheets, pivot tables, or charts that represent the same data. Feel free to experiment with different versions of the same data set, different designs, and different pivot table structures. Finally, you can discuss with your teammate and decide on choosing the most relevant charts and recommendations to include in the final report.

3) Build dashboards. Give yourself enough time to finalize the design of your dashboard including the visual elements and linkage to your data source(s).

4) Build management report: The report can be in the format of a PPT presentation (recommended) or a Word document. The design and layout of the report must be professional and elegant. The content must present answers to all the management requirements and must be supported by evidence from data analysis.