# FORECAST Function in Excel

### Part 1: Introduce

#### Definition

The FORECAST function in Excel calculates or predicts a future value using existing values. It’s based on linear regression and can be used to predict future sales, inventory requirements, or consumer trends.

#### Purpose

To predict a y-value for a given x-value using known x-values and y-values.

#### Syntax & Arguments

syntax
`FORECAST(x, known_y's, known_x's) `
• x: The data point for which you want to predict a value.
• known_y’s: The dependent array or range of data.
• known_x’s: The independent array or range of data.

#### Explain the Arguments in the function

• x: The value you want to predict.
• known_y’s: The existing y-values in the data.
• known_x’s: The existing x-values in the data.

#### Return value

The function returns the predicted y-value for the given x-value.

#### Remarks

• If x is non-numeric, FORECAST returns the #VALUE! Error value.
• If known_y’s or known_x’s is empty or one has more data points than the other, FORECAST returns the #N/A error value.
• If the variance of known_x’s equals zero, FORECAST returns the #DIV/0! Error value.

### Part 2: Examples

Here are five examples of using the FORECAST function in a business context:

#### Example 1

• Purpose of example: Predicting sales for a given marketing spend.
• Data tables and formulas:
ABC
1SpendSales
2100200
3150300
4200400
5=FORECAST(175, B2:B4, A2:A4)Result: 350
• Explanation: Using the FORECAST function, we can predict that a marketing spend of 175 will result in sales of 350.

#### Example 2

• Purpose of example: Predicting inventory requirements based on past demand.
• Data tables and formulas:
ABC
1DemandInventory
25001000
36001200
47001400
5=FORECAST(800, B2:B4, A2:A4)Result: 1600
• Explanation: This example predicts that a demand 800 will require an inventory of 1600.

#### Example 3

• Purpose of model: Predicting consumer trends based on previous data.
• Data tables and formulas:
ABC
1MonthTrend
215
3210
4315
5=FORECAST(4, B2:B4, A2:A4)Result: 20
• Explanation: This example predicts that the trend will continue to increase, reaching 20 in the fourth month.

#### Example 4

• Purpose of illustration: Predicting revenue based on the number of customers.
• Data tables and formulas:
ABC
1CustomersRevenue
2501000
3751500
41002000
5=FORECAST(125, B2:B4, A2:A4)Result: 2500
• Explanation: This example predicts that 125 customers will generate a revenue 2500.

#### Example 5

• Purpose of example: Predicting profit based on the number of products sold.
• Data tables and formulas:
ABC
1ProductsProfit
210100
320200
430300
5=FORECAST(40, B2:B4, A2:A4)Result: 400
• Explanation: This example predicts that selling 40 products will result in a profit 400.

#### Example 6: Using FORECAST with IF

• Purpose of illustration: Predicting sales and determining if the target is met.
• Data tables and formulas:
ABCD
1SpendSalesTarget
2100200Met
3150300Met
4200400Met
5=FORECAST(175, B2:B4, A2:A4)350=IF(C5>=350, “Met”, “Not Met”)
• Explanation: This example predicts sales based on marketing spend and uses the IF function to determine if the sales target 350 is met. If the expected sales exceed or equal 350, the target is considered “Met”; otherwise, it’s “Not Met.”

#### Example 7: Using FORECAST with SUM

• Purpose of example: Predicting inventory requirements and calculating the total inventory.
• Data tables and formulas:
ABCD
1DemandInventoryTotal Inventory
250010001000
360012002200
470014003600
5=FORECAST(800, B2:B4, A2:A4)1600=SUM(C2:C5)
• Explanation: This example predicts the inventory required for a demand of 800 and calculates the total list using the SUM function. It helps in understanding the overall inventory position, including the predicted requirement.

#### Example 8: Using FORECAST with VLOOKUP

• Purpose of example: Predicting sales and finding the corresponding commission rate.
• Data tables and formulas:
ABCDE
1SpendSalesCommission RateCommission
21002005%10
315030010%30
420040015%60
5=FORECAST(175, B2:B4, A2:A4)350=VLOOKUP(C5, B2:D4, 3, FALSE)=C5*D5/100
• Explanation: This example predicts sales for a spend of 175 and uses the VLOOKUP function to find the corresponding commission rate. The commission is then calculated based on the expected sales and the found commission rate.

#### Example 9: Using FORECAST with AVERAGE

• Purpose of example: Predicting temperature and calculating the average temperature.
• Data tables and formulas:
ABCD
1DayTempAverage Temp
212020
322522.5
433025
5=FORECAST(4, B2:B4, A2:A4)35=AVERAGE(B2:B5)
• Explanation: This example predicts the temperature for the fourth day and calculates the average temperature using the AVERAGE function. It provides insights into the overall temperature trend.

#### Example 10: Using FORECAST with MAX

• Purpose of example: Predicting sales and finding the maximum sales value.
• Data tables and formulas:
ABCD
1MonthSalesMax Sales
21500500
32600600
43700700
5=FORECAST(4, B2:B4, A2:A4)800=MAX(B2:B5)
• Explanation: This example predicts sales for the fourth month and uses the MAX function to find the maximum sales value. It helps in identifying the peak sales period, including the expected month.

#### Example 11: Using FORECAST with MIN

• Purpose of example: Predicting production and finding the minimum production value.
• Data tables and formulas:
ABCD
1WeekProductionMin Production
2110001000
32900900
43800800
5=FORECAST(4, B2:B4, A2:A4)700=MIN(B2:B5)
• Explanation: This example predicts production for the fourth week and finds the minimum production value using the MIN function. It helps in identifying the lowest production period, including the expected week.

#### Example 12: Using FORECAST with CONCATENATE

• Purpose of example: Predicting revenue and creating a summary statement.
• Data tables and formulas:
ABCD
1CustomersRevenueSummary
2501000
3751500
41002000
5=FORECAST(125, B2:B4, A2:A4)2500=CONCATENATE(“Predicted revenue for 125 customers is “, C5)
• Explanation: This example predicts revenue for 125 customers and creates a summary statement using the CONCATENATE function. It provides a clear and concise summary of the expected revenue.

#### Example 13: Using FORECAST with ROUND

• Purpose of example: Predict profit and rounding the result to the nearest whole number.
• Data tables and formulas:
ABCD
1ProductsProfitRounded Profit
210100100
320200200
430300300
5=FORECAST(40, B2:B4, A2:A4)400.5=ROUND(C5, 0)
• Explanation: This example predicts profit for selling 40 products and rounds the result to the nearest whole number using the ROUND function. It ensures that the expected payoff is presented in a format suitable for financial reporting.

### Part 3: Tips and Tricks

• Consider using the newer FORECAST.LINEAR function, as the older FORECAST function, will eventually be deprecated.
• Ensure that the known_x’s and known_y’s ranges are the same size to avoid errors.
• Use the FORECAST function to analyze trends and make informed business decisions.