Sales forecasting in Excel: How to build the right forecasting model (free Excel/Google Sheets template)

While interpreting total revenues might look easy, building an accurate sales forecasting model is harder than it looks. Every business has a unique product mix and market dynamics, such as competitive environment, market share, or industry trends, and historical performance might be a poor predictor of future performance.

Accurate sales forecasting is especially hard for startups and small businesses that don’t have historical benchmark values or consistent sales performance.

In this guide to sales forecasting, we explain the importance of accurate sales forecasting and the different sales forecasting methods. We also share our top sales forecasting best practices to help you forecast more accurately and highlight some common mistakes to avoid.

Follow along by downloading our free sales forecast template as a starting point for your own sales budget.

Table of contents

• What is sales forecasting?
• Why sales forecasting is important for profitability
• Using a sales forecast to create a master budget
• Top sales drivers that influence forecasting
• Types of sales forecasting methods
• Sales forecasting best practices and common mistakes
• Conclusion: Keep a healthy bottom line with accurate sales forecasting

What is sales forecasting?

Sales forecasting is a process of estimating future sales based on past performance and trends.

Forecasts are used to predict how much the company will earn in revenue over a certain period of time, such as monthly or quarterly. They use different inputs to generate revenue estimates, and these inputs can come from a variety of qualitative and quantitive points.

Because sales forecasts affect profit and cash flow generation, they’re a key driver of a company’s financial model. Properly done sales forecasts can predict future revenues with a high degree of accuracy. This means that sales forecasts are one of the most important aspects of business planning because they’re the starting point of the master budget process.

Why sales forecasting is important for profitability

Revenue or sales is a key driver of a company’s income statement. Because it’s the first line item on a company’s profit-and-loss statement (P&L), it’s also sometimes called the top line. Revenues flow through the rest of the income statement and affect both the subtotals and net income.

To get from revenue to net income, you need to subtract several groups of expenses. The first is the cost of goods sold (COGS), representing direct costs associated with a product or service. Common categories of COGS include direct materials, direct labor, and overhead expenses, which are costs that typically scale as revenue grows. Subtracting COGS from sales results in a subtotal called gross income.

After you’ve calculated your gross income, you then subtract all operating expenses not directly tied to revenue volume. These expenses can include salaries, marketing, office rent, and non-cash depreciation and amortization charges. Subtracting these costs from gross income leaves you with your operating income, also called earnings before interest and taxes (EBIT). This subtotal is an important financial metric because it represents the company’s ability to make money. 

Finally, subtract interest expenses and taxes from your operating income to arrive at your company’s net income or bottom line. 

Because the health of a company’s net income is dependent on sales, it’s important to be able to accurately predict revenues. Not only is a company’s profitability very sensitive to changes in sales forecasts, but net income, or EBITDA, is also a key input of the company’s cash flow statement.

Over-optimistic sales forecasts can mislead management into thinking that they are cash-rich while they are at risk of running out of funds. 

As we’ll see next, getting the sales forecast wrong can also lead to over- or under-producing, adding operating expenses faster or slower than necessary, and other sub-optimal operational decisions. This explains why the sales forecasting model is the starting point of the master budget cycle.

Using a sales forecast to create a master budget 

No matter its size or offering, every company requires a master budget.

The master budget comprises an operating budget and a financial budget over a specific time period, such as one year, or the operating cycle. The focus of the operating budget is on the management and planning of resources, while the financial budget outlines cash inflow and outflow forecasts.

The sales budget is the first budget in the master budget cycle. This is because sales volumes affect other budgets, such as the production budget and its sub-budgets, which include the direct materials budget, direct labor budget, and manufacturing overhead budget. Non-manufacturing budgets, which include sales, marketing, administrative, and R&D, as well as cash flow budgets, are also affected by the sales budget. 

The type and number of budgets that comprise a master budget depend on your business. For example, not every company requires an R&D or even a direct materials budget. However, in every company, all budgets are interrelated and driven by the sales forecast budget. 

Top sales drivers that influence forecasting

Since we are now clear about the importance of sales forecasts in an overall financial planning and forecasting framework, let’s talk about the key drivers that can enhance your sales forecast model’s accuracy.

In their most rudimentary form, sales forecasts are a function of sale prices multiplied by unit sales for various products, irrespective of whether it’s a physical good or a service. However, since businesses are extremely diversified, revenue models come in all shapes and colors. That’s why you must develop a thorough understanding of your company’s business model.

Sales forecast models usually consist of monthly sales projections. While the best financial models are built by individuals with solid financial modeling skills, they rely heavily on the inputs provided by other business partners. Sales, Production, Marketing, and HR departments can all impact your pro forma sales numbers.

We’ve created a simple template for a sales forecast in Excel to use for illustration purposes. You can download the Excel/Google Sheets example here.

Below we cover a non-exhaustive list of sales drivers that impact sales forecasting.

1. Trends and seasonality

Sales volumes might be a function of a sales base multiplied by a growth rate. The sales growth rate doesn’t need to be constant but could also be exponential. The demand for a minimum viable product (MVP) or a new product, for example, might be quite limited initially. But once the product receives acceptance, sales growth could skyrocket. 

If there is a trend, the sales forecasting model should exhibit an upward or downward pattern.

As an example from our template, the growth of product B is exponential and the growth rate needs to be reduced because otherwise, the number explodes. This is something you need to be very cautious about when building a sales forecasting budget.

A trend could also be combined with seasonality effects. Champagne, toys, umbrellas, and ice cream are all textbook examples of products with seasonal demand businesses. Their sales volumes are heavily correlated to weather conditions or holidays. This is reflected in the growth rates of product C in the template, which exhibits a seasonal pattern.

2. Launch of a new product and timing uncertainty

A new product introduction is always exciting, especially if the products are expected to generate lofty returns. However, the development of a new product or service is often plagued by hiccups and delays.

Production barriers lead to uncertainty regarding which month you should start recording revenues in your sales forecast model. The number might end up being pushed back by several months.

3. Sales management

Your sales forecasting model also needs to be aligned with your sales and marketing plans, sales pipeline, and the size of the sales team. If you have insufficient sales reps or account managers to realize your sales target, the sales projections are based on thin air.

Many B2B organizations end up bidding or tendering for new business. The probability of signing a new customer differs according to the status of the negotiations. If you include a potential contract prematurely in your financial projections, you create a risk of underperforming relative to your sales budget.

Only potential business deals with a sufficiently high probability of materializing should be included in your sales forecasts. 

Assigning a probability in your CRM to each stage of the contract negotiations and using that in your forecast can greatly increase the accuracy and reliability of your projected sales data. This probability should be based on historical data and vetted by sales leadership.

4. Marketing strategy

The top line of some businesses, especially SaaS, consumer apps, marketplaces, and e-Commerce, is very dependent on customer acquisition through marketing campaigns. If your conversion rates are low because of ineffective marketing campaigns, wrong product-market fit, or poor sales funnel architecture, your sales numbers are going to disappoint. That’s why it’s important to measure your customer acquisition cost (CAC) and conversion rates.

Types of sales forecasting methods

There are several widely accepted sales forecasting models. Choosing among them is a judgment call based on what’s most appropriate to your particular context. You might also consider forecasting with several approaches and studying whether those approaches converge. If they don’t, it can help you refine your thinking about how the business works and the best forecasting technique for yours. 

In our sales forecasting template, we used a mixture of different forecasting techniques in column O and left some comments for you in column AB.

Below we compare some of the most common methods of sales forecasting.

Top-down vs. bottom-up sales forecasting

At the simplest level, you can think about sales forecasting from either a top-down or bottom-up approach:

• Top-down forecasting starts with macro factors, like overall market size, market share, and Board-level goals and aspirations, to project the company’s aggregate sales.
• Bottom-up forecasting starts with micro factors, such as selling price and volume, the number and ramp of salespeople, the length of the sales cycle, conversion rates from lead to opportunity to closed and won deal, constraints on production capacity, and more. 

One way to think about top-down vs. bottom-up sales forecasting is that the top-down forecast represents factors the business has less control over, and the bottom-up forecast represents the business’s operational ability to execute. If you rely only on a top-down forecast, it’s easy to get caught up in your own hype without a realistic plan or budget to deliver. 

However, if you rely only on a bottom-up plan, you’ll never push the business beyond its comfort zone, and people’s natural desire to underpromise and overdeliver will compound in every department and at every level, and the business will likely underperform its potential. 

It’s best to use both forecasting approaches and triangulate between them. Additionally, watching key SaaS metrics, like CAC, magic number, and others, can help ensure the plan is aggressive but realistic.

The table below illustrates the difference in granularity between the top-down approach and the bottom-up approach.

Other common forecasting techniques

There are only a few forecasting techniques that FP&A professionals use widely in creating robust forecasting models.

These other techniques fall into two major groups—historical data forecasting and regression analysis—which are explained below.

Historical data forecasting

Historical data forecasting takes data from different points throughout the company’s history and uses it to identify quantifiable trends in revenue that can be used to estimate future sales. 

There are three ways to conduct historical data forecasting using different data sets:

1. Point estimate: If you believe a particular sales number is likely to be stable, you could simply forecast the value of the previous period into the future. This might be the case when a company has a small sales presence in a particular market but doesn’t plan to invest in it. To perform a historical data forecast using a previous period, refer to the relevant cell and use the value for the entire forecasting period.
2. Average: Another popular forecasting technique is to take the average of the last 6 or 12 months. In this case, you just need to type =Average(range of cells). This is useful if you expect stability generally but the historical data is a bit noisy.
3. Seasonality: If your business has seasonal patterns visible in historical sales, you could refer to the corresponding period in the previous year.

Regression analysis

Regression analysis is a set of statistical methods used to predict the value of a dependent variable based on one or more independent variables. The independent variables could be bottom-up variables, like the number of fully-ramped salespeople or the marketing budget, or top-down variables, like economic growth, inflation, or sales of complementary products. 

For example, if you sell roof racks for sedans or iPhone cases, the sales of sedans and iPhones would be leading indicators that could forecast potential demand for your products. 

Don’t spend too much time getting exact values for every minor driver. Instead, spend 80 percent of your energy on inputs that have a major impact on your company’s sales forecast.

Sales forecasting best practices and common mistakes

Establishing accurate sales forecasts is hard. That’s why even well-established and well-funded public companies regularly miss their revenue targets or revise them down because their outlook is no longer achievable. However, following a few best practices go a long way toward creating a credible sales forecast model.

Keep in mind the following best practices when doing your sales forecasting:

• Avoid seeking 100% accuracy: Realize that your sales forecast will not be 100% accurate. Market conditions are dynamic, and your sales forecast is your best guesstimate based on the available information when you created the forecast.  
• Perform regular variance analyses: A variance analysis between the sales budget and the actuals should be performed on a weekly or monthly basis. If there is any discrepancy between the two, it needs to be analyzed and explained properly. This will lead to improved insights and potentially more accurate projections going forward. See our guide and template on the variance analysis for further help.
• Challenge your assumptions: Historical relationships should not be considered an eternal truth because they can break down. It is important that you continuously revalidate your major assumptions to ensure that your sales forecast model remains valid.
• Don’t get over-optimistic: Beware that your sales projections are realistic. While it might be enticing to portray fantastic growth numbers, nobody is going to attach any value to your projections if you constantly miss your targets.

In addition to the above tips for accurate sales forecasting, it’s also important to collaborate with other departments. While finance professionals are great at building financial models, they’re not in the best position to make assumptions. The best financial models factor in bottom-up operational dynamics from Sales, Marketing, Production, and all other departments. 

Getting input from other departments requires substantial collaboration with departmental leaders. This is challenging to do in spreadsheets because you need to keep data inputs from other systems, such as CRM and HR, up to date while keeping sensitive data, like budgets and salaries, segregated. Finance departments end up spending a lot of time updating and juggling spreadsheet versions manually instead of enhancing models or understanding the business drivers better.

Keep a healthy bottom line with accurate sales forecasting

The sales forecasting budget is a vital driver of pro forma financial statements. It’s the first budget in the master budget cycle that drives the development of all other budgets and creates the capacity for the business to operate. While a reliable sales budget is a prerequisite for the company to make appropriate decisions, it can be tough to create an accurate sales forecast.

Sales forecasting accuracy depends on numerous factors, including complex and dynamic market environments and historical relationships that might not be valid in the future. 

A robust sales model must be based on the collective input of all business partners, such as your Sales, Marketing, Production, and HR departments. Companies cannot rely only on Finance alone to develop the sales budget. Finance rarely possesses the most accurate and latest information to make assumptions. Finally, remember that even the best model is likely to contain inaccuracies. It should be perceived as the company’s best guesstimate given the information that was available at the time.

To develop your sales forecasting skills, download and use our free sales forecasting Excel template to help launch your company’s sales forecasting model. 

Sources:

1. https://www.investopedia.com/terms/p/plstatement.asp 
2. https://www.accountingtools.com/articles/master-budget 
3. https://hbr.org/1971/07/how-to-choose-the-right-forecasting-technique
4. https://hbr.org/2015/11/a-refresher-on-regression-analysis