FORECAST.LINEAR | FORECAST.ETS | Forecast Sheet

The FORECAST (or FORECAST.LINEAR) function in Excel predicts a future value along a linear trend. The FORECAST.ETS function in Excel predicts a future value using Exponential Triple Smoothing, which takes into account seasonality.

You can use your own formulas, or have Excel automatically do this with its Forecast Sheet feature. Excel’s Forecast Sheet feature automatically adds formulas and creates a chart in a new sheet. Follow the steps below to use this feature. Select the data that contains timeline series and values. Go to Data Forecast Forecast Sheet. Click here to download the Excel template. Excel Holiday Template. This Excel template will give you all the national US holidays for the selected year. Even better, it tells you the number of days to the next holiday and the long weekend. Read more about this holiday calendar template. Click here to download the Excel template.

Note: the FORECAST function is an old function. Microsoft Excel recommends using the new FORECAST.LINEAR function which produces the exact same result.

FORECAST.LINEAR

Forecast Sheet Excel Mac Download Free

1. The FORECAST.LINEAR function below predicts a future value along a linear trend.

Explanation: when we drag the FORECAST.LINEAR function down, the absolute references ($B$2:$B$11 and $A$2:$A$11) stay the same, while the relative reference (A12) changes to A13 and A14.

2. Enter the value 89 into cell C11, select the range A1:C14 and insert a scatter plot with straight lines and markers.

Note: when you add a trendline to an Excel chart, Excel can display the equation in a chart. This equation predicts the same future values.

FORECAST.ETS

The FORECAST.ETS function in Excel 2016 or later is a great function which can detect a seasonal pattern.

1. The FORECAST.ETS function below predicts a future value using Exponential Triple Smoothing.

Note: the last 3 arguments are optional. The fourth argument indicates the length of the seasonal pattern. The default value of 1 indicates seasonality is detected automatically.

Forecast Sheet Excel Mac Download

2. Enter the value 49 into cell C13, select the range A1:C17 and insert a scatter plot with straight lines and markers.

3. You can use the FORECAST.ETS.SEASONALITY function to find the length of the seasonal pattern. After seeing the chart, you probably already know the answer.

Conclusion: in this example, when using the FORECAST.ETS function, you can also use the value 4 for the fourth argument.

Forecast Sheet

Use the Forecast Sheet tool in Excel 2016 or later to automatically create a visual forecast worksheet.

1. Select the range A1:B13 shown above.

Forecast Sheet Excel Mac Download Windows 10

2. On the Data tab, in the Forecast group, click Forecast Sheet.

Excel launches the dialog box shown below.

3. Specify when the forecast ends, set a confidence interval (95% by default), detect seasonality automatically or manually set the length of the seasonal pattern, etc.

4. Click Create.

This tool uses the FORECAST.ETS function and calculates the same future values. The lower and upper confidence bounds are a nice bonus.

Explanation: in period 13, we can be 95% confident that the number of visitors will be between 86 and 94.

A sales forecast is an essential part of a business plan. It is also essential if you are looking to get a bank loan or investors. Our free Sales Forecast Template lets you analyze and forecast the unit sales, growth rate, profit margin and gross profit for your products and services. It provides a quick starting point for setting up your sales forecast and includes some sample charts.

Template Details

Forecast Sheet Excel Mac

'No installation, no macros - just a simple spreadsheet' - by Jon Wittwer

Description

This Excel spreadsheet lets you compare and analyze multiple products and services by enter monthly units sold, unit price and cost of goods sold (COGS). The template then calculates the total revenue, growth rate, margin per unit, and gross profit.

Note: If your service is based on an hourly rate, you can enter the number of hours under Units Sold and the hourly rate under Unit Price.

The final two columns compare the yearly totals for Year 2 vs Year 1 and Year 3 vs Year 2.

In a business plan or presentation, you should probably use a chart to help communicate your forecast. We've included a few sample charts, both for the first year's data as well as all 3 years. Remember to use charts that effectively and accurately communicate your sales to lenders and investors.

Printing Monthly or Yearly Forecasts

The spreadsheet was designed to let you print a couple different types of reports using the same worksheet. If you make significant changes to the template, you will probably need to make adjustments with the Page Break Preview, page scaling, or other Page Layout options.

Forecast Sheet Excel Mac Download Software

If you print the worksheet as-is, you will have each 12-month year on one page with the final page showing the yearly comparisons.

Forecast Sheet Excel Mac Download Version

For a Year-to-Year summary on 1 page, you can hide the columns containing the monthly data prior to printing.

Sales Forecasting Resources

• Financial Projections Template at score.org - An Excel Workbook that helps you see the potential of a business idea. If you are starting a new business, this can be a great resource because it combines a lot of important statements and projections into a single workbook.
• Financial Forecasting at TheStartUpPath at youtube.com - A great overview of the important details to consider when creating a sales forecast.

Related Content