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

### Method 1 – Forecasting Using the 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.

- Insert 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**.

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

- A new dialog box will appear containing the required chart. Click on
**Create**to make the 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 make the same chart for expenses.

### Method 2 – Forecasting Using Seasonality in Excel

**Steps**

- Create three years sales budget for each month.

- Calculate the average sales for each month. Select cell
**D9**.

- Use 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**to the right.

- Select cell
**P9**.

- Use the following formula in the formula box

`=AVERAGE(D9:O9)`

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

- Select cell
**D11**.

- Use 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. 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. Select cell
**D14**.

- Use 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**.

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

**Download the Practice Workbook**

## Related Articles

- Time Series Forecasting Methods in Excel
- How to Calculate Forecast Accuracy Percentage in Excel
- How to Calculate Accuracy and Precision in Excel
- How to Forecast Sales Using Regression Analysis in Excel
- How to Forecast Call Volume in Excel

**<< Go Back to Excel for Finance | Learn Excel**

Get FREE Advanced Excel Exercises with Solutions!