# How to Do Budgeting and Forecasting in Excel: 2 Easy Ways

## 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. 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. We will do the forecasting for this dataset in method 1 from the following methods.

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

Steps

• Create a budget with income, expenses, and profit for six months. We need to find out the budget for the next six months.

• Select cell D11 where you want to apply the forecast formula.

• 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.

• 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.

• Drag the Fill handle icon or double-click on it to apply the formula down the column.

• We have the forecasting for the future six months.

• To make a chart between the date and income or the date and expenses, we can use the Forecast Sheet command in Excel.
• Select the date and income column.

• 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.

### Method 2 – Calculate Forecasting Using Seasonality in Excel

Steps

• Create three years sales budget for each month.

• 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.

• 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.

• 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, and we will get the seasonality for each month.

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

• 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.

• Apply the same procedure for the other 11 months. It will give the sales of a specific product for a specific month.

• 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.

## Related Articles

<< Go Back to Forecasting in ExcelÂ |Excel for FinanceÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF