How to create forecast in excel | Illustration with Example

How to create forecast in excel | Illustration with Example:

Hi Readers! Here we are going to learn today on how to create forecast in excel. We have already published an article on basic knowledge and the selection process for best forecasting methods. As we know that forecasting is one of the common methodologies used in various types of industries for predicting the future based on the results of previous data. Here we will give more focus on the creation or preparation of the forecasting method in excel (note that, given excel process may be different from excel to excel depending on excel’s version).

Suppose a bike showroom owner would like to do the purchasing plan considering the last 4 month’s selling quantity, store constrain, Market demand, festive season, etc.  But he could not finalize the quantity. So, here we are going to do the forecast method to finalize the value (this is only the reference method and forecast value, the actual value may vary depending on various factors). Through this example, we shall learn only on how to create forecasts in excel (Some common methods only). We are going to use the excel-2007 version, the process may vary from excel version to version.

Illustration of How to create forecast in excel?

The last 9 months’ bike selling quantity is given below as;

We have calculated a 4-Months Moving Average Forecast.

Excel Function = Average (Select data array of 4 months)

In the above table we have mentioned the excel function. Accordingly calculated the 4-months moving average forecast value, the same table is given below. Just try to calculate the value by yourself in excel and verify the same.

How to calculate Exponential Smoothening Forecast? By considering the alpha value 0.8

Excel function, ESF of desire month = (0.8*previous month bike selling quantity + 0.2* previous month ESF)

Linear Regression Forecasting calculation in Excel:

Based on the above data “month” & “bike selling quantity” plot the scatter diagram including linear regression function as per below;

From the Scatter diagram, you can get the function, here y=0.416*X + 100.4

By using the function we have calculated the forecast value which is given in the below table.

Accordingly, you can calculate the Forecast value using several types of methods. But if you would like to choose or decide the best forecast value among all types then read our previous article.

Free Templates / Formats of QM: we have published some free templates or formats related to Quality Management with manufacturing / industrial practical examples for better understanding and learning. if you have not yet read these free template articles/posts then, you could visit our “Template/Format” section. Thanks for reading…keep visiting techiequality.com

QC Template, Advanced Tools and Techniques

Internal Audit | Types | Types of Auditors | Manufacturing Example

How to Create a Drop Down List in Excel Template with Manufacturing Example

SDCA Cycle understanding with Manufacturing Example | PDCA vs SDCA

Normal Distribution Probability Formula, Calculation & Manufacturing Examples

DMAIC Tools | DMAIC Template | Six Sigma Methodologies | 30+ Free Tools

Six Sigma Project Examples | 15+ Project Examples

Six Sigma Project Charter Template

DPMO Calculation, Formula, Template & Manufacturing Example

Process Flow Diagram Template | Examples |Symbols |PFD

What is Visual Management? | Visual Management Board Examples

ABC Analysis of Inventory with Industrial Example

• 1
• 2
• 3
• …
• 10
• ›

Popular Post