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.

Budgeting in Excel


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.

Budgeting in Excel

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

Do Budgeting and Forecasting in Excel

  • Press Enter to apply the formula.

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

Do Budgeting and Forecasting Using FORECAST function

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

Do Budgeting and Forecasting Using FORECAST function

  • Press Enter to apply the formula.

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

Do Budgeting and Forecasting Using FORECAST function

  • We have the forecasting for the future six months.

Do Budgeting and Forecasting Using FORECAST function

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

Do Budgeting and Forecasting Using FORECAST function

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

Do budgeting and Forecasting Using Seasonality

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

Do budgeting and Forecasting Using Seasonality

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

  • Drag the Fill handle icon to the desired position.

Do budgeting and Forecasting Using Seasonality

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

Do budgeting and Forecasting Using Seasonality

  • 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

Do budgeting and Forecasting Using Seasonality

  • Press Enter to apply the formula.

  • Drag the Fill handle icon to the desired position, and we will get the seasonality for each month.

Do budgeting and Forecasting Using Seasonality

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

Do budgeting and Forecasting Using Seasonality

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

Do budgeting and Forecasting Using Seasonality

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

Do budgeting and Forecasting Using Seasonality

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

Do budgeting and Forecasting Using Seasonality

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

Download Practice Workbook

Download this practice workbook.


Related Articles


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

 

 

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo