Ultimate Month-End Profit & Loss Report
The BI4 Ultimate Budget Forecast report provides a traditional P&L with Actual, Budget and Forecast in pre-calculated columns grouped in Month-To-Date (MTD), Year-To-Date (YTD) and Full Year FY. This one P&L report has virtually every column you need to create the all in one "Ultimate" month end P&L.
These are shown diagrammatically below. The columns can be hidden or arranged in any order.
Using pre-built metrics and comparison date offsets as columns allows the creation of the "Ultimate" Profit & Loss report including options for:
- Actual vs Budget
- Month To Date vs Year To Date vs Last Year
- Full Year Budget vs Forecast where Forecast = Actual~Budget (BudgetFullYear - ActualsYTD)
- Full Year Budget vs ReForecastNew where ReForecast is a second Budget
- uses QuickBooks multiple Budgets. ReForecast last entered Budget
- uses Xero Overall Budget as the initial Budget and a budget named Forecast for the ReForecast. Optionally divisonal budgets can be read from Xero.
- By Account or at a Summary P&L level
- Filter or Group by Division, Cost centre, Class, Location, Job etc
P&L - Actual v Budget inc Last Year - Ultimate Budget Forecast!
Search for the Ultimate Budget Forecast report which is in the Workbook
Accounts Profit & Loss [base]
This runs the following default P&L layout
Defining your P&L Report
- Select Columns (Add/Remove) to display or rearrange
- Select Rows to display
- Select Sub-Totals to display
- Select Divisions to display
What Columns are available to select ?
-
Month to Date
- Actual | Budget | Variance $ | Variance % | Actual Last Year -
Year to Date
- Actual | Budget | Variance $ | Variance % | Actual Last Year -
Full Year
- Forecast | Budget | Re-Forecast |
Forecast is Actual up to the Prior Month plus Budget values for remaining months
Re-Forecast uses a second Budget when available**
**QuickBooks Online: BI4Cloud reads the budget entries in QuickBooks Online using the first entered budget of a year as the Budget and the last entered budget of a year as the ReForecast
**Xero: BI4Cloud uses the Overall Budget budget entry in Xero as the Budget
If Divisional Budgets using Tracking Categories have been entered in Xero then an option exists to read these budgets into BI4Cloud. With this option is set then TC budgets with '*Budget*' in their name will be the Budget values and TC budgets with '*Reforecast*' in their name will be the ReForecast value. This feature is enabled on request by contacting support@bi4cloud.com
Change Columns using Report Designer
To access the Report Designer and change columns:
- Be in Edit Mode (click the Eyeball icon to change to Edit mode)
- Click the Edit button
- To Re-order columns drag and drop a field
- To Delete a column hover over a field to see X
-
To Edit a column hover over a field then click the " ... "
- Change the field name
- Hide a field by toggling the Visible button (suggest hide rather than delete as it is then easier to bring the field back if you change your mind)
- Change the Format, Decimal places, Background and font colour etc
Change Columns using Settings-Report Panel
To access the Settings-Reprot panel to change columns:
- Be in Edit Mode (click the Eyeball icon to change to Edit mode)
- Click on Setting tab then select the Report tab
- The Report Fields, Group by and Comparison options display (exactly like in the Report Designer)
- See instructions above to edit, move or delete columns
Change Rows using Analyse By
Use the Analyse By field to see the many options for the level of detail to display in Rows. Summary P&L reports can be fully automated with the option to Drill down to see the underlying accounts.
The choice of row dimensions varies depending on which accounting system you use. Here is what is available by accounting product.
Choose from Account, Account Levels 1-4, Flexi COA, JOb or JOb Header, Custom Tags
- Account - The lowest level by every GL account
- GL Account Levels 1, 2, 3, 4 - GL Header Accounts - Click here to learn how to setup Header accounts in MYOB itself
-
Available on the Cool Stuff - Enterprise 5 user version of BI4Cloud try these more advanced options:
- User Defined COA using Flexi Levels - Click here to learn how to create your own custom account groups in BI4Cloud using Flexi Levels
- Job or Job Header - Assign Jobs in MYOB to reflect Department, Cost Centre etc.- Click here to learn how to load in Monthly Budgets by Job in BI4cloud
- GL Custom Tags - Tag each account to belong to a specific Cost Centre, Division, State - Click here to learn how to assign GL Tags to accounts
- Category - by MYOB Category
Account, Location, Class, Class Levels, Account Levels 1-4, Flexi COA or GL Custom Tags
- Account - The lowest level by GL account
- Location
- Class or Class Level 1, 2, 3 - as setup in QBO itself
- GL Account Level 1, 2, 3, 4 - GL Header Accounts as setup in QBO itself
- User Defined COA using Flexi Levels - Click here to learn how to create your own custom account groups in BI4Cloud using Flexi Levels
- GL Custom Tags - Tag each account to belong to a specific Cost Centre, Division, State - Click here to learn how to assign GL Tags to accounts
Choose Account, Tracking Category 1 or 2, Flexi COA or Custom Lists
- Account - The lowest level by GL account
- Tracking Category 1 - this will have a Label defined in Xero
- Tracking Category 2 - this will have a Label defined in Xero
- User Defined COA using Flexi Levels - Click here to learn how to create your own custom account groups in BI4Cloud using Flexi Levels
- GL Custom Tags - Tag each account to belong to a specific Cost Centre, Division, State - Click here to learn how to assign GL Tags to accounts
- Note Xero does not make Account Groups you setup in Xero available to use in BI4Cloud
Change Sub-totals using Group By
The base/default Ultimate Budget Forecast report is set up wth a single sub-total as follows to create a Classic P&L:
- Analyse by - Account (GL Account) in the rows
-
Group by - P&L Format Totals - when used with the Classic Format it directs the report to display
- Income /COS/Gross Profit/Expenses/Operating Profit/Other Inc & Exp/Net Profit
- Income and Expenses all display as positive numbers -
Warning - Do not remove or change the "P&L Format Totals" dimension from the Group by field when using the Classic, EBITDA or Divisional P&L Formats
See Accounts Profit & Loss - Advanced P&L Formating for other options. - Add other Sub-totals - You can choose to add additional sub-totals depending on what level of detail you have set for the rows. e.g. show all accounts in detail then add a subtotal by GL Header, by Tracking Category, by Department or by the Flexi headers you have created
Example, group GL accounts and sub-total by an Account Header Level.
To edit the Sub-totals or add more Group by levels click the Edit button to open the Report Designer or use the Settings-Report Panel (see Changing Columns above).
- Add a Subtotal for GL Account level 2 by indenting this field under the P&L Format Totals
- See above for other report grouping options in MYOB, QuickBooks and Xero
Example - High Level Summary P&L
To run a summary level P&L report simply change the row dimension to a higher level:
- Analyse by - GL Account Level 2 (MYOB users see Streamline your COA) See above for other summary report groups in MYOB, QuickBooks and Xero; or
- Edit or use Settings-Report - then select the summary report groups to display in the rows
- Drill down - by GL Account so you can drill down on a Summary group to see the underlying GL Accounts that make up the balance.
Divisional Reporting
Divisional Reporting is achieved by utilising the Analysis Codes available in each accounting product (see above) these are implemented differently in each of the accounting products. The various options available in Xero, QuickBooks and MYOB are described in Analysis Code Comparison
Example Divisional P&L - Division as Rows
- Analyse by - Division (example below is using the Tracking Category called Venue in Xero)
- P&L Format - In filters select the Divisional P&L format, his will show Income | Gross Profit | Net Profit all broken out by Division