Lean Excel Solutions: Interactive Sales Dashboard Tutorial

Overview

• Tutorial on creating a fully interactive sales dashboard in Microsoft Excel from scratch.
• Covers steps to change the dashboard theme using standard or customized colors.

Steps to Create the Dashboard

1. Overview of Dashboard
2. Overview of Data Sheets & Updating Data
3. Prepare the Database
4. Data Analysis & Prepare Visuals
5. Design the Background
6. Prepare the Dashboard
7. Formatting

Step 1: Dashboard Overview

• Designed for B2C businesses (e.g., Dmart, Walmart, Amazon, etc.).
• Data slices include year, month, sales type, and payment mode.
• Displays total sales, profit, and profit percentage (in a monthly format).
• Product-wise sales visualized for the selected period (top 10 products visible).
• Day-wise sales and sales percentage based on sales type and payment mode.
• Shows top-selling product and product category.
• Category-wise sales contribution.

Step 2: Data Sheets Overview

Master Data Sheet (Catalog)

• Product ID: Unique identifier for each product.
• Product Name: Can be the same for different products if other parameters differ.
• Category: Product category (cosmetics, food, drinks, electronics, etc.). Create drop-down list using data validation.
• Unit of Measure (UOM): Based on products.
• Buying Price: Unit purchasing price.
• Selling Price: Unit selling price.

Input Sheet

• Columns to Fill: Date of selling, product ID, quantity, sales type, mode of payment, discount percentage.

Dashboard Sheet

• Contains icons and elements for dashboard design from the built-in library.

Analysis Sheet

• Backend calculations.

Step 3: Data Preparation

• Extract columns from the master data to the input data table using VLOOKUP (based on product ID).
• Calculate total buying and selling values.
• Add day, month, and year columns (formatted as a table).

Step 4: Data Analysis & Visuals

Pivot Tables

• Insert pivot tables for input data and rename accordingly.
• Remove grand totals where not required.

Creating Visuals

1. Add Slicers: Insert slicers in pivot tables and link them using report connections.
2. Area Chart: For day-wise sales.
3. Monthly Sales: Create table using pivot table and insert column chart. Add checkboxes to show/hide sales, profit, or profit percentage. Use IF function to link TRUE/FALSE conditions with table values.
4. Top Product Visual: Use RANK and OFFSET functions to create dynamic range charts and extract top products.
5. Scroll Bar: To navigate through the top product list using OFFSET function.
6. Treemap Chart: Create using OFFSET function and name manager for a dynamic range.

Step 5: Design the Background (PowerPoint)

• Advantages: Desired aspect ratio; rulers, guidelines and guides; align shapes easily; create shapes using merged shapes.
• Use theme colors for consistency.

Step 6: Prepare the Dashboard

• Copy designed background from PowerPoint and paste it into the dashboard sheet in Excel.
• Group and format shapes for clarity.
• Insert and format text boxes linked to the analysis sheet.
• Paste visuals into the dashboard sheet and arrange them.

Step 7: Formatting

• Ensure visuals linked correctly.
• Customize slicer formats and match with background colors.
• Finalize gradient fill colors and overall formatting.
• Change dashboard look by selecting different theme colors.

Summary

• Created a dynamic and interactive sales dashboard.
• Designed in Office 365.
• Mention of the tutorial video's thumbs up and subscription request.