P&L Forecast - Ultimate Month End

 logo-myob-60.pnglogo-qbo-60.png logo-xero-60.pngUltimate Month-End Profit & Loss Reporting

The BI4 GL Profit and Loss Forecast report provides a classic P&L with Actual, Budget and Forecast in 18 pre-calculated columns grouped in  Month-To-Date (MTD), Year-To-Date (YTD) and Full Year. These are shown diagrammatically below. The columns can be hidden or shown in any order.

Ultimate_GL_Image.png

Selecting various metrics as columns allows the creation of Profit & Loss reports with many different formats including:

  • Actual vs Budget
  • Month To Date vs Year To Date vs Last Year
  • Full Year Budget vs Forecast where Forecast = BudgetFullYear - ActualsYTD
  • Full Year Budget vs ReForecastNew where ReForecast is a second Budget
    • uses QuickBooks multiple Budgets. ReForecast last entered Budget
    • uses Xero  Overal Budget as the initial Budget and a budget named Forecast for the ReForecast
  • By Account or Summary P&L
  • By Division or cost centre

logo-myob-60.png To run this same report over the MYOB Jobs P&L instead of GL Click here.

P&L - Actual v Budget inc Last Year - Ultimate Month End 

Run the default BI4Cloud report  P&L - Act v Bud inc LYR - Ultimate Month End 

Ultimat_P_L.png

Defining your P&L Report

Screen_Shot_2018-07-19_at_8.42.27_am.png

What Columns are available ?

  • Month to Date
    - Actual | Budget | Variance $ | Variance % | Actual Last Year
  • Year to Date
    - Actual | Budget | Variance $ | Variance % | Actual Last Year
  • Full Year
    - Forecast | Re-Forecast | Budget | Variance $ | Variance %
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 Re-Forecast

**Xero: BI4Cloud uses the Overall Budget budget entry in Xero as the Budget and a budget entry named Forecast  the Re-Forecast

The animation below shows these available columns

Change Columns using Add/Remove Columns

Using Add/Remove Select and Reorder Columns - Click on +/- Cols icon.
Choose Columns to Show or Hide, use Green arrows to Reorder Columns.

Hide_-_Show.png

Change Rows using Analyse By

Use the Analyse By to see the options for the level of detail in Rows. The choice of row dimensions varies depending on which accounting system you use. Here is what is available by accounting product.

logo-myob-60.png Choose from Account, Category, Account Levels 1-4, Flexi COA or Custom List

  • Account - The lowest level by GL account
  • Category 
  • GL Account Level 1, 2, 3, 4 - Header Accounts - Click here to learn how to setup Header accounts
  • User Defined COA using Flexi LevelsNew - Click here to learn more about Flexi Levels
  • GL Custom Lists - e.g Cost Centre/Division/State - Click here for more info

logo-qbo-60.pngAccount, Location, Class, Class Levels, Account Levels 1-4, Flexi COA or Custom Lists

  • Account - The lowest level by GL account
  • Location
  • Class
  • Class Level 1, 2, 3 - Header Accounts
  • GL Account Level 1, 2, 3, 4 - Header Accounts
  • User Defined COA using Flexi LevelsNew - Click here to learn more about Flexi Levels
  • GL Custom Lists - e.g Cost Centre/Division/State - Click here for more info

logo-xero-60.png 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 LevelsNew - Click here to learn more about Flexi Levels
  • GL Custom Lists - e.g Cost Centre/Division/State - Click here for more info

Change Sub-totals using Group By

Use the Group by feature to add one or more Sub-total Levels.

What you choose to sub-total on will depend on what level of detail you have set for the rows.

The standard Favourite (see above) is set up wth a single sub-total as follows:

  • Analyse by - Account (GL Account)
  • Group by - Account Type - Income /COS/Gross Profit/Expenses/Net Profit

To edit the Sub-totals or add more Group by levels click on the Green +

  • Add a Subtotal for GL Account level 2 (See above other report groups in MYOB QBO and Xero)
  • Next Group by Account Type 
  • Select Group by Type - Headings and Summary
  • See Add Multiple Sub-totals - Group by Levels for more examples

Group_by.png

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 report groups in MYOB QBO and Xero)
  • Group by - Account Type - Income /COS/Gross Profit/Expenses/Net Profit
  • Then by - GL Account so you can drill down on a Summary group to the underlying GL Accounts

Summary_P_L.png

Divisional Reporting

Divisional Reporting is achieved by utilising the Analysis Codes available in each accounting product these are implemented differently in each of the accounting products. The various options availalbe in Xero, QBO 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)
  • Group by - Account Type - Income /COS/Gross Profit/Expenses/Net Profit

Divisions.png

Example Divisional P&L - Pivot to see Division as Columns

  • Analyse by - GL Account
  • Then by - Division (example below is using the Tracking Category called Venue in Xero)
  • Group by - Account Type - Income /COS/Gross Profit/Expenses/Net Profit
  • To create a Pivot style report see - Pivot to see Multiple Dimensions at once

Divisional_P_L.png

Other Divisional P&L Options

  • Filter on one or a few Divisions
  • Group the P&L by each Division

Other Flexible P&L Options 

Looking for an even more flexible P&L format? Try the BI4 GL Profit & Loss report:

  • Compare any custom date ranges with variances
  • Pivot Weekly, Monthly, Quarterly or Yearly 

Create and Save as a Favourite

Once your have defined the report to your liking save it as a favourite and share it with others. Create as many variations as you wish and save these as favourites.

 

www.BI4Cloud.com

 

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Please sign in to leave a comment.

Powered by Zendesk