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鈥檒l 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 . 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鈥檛 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鈥檒l require you to use the formula:
| st = 伪xt+(1 鈥� 伪)st-1= st-1+ 伪(xt 鈥� st-1) |
In this formula:
Despite this being the 鈥渟implest鈥� exponential smoothing formula, it鈥檚 still a complicated calculation. That鈥檚 why most retailers let 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 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鈥檙e 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鈥檚 commonly used alongside 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鈥檚 called a 鈥渕oving鈥� 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 . You鈥檒l 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鈥檒l 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鈥檝e 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鈥檛 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鈥檙e 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鈥檚 easy to digest and apply to your inventory forecasts.
The diagrams in Excel often lead to inventory insights that can鈥檛 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鈥檚 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鈥檚 important to note that data visualization is geared towards Excel experts. Meaning it鈥檚 not super intuitive for anyone new to the platform. That鈥檚 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 鈥榮tatistical鈥� category, Excel has 6 different time series :
Utilizing these functions can help your brand predict future values based on its historical data.
Be aware, however, that you鈥檒l 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鈥檚 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鈥檙e an up-and-coming DTC brand, you can鈥檛 afford to spend tons of time figuring out your spreadsheets. You鈥檒l 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鈥檙e 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鈥檝e 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鈥檛 exactly efficient. The whole approach is complicated, confusing, and clunky.
What鈥檚 more, because Excel files contain so many details, functions, and so on, there鈥檚 way too much information to try and organize onto a single page. This means you鈥檒l 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, 网红黑料 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鈥檝e 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鈥檙e 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鈥檛 a one-time occurrence. It鈥檚 an ongoing process your brand will repeat again and again, and one that grows as you introduce new products.
That鈥檚 why it鈥檚 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 网红黑料.
Sure, Excel鈥檚 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 网红黑料 subscription.
Not only does 网红黑料 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.
网红黑料 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, 网红黑料 understands that your forecasts won鈥檛 ever be 100% accurate (especially with the unpredictability of today鈥檚 supply chains). That鈥檚 why 网红黑料 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? 网红黑料 offers connected spreadsheets that pull real-time product and purchase order data from any of 网红黑料鈥檚 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 网红黑料 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]](/wp-content/uploads/2023/06/Multi-location-inventory-management-scaled.jpg)
