Part A -
Data Cleaning and Preparation.
The crude transactional data were refined and enhanced to make them accurate for subsequent analysis:
Duplicate Removal:
The similar rows were detected and deleted based on the whole data, which was the order ID. One duplicate record was processed.
Data Type Corrections:
The fields like "Order Date" and "Required Date" were changed to date fields. Quantity, UnitCost, and UnitPrice were numeric fields that were standardized to provide mathematical manipulations.
Missing Value Imputation:
All missing values in key columns such as City, Salesperson and Channel were processed. To illustrate, any record of entries that did not have a salesperson was coded as unnamed or unassigned to ensure data integrity.
Suspicious Data Flagging:
4 Unit Price: Checked on negative values (there are no such in the last set).
Discounts: 7 records were found where DiscountPct exceeded 30%. The high discount flag was used to mark these so as to review compliance.
Date Logic Correction: There were 34 cases when the RequiredDate was less than the OrderDate. These were adjusted in a required date column that was corrected (usually with a standard lead time added to the order date) so as to provide logical consistency.
Calculation of LeadTimeDays: A derivation column lead date was generated through computing the difference between the fixed required date and the order date. if(required date < ordered date, required date date+7))
Part B- Sales Operations Analysis.
There are a number of analytical frameworks that were implemented through Pivot Tables and formulaic calculations:
Cohort Analysis:
A summary table was developed to monitor the sum of gross revenue by country and time (years/quarters). This will enable the business to capture the revenue pattern of every market since the time it entered that market.
ABC Analysis:
SKUs in their product category were reviewed in terms of their contribution to gross revenue. This category aids in prioritizing high-value inventory (Category A).
Salesperson Productivity:
Each salesperson's was calculated using the following metrics:
Sum of Gross Revenue
Number of OrderID (Total orders)
Average Revenue per Order
Average Gross Profit
Channel Mix and Cannibalization:
The comparison of revenue share has been made among various regions in relation to channels such as Online, Retail, and Marketplace. The analysis is useful in determining whether online sales are increasing at the cost of the traditional retail in particular areas (e.g., the Americas or Africa).
Service Level Proxy:
The percentage of on-time orders was determined by country and category using the met helper column (which determines whether LeadTimeDays = 7 or not).
Price Compliance:
Region and Salesperson aggregated the high discount flag to calculate the proportion of orders that exceeded the 20% discount level to identify outliers such as A. Patel (Americas) or C. Otieno (Asia).
Part C Scenario Modeling (What-If).
One control panel was created that would replicate various business situations:
Input: Users have the option of modifying a global discount cap, unit cost inflation, and an uplift in quantity (sales boost).
Recalculation: The scenario profit column in the main data sheet dynamically recalculates itself based on these inputs, and the results could be compared to compare the current baseline profit with the scenario profit projected.
Part D Interactive Dashboard.
The last insights were represented in the form of visualization in a centralized dashboard:
KPIs: It has total revenue, gross profit, margin percentage, average order value, and on-time service level percentage.
Revenue per month: A line chart illustrating seasonality.
Profit by Region and Channel: A stacked column chart that visualizes the channels that bring the highest profit in every territory.
Top 10 SKUs: bar chart showing the main revenue earners.
Interactive Slicers: The dashboard will be linked to the slicers of Region Country, Channel, and ProductCategory, where users can drill down to particular units of information.
Top comments (0)