min read
3 Excel Inventory Forecasting Methods & A Better Alternative
Almost every DTC brand uses Excel. But we can all agree it doesn’t excel at everything – especially not inventory forecasting.
Brands just getting started often see the value in spreadsheets for basic computations, data entry, analysis, and accounting. And sometimes, they even use Excel inventory spreadsheets to guide their demand planning.
The trouble is that spreadsheets aren’t optimal for brands wanting to scale.
But by the time a DTC seller is ready to move on from Excel, it’ll already be too late. Their spreadsheet workflow will have provided subpar recommendations that hindered revenue and growth.
Fortunately, a better alternative to Excel inventory forecasting can help DTC brands achieve the sustainable growth they deserve.
The goal of inventory forecasting is to use historical data and sales trends to predict the future demand for your products. Creating accurate forecasts is the key to keeping your business profitable and satisfying your customers.
When working in Excel, there are 3 main ways to forecast inventory: exponential smoothing, linear regression, and moving averages.
Exponential smoothing is based on the AAA version — that is, additive error, additive trend, and additive seasonality — of the Exponential Triple Smoothing (ETS) algorithm.
This algorithm helps to smooth out deviations in past data trends by identifying seasonal patterns and confidence intervals. More simply, the ETS has a way of smoothing out your data by eliminating random effects or outliers.
The idea behind exponential smoothing forecasting is to give DTC sellers a more realistic picture of their sales trends and product movement. This way, brands can build accurate forecasts for future sales and create more precise purchase orders.
Generally speaking, the smoothing method is best suited for non-linear data models (meaning, the data isn’t constant because it changes or fluctuates over time). Non-linear data can happen for a number of reasons, though seasonal demand tends to be the primary factor.
There is an option to forecast with exponential smoothing on your own, but it’ll require you to use the formula:
| st = αxt+(1 – α)st-1= st-1+ α(xt – st-1) |
In this formula:
Despite this being the “simplest” exponential smoothing formula, it’s still a complicated calculation. That’s why most retailers let Excel create a forecast sheet and run this for them instead.
Linear regression looks at the relationship between a dependent variable and 1 (or more) independent variable in your retail operations. In other words, this statistical forecasting technique examines the relationship between 2 continuous, numerical variables.
With the linear regression model, your independent variables are actually used to predict the value of your dependent variable.
Independent variables are things like customer demographics and economic factors that do not depend on other variables. Meanwhile, dependent variables might be your production planning or product price that do rely on other variables.
Linear regression works by fitting a linear equation to your observed data (which is made up of all the variables you’re working with).
Say you have the data for your previous year’s sales and want to forecast sales for the current year. Since you only have a single cycle of historical data, Excel won’t be able to properly identify patterns in seasonality (meaning exponential smoothing is out of the question).
However, Excel can perform a linear regression analysis to calculate your forecasts and give you an idea of what you might sell for the duration of the year.
The linear regression formula looks like this:
| Y = a + bX |
In this formula:
If that equation feels a bit overwhelming, you’re not alone. Excel can compute linear regression on your behalf and then turn the answers into a graph for further analysis by your brand.
This graph is simply a visual representation of quantitative variables. It shows the correlation between all your data and helps explain your dependent variables’ behavior.
The goal of linear regression is to estimate your variables’ impact and determine whether their relationship will be statistically significant to your forecasting estimates.
Moving average in Excel is used to find the average from a rolling set of data. This method analyzes a wide range of data points by creating a series of averages from the full data set. If you’re a DTC brand, this data set will come from your historical sales information.
Moving average is another statistical forecasting model that can help identify trends or buying patterns that may affect your predictions.
It’s commonly used alongside time series data to smooth out short-term fluctuations (like seasonality) and shed light on long-term sales cycles.
In simple terms, moving average finds the average amount from a set of time-stamped data. With it, you can apply that found average to future demand forecasts.
It’s called a “moving” average because old data is typically dropped when new data becomes available. This causes the average to move along this sliding time scale.
A popular way to calculate the moving average is by adding all the data points during a designated time frame, then dividing that sum by the number of time periods.
With that said, an easier way to use the moving average method is by letting Excel do the heavy lifting. You’ll first need to install the Analysis ToolPak in Excel. Then, you can use the Moving Average tool to complete these calculations for you.
Keep in mind that moving average ranks fairly low in forecast accuracy. But if you decide to use this method, it’ll work best for short-term trends and long-term demand.
Forecasting inventory in Excel can sometimes be a good option for DTC brands (but usually not). That’s because Excel offers a cheap price point, conditional formatting, data visualization, and access to this program’s built-in forecast functions.
If your brand is new to the retail scene, you’re probably looking to save money anywhere you can.
Perhaps the main advantage of forecasting inventory in Excel is that it comes with a really low price point. Microsoft has plans starting as low as $2.50 per month per user.
This price is significantly lower than other forecasting software and demand planning programs. And it’s the reason why so many new businesses adopt Microsoft.
The introductory price comes with a range of features to help budding brands manage their stock levels and forecasts without busting their budget right out of the gate.
That said, keep in mind you get what you pay for — so by spending less on Excel, brace yourself for fewer capabilities than a more advanced forecasting platform.
Conditional formatting in Excel makes it easy to highlight certain values or cells within your spreadsheets. Essentially, this feature changes the appearance of a given cell range based on the condition or criteria you’ve assigned.
For example, you might highlight all your odd numbers in green or color all your cells with a value of less than $1,000 in red. You can also customize a cell range using data bars and icon sets (like shapes, ratings, and so on).
Doing so helps to identify sales trends and patterns in buyer behavior. Both of which are likely to impact your future inventory levels.
Overall, conditional formatting makes your numbers more visually compelling, so your eyes don’t wear out from staring at the same size, color, and font all day. And yet, this benefit is also rather time-consuming since all your formatting will have to be done manually.
Speaking of visual components, Excel also does a good job with its data visualization. This makes your data easier to understand via pie charts, column charts, line graphs, and more.
Whether you’re a visual learner or just appreciate a fresh perspective on your sales, data visualization can really come in handy. This feature helps to communicate data-related trends in a way that’s easy to digest and apply to your inventory forecasts.
The diagrams in Excel often lead to inventory insights that can’t be derived from static spreadsheet data. And those insights are critical to seeing the whole picture with your products (not just the numbers themselves).
Using Excel’s chart function, you can create a variety of visuals that provide an overview of your historical, current, and forecasted data. From there, you can improve your decision-making around forecasts, replenishment, and optimal order quantities.
Still, it’s important to note that data visualization is geared towards Excel experts. Meaning it’s not super intuitive for anyone new to the platform. That’s probably why there are entire classes on navigating the Excel system!
Although Excel can fill in your predicted values (based on your past and existing values), its built-in formulas and forecast functions help this program stand out.
Excel functions are predefined formulas that actually perform calculations for you. They use specific values (called arguments) that follow a particular order or structure.
You can apply these functions to either simple or complex calculations — whatever your brand is working with.
More specifically, simple formulas will calculate the values in a cell range. In contrast, complex functions calculate the logical, mathematical, financial, and statistical side of things.
Within the ‘statistical’ category, Excel has 6 different time series forecasting functions:
Utilizing these functions can help your brand predict future values based on its historical data.
Be aware, however, that you’ll need to manually keep this data up to date (which can be nearly impossible). Otherwise, these 6 functions are useless.
When kept up with, each Excel function uses advanced machine learning algorithms — like Exponential Triple Smoothing, mentioned above — to guide your forecast predictions.
Despite the benefits Excel offers, there are many drawbacks to using this platform for inventory forecasts, including the time demand, risk of human errors, and lack of scalability.
Perhaps the biggest issue retailers have when forecasting with Excel is that the end-to-end process is extremely time-consuming.
It takes hours to locate reports from your various data sources, export those reports to Excel, and then organize your data volumes into an inventory spreadsheet. And of course, the more SKUs you have, the longer everything takes to complete.
Using Microsoft Excel is especially daunting for omnichannel retailers. That’s because data input is even more tedious when you have to collect this data from multiple selling channels.
And that all goes without mentioning the time it takes to manually update your spreadsheets — or the time required to analyze your data in preparation for forecasting.
If you’re an up-and-coming DTC brand, you can’t afford to spend tons of time figuring out your spreadsheets. You’ll be much better off using inventory forecasting software with automation and integrations to speed up the forecasting process in a snap.
As noted above, Excel users are responsible for manually updating their own spreadsheet data. And on top of wasting lots of time, updating this information yourself will impact the data’s accuracy.
In reality, you’ll never be able to keep up with the pace of your product movement.
You might update your inventory spreadsheets first thing every morning at 9:00 am. But an hour later, that data will already be outdated. This is particularly true if your brand is running a promotion or you’re selling during peak times around the holidays.
It’s nearly impossible to manage these static numbers with any kind of precision if you’re doing it all on your own. And that’s exactly why most modern retailers opt for inventory software.
The leading inventory systems track and update your inventory data in real-time. That way, your forecasts always cite the most current statistics.
Operating with real-time sales data is the only way to guarantee the accuracy of the information and improve every one of your demand forecasts.
Another drawback with Excel is that you’re putting your brand at risk for human errors. That’s because the only automated aspect is the process of generating the actual forecast.
This model leaves tons of room for errors, considering even the most experienced data entry operators will make mistakes from time to time. To err is human, right?
But the worst part is, even a single cell with the wrong data can throw off your inventory forecasts.
For example, say a team member mistakenly typed 120 units for the total sales quantity in June, but what they really should’ve input was 210 units.
The result? Your brand will be seriously understocked on that SKU and likely to stockout. So while the mistake may seem minor, it will majorly impact your bottom line.
On the flip side, inventory software comes with advanced automations to manage, track, and update your data with minimal (or zero) effort on your part.
These automations limit the number of errors your brand has to experience and help you feel confident at every stage of the sales forecasting process.
More often than not, Excel-based reports are long and complex documents that can be difficult to make sense of. Even seasoned Excel users can have trouble unpacking this information.
Plus, moving up and down, scrolling left to right, and hiding then unhiding columns to locate relevant cells isn’t exactly efficient. The whole approach is complicated, confusing, and clunky.
What’s more, because Excel files contain so many details, functions, and so on, there’s way too much information to try and organize onto a single page. This means you’ll have countless sheets (and subsequent reports) to try and manage.
As you can imagine, this complexity will influence your inventory forecasts.
Without a clear and concise report, your company will likely struggle to accurately predict demand. The fallout of inaccurate forecasting often translates to understock or overstock situations (both of which are expensive and largely preventable).
A smarter option is to use retail software to help you turn your static, complex reports into actionable insights. For instance, Cogsy forecasts future demand for your products, then transcribes that information into a 12-month inventory production plan.
If your DTC brand has used Excel for any length of time, you’ve probably noticed some issues with its scalability.
You might have worksheets filled with Excel templates and forecast functions. Still, the program starts lagging when you think you’re becoming more efficient. Unfortunately, Excel has difficulty accommodating more and more data as your brand continues to grow.
This is a big concern, as forecasting isn’t a one-time occurrence. It’s an ongoing process your brand will repeat again and again, and one that grows as you introduce new products.
That’s why it’s so important to use an operations platform that can grow with your brand. This way, your brand can generate more revenue and actually reach its full potential.
You need software that can scale at the same rate and has all the features and functionality you need to ensure your long-term success.
Like it or not, Excel is becoming increasingly obsolete with the advent of newer technologies and innovative forecasting software, like Cogsy.
Sure, Excel’s low price point is nice. But the issues associated with this platform (like the increased stockouts) will cost your brand a lot in the long run. After all, you get what you pay for.
On the other hand, the revenue you bring in by keeping your products in stock will more than pay for your Cogsy subscription.
Not only does Cogsy track all your inventory data in real-time, but it uses this data — coupled with historical insights — to deliver the most reliable forecasts for your business.
Cogsy also updates your forecasts as new information presents itself, so your projections adapt to demand in real-time (rather than staying static). Best part? Every time information is introduced, your final forecast gets wildly more accurate.
Still, Cogsy understands that your forecasts won’t ever be 100% accurate (especially with the unpredictability of today’s supply chains). That’s why Cogsy helps you seamlessly transition to selling on backorder, so you can keep revenue flowing even when your forecasts miss the mark.
Need the flexibility of staying within spreadsheets for some tasks? Cogsy offers connected spreadsheets that pull real-time product and purchase order data from any of Cogsy’s data sources — including Shopify, Amazon, and ShipBob.
This way, brands can always work with the most up-to-date and optimized information (you can’t say that about Microsoft Excel’s reporting feature).
Brands that use Cogsy save 20+ hours a week and generate 40% more revenue on average. So, what are you waiting for?
Although Excel helps retail brands “keep track” of their inventory, this platform doesn’t have a proper inventory tracking feature. You’ll need to use an inventory management system or retail operations software with this functionality to track your inventory in real-time.
Accuracy is not guaranteed when forecasting in Excel. Because Excel spreadsheets have to be manually updated, this leaves the door open for countless human errors or inaccuracies. Even a single cell with the wrong data can seriously throw off your inventory forecasts.
When working in Excel, there are 3 main methods for forecasting inventory: exponential smoothing, linear regression, and moving averages. While these Excel methods can technically get the job done, most are unnecessarily complicated, time-consuming, and error-prone.
![Your Guide To Improving Multi-Location Inventory Management [7 Strategies]](https://cogsy.com/wp-content/uploads/2023/06/Multi-location-inventory-management-scaled.jpg)
