LivePlan's Forecast Builder makes it easy to create a starting forecast from your accounting data when connected to QuickBooks Online or Xero. It provides step-by-step guidance to ensure that your accounting data is properly mapped to its corresponding forecast items in LivePlan.
In this article:
- Accessing the Forecast Builder
- Configuring your revenue, expenses, and direct costs
- Configuring special expenses
- How the starting forecast is calculated
This feature is available to LivePlan Premium users. To learn more about LivePlan Premium, please click here.
Accessing the Forecast Builder
The Forecast Builder appears when a plan is connected to QuickBooks Online or Xero before making any entries or edits to the forecast or when creating a new scenario from your accounting data.
Creating a new forecast scenario using accounting data:
- Start a new Forecast scenario:
- If you don't have any additional scenarios in your forecast, click Create near the top-left of your LivePlan window:
- If you have already created one or more scenarios, click on the Forecast Scenario drop-down and select Create New Scenario:
- Select Use QuickBooks Data or Use Xero Data and click Next:
Connecting to QuickBooks Online or Xero before editing your forecast:
When a plan is connected to QuickBooks Online or Xero before making any edits to the forecast, the Forecast Builder will guide you through creating a starting forecast from your accounting data.
- Click on the company drop-down in the left sidebar and select Create New Company:
- Click Forecast in the left sidebar:
- Click Get Started:
- Select "Import QuickBooks or Xero Data"
- Select your accounting solution and follow the connection steps on-screen:
- Once your accounting data has been downloaded, you will be taken to the Forecast Builder:
Configuring your revenue, expenses, and direct costs
Once you have successfully connected and synced to your accounting solution, you will arrive at the first step of the Forecast Builder. Here, you will organize your income accounts from QuickBooks Online or Xero (displayed in the right column) into LivePlan revenue streams (shown on the left):
Income accounts will show the percentage of your total income each account represents along the right side of the account box. Similarly, the revenue stream created in LivePlan will display the total income that the new stream will represent below the name of the LivePlan revenue stream. Note that multiple revenue accounts can be connected to a single LivePlan revenue stream:
Note: Percentages displayed in the Forecast Builder represent the past 12 months of data from your accounting software. The definition of "most recent year of data" is based on the start date you've set for your forecast. For example, suppose you've set your forecast to start in January 2023. In that case, LivePlan will use accounting data from January - December 2022 to build your starting forecast. LivePlan will download your current fiscal year and the two prior fiscal years from your accounting solution. LivePlan's Dashboard can display, analyze, and present all available accounting data.
You can rearrange accounts, exclude accounts, rename accounts, and create new LivePlan forecast items from here. Your expenses and direct costs can be configured similarly.
Moving accounts:
All accounts in the left column can be rearranged by clicking and dragging the forecast item to its new location:
Excluding accounts:
LivePlan automatically excludes accounts from the Forecast Builder that have no data within the current fiscal year and the two prior fiscal years. However, you may still see accounts displaying 0% if they contain no data from the past year but do contain data older than one year.
To exclude accounts from the forecast builder, click "Show accounts excluded from LivePlan" to open the Excluded Accounts box and drag the account to the box:
Note: Excluded accounts will not contribute data to the starting forecast or Dashboard. You can update your account mapping if you wish to include these accounts later.
Renaming LivePlan forecast items:
You can rename any forecast item from the column on the right by clicking on the name of the item you wish to rename. Type the new name, then click on the checkmark to rename or the X to cancel:
Creating new forecast items
To create additional forecast items for your revenue, expenses, or direct costs, click the New button near the lower-right corner of your LivePlan window. Type the name of the forecast item, and click the checkmark to save or the X to cancel. Then, drag accounts that you wish to relate to that new forecast item and place them next to the newly created forecast item:
Configuring special expenses
After setting up your revenue accounts, Forecast Builder will guide you through setting up payroll, income taxes, and interest expenses from your accounting data. On each of the three pages, you will be presented with the account that LivePlan identified as containing your payroll, interest, and income tax data. If you wish to include additional accounts, place a check next to any of the accounts below:
If you don't want to include any accounts, leave all boxes unchecked. When you have made the selections, click Next in the lower-right corner to continue.
Once all sections of the Forecast Builder have been completed, you will be presented with a final confirmation of the forecast that will be created:
Click Create Forecast to finish the Forecast Builder. You will be taken to your new forecast next, where you can now adjust the starting forecast.
How the starting forecast is calculated
The Forecast Builder generates your starting forecast based upon the actual data in your accounting solution using one of two methods.
- If you have at least 13 months of actuals, LivePlan will automatically calculate a rate of change and apply that to the subsequent years of your starting forecast.
- If you have less than 13 months of actuals, LivePlan will still generate a starting forecast. But it will use the most recent available values and copy them year-over-year as your starting forecast.
Rate of Change Calculation (with 13+ Months of Data)
When 13 or more months of actual data are available, LivePlan calculates an average rate of change based on the overlapping months between the current year and the prior year. The rate of change is calculated as follows:
- Identify Overlapping Months: The system identifies the months for which there is actual data for at least two years.
For example, if data is available for June to August in both years, these will be the overlapping months.
- Sum Overlapping Values: The system sums the total values for the overlapping months for the current year and for the previous year to get the overall totals for both.
For example, if the total revenue for June, July, and August in the previous year was $30,000, and the total for the same period in the current year is $36,000, the system sums these values to calculate overall totals for both years.
- Calculate Rate of Change: After summing the values for the overlapping months, the system calculates the overall average rate of change by comparing the totals from the current year and the previous year.
For example, if the total revenue for June through August in the previous year was $30,000 and it’s $36,000 this year, the average rate of change would be +20%..
- Applying the Rate of Change to Any Non-Overlapping Months: For months where actual data is not available (non-overlapping months), the system uses the average rate of change calculated from steps 1 - 3 to project future values. This ensures a consistent application of the trend observed in the available data.
For example, if the system has overlapping data from June to August, and the average rate of change from these months is +20%, this same rate will be applied to non-overlapping months of September through May.
Starting Forecast with Less Than 13 Months of Data
Let’s say for example you only have 8 months of actual data, covering the period from January through August of the current year. Since the forecast builder requires 13 complete months of data to calculate a rate of change, LivePlan will use a simpler approach to generate the starting forecast. LivePlan will use the most recent available values and copy them year-over-year as your starting forecast.
Available Actuals (January to August):
- January: $10,000
- February: $12,000
- March: $11,000
- April: $13,000
- May: $14,000
- June: $15,000
- July: $16,000
- August: $17,000
- September - December: No data
Since there is no data for September to December and the system cannot calculate a rate of change with fewer than 13 months, LivePlan will simply copy the existing data over for the subsequent years of the for your starting forecast. From here, your existing data will help inform your projections for the remainder of the first year as well as following years.
Note: This starting forecast is just that - a starting point. You'll want to make your forecast more strategic by making adjustments to the automated starting forecast.