Management report pack - locking in start dates
We run a P&L Forecast report each month, with 12 monthly columns across the financial year.
Start date = start of the financial year.
Reporting on Month 1, this report shows Actuals for month 1 + Budgets for months 2 to 12, to give forecast total for year.
Reporting on Month 2, this report shows Actuals for months 1 to 2 + Budgets for months 3 to 12, to give forecast total for year.
Reporting on Month 11, this report should show Actuals for months 1 to 11 + Budget for month 12, to give forecast total for year.
I expect to (and am happy to) change the current month's reporting column from Budget to Actuals.
But I've realised that I also need to check which month is appearing in each of the 12 monthly columns, as Xero isn't looking at the dates selected in the report layout, but is looking at Current month, Current month -1, Current month -2, etc.
So the report dates advance each month, so too does the Actuals data. The first column date may have been Apr-22 when the report was first set up, but the next month it crept to May-22, then Jun-22.
Is there a way of locking the first Actuals month of a report to the first month of the financial year?
And then locking the last Budget month to the last month of the budget?
Then each month in between could be start + 1, start + 2, etc, and Budget -1, Budget -2, etc.
Jo Russell
shared this idea
-
Vincent Donnat
commented
Hi Jo, late to this one but your description is exactly the report we built K-phi around, so sharing in case it helps someone landing here.
K-phi plugs into Xero and shows the P&L as actuals for the closed months, forecast for the rest, with the year total at the end. The first column stays locked to the start of your financial year, the last one to the end of your budget, and the current month flips from budget to actuals on its own once it’s closed. No layout to reset every month.
Budgets per tracking category (Neil’s point) work the same way, one budget per dimension, picked up automatically in the report.
There’s a 30-day free trial if you want to check it against your own numbers: https://us.k-phi.com/app
Happy to answer questions here.
-
Neil Kagan
commented
When generating a P & L report and filtering for a tracking category, the budget does not automatically change to the option selected. ie a new budget needs to be created for each option budget.
Make this automatic that when a new option is selected to generate a P & L report, the related budget appears automatically without the need to create a new budget. -
Jo Russell
commented
Have since discovered that this functionality existed in the Old Reports under Profit and Loss - see 'Common Format' which was called "Current financial year".