Sign In Book a demo
Return Back

Sales Forecasting in Excel: How to Choose and Build the Right Forecasting Model (Free Excel/Google Sheets Template)

Everything you need to know about Sales Forecasting and how to simplify it for your organization.

person
Uri Kogan

April 4, 2022 2 min read

Sales Forecasting in Excel: How to Choose and Build the Right Forecasting Model (Free Excel/Google Sheets Template)

What is Sales Forecasting?

Sales forecasting is a process of estimating future sales based on past performance and trends. Sales forecasts are a key driver of a company’s financial model because they affect profit and cash flow generation. It is also one of the most important aspects of business planning because it is the starting point of the master budget process.

Want to learn the best way to do Sales Forecasting in Excel? You’re in the right place.

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 (competitive environment, market share, trends, etc.), 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 article, we’ll explain the importance of accurate sales forecasting, different sales forecasting methods, and share sales forecasting best practices to help you forecast more accurately. We’ll also highlight common mistakes to avoid. You can also download our free sales forecast template as a starting point for your own sales budget, or just to follow along with our examples.

Want to simplify sales forecasting?

Then try it by automating the template with OnPlan FP&A software. We've made it easy to update data instantly with the click of a button and without relying on manual inputs.

Book a Demo Now

Why are sales forecasting models of vital importance?

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

To get from revenue to net income, you have to subtract several costs. The first is 

Cost Of Goods Sold (COGS). COGS represent all the direct costs associated with a product or service, such as direct materials, direct labor, and overhead expenses. These typically scale as revenue grows. This results in a subtotal which is called gross income.

Next, you subtract Operating Expenses not directly tied to revenue volume, including salaries, marketing, office rent, and non-cash depreciation and amortization charges. Taking these costs out of Gross Income leaves you with Operating Income, or Earnings Before Interest and Taxes (often abbreviated EBIT). This subtotal is a very important financial metric because it represents the company’s ability to make money. Finally, by subtracting interest expenses and taxes from operating income, you finally get to the company’s net income or bottom line. 

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. That’s why overoptimistic 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.

Sales forecasting drives the master budget 

No matter its size and the nature of its offering, every company requires a master budget. The master budget comprises an operating budget and a financial budget over a specific time period (usually one year) or the operating cycle. The focus of the operating budget is on the management and planning of resources, while the financial budget is centered on cash inflows and cash outflows forecasts.

Diagram  Description automatically generated

The sales budget is the first budget in the master budget cycle because sales volumes affect other budgets, such as the production budget and its sub-budgets (direct materials budget, direct labor budget, and manufacturing overhead budget), non-manufacturing budgets (sales, marketing, administrative, R&D) and cash flows. All these budgets are interrelated and are driven by the sales forecast budget. (The type and number of budgets depend on your business. For example, not every company requires an R&D or even a direct materials budget.)

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.

Aren’t forecasted sales simply forecasted unit price multiplied by forecasted units sold? Not so fast!

In their most rudimentary form, sales forecasts are a function of sales prices multiplied by unit sales for various products, irrespective of whether it is 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 can all impact your pro forma sales numbers.

We created a simple sales forecasting template for illustration purposes. You can download the Excel/Google Sheets example here.

Here’s a non-exhaustive list of sales drivers:

1. Trend & 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 initially be quite limited. 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. For example, 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 specific months. The growth rates of product C in the above table exhibit a seasonal pattern.

2. Launch of a new product & 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/service is often plagued by hiccups and delays. This leads 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 marketing plan, sales plan, 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 is important to measure your customer acquisition cost and conversion rates.

sales forecasting

The most popular sales forecasting methodologies

There are several widely accepted forecasting techniques. 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.  

  1. Top-down vs bottom-up sales forecasting. There are two approaches to forecasting: top-down and bottom-up. Top-down forecasting starts with macro factors — such as overall market size, market share, and Board-level goals and aspirations — to project the company’s aggregate sales.
  2. The bottom-up forecasting starts with micro factors, such as selling price and volume, the number and ramp of sales people, the length of the sales cycle, conversion rates from lead to opportunity to closed and won deal, constraints on production capacity, and so forth. 

Comparing top-down vs bottom-up sales forecasting

One way to think about tops-down vs bottom-up sales forecasting is that the tops-down forecast represents factors the business has less control over, and the bottoms-up forecast represents the business’s operational ability to execute. If you rely only on a tops-down forecast, it’s easy to get caught up in your own hype without a realistic plan (or budget!) to deliver. If you rely only on a bottoms-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. That’s why it’s best to use both forecasting approaches and triangulate between them. In addition, 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.

Common forecasting techniques

There are only a few techniques that FP&A professionals use widely in creating robust forecasting models. These techniques fall in two major groups:

  1. Historical data forecasting
    1. Point estimate: If you believe a particular figure is likely to be stable (for example, you have a small sales presence in a particular market and don’t plan to invest in it), you could simply forecast the value of the previous period into the future. Just 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.
  2. Regression analysis: Regression analysis is a set of statistical methods to predict the value of a dependent variable, in our case sales, based on one or more independent variables (these could be bottoms-up variables like number of fully-ramped sales people or marketing budget, or tops-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 on getting exact values for every minor driver. Instead, spend 80% of your energy on those inputs that have a major impact on the sales forecast of your company.

Sales Forecasting best practices and common mistakes

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

  • 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.  
  • 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. (We have a whole guide and template on the variance analysis to help you!)
  • Historical relationships should not be considered an eternal truth because they can break down. It is important that you continuously re-validate your major assumptions to ensure that your sales forecast model remains valid.
  • 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.
  • While finance professionals are great at building financial models, they are not best positioned to make assumptions. The best financial models factor in bottoms-up operational dynamics from sales, marketing, production, and all other departments. That requires substantial collaboration with departmental leaders. Frankly, that’s challenging to do in spreadsheets because you need to keep data inputs from other systems (CRM, HR, etc) up to date and keep sensitive data (budgets, salaries) segregated. Finance ends up spending a lot of time manually updating and juggling spreadsheet versions, instead of enhancing models or understanding the business drivers better.
Want to implement these best practices from the get-go?

OnPlan FP&A software was built to help you avoid common mistakes and evolve your forecast thanks to incoming, real-time information. Use our platform to become a master of your data and overcome the challenges of spreadsheets.

Book a Demo Now

Executive Summary

The sales forecasting budget is a vital driver of pro forma financial statements. It is 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’s financial plan and management to take appropriate decisions, it can be tough to create an accurate sales forecast. This is because the market environment is very complex, dynamic, and historical relationships might not be valid in the future. 

A robust sales model must be based on the collective input of all business partners, such as sales, marketing, production, and HR. Companies cannot rely  only on Finance to develop the sales budget. Finance rarely possess the most accurate and latest information to make assumptions. 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.

Looking to improve your Sales Forecasting process?

Start by downloading our free Sales Forecasting template by clicking here. Or take things to the next level with OnPlan FP&A software.

Book a Demo Now
bg
person

30

Publications
About Author
Uri Kogan

Uri Kogan leads marketing and go to market strategy at OnPlan. Uri brings experience as a B2B SaaS marketing executive at a number of high-growth companies, including Nuxeo, where he created a new category of Product Asset Management, leading to a 12x ARR exit, and AppZen, where he led AppZen’s entry into the AP market and served as interim CMO. Earlier in his career, Uri led software marketing teams, incubated new services products, executed turnarounds, and led award-winning global supply chain initiatives at HP. Uri holds a B.A. in economics and a B. Music in opera performance from Northwestern University, and an MBA from Kellogg.

Show more