Forecasting isn’t just about numbers, it’s about empowering your eCommerce business with the insights needed to plan effectively and grow sustainably. By looking ahead with data-driven insights, you can make smarter decisions to best shape the future of your business. Here’s everything you need to know to get started.
What is Forecasting
Forecasting is all about using past data to predict future sales, and it’s a key aspect of running a successful, well-organized, and sustainable business. It’s your crystal ball (backed by numbers) that helps you anticipate incoming revenue and make smarter decisions. But let’s be real: predicting future sales, especially in eCommerce, is no walk in the park.
For the best results, aim to create a forecast that spans the upcoming year. If that feels too ambitious, start with at least six months. This timeline gives you enough of a view into the future to plan and order stock to keep your inventory at the right levels based on realistic sales projections.
The thing about forecasting is your predictions are never going to be spot on. So instead of aiming for perfection, the goal for successful forecasting is to hit within 10% of your forecast.
That said, forecasting isn’t a one-size-fits-all solution. For smaller eCommerce businesses generating less than 7 figures annually, it might not be the best tool just yet. Why? Revenue and acquisition costs can fluctuate wildly in those early stages, making it tricky to create reliable forecasts. Once your business hits the 7-figure mark, you’ll likely have the stability needed to reap the full benefits of forecasting.
Why Forecasting is Important
The main benefit of forecasting is that it allows you to plan ahead with confidence. A well-crafted forecast highlights peak periods, enabling you to strategically manage your inventory, stock levels, customer demand, and marketing efforts. This helps ensure you don’t overstock or understock, while also targeting your audience at the most opportune times.
Beyond inventory management, forecasting helps you spot industry trends and learn from past mistakes. By analysing what’s worked and what hasn’t, you can better predict future patterns and address potential issues before they escalate.
To get the most out of forecasting, I recommend combining daily and monthly tracking. Daily tracking keeps you informed on whether you’re on course to meet your goals in real-time. Then if something’s off, you’ll have the chance to adjust it immediately, saving you from realising you’ve missed your monthly targets only after the month has ended, when it’s too late to course-correct.
Forecasting also opens the door to scenario planning. The reality is, there are countless variables that can impact your predictions—shipping costs, customer behavior, ad performance, and even external factors like economic shifts. These unknowns mean that alongside your main forecast, it’s crucial to prepare for “what-if” scenarios. While you can’t account for every possibility, you can create plans for situations where things go better or worse than expected. This way, you’re ready to adapt regardless of what happens.
Types of Forecasting
The approach you take to forecasting depends on how much historical revenue data you have.
Qualitative Forecasting
If you’re a startup or don’t have access to past sales data, qualitative forecasting is your best option. Instead of using your own sales data, this method relies on external insights, like market research, customer feedback on social media, reviews, surveys, and focus groups. While it’s not as precise as quantitative forecasting, it can still provide valuable guidance as you navigate the early stages of your business. Think of it as a starting point until you gather enough revenue data to upgrade your approach to quantitative forecasting.
Quantitative Forecasting
If your business has been around long enough to accumulate a year (or more) of revenue data, you can leverage quantitative forecasting. This method uses your historical sales numbers to predict future performance, enabling both short-term and long-term planning. By analysing past sales, growth rates, and industry trends, you can create data-driven forecasts that are much more actionable and accurate to your business than qualitative methods. Ultimately, quantitative forecasting helps you make informed decisions to optimize revenue, inventory, and overall strategy.
3-Tiered Pyramid Model
The 3-Tiered Pyramid Forecasting Model helps you structure your forecast by focusing on the most predictable, stable elements first, and then layering on more variable, less reliable data as you move up the pyramid. By combining all three tiers, you create a balanced and comprehensive forecast that accounts for different levels of certainty.
Base Tier
At the foundation of your forecast are your existing customers, your most reliable and predictable revenue source. By leveraging cohort lifetime value (LTV), you can create a reasonably accurate prediction of future revenue. Cohort LTV involves analysing the lifetime value of customers who made their first purchase in each specific month, forming monthly customer cohorts. This approach provides a clear picture of how much revenue you can expect from your existing customer base on a month-by-month basis. In the next section I’ll explain how to track and forecast this.
Middle Tier
The middle tier of your pyramid focuses on revenue from organic audience channels such as email, social media, and organic search. This step involves assessing your marketing plans for these channels and projecting revenue accordingly.
For example, this is the process you could take for organic search:
- Identify the keywords your site ranks highly for.
- Estimate your click-through rate (CTR) and purchase rate for traffic from these keywords to calculate your revenue from each keyword.
- Combine the predicted revenue for each keyword to calculate your total forecasted revenue for organic search.
Apply this approach to other organic channels, tailoring the revenue predictions based on their unique metrics and expected performance.
Top Tier
At the top of the pyramid, we have your most variable and unpredictable source of revenue: new customers from paid media. Paid acquisition is critical to growth, but it comes with uncertainty due to fluctuating conversion rates, ad performance, and costs like CPMs.
To forecast revenue from paid media:
- Plan your ad spend.
- Estimate your customer acquisition cost (CAC) based on historical performance.
- Predict the revenue your campaigns will generate.
While it’s essential to include paid media in your forecast, be cautious about relying too heavily on it for your business’s future. It’s the least stable tier and should complement, not drive, your overall strategy.
Forecasting Spreadsheet
Now that you understand why forecasting is important and which type of forecasting suits your business, it’s time to put it all into practice. A great starting point is the Lightspeed Forecasting Spreadsheet Template, which combines historical revenue data with real-time sales insights to create a robust forecasting tool.
Download the Lightspeed forecasting template here and let’s walk through it together.
Cohort Tab
When you open the spreadsheet, you’ll notice it has three tabs. Let’s start with the ‘Cohort Model’ tab, where most of the groundwork for your forecast will take place.
Start by filling in the months for which you already have cohort data in column A, as well as the upcoming months you want to include in your forecast. This step ensures your forecast remains up-to-date. In cell B5, you’ll also need to add into the assumptions your number of initial buyers.
The Cohort Model tab works by tracking the revenue generated by each cohort (the group of customers who made their first purchase during a specific month). Each row tracks each monthly cohort. The tracking begins at month 0, which is the cohort’s first month of purchase, and continues across subsequent months (month 1, month 2, month 3, etc.). The data is recorded in columns Q and onward, with actual revenue written in black and predicted revenue written in gray. These predictions are calculated based on the averages of earlier cohorts at the same stage in their month-by-month lifecycle.
For example, imagine you have lifetime value (LTV) data starting in January 2024. By December 2024, you’ll have 11 monthly cohorts, each with up to 11 months of revenue data. This allows you to see how much revenue each cohort generated with their first order in month 0, then again in their 1st month, 2nd month, 3rd month, and beyond. With this data, you can predict how much revenue the December cohort is likely to generate over the next 11 months using the averages of earlier cohorts. As time goes on and more and more monthly cohorts are added, the more accurate the predictions are likely to be.
Now we’ve covered column A and column Q onwards, let’s look at columns B-O in this tab.
Manual columns:
AOV – Simply add in the average order value from each month from your eCommerce store analytics. This data will be automatically pulled through to row 53 in the ‘Monthly Model’ tab.
Retention Change – This percentage reflects how the retention rate changes each month compared to the baseline. A negative percentage indicates retention has worsened below the baseline and should be addressed to improve performance. You can find this data in your Shopify Customer Cohort Analysis Report or through a 3rd party tool.
Ad Spend – Input the amount of ad spend used each month. I recommend spending some time playing around with this number to see how much you need to spend in order to actually hit your revenue goals. This figure is pulled through to row 39 in the ‘Monthly Models’ tab.
Paid CAC – Paid Customer Acquisition Cost (CAC) tells you how much acquiring a new customer via paid media has cost. A lower CAC indicates greater efficiency in your paid marketing efforts. You’ll be able to find this information from your in-platform tools on Meta or Google Ads.
Organic: Paid Ratio – This ratio shows the balance between new buyers acquired organically versus new buyers acquired through paid media. For example, a 2:1 ratio means you acquire twice as many buyers organically compared to paid. You’ll be able to work this out by looking at your traffic sources on Google Analytics.
Dynamic columns:
Paid New Buyers – This is calculated by dividing your estimated ad spend by your estimated paid CAC.
Organic New Buyers – Based on your organic: paid ratio, the spreadsheet calculates the estimated number of new buyers gained through organic channels.
Total New Buyers – This number is the combination of paid new buyers and organic new buyers for the month.
Weighted Average CAC – The weighted average CAC shows you how much ad spend is used on each new buyer (both paid and organic). This data is pulled through to row 54 of the ‘Monthly Model’ tab.
Total transactions – This is the total number of transactions in a given month, calculated by combining the monthly transactions from all active cohorts. For example, June 2024’s total transactions would include month 0 transactions for the June 2024 cohort, month 1 transactions for the May 2024 cohort, month 2 transactions for the April 2024 cohort, and so on. This data is pulled through to row 50 in the ‘Monthly Models’ tab.
Lifetime Txns (3yrs) – This metric estimates the total number of transactions expected over three years for each cohort.
3-year LTV – This column gives you an approximate LTV over 3 years for each cohort by dividing total transactions by new buyers and multiplying it by the AOV.
LTV/CAC ratio – The LTV/CAC ratio measures the return on investment (ROI) of acquiring a customer by comparing their lifetime value to the acquisition cost. It’s calculated by dividing the 3-Year LTV by the CAC. This data is pulled through to row 56 in the ‘Monthly Models’ tab.
Repeat rate – This percentage indicates the proportion of transactions in a given month made by returning customers.
Monthly Model Tab
Begin by inputting your numbers into the yellow-highlighted cells in column A. The type of data required for each cell is clearly indicated in column C. Once these cells are filled in, the spreadsheet will automatically generate a detailed, month-by-month forecast of your incoming revenue and outgoing costs. It’s important to note that if your business doesn’t charge for shipping, set cells A10 and A11 to $0.
For greater flexibility, you may want to customise the formulas in rows 40-42. This is the only section of the spreadsheet where you could consider overwriting the formulas. By modifying these formulas, you can switch from a fixed approach to a month-by-month approach to your operating expenses (OPEX), allowing for fluctuations over time. This adaptability can provide a more accurate picture of your business’s financial trajectory.
Summary Tab
Finally, the summary tab provides an at-a-glance overview of your forecasting data, broken down into quarterly and yearly summaries. This tab is ideal for quickly evaluating your progress without digging into the granular details of other tabs.
Forecasting is a powerful tool for businesses with stable revenue and a solid foundation of data. It gives you the insights you need to understand where your business is headed and highlights opportunities for improvement if you’re falling short of your goals.Are you ready to take a glimpse into the future of your business? Forecasting involves a lot of moving parts, and it can sometimes feel overwhelming, confusing, or time-consuming. If you have any questions or would like expert guidance to make the most of this process, don’t hesitate to get in touch.