Maven Toys - Monthly Regional Revenue Dashboard

This is my portfolio project done for fictitious toy store chain Maven Toys (credit to Maven Analytics and Enrique Ruiz for the Advanced Excel Dashboard Design course). It is a formula-based Excel dashboard that started with a dataset of 2000+ rows and 10 columns (Year, Month, Store Name, Region, Store Type, Product Name, Product Category, Units Sold, Revenue, and Profit). This dashboard is capable of automatically updating with growing data.

Defining the purpose of the dashboard

In this project, I've played the role of a Lead Business Intelligence Analyst, tasked with preparing a new monthly dashboard for Maven Toy's Regional Sale Managers. The end users would like to be able to:

• filter by region
• track by monthly revenue trends
• see performance year-over-year
• compare performance across stores
• and identify which specify products drove the biggest gains and losses

Preparing the data

After determining the regional sale managers' needs, I decided that I only needed date-related dimensions, store names, regions, product names, and revenue measures.

I created a sheet where I would do all my behind-the-scenes work in terms of setting up my dashboard. In preparing the dashboard for scale and setting up the ability to compare year and monthly performance, I used structured table and name references as well as formulas. For example, to determine the current month of my data, I used the following expression: =MAXIFS(Data[Month], Data[Year], CurYear)

I then organized the data in an appropriate format for my visualizations.

Designing the visuals and layout

Color choice: the Maven Toys branding had a dark blue that I used as a key colour across my dashboard. The Advanced Excel Dashboard Design series used a green and red combination to represent growth and losses. I opted for a lighter blue and red, keeping in mind color-blindness and using online color-blindness simulators to ensure accessibility.

KPI cards: as the most important metric, I reserved the top-left space for monthly total revenue and comparisons MoM and YoY, using redundant visual encoding (both colour and symbol) for the supporting KPIs. In creating these cards, I used conditional formatting, linked pictures, and custom formatting.

Line chart: the year in focus was highlighted in a thicker dark blue line while the previous year was kept to the background in a thinner grey. Data labels were not used as I wanted to focus more on the trend rather than granularity.

Bar chart: a visual that combines four bar charts into one single visual that dynamically highlights stores in the dashboard's filtered region, as well as show the MoM change in revenue.

Tables: using the RANK function while preparing my data for these tables was essential in making sure the top and bottom performers were dynamic when adding new data or changing the filter.

Conclusion

As an hybrid exploratory-explanatory dashboard that would be viewed monthly at a high-level, there was no particular need to provide recommendations at this time. The information provided at any point in time or view could be used as reason to further investigate any concerns or questions.

---

Created by: Mars Huynh | Data Analyst