Sales Revenue Analysis (Forecasting)

Project Overview

This project focuses on analyzing daily and monthly revenue for a small business to find trends and use basic statistical concepts to make future predictions. The analysis explores growth rates, (average, median and max values) Geometric and Arithmetic mean, and seasonal forecasting.

Business Problem

The primary objective is to analyze revenue performance in 2023 and forecast future revenue. A comprehensive analysis on quarterly performance patterns will help strategize for the following year.

Key Metrics

  • Growth Rate
  • Outliers
  • Seasonality
  • Monthly Fluctuations
  • Daily and Monthly Revenue

Tools Used

  • Excel
  • MySQL

Statistical Concepts

Arithmetic Mean – Calculates growth rate by finding the average value in a dataset. Excel Formula Used: =AVERAGE(C3:C13)

Geometric Mean – Calculates growth rate by compounding the average rate of values. Excel Formula Used: =GEOMEAN(C3:C13+1)-1

Seasonal Forecasting – Predicts peak and slow periods using data. Findings can be measured in single months, Quarters, every 6 months, or a 12-month period. Formula Used: =FORECAST.ETS(D14,B2:B13,D2:D13,6)

Dataset Used (Original and Cleaned)

Click on Image to view dataset:

Original Dataset

Cleaned Dataset

  • Dataset contains purchase records for the year 2023, removed any incorrect dates.
  • Filled all empty cells with “Not Provided”
  • Identified all values that were filled incorrectly such as ERROR for Quantity of Smoothies purchased, when the price and total spent were filled correctly. Used MySQL to input the correct value.

Key Findings

  • Data shows total monthly revenue, growth rate, arithmetic and geometric mean and seasonal forecast.
  • The conservative metric measures the predicted revenue for January 2024 without forecast seasonality, which brings the revenue to $6382. The aggressive metric shows the highest predicted revenue for this month at $6388
  • With seasonality forecasting, this brings the revenue down to $6309.

Charts

  • Highest performing month is June at a growth rate of 7.87%
  • Lowest performing is February at a decrease of -6.16%
  • April appears almost stagnant, a very slight increase at .05%
  • February’s drop signals a potential outlier, which could result in a pattern that happens in the following year.

Recommendations:

Based on current findings, analyze the following year based on quarterly performance by using the seasonality metric. Explore monthly peak and slow periods to strategize upcoming months. Take into account any outliers as this will also help determine causes for lower revenue.

Summary:

At 1.4% growth rate in 2023, the expected conservative revenue for January 2024 to be around $6382 without seasonality. With seasonality, this drops to $6309. The median daily revenue is $208 based on current data. Theres a steady amount of fluctuation each quarter with 1 out of three months performing well, which indicates a similar trend in 2024. Also, February being the outlier suggest the following year may show another outlier, uncertain whether it may be the same month or a different month. The question is whether seasonal or external factors are causing a dip in revenue for low performing months.