Consulting Project Dashboard
In this write-up, I will guide you through the process of creating a consulting project dashboard using Excel. The dashboard includes data collected from Kenji Explains at careerprinciples.com, covering various aspects of the project, such as the project roadmap, sales by country, satisfaction scores, budget vs. actual costs, and staff turnover.
Part 1: Project Roadmap
For the project roadmap, I visualized the data using Excel. I obtained the raw data from careerprinciples.com, which included start and end dates for each stage of the project. To create the Gantt chart or horizontal stacked chart, I formatted the data with the “Duration” column stacked on the “Start Date” column. On the vertical axis, I labeled the names of the stages.
To ensure the horizontal axis displayed correct start and end points, I converted the first start date and last end date to the number format. Additionally, I added data labels to show the number of days spent on each stage, providing a clear overview of the project timeline.
Part 2: Sales by Country
For the Sales by Country section, I utilized a trend analysis based on four months of sales data. I incorporated trend lines to visualize the growth patterns effectively. To further enhance the representation, I applied conditional formatting to the “4m Growth” column using flag colors.
- Red flag: Indicates less than 0% growth.
- Yellow flag: Signifies 0% or higher growth but less than 100%.
- Green flag: Represents growth of 100% or more.
This color-coding system allows for quick identification of sales performance in different countries.
Part 3: Satisfaction Scores
In this part, I presented satisfaction scores for both customers and employees. To achieve this, I used two doughnut charts stacked on top of each other. The range was divided into three categories:
- Red (0% — 25%): Low satisfaction level
- Yellow (25% — 75%): Moderate satisfaction level
- Green (75% — 100%): High satisfaction level
The satisfaction score was displayed as a black line within the doughnut charts. To enhance readability, I highlighted the ranges on the initial doughnut chart and formatted it accordingly. Data labels were added to provide specific scores from the raw data.
Part 4: Budget vs. Actual Expenses
For the Budget vs. Actual Expenses section, I created a clustered column chart. To better compare the data, I represented the actual data on a secondary axis. The bars with larger widths represented the budget, while the bars with smaller widths represented the actual expenses. This visual representation allows for a quick understanding of budget adherence throughout the project.
Part 5: Staff Turnover
In this section, I employed a waterfall chart to visualize staff turnover data. The total turnover for 2023 was set as the base for the chart. The waterfall chart helps identify positive and negative changes in staff turnover, providing a comprehensive view of the overall turnover trends.
Conclusion: By utilizing Excel and the data from careerprinciples.com, I successfully built a comprehensive consulting project dashboard. The dashboard visually represents crucial aspects of the project, including the roadmap, sales performance, satisfaction scores, budget adherence, and staff turnover. The visualization and color-coding techniques employed in each section enhance the dashboard’s readability, making it a valuable tool for project analysis and decision-making.
