Sometimes you need to create a financial budget and forecasting for your company and you want to do this in Excel. You find it very promising that Excel provides you with all the necessary items through which you can easily create your **budget** and predict the budget for the future by using the forecasting method. This article will show you all the necessary steps on how to do budgeting and forecasting in Excel

## Download Practice Workbook

Download this practice workbook.

## What Is Budgeting?

A budget is primarily a financial plan under a definite period. It estimates the revenue and expenses of a specific company. It is useful for the Government or any individual to have a proper monthly or yearly budget through which one can easily change their lifestyle. Budgeting is necessary for our day-to-day life. It provides a clear view of how much we earn and how much we spend.

## What Is Forecasting?

**Forecasting** is a system through which a company can predict its future revenues. It is mainly a prediction based on past and present datasets. Then compare the forecasting data with the original data. If it gives desirable results then forecasting is accurate in that case. Companies utilize forecasting to estimate the future allocation of budget and anticipate the expenses also.

## Prepare a Budget in Excel

To prepare a budget in Excel, we need to consider past and present values. For example, if we make a budget for the year 2021, we need to include all the income, expenses, and profit of that year. Again if we want to make a budget for two previous years and a running year to compare one another, we can easily do it through Excel. In this article, we make a budget of six months in the year 2021 including income, expenses, and profit. We will do the forecasting for this dataset in method 1 from the following methods.

## 2 Ways to Do Budgeting and Forecasting

We can do forecasting by using two different ways. One is mainly based on the **forecasting function in Excel** and the other is by using seasonality. Both methods are practically proven to do budgeting and forecasting in Excel.

### 1. Calculate Forecasting Using FORECAST.ETS Function in Excel

Firstly, We need to create a budget dataset that includes income, expenses, and profit for six months and we want to calculate the next six months’ budget using **the FORECAST.ETS function**. To do this you need to follow the following steps.

**Steps**

- First, we need to create a budget having an income, expenses, and profit for six months. And we need to find out the budget for the next six months.

- Next, select cell
**D11**where you want to apply the forecast formula.

- Now, write down the following formula in the formula box

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

- Press
**Enter**to apply the formula.

** **

- Now, drag the
**Fill handle**icon or double-click on it to apply the formula down the column.

- In the
**Expenses**column, select cell**E11**.

- Write down the following formula in the formula box

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

- Press
**Enter**to apply the formula.

- Now, drag the
**Fill handle**icon or double-click on it to apply the formula down the column.

- There we have the forecasting for the future six months.

- Now, to make a chart between the date and income or date and expenses, we can use the
**Forecast Sheet**command in Excel. - First, select the date and income column.

- Now, go to the
**Data**tab in the ribbon and select the**Forecast Sheet**from the**Forecast**group.

- After clicking on it, a new dialog box will appear containing the required chart. Click on
**Create**to make this chart.

- It will create a dataset including
**Lower Confidence Bound(Income)**and**Upper Confidence Bound(Income)**. The**Forecast Sheet**can predict the lower and upper income and show them in the chart.

- It will create the following chart.

- We can do the same chart between date and expenses.

**Read More:** **Forecasting in Excel Using FORECAST Function & Auto Fill Handle Tool**

**Similar Readings**

**How to Forecast Sales Growth Rate in Excel (6 Methods)****How to Forecast Growth Rate in Excel (2 Methods)****How to Make a Budget Pie Chart in Excel (with Easy Steps)**

### 2. Calculate Forecasting Using Seasonality in Excel

Another useful way to calculate forecasting in Excel is by using seasonality. Seasonality is basically defined as a coefficient that is found through historical data. Using seasonality will give us a proper way to predict future sales. To demonstrate this method, we will use the **AVERAGE** and **SUM** functions. To do this method, you need to follow the following steps.

**Steps**

- First, create three years sales budget for each month.

- Next, calculate the average sales for each month. Select cell
**D9**.

- Write the following formula to get the average for January of those 3 years.

`=AVERAGE(D6:D8)`

- Press
**Enter**to apply the formula. It will provide the average for January.

- Drag the
**Fill handle**icon to the desired position.

- To get the ultimate average sales for those three years, first, select cell
**P9**.

- Next, write the following formula in the formula box

`=AVERAGE(D9:O9)`

- Press
**Enter**to apply the formula. It will give the ultimate average of those three years.

- Next, to find the seasonality, select cell
**D11**.

- Write down the following formula in the formula box. Here, use the ($) sign to fix cell
**P9**because we need to use it throughout the row.

`=D9/$P$9`

- Press
**Enter**to apply the formula.

- Drag the
**Fill handle**icon to the desired position then we will get the seasonality for each month.

- Now, we need to predict the forecast for future sales. To do these, we need to assume the sales for some specific product.

- Now, using seasonality, we need to calculate the sales for each month for the specific product. First, select cell
**D14**.

- Write down the following formula in the formula box. Here, we need to fix the seasonality cell that’s why we use the
**($)**sign.

`=($C14/12)*$D$11`

- Press
**Enter**to apply the formula.

- Drag the
**Fill handle**icon down the column.

- Now, apply the same procedure for the other 11 months. It will give the sales of a specific product for a specific month.

- Next, to find out the total sales for the year 2022, we need to add all the month’s cell values using the
**SUM**function Select cell**P17**.

- Write the following formula in the formula box.

`=SUM(D17:O17)`

- Press
**Enter**to apply the formula.

- That is our required forecasting for the year 2022 which is predicted from three different years of sales data and seasonality.

In these ways, you can easily do budgeting and forecasting in Excel.

**Read More:** **How to Forecast Sales in Excel (5 Easy Ways)**

## Conclusion

We have shown two different approaches to budgeting and forecasting in Excel. These methods are really useful for your company’s financial purpose. I wish you can acquire the necessary knowledge about budgeting and forecasting. If you have any questions, feel free to ask in the comment box, and don’t forget to visit our **Exceldemy** page.

## Related Articles

**How to Calculate Forecast Accuracy Percentage in Excel (4 Easy Methods)****Time Series Forecasting Methods in Excel****How to Forecast Sales Using Regression Analysis in Excel (3 Methods)****How to Create a Business Budget in Excel (With Easy Steps)****Make a Budget in Excel for College Students (with Quick Steps)****How to Prepare Annual Budget for a Company in Excel**