Microsoft Excel is undoubtedly the most commonly used software in professional settings, and accounting and finance professionals can especially benefit from Excel’s wide variety of budgeting and forecasting tools. Most business professionals already use Excel to some extent for their budgeting and forecasting activities; however, many don’t take advantage of Excel’s built-in tools. Are you using inefficient and outdated techniques in your Excel sheets?
It’s especially important for accountants to understand how to take advantage of Excel’s budgeting and forecasting tools because these features – especially the FORECAST function – can be put to work right away to help you solve problems and do your job more efficiently.
Predicting Future Values with the FORECAST Function
It does you no good if the data you’ve recorded over weeks, months and even years can’t be used as a reference for predicting the future. The Excel FORECAST function can be used to easily future data based on trends, relationships, and comparisons among your existing data in your worksheet.
The FORECAST function uses the syntax: =FORECAST(x, known y’s, known x’s).
By detailing the relationship of one set of values with another set, expressed in x’s and y’s, the FORECAST function provides the value you want to predict. In the formula, the x value is the value you already know, known y is the range of values that match the type of value you want to predict, and known x is the range of known values that match the type of value you already know. If that sounds confusing, don’t worry! When using the FORECAST function, first think of the x and y values in terms of the goal data (x) and the data you want to predict (y).
The 2016 Excel Forecast Sheet for Easier Forecasting
You can use Excel 2016’s Forecast sheet to easily perform difficult data analysis to identify trends and predict the direction of future data. The Forecast sheet is also useful if you don’t have much historical data available. Office Support lays out the first step to creating the Forecast sheet: Enter two data series, one for the date entries that will be used on the timelines and the other for corresponding historical values. After selecting both these series, go to the Forecast group in the Data tab, and select Forecast Sheet button to open the Create Forecast Worksheet dialog box.
Excel creates a generically named worksheet in the workbook, and includes a table with current data and forecasted data, along with a chart. Although Excel displays a line chart by default, you can choose to change the type of chart. A further benefit of the Forecast sheet is that it also allows you to easily choose how much historical data to add to your analysis, and offers other customization options as well.
Stop Chasing Your Tail in Circular References
Anyone who works with Excel, especially for the purpose of budgeting, is going to be confronted with a circular reference error message at some point. In order to use circular references, i.e. when a formula needs to calculate itself, you must have iterative calculations turned on.
According to Office Support, you can turn on iterative calculations by clicking through File/Options/Formulas, and then selecting the “Enable iterative calculation” check box in the Calculations options section. Understanding how to handle circular references in workbooks is essential for capital budgeting calculations, and learning best practices for handling circular references in budgeting workbooks is especially essential for accountants who primarily use Excel for their work.
Improving the Quality of Your Budgeting and Forecasting Spreadsheets
You can easily learn to take advantage of Excel’s key budgeting and forecasting tools by joining CPA and tech consultant Thomas G. Stephens, Jr. in “Excel’s Best Budgeting Features for Accountants,” a webinar with Eli Financial. Tommy provides insights on how to handle circular references that appear in budgeting workbooks, how to use regression analysis to improve the quality of your budgeting and forecasting spreadsheets, and more. Let Tommy help you avoid lost time, errors and reports that are not as accurate as they should be by brushing up on the latest tips and features for using this powerful budgeting software.
