Skip to content
Gallery
Gwendolyn Ang - BI Portfolio
Gwendolyn Ang
About Me
Share
Explore
Database Design, Querying & Analysis

icon picker
Part 2: Querying & Data Analysis

This section contains comprehensive data analysis and visualizations using SQL-extracted data to assist executives in making key decisions.
Regional Revenue Growth Analysis
Forecasting Methods Evaluation
Product Line Discontinuation
Factory Proximity Optimization
This page provides visualizations and analysis of various challenges faced by the company, with corresponding SQL queries and code blocks available in the subpages. Details on specific strategies used to improve data retrieval efficiency can be found in the
Query Optimization
⁠
page, while a summary of queries and key questions per use case are found towards the end of this page.

💵 Regional Revenue Growth Analysis

Every month, the Chief Operating Officer meets with the regional managers overseeing the four key areas of the business. Each region plays a crucial role in the company's overall performance and spans diverse geographical locations across the United States: Atlantic encompasses the northeastern states, Interior covers central US, Pacific includes the western states, and Gulf comprises southern states.
To facilitate these discussions and enhance strategic planning, the COO emphasizes the need for accurate, timely data. As such, SQL queries were developed to enable each regional manager to easily extract information on revenue shares, compound yearly and monthly growth, as well as month-over-month (MoM) and year-over-year (YoY) growth metrics.

Revenue Shares

Compound Yearly and Monthly Growth

Atlantic’s CMGR decline from 52.79% in March 2024 to 35.28% in December highlights an ongoing struggle to sustain growth over the past three years.

MOM and YOY Growth

📊 Forecasting Method Evaluation

It was found that Division Leads have been using inconsistent forecasting methods to project revenue, prompting the COO to standardize the process. Implementing a unified approach will ensure greater accuracy, consistency, and comparability across divisions, leading to better decision-making and resource allocation.
This section evaluates Moving Average, Exponential Smoothing, and Seasonal Ratio forecasts using two performance metrics. Pilot experiments are focused on the Chocolates and Other divisions, as Sugar revenue has been erratic, with sales recorded in only 21 out of 48 months over the past four years. Moreover, margins are assumed to be constant, so any revenue changes can be attributed to demand rather than operational factors.
⁠
image.png
⁠
⁠

Performance Metrics

Forecasting Methods

Findings

Conclusion and Recommendations

Despite the COO's intention to quickly standardize forecasting methods, it is evident that consumer behavior in the Chocolates and Others divisions differs significantly based on the available data.
Utilizing the seasonal method for the Others division would likely lead to inflated sales projections for most months, resulting in resource misallocation. Although the 6-month moving average demonstrates only a Mean Squared Error (MSE) of +593.65 and a Mean Absolute Error (MAE) of +0.39 relative to exponential smoothing, it should still be avoided due to its tendency to underproject sales. This could jeopardize the company’s ability to meet demand and risk losing market share to competitors. Therefore, exponential smoothing with an alpha of 0.4 is recommended for the Others division.
Meanwhile, the Chocolates division relies more heavily on historical seasonal patterns rather than recent market conditions, making the percentage of year method more suitable for this segment.
Further research could be beneficial to identify a unified forecasting method that accommodates the distinct characteristics of both divisions.
Revenue comparison. The revenue for the Others division is significantly lower than that of Chocolates. By enhancing our understanding of consumer behavior, we can identify trends more effectively, motivating Division Managers to increase sales and better meet market demand.
Parameter optimization. While parameter optimization has been conducted for both Chocolates and Others collectively, it’s essential to recognize that the ideal number of months and alpha value may differ for each division. Future studies should focus on analyzing these parameters separately to enhance accuracy.
Revenue estimation methodology. Current revenue estimations are derived from carrying forward the growth rate and actual revenue from the previous year. Implementing Compound Annual Growth Rate (CAGR) could yield a more precise total revenue figure for establishing seasonal ratios.
Forecasting methodvariations. To enhance forecasting precision, consider adopting variations of the current methods:
Current Method
Alternative
Explanation
Simple Moving Average
Weighted Moving Average
Assigns different weights to each period, giving more importance to recent data
Simple Exponential Smoothing
Holt-Winter’s Seasonal Trends
Captures both seasonal variations and long-term trends, leading to more robust and adaptable forecasts
Percentage of Year Method
Ratio to Average Method
Compares each period’s actual value to the yearly average, accounting for more stable seasonal patters and reducing bias from extreme fluctuations
Percentage of Year Method
Seasonal Decomposition
A comprehensive technique that separates a time series into its components: trend, seasonality, and residual (or noise), with both additive and multiplicative variations
There are no rows in this table
⁠


🛒 Product Line Discontinuation

Confronted with economic challenges and logistical constraints, the leadership team has resolved to streamline the product portfolio and focus on their core offerings, strengthening their lead in markets and segments where they are already winning.
The difficult but necessary choice will involve shutting down an entire category due to the costs associated with operating underutilized factories.
Additionally, to enhance overall profitability, they plan to eliminate less lucrative products from another line.
Finally, the team is also committed to developing more granular product classifications that will not only deepen their understanding of the company's offerings but also facilitate more informed decision-making and precise statistical analyses in the future.

Sales Target Accomplishment

Product Discontinuation

Product Classifications

🏭 Factory Proximity Optimization

Following the earlier decision to discontinue Sugar products, two of the five factories will be left without any products to produce. This section makes the case for repurposing these facilities to manufacture top revenue-generating products from other factories.
It’s important to emphasize that the analysis focuses solely on factory utilization and the transportation challenges associated with delivering to distant states. A more comprehensive evaluation could incorporate actual financial data, such as the net present value of shutting down a plant versus maintaining operations with new equipment that requires upfront investment.
⁠
image.png
Current factory-product assignments are 1:1. This section proposes some products to be produced in multiple factories.
⁠
⁠

Problem

Recommendations

To tackle this issue, the two facilities that were fully / partially vacated as part of the product portfolio restructuring can be utilized.
For instance, Illinois ranks among the top five income-generating states for Lot’s O’ Nuts and sixth for Wicked Choccy, yet the state-factory distances are significant, measuring 2,191.92 km and 1,171.66 km, respectively.
In contrast, Secret Factory is located within Illinois, resulting in an average distance of only 228.1 km to in-state customers.
While manual comparisons can be effective, coding can facilitate matches beyond the top 10 lists presented here.


❔Use Case Summary

Use Case
Key Question
Queries
💵 Regional Revenue Growth Analysis
How can monthly and yearly revenue growth across four key regions be tracked to support strategic planning for regional managers?
Revenue Shares. Generate wide format tables that can easily be converted to graphs without additional processing.
Year as rows and Region as columns
Year and Month as rows and Region as columns
Region and Year as rows and Month as columns
Compound Yearly and Monthly Growth
Annual Compound Growth Rate: Calculate the compound annual growth rate (CAGR) for each year compared to the base year (e.g., from 2021 to 2022, 2021 to 2023, and 2021 to 2024) for each region.
Monthly Growth Rate for Regional Managers: Develop a query to present the compound monthly growth rate for each region from January up to the current month of the year.
Month-on-Month and Year-on-Year Growth : For each region and each month, retrieve the month-on-month growth rate and the year-on-year growth rate to assess performance trends.
📊 Forecasting Method Evaluation
Which standardized forecasting methods provide the most accurate revenue projections for each division?
Forecasting Method Evaluation: Assess the effectiveness of different forecasting methods used by division managers (chocolate, others) based on their degree of errors: moving average, exponential smoothing and seasonal ratio. For the first two, also identify the ideal window size and alpha value for optimal accuracy.
🛒 Product Line Discontinuation
How can sales and performance data help identify underperforming product lines for discontinuation and better portfolio management?
Sales Target Accomplishment
Write a script that calculates and displays division sales targets for each year, assuming the initial value represents Year 1 and increases by 10% each subsequent year.
Display the sales target achievement and deviation for each division for every year.
Product Discontinuation: For divisions falling short of targets in 2024, query the following to aid in decision-making regarding discontinuation:
Historical Metrics: Retrieve historical sales value, sales volume, revenue, unit price, and unit cost for the lowest-performing division.
Contribution Analysis: Indicate the percentage contribution of each product to the aforementioned metrics.
Product Categorization Table: The VP for Commercial wants to generate relevant product classifications to better understand the company’s portfolio and make comparisions. Generate a table categorizing products by price, sales, margin, division, and factory with a maximum of 3 buckets each. Also append total value, volume, and revenue data.
Visualize the company’s portfolio based on margin, price, and division categories
Products with low sales face the risk of discontinuation, making it essential for factory managers to proactively anticipate these changes when developing production plans. Present a list of high-risk products along with their respective divisions.
🏭 Factory Proximity Optimization
How can factory reassignment optimize the production and delivery of top-selling products by minimizing transportation costs?
Factory Location Identification: Use longitude and latitude data to determine the city, state, and region of each factory.
Proximity Analysis: Query the top 10 states closest to each factory.
Revenue Analysis for Top States: For each factory, retrieve the top 10 states by revenue:
Include proximity rankings, with 1 being the closest and n being the farthest.
Calculate the average distance of these top 10 states by revenue for each factory.
Determine the percentage of total sales contributed by the top 10 states for each factory.
Factory Reassignment. For each product, calculate the average delivery distance to the top 10 revenue-generating states and identify the top 3 nearest factories. This query evaluates potential new factory-product pairings by a) finding the average delivery distance for the top 3 states by revenue and their percentage contribution to the product's total revenue, and b) comparing the current factory’s average distance and recommending a new factory based on proximity and efficiency.
There are no rows in this table
⁠


Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.