How we helped a private equity firm with financial data analysis for an E-commerce hunting company

The Context

The client, a Private Equity firm, wanted the TresVista team to consolidate and clean up the raw fragmented data provided by an E-commerce hunting private company, and to perform the financial data analysis. The client also wanted the team to utilize the customer data to find the Customer Lifetime Value (CLV) and Customer Acquisition Cost (CAC) for the company. Finally, the team was asked by the client to provide outputs in the form of charts to be incorporated in the Investment Committee Memorandum (IC-Memo).

The Objective

To convert the raw unfiltered data into summary sheets to gauge the revenue bifurcation based on different criteria, incorporate them into the investment deck in the form of visuals (charts, graphs, tables) and calculate CLV and CAC.

The Approach

The TresVista team followed the following process:

• Compiled the raw data into a single spreadsheet and incorporated missing data by extracting it from other files
• Created output summary sheets of product revenues based on different parameters such as category, suppliers, brands, etc.
• Utilized unique customer data to isolate each customer, find their average lifespan in days & years, and narrowed down the average number of purchases & Average Order Value (AOV)
• Calculated Gross Profit Lifetime Value of the customers with the help of an assumed margin rate (based on product margins and overhead charges)

The Challenges We Overcame

The major hurdles faced by the TresVista team were:

• Entries were missing and outliers were present. E.g., there were negative values for COGS & quantity of the products sold
• Refining the data (850,000+ entries of sales over 4 years) and making it usable for further analysis

The TresVista team overcame these hurdles by converting the entire data set from range into tables, filtering out outliers and negative entries, and populating the missing data from reference files.

Final Product (Sanitized)

The Value Add – Catalyzing the Client’s Impact

The TresVista Team helped provide insights on the financial and customer data of the target company and the client got clarity about a potential turn-around opportunity by optimizing the supply chain and improving the sales and marketing operations after the acquisition.