08.12 Balance Sheet Planning (Advanced)
Most planning cycles focus on profit and loss accounts but occasionally it is also necessary to forecast cash flow or balance sheet positions — planning where an account ends each period rather than what moves through it in the period.
Balance sheet planning requires slightly different handling because of the distinction between an opening balance and the net movements within each period. In essence the solution is to submit an opening balance for your planning scenario, ensure that the cumulative period balance calculations include that opening balance, and then submit period movements to your planning scenario.
Here is a video, or read below.
The key difference: opening balance and net movements
P&L planning is simple: a movement is submitted for each period and FastClose reports them as-is. Balance sheet planning requires two types of submission:
Opening balance: the forecast position at the start of the planning year. This seeds the cumulative balance calculation.
Net movements: the forecast movement for each period. FastClose adds the opening balance to the cumulative movements to produce the period-end balance.
FastClose includes pre-built solutions that handle this automatically.
The text here refers to the Epicor platform but the principles are the same for other ERPs.
Using the Balance Sheet Planning solution reports
Open the solution Balance Sheet Planning Entry for Next Year from File → Home → Solutions in Designer. Set the following mandatory filters in the solution:
Connection, Company, and Book: scope these to the entity being planned for.
Planning Scenario: select the planning scenario.
This Year: select the current fiscal year. Actuals will then display for this year; budget submissions are for next year.
Account Type and Account Type filter (preset to B for balance sheet). Use the Account filter to narrow down further if needed.
Tip: the report will have quite a lot of columns. While learning, temporarily unlock the Fiscal Period filter and change it from 1:12, Cve01:Cve12 to 1:3, Cve01:Cve03. This reduces the number of columns considerably.
Once configured, save it as your own report in your personal folder or in Shared Reports, and then open it in Excel using the Add-In.
The report when run should now show you:
Up to 12 periods of actual period-end cumulative balances from your ERP.
A budget opening balance for next year: yellow and submittable.
Net movements by period for next year: also yellow and submittable.
Cumulative period-end balances for next year: calculated automatically from the opening balance and submitted movements.
Enter the opening balance budget amounts by account in the first submittable column, then enter the net movement for that account by period in the subsequent columns. Once submitted, the cumulative budget balances will be calculated.
As with P&L planning, Excel formulas can be used to pull through values from a separate cash flow model or working capital schedule on another worksheet.
Replanning within the current year
To revise balance sheet forecasts for the current year (rather than next year), open the solution Balance Sheet Planning / Replanning Entry for This Year from File → Home → Solutions in Designer. It works identically but scoped to the current fiscal year — useful for mid-year reforecasting where future periods need updating while closed periods are left intact.
Balance Sheet Reporting : Plan vs actuals
The Balance Sheet Planning Reporting for This Year solution shows period-end cumulative actuals for closed periods and budget figures for future periods — seamlessly blended, taking account of the latest actual closing balance to ensure the forward-looking budget numbers remain grounded in reality.
Now try the downloadable exercises below:
LINK HERE
Your feedback helps us do better, to fill in a short survey on this tutorial, click here.
Move on to 0813 - Reviewing and Auditing Submitted Data
