How to create more accurate forecasts with Excel

Do you need to future-proof your business? The Excel Forecast sheet may be the right tool for you! The Forecast sheet allows you to take your historical business data (sales, cash flow, etc.) and create more accurate forecasts for the future.

You are welcome to download the workbook to practice!

1. To create a Forecast Sheet in Excel 2016, you must first ensure that you have arranged your reporting data correctly. You will need two columns of data—one column containing dates and a second column containing historical data that corresponds to the dates in the first column. (Date & Monthly Sales). The timeline requires consistent intervals between its data points. For example, monthly intervals with values on the 1st of every month, yearly intervals, or numerical intervals.

2. Once you have arranged your data properly, simply click on any of the data points in the data and choose Forecast Sheet from the Data tab of the Ribbon.

3. Excel then automatically presents a preview of your forecast based on your data. In the Create Forecast Worksheet box, pick either a line chart or a column chart for the visual representation of the forecast. Clicking Options allows you to access and edit numerous other options related to the presentation of the Forecast Sheet.

4. Once you have finished editing any Options, click Create to complete the process of creating your Forecast Sheet. Excel builds the Forecast Sheet, including forecasted values and forecasted upper and lower confidence intervals.

5. You can then move your forecasts chart to its own sheet in Excel to further analyse the predicted values for your business. This chart can be further customised to suit your companies specific reporting requirements and standards by using Excel many features.

The ability to forecast accurately provides many benefits for all businesses. Forecasting allows you to predict the future, learn from the past, keeps you looking ahead, helps you remain competitive, and prepare for new business.

Watch the video for a quick overview of the forecasts tip.