While dealing with the sales of a company, sometimes we need to forecast the sales of that company to predict the profit or loss. In this case, Microsoft Excel can be a handy tool for you. Today I am going to show you **five **easy and suitable steps to calculate **forecast confidence interval in Excel** effectively with appropriate illustrations.

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## Step-by-Step Procedures to Calculate Forecast Confidence Interval in Excel

Letâ€™s say, we have a dataset that contains information about the sales forecast of the XYZ group. From our dataset, we will calculate forecast confidence intervals in Excel. We can easily calculate forecast confidence intervals in Excel by using the **FORECAST.ETS****, ****FORECAST.ETS.CONFINT** functions and mathematical formulas. Hereâ€™s an overview of the dataset for todayâ€™s task.

### Step 1: Create Dataset Header

In the first step, you will have to **insert headers **to calculate the forecast confidence interval and then **format **them in Excel. The header columns are the **Sales representativeâ€™s** name, **Period**, **Sales **in several periods, **Forecast**, **Confidence Intervals (CI)**, **Upper Confidence Intervals, **and **Lower Confidence Intervals**. So, our dataset becomes as below.

**Read More: ****How to Calculate Confidence Interval Without Standard Deviation in Excel**

### Step 2: Apply FORECAST.ETS Function

In this step, we will apply **the FORECAST.ETS function **to calculate the forecast. From our dataset, we can easily do that. Firstly, we will calculate the forecast for **July**. Hence, we will calculate the forecast for the rest of the months in column **E**. Letâ€™s follow the instructions below to learn!

- First of all, select cell
**E11**and write down the below**FORECAST.****ETS function**in that cell.

`=FORECAST.ETS(C11,$D$5:$D$10,$C$5:$C$10,1,1,1)`

- Where
**C11**is the**target_date, $D$5:$D$10**is the**values**,**$C$5:$C$10**is the**timeline**, first**1**is the**seasonality**, second**1**is the**data_completion**, and the rest**1**is the**aggregation**of**the FORECAST.ETS function.** - We use the
**Dollar sign ($)**for absolute reference.

- Hence, simply press
**Enter**on your keyboard. As a result, you will get the estimated forecast of**July 2018**which is the return of**the FORECAST.ETS function**. The**return**is**$1,022.38**.

- Hence,
**AutoFill the FORECAST.ETS function**to the rest of the cells in column**E**.

**Note: Accuracy of Excel FORECAST Function**

*The survey results are always within Â± 5%, Â± 15%, or any other percent; they are never a precise figure. You canâ€™t rely on a forecast if you donâ€™t know how accurate it is. The world is full of uncertainty. In Excel, there isnâ€™t a straightforward way to assess sales forecasting accuracy, or at least not one that wouldnâ€™t require years to develop.*

**Read More: ****How to Calculate 90 Percent Confidence Interval in Excel**

**Similar Readings**

**Excel Confidence Interval for Difference in Means (2 Examples)****Calculate P-Value from Confidence Interval in Excel****How to Make a Confidence Interval Graph in Excel (3 Methods)**

### Step 3: Use FORECAST.ETS.CONFINT Function

Now, we will **calculate the Confidence Intervals **using **the FORECAST.ETS.CONFINT function** in Excel**. **From our dataset, we can easily calculate the **Confidence Intervals. **Letâ€™s follow the instructions below to learn!

- First of all, select cell
**F11**and write down the below**FORECAST.****ETS.CONFINT function**in that cell.

`=FORECAST.ETS.CONFINT(C11,$D$5:$D$10,$C$5:$C$10,0.95,1,1,1)`

- Where
**C11**is the**target_date, $D$5:$D$10**is the**values**,**$C$5:$C$10**is the**timeline**,**96**is the**Confidence_level**, first**1**is the**seasonality**, second**1**is the**data_completion**, and the rest**1**is the**aggregation**of**the FORECAST.ETS.CONFINT function.** - We use the
**Dollar sign ($)**for absolute reference.

- Hence, simply press
**Enter**on your keyboard. As a result, you will get the estimated forecast which is the return of**the FORECAST.ETS.CONFINT function**. The**return**is**$220.61**.

- Hence,
**AutoFill the FORECAST.ETS.CONFINT function**to the rest of the cells in column**F**.

**Read More: ****Linear Regression Confidence Interval in Excel (Calculation and Interpretation)**

### Step 4: Calculate Upper Confidence Interval

Here, we will learn how to calculate the **Upper Confidence Intervals. **Letâ€™s follow the instructions below to learn!

- First of all, select cell
**G11**and write down the below**mathematical formula**in that cell.

`=E11 + F11`

- Where
**E11**is the forecast,**F11**is the**Confidence Intervals**.

- Hence, simply press
**Enter**on your keyboard. As a result, you will get the Upper**Confidence Intervals**which is the return of**the mathematical formula**. The**return**is**$1,242.99**.

- Hence,
**AutoFill**the formula to the rest of the cells in column**G**.

**Read More: ****How to Find Upper and Lower Limits of Confidence Interval in Excel**

### Step 5: Calculate Lower Confidence Interval

Last but not least, we will calculate the **Lower Confidence Interval.** Letâ€™s follow the instructions below to learn!

- First of all, select cell
**H11**and write down the below**mathematical formula**in that cell.

`=E11 - F11`

- Where
**E11**is the forecast,**F11**is the**Confidence Intervals**.

- Hence, simply press
**Enter**on your keyboard. As a result, you will get**Lower Confidence Intervals**which is the return of**the mathematical formula**. The**return**is**$801.77**.

- Hence,
**AutoFill**the formula to the rest of the cells in column**H**.

**Read More: ****How to Calculate 95 Percent Confidence Interval in Excel (4 Ways)**

## Bottom Line

ðŸ‘‰ **#N/A!** the error arises when the formula or a function in the formula fails to find the referenced data.

ðŸ‘‰ **#DIV/0!** the error happens when a value is divided by **zero(0)** or the cell reference is blank.

## Conclusion

I hope all of the suitable methods mentioned above to **calculate ****forecast confidence interval** will now provoke you to apply them in your **Excel** spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.