Budget variance analysis is an important component of corporate performance management and learning how to build flexible budget variance formulas in excel can help you manage the sometimes time-consuming process. While advancements in data collection and management have made utilizing raw data easier, the vast majority of businesses continue to rely on excel based financial models and reporting.
While most corporate finance professionals are proficient in excel, this article will outline some of the more common budget variance formulas. Use this as a guide to help you build a flexible budget variance analysis in excel.
How To Make A Variance Analysis Excel Workbook
When building your budget variance excel formulas in your workbook consider using a separate tab for each important section. In this guide, we will walk through the more commonly used excel formulas and how to structure your workbook to make it relatively easy for anyone to use.
Create A Menu Sheet
Consider making the first tab a menu, or table of contents of sorts. This will help provide context to yourself or the end-user by providing information on the budget period. It can also be used to show which location or department the budget, forecast, and variance analysis is for. If it is for a top-level budget variance report, then you can simply show that rather than displaying a location or department.
Sometimes a menu sheet also contains instructions for how to build or update the model. This makes it easy for new users to pick up and creates a business continuity measure.
On this tab enter a start date and end date for the budget period. These cells will be referenced in later parts of your workbook. Label the worksheet “Menu”.
Make A Sheet For Forecasted Values
Create a new sheet, and label it Forecast. Here you will enter the budget forecast amount for the specified time period. Be sure to make your labels for each budget category easy to reference from raw data.
Across your column headings should be dates, usually monthly. You can create dynamic and flexible dates in excel by using the below formula, and referencing the date ranges you put into the “Menu” worksheet of your workbook.
First, link the starting date of your budget forecast to the start date in your “Menu” worksheet. Then use the below formula in the cell next to it to create a dynamic date range:
=DATE(YEAR(START DATE CELL),MONTH(START DATE CELL)+1,1)
You can then simply drag this cell across the desired number of columns and the dates will auto-populate. Any time you change the start date in the “Menu” worksheet it will automatically change the dates in your columns on your “Forecast” worksheet.
Insert Totals And Subtotal Formulas
Sum each budget category and the totals in each column using the sum function.
=SUM(STARTING CELL:ENDING CELL)
This formula will add all of the numbers in the specified cells. This can then be dragged across the specified date range.
To get the most from your budget forecast, sum your revenue lines, and expense lines, and create a separate row for net income by simply using the below formula:
=TOTAL REVENUE CELL – TOTAL EXPENSE CELL
You can drag this formula across the specified date range as well.
Create A Worksheet For Actual Values
Create a third worksheet and title it “Actual”. This tab will contain all of the actual amounts for each period and each category in your budget. This is the place where you can drop a raw data export if you have one.
If you do not have a similar format using raw data, then model this tab after your “Forecast” tab and create a new tab called “Data”. Copy and paste the raw data into your “Data” tab and use reference formulas to pull the values into your “Actual” tab.
There are many formulas to reference raw data. For example, if your budget categories in your “forecast” tab match the budget categories in your “actual” tab you can use a VLOOKUP function.
The VLOOKUP function is a basic function used by finance professionals. It stands for vertical lookup and it searches for a specified value in a column and returns a corresponding value from a different column in the same row. It is used when raw data sets are vertically organized.
Another common formula is the INDEX MATCH formula which is a little more dynamic than VLOOKUP. VLOOKUP requires your reference cell to be the first cell in a series of columns. For example, if you want to return “Sales” from your data set, then “Sales” needs to be in the first column you reference in your VLOOKUP formula.
INDEX MATCH allows you to reference a value regardless of its placement and is more widely adopted by advanced users of excel. The difference between the two functions and their use can be found here.
Create A Year To Date (YTD) Worksheet
Create a fourth worksheet and label it “YTD”. Model this worksheet exactly after your “Forecast” sheet. In this tab, you will pull in the actual values for the time periods that you have them for and the forecasted amounts for the time periods that have yet to occur.
You can do this by using the below formula:
=IF(DATE CELL>=TODAY(), FORECASTED VALUE CELL, ACTUAL VALUE CELL)
This formula will automatically reference today’s date and compare it to the date in the date column heading. If the column heading date is further in the future than today’s date, then it will reference the corresponding cell in the “Forecast” tab. If it is equal to or less than today’s date, it will reference the actual tab.
Create Variance Tab And Calculate Variances
In a final worksheet, labeled “variance” copy and paste the same layout and format you have for your “actual” and “forecast” tabs.
In the cells use the below formula to automatically calculate variances as actual data becomes available.
=IF(ACTUAL VALUE CELL=””,0,ACTUAL VALUE CELL/ CORRESPONDING FORECAST VALUE CELL-1)
This formula will reference the “actual” tab and if there is no value available, then it will return a zero. If there is an actual amount in the cell referenced, it will divide it by the same forecasted amount in the “forecast” worksheet and subtract 1 to derive a percentage variance.
You can choose to format the cells further using the “conditional formatting tool” provided in excel. For example, if revenue actual amounts are less than forecasted amounts, the conditional formatting would be “highlight red if <0”, meaning the cell will be highlighted red for any negative amounts.
Using Datarails, a Budgeting and Forecasting Solution
Datarails replaces spreadsheets with real-time data and integrates fragmented workbooks and data sources into one centralized location. This allows users to work in the comfort of Microsoft Excel with the support of a much more sophisticated data management system at their disposal.
Every finance department knows how tedious building a budget and forecast can be. Integrating cash flow forecasts with real-time data and up-to-date budgets is a powerful tool that makes forecasting cash easier, more efficient, and shifts the focus to cash analytics.
Regardless of the budgeting approach your organization adopts, it requires big data to ensure accuracy, timely execution, and of course, monitoring.
Datarails’ FP&A software can help your team of FP&A analysts to create and monitor cash flow against budgets faster and more accurately than ever before.