You can apply the TREND function and seasonality techniques to products, marketing channel, geography, and so on. Then we apply the monthly seasonality adjustment to the 2019 forecast.Īpplying the seasonality adjustments to the 2019 forecast alters projected monthly totals. Next, we apply the TREND function to forecast average 2019 monthly sales $6,924 in this example.Īpply the TREND function to forecast 2019 sales $6,924 in this example. Sales in October, November, and December exceeded the average by, respectively, 98 percent, 105 percent, and 301 percent. Heres a screenshot for 2018.Īverage 2018 monthly sales were $6,638. We have calculated the average sales per month for each of those years (2016 through 2018) and assigned a seasonality adjustment for each month. Multiply the seasonality adjustment times the average monthly total sales to get your forecast.įor example, assume we have annual sales data for the past three years. Compute a seasonality adjustment for each month. Calculate the average historical sales per month. Building seasonality effects into a forecasting model is a bit more complicated. Sales in October, November, and December can account for most of the annual total. But, for most ecommerce companies, sales are not consistent throughout the year. Note that the TREND formula in Excel is linear. Once you have the data in a table form, use the TREND function in Excel for forecasts. In the Excel screenshot, below, the formula is: = TREND(Historical Sales, Historical Timeline, Forecast Timeline) Once you have the data in a table form, use the TREND function in Excel for your predictions, as follows. You can predict sales for most key metrics as long as you have the historical data. Next, gather the data based on the type of forecast.Type of ForecastData RequiredOverall salesTotal sales volume by years, months, or days.Sales for each productSales volume by product name or type.Sales by geographySales volume by desired region (i.e., country, state).Sales by marketing channelSales volume by channel, such as Facebook, organic search, Google Ads. If you only have a few months of data, use it to estimate the next 30 days or so. Are you looking to predict the next 12 months, the next five years, or the next 30 days? For a 12-month analysis, it is best to have at least three years of data to establish seasonality trends. What is the purpose of your analysis? Then gather data and run a forecasting model.įirst, establish the timeline.
#EXCEL MONTHLY EXPENSES HOW TO#
In this post, Ill explain how to generate forecasts using Microsoft Excel.
But producing accurate forecasts can be confusing. Forecasting sales is useful for many reasons, such as inventory management, investor relations, and setting expense and marketing budgets.