Using Historical Data For Sales Forecasting

How to master Forecasting as an Accountant #1 Use your accounting skills to identify business drivers You can prepare financial statements, so you have attention to detail. So, take a closer look at the financials. Dive deep into the variances from one quarter to the next and compare them to the same quarter of the previous year. Consider what happened in other parts of the P&L when revenue jumped or dropped suddenly? Did marketing expenses increase? Or did a new feature come online (R&D cost hitting operational costs)? #2 Understand your business drivers Now that you identified issues to talk about, take them to the budget owners in the commercial team. However, I’d recommend not to ask what they think will happen in the future. That’s because you haven’t yet built enough of an understanding to be able to tell when an assumption is overly-optimistic or pessimistic. Instead, ask them about why drivers changed in the past. Do we have a good understanding of the causes? If so, what are internal and external factors that contributed? #3 Create ranges Look at each major driver and create worst-case, best-case, and expected scenarios. Refer back to what you learned about how much things fluctuated in the past. While not perfect, taking possible ranges from historical data is a good starting point. #4 Consolidate Now, simply put the scenarios for each business driver together and calculate what the overall best, worse, and expected outcome is. Don’t forget to brainstorm probabilities for each case so you can arrive at the average of all scenarios. #5 Ask business partners for their forecast Congratulations, you are now ready to ask your cross-functional business partners about what they think the forecast should be for their respective areas of expertise. #6 Compare business partner forecasts to your version Combining methods is a fantastic way to remove bias. See, your business partners will have likely given you highly conservative figures. That’s because they want to “beat” the forecast. And your top-down approach may lean optimistic since you are farther away from the details. #7 Discuss the differences Most value is generated in this final step. That’s because the second you finalize a forecast it becomes outdated. So, make sure you have enough time to talk to your business partners about the differences between their forecast and yours. Again, it’s not about who is right but about what the risks and opportunities are and - crucially - how to mitigate or use them to your advantage. ❓What would you add? Comment below to help others. -Christian P.S.: If you’d like to learn more from me: Subscribe to my weekly newsletter! Join 20,000+ Finance & Accounting professionals and get: ➢ 3 FP&A ideas from me ➢ 2 insights from others, and ➢ 1 infographic in your inbox ...every Tuesday. 👉 Subscribe at (free): https://lnkd.in/dredP3d5

Forecasting accuracy can make or break operations & supply chain. Why? It drives all confidence for inventory management decisions. Done right and improving it, can be the difference of ✅ Growth & Cashflow ❌ Stockouts and lost revenue + headaches But how to do that? 1️⃣ Forecast unit sales per SKU on a weekly and monthly granularity 2️⃣ Measure the planning accuracy for every SKU → Bias = Units Forecast / Actual Sales * 100 → MAE to get actual median error values per SKU 3️⃣ Group by category to get forecast accuracy on a parent-level → Bonus: Weigh by revenue share to integrate revenue importance factors Now you can dig deeper where the accuracy was low and find out … 🔹 Was my baseline forecast off? → Improve automatic baseline forecasting by adopting more advanced methods 🔹 Did I not account for demand outliers? → Extract out-of-stock and single events from your history 🔹 Do we have campaigns or events we did not consider? → Improve alignment with marketing Spherecast can help if you don’t want to use Excel for that ✌️

Want to use machine learning for better forecasting? Your models must learn whether seasonality exists in your business and successfully predict it. Here's how. First up, we need a working definition of trend: Patterns that appear at regular intervals (e.g., weekly or monthly). Think of seasonality as a factor that modifies the KPI you are trying to forecast: - Retailers make more sales in November and December. - Customer service receives fewer calls on weekends. - Airlines carry more passengers around holidays. - Website visits are higher in the morning. As always, the key to building a powerful machine learning model is knowledge of the business process. For this post, the business knowledge takes on two forms: 1 - Knowing that seasonality is part of the business process. 2 - Understanding the nature of the seasonality. For this post, we'll assume that seasonality exists and that its nature aligns with the calendar year - for example, the classic seasonality of brick-and-mortar retail (i.e., Black Friday). As with any machine learning model, you must provide the algorithm with enough data so that patterns can be learned. I will cover one aspect of this in a later post, when I discuss lagged features. A powerful way to help ML forecasting models learn seasonality is to provide features that explicitly detail seasonal aspects of the business process. This is a bit abstract, so let's explore the scenario of seasonality manifesting within a calendar year. Let's say you're trying to build an ML forecasting model for a monthly KPI (e.g., sales). Since you are aware that the business process exhibits seasonality within each calendar year, providing the month name as a feature often helps the algorithm learn this seasonality. For example, the resulting ML forecasting model can learn: - Sales are highest in November and December. - Sales are lowest in January and February. - Sales bump in August. However, keep this in mind. Months are categorical data, and you need to handle them correctly in your ML forecasting models. While you can use month numbers instead (e.g., January = 1), I prefer to use month names explicitly. Regardless of whether you use month numbers or month names, be sure to encode the data as needed to ensure that the ML algorithm treats it as categorical. For example, when using Python's scikit-learn library, be sure to use a OneHotEncoder on the month data before training your model. BTW - Millions of professionals now have access to the tools to craft powerful ML forecasting models. Python in Excel is included with M365 subscriptions and provides access to libraries such as scikit-learn and statsmodels. Everything you need to go far beyond Microsoft Excel's forecast worksheet.

“𝘐𝘵'𝘴 𝘵𝘰𝘶𝘨𝘩 𝘵𝘰 𝘮𝘢𝘬𝘦 𝘱𝘳𝘦𝘥𝘪𝘤𝘵𝘪𝘰𝘯𝘴, 𝘦𝘴𝘱𝘦𝘤𝘪𝘢𝘭𝘭𝘺 𝘢𝘣𝘰𝘶𝘵 𝘵𝘩𝘦 𝘧𝘶𝘵𝘶𝘳𝘦.” - Yogi Berra Business teams often need quick forecasts of metrics to plan resource allocation and set goals. While these forecasts don't need to be perfectly accurate, they should provide reasonable approximations of expected outcomes. Though forecasting is a rich and complex field, sometimes what you need most is interpretability and flexibility to account for various one-off events and still get a reasonable enough forecast. The amusingly named forecasting library - 'Prophet' is a popular tool for generating such forecasts. Built at Facebook and released as an open source library, Prophet is widely used for forecasting business data. It's an additive linear model that breaks down time series into long-term trends, seasonality, and special events. The model quickly learns trends and seasonality from historical data, while analysts must provide domain expertise about special events that effect past trends. I've used Prophet to set team goals where stakeholder buy-in was crucial. The model's interpretability and flexibility proved particularly valuable in these situations. Typically, in a year, there are a few one-off unexpected events that had a business impact but that we do not expect to see next year again. Prophet allows for handling such events easily, and being able to do so builds stakeholder confidence in the forecast. Being able to quickly decompose the forecast into its components is also helpful in interpreting the results. While Prophet isn't the most accurate model available - and more advanced models should be considered when precision is paramount - it remains a valuable tool for creating reasonable business forecasts. Key requirements and limitations when using Prophet: ▪️ 𝐃𝐚𝐭𝐚 𝐟𝐫𝐞𝐪𝐮𝐞𝐧𝐜𝐲 𝐫𝐞𝐪𝐮𝐢𝐫𝐞𝐦𝐞𝐧𝐭𝐬: Prophet works best with data that has regular timestamps (daily, weekly, monthly) and performs poorly with irregular time intervals ▪️ 𝐇𝐢𝐬𝐭𝐨𝐫𝐢𝐜𝐚𝐥 𝐝𝐚𝐭𝐚 𝐧𝐞𝐞𝐝𝐬: Requires at least one full season of historical data to capture seasonality patterns effectively ▪️ 𝐀𝐬𝐬𝐮𝐦𝐩𝐭𝐢𝐨𝐧 𝐨𝐟 𝐩𝐚𝐭𝐭𝐞𝐫𝐧𝐬: Assumes that historical patterns will continue into the future, which may not hold during major market shifts or disruptions, though this is something most forecasting models assume ▪️ 𝐂𝐡𝐚𝐧𝐠𝐞 𝐩𝐨𝐢𝐧𝐭 𝐝𝐞𝐭𝐞𝐜𝐭𝐢𝐨𝐧: May struggle with abrupt changes in trends unless explicitly specified through changepoints 💡 𝐏𝐫𝐨 𝐭𝐢𝐩: Always validate Prophet's forecasts against simpler models (like moving averages) and use cross-validation to assess forecast accuracy before implementation (𝘓𝘪𝘯𝘬𝘴 𝘵𝘰 𝘗𝘳𝘰𝘱𝘩𝘦𝘵 𝘸𝘦𝘣𝘱𝘢𝘨𝘦 𝘢𝘯𝘥 𝘱𝘢𝘱𝘦𝘳 𝘪𝘯 𝘵𝘩𝘦 𝘤𝘰𝘮𝘮𝘦𝘯𝘵𝘴. 𝘍𝘪𝘨𝘶𝘳𝘦 𝘤𝘳𝘦𝘥𝘪𝘵𝘴 𝘧𝘳𝘰𝘮 𝘗𝘳𝘰𝘱𝘩𝘦𝘵'𝘴 𝘸𝘦𝘣𝘱𝘢𝘨𝘦)