Turning Raw Data Into Insights

At Discover Talent, we believe learning becomes impactful when theory meets practice. Today, we are excited to share a detailed walkthrough of a real-world scenario — building an insight-driven dashboard for a rice mill business. This topic was requested by one of our YouTube viewers, and we truly appreciate the engagement and curiosity from our growing community. Let’s dive into how sales, purchases, inventory, and operating expenses (OPEX) can be analyzed to calculate Revenue, Gross Profit, Net Profit, COGS (Cost of Goods Sold), and OPEX using Excel. 

On our channel, we’ve already introduced:

• HR Dashboard (Work in Progress)

• Nine Essential Excel Functions that save time

• Beginner’s Guide to Pivot Tables

All of these laid the foundation for today’s lesson: how to transform raw data into an interactive, business-ready dashboard. 

User Query: Rice Mill Data Analysis

A viewer asked:

“Can you create a video explaining how sales, bad purchases and inventory are managed in a rice mill, and how gross profit, net profit, revenue, COGS and OPEX are calculated from that data?”

We took up the challenge. Since no real dataset was provided, we created a sample dataset with three core sheets:

1. Purchases – paddy procurement

2. Sales – rice sold

3. OPEX – expenses like wages, electricity, transport

Step 1: Data Preparation

• Converted raw ranges into Excel tables (dynamic and formula-friendly).

• Applied formatting for dates, currency, and totals.

• Cleaned the dataset by sorting by date, removing duplicates, and checking for blanks/errors.

Remember: Clean data = Reliable insights.

Step 2: Monthly Grouping

We added a Month column using formulas (=TEXT(Date,"MMM-YYYY")) to simplify grouping in SUMIFS and Pivot Tables. This enabled easy monthly revenue and expense tracking.

Step 3: Financial Calculations

On the Report Sheet, we calculated:

• Revenue (Total Sales) – SUM of Sales

• COGS (Purchases) – SUM of Purchases

• Gross Profit = Revenue – COGS

• OPEX – SUM of Expenses

• Net Profit = Gross Profit – OPEX

Additionally, we introduced KPIs:

• Gross Margin %

• Net Margin %

• Average Purchase & Selling Rate

• Quantity Purchased & Sold 

Step 4: Visual Insights

Using Pivot Tables & Charts, we built visual dashboards:

• Monthly Sales Trends (bar chart)

• Top Suppliers by Purchase Value (pie chart)

• OPEX Breakdown by Category (donut/pie chart)

Each chart helps decision-makers:

• Track monthly sales growth

• Identify top suppliers for negotiation

• Spot cost-heavy expense categories

Step 5: Final Dashboard

We combined everything into a Dashboard Sheet:

• KPI cards (Revenue, COGS, Gross Profit, Net Profit)

• Interactive Slicers for Supplier & Customer filters

• Timeline filters for month-by-month analysis

• Visual appeal with proper formatting, grouping, and clean design

The final dashboard offers a quick view of financial performance, ready to support smarter business decisions.

Key Learnings

1. Data Cleaning is Non-Negotiable – Always check for duplicates, blanks, and errors.

2. Use Tables Instead of Ranges – Dynamic, expandable, and formula-friendly.

3. Leverage SUMIFS & Pivot Tables – Perfect for monthly trends and category analysis.

4. Visualize KPIs – Dashboards turn raw numbers into actionable insights.

5. Engage with Data – Pause videos, practice with sample datasets, and learn by doing.

If incase you are fan of listening PODCAST then we have this https://audio.discover-talent-presents.com/ available freeware podcast data analystics related tutorial FREE of COST on spotify , RSS Feed and Amazon 

Try It Yourself

👉 If you’d like the dataset used in this demo, simply comment “I need this data set” on our YouTube video — and we’ll share it with you free of cost.

Don’t forget to like, share, and subscribe to Discover Talent for more tutorials.

I’m Vinod, signing off. See you in the next class!