How to Do Budgeting and Forecasting in Excel (2 Suitable Ways)

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

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.

How to Do Budgeting and Forecasting in Excel: 2 Ways

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: How to Forecast Revenue in Excel

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.

Download Practice Workbook

Download this practice workbook.

Budgeting and Forecasting.xlsx

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 hope you can acquire the necessary knowledge about budgeting and forecasting. If you have any questions, feel free to ask in the comment box.

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

Get FREE Advanced Excel Exercises with Solutions!