eCommerce DTC Modeling Case Study
Process
Company A is pitching a prospective client which is an eCommerce DTC company focused on selling high end digital photo frames. The company is vertically integrated and has its own factory here in the U.S. The business is completely online with all sales done through the company’s website. The product(s) are sold directly to consumers who learn about the product through online advertisements on search engines, social platforms etc. or through TV ads.
The objective is to create an income statement model for 2022-2026 using information in Exhibit A.
Provide historical figures including:
- P&L items – Gross Profit and D&A
- Margins – Gross Margin, EBITDA Margin, and EBIT Margin
- Operating Metrics – Grow rate of orders and units shipped, Average Order Value (AOV), Average Unit Value, and Number of Units per Order
Build out projections for 2022 - 2026:
- Project out Revenue, Gross Profit, EBIT, and EBITDA
Result
The completed financial model provides a comprehensive income statement for the years 2022-2026, considering multiple revenue scenarios and key operational metrics. By leveraging a bottoms-up approach, to forecast "Total Revenue," driven by "Total Orders Shipped" and "Average Order Value (AOV)," the model effectively addresses the challenge of accurately projecting the company's financial performance.
The following assumptions and calculations were made:
- Discounts and refunds were projected based on historical 2021 percentages of total revenue.
- Cost of Goods Sold (COGS) was forecasted to grow proportionally with revenue.
- Operating expenses were projected to grow in line with annual revenue growth.
- Depreciation and Amortization (D&A) was forecasted based on the 2021 historical ratio to revenue.
- Revenue scenarios for base, upside, and downside cases were incorporated to enhance the model's robustness and included an annual step-up percentage.
- A 6.0% base case growth rate was selected for both total number of units and orders shipped, representing a conservative and normalized terminal growth level.
- A 0.5% growth rate was chosen for both the number of units per order and average unit value, reflecting a conservative estimate based on similar historical values.
