Using Jobs to run Departments or Cost Centres - With Monthly Job Budgets

Using Jobs is a great way to separate out and report on different Departments or Cost centres in MYOB.  However, if you also want to maintain monthly Budgets it is not possible to maintain them and report on Actuals v Budgets from within MYOB.

Using Business Intelligence for Cloud (BI4Cloud) you are now able to enter in Monthly Job budgets into BI and then report on Actuals v Budgets using BI. This feature is available in our Enterprise 5 User (Cool Stuff) version.

Initial Setup - See instructions at the end of this Article

Enter Monthly Job Budgets

Login to BI as the main BI Account Admin User, you will see the Admin Console or if already logged in go to the Company page.

To get help from a BI Expert to setup Budgets in BI - Book a BI Consulting Appointment

1. Click on the Cool Stuff button.(Available in Enterprise 5 user version)

Screen_Shot_2017-03-13_at_9.44.28_am.png

 2. The Job Budgets screen will display all of your Active Jobs.

Jobs display under Job Headers (See Using Job Headers to Organise Jobs)

3. Filter to Find a Job number 

4. Click on "Budgets" next to the Job you wish to enter Budgets for

5.Choose the Financial Year

6. Enter Budget Amounts 
- Enter in Whole $ with no cents
- Revenues are Positive
- Costs are Positive   
(Note this is how Regular Job Budgets are entered into MYOB so we have kept it the same).

To import Job Budgets from a spreadsheet see detailed instructions at the end of this article.

7. Save Changes

8. Totals will update once you Save.

 

9. Click Exit to Sync the Budgets to your BI reports and Exit back to the Admin screen.

It is not necessary to Refresh your MYOB data to be able to see the Period Budgets in your BI reports.

 

Run Monthly Job Budget Reports

 10. Login to BI - Run Jobs Favourite - Job Actual v Period Budget Monthly

This report
- Analyses by GL Account  (P&L)
- Then Pivots by Transactions by Month to produce a monthly P&L report
- Display All(H) - shows a Horizontal Pivot on Actual,Budget and Variance
- Grouping by Account Group (Income, Costs, Expense etc)
- 2nd Group by Job Number (to Subtotal P&L by each Job Number)
      (go to the Advanced "Cog" to set up multiple Group bys)

- Budget Type - Period Budget

11. Run Jobs Favourite - Job Actual v Period Budget MTD YTD

This report is a Comparison P&L report - Job by Job 
- Analyses by GL Account (to produce a mini P&L)
- Then compares Last month and Year to Last month Act v Budget 
- Groups by Job Number (to subtotal by Job number)
- Budget Type - Period Budget

 

12. Run Jobs Favourite -  Job Actual v Period Budget - MTD YTD by GL Account

This is another interesting take on the standard Jobs P&L where each line of the GL is broken down into the underlying Jobs that make it up.

This report is a Comparison P&L report - GL Account by GL Account 
- Analyses by Job Number  (to see jobs in the Rows)
- Then compares Last month and Year to Last month Act v Budget 
- Groups by GL Account 2 (to subtotal by GL Account Level 2)
- Budget Type - Period Budget

 

13. Run Jobs Favourite -  Job Actual v Period Budget - YTD by Job

This is yet another interesting take on the standard Jobs P&L which shows Jobs as Columns and works well if you do not have too many Jobs. 

This report is a Pivot report 
- Analyse by GL Account  (to see a P&L in the Rows)
- Then by (versus)  Job Number - To show Jobs as Columns
   (or change to Job Header if you have numerous detail Jobs)
- Group by Account Group (Income, Costs, Expense etc)
- Budget Type - Period Budget

 

 

Initial Setup 

Monthly Job Budgets can be entered into BI4Cloud and store outside of your MYOB file.
To turn this feature on Upgrade to the Cool Stuff - Enterprise 5 user version.

To get help from a BI Expert to setup Budgets in BI - Book a BI Consulting Appointment

1. Organisation Page
Login to BI as the main BI Account Admin User and go to the Organisation Page.

 

2.Set Advanced Database to Yes

Select Advanced Database to Yes

3. Refresh your Data 

Select the Company menu option and then press the Refresh icon for the company to refresh. Wait until the refresh complete before you try to enter budgets. You will get an email upon Refresh completion.

Load Job Budgets from Excel 

You can load Monthly Job Budgets from an Excel spreadsheet, however they must be in an exact format. Follow instructions above under Enter Monthly Job Budgets to get to the Budget Entry Screen

Enter_Job_Budget_screen.png

  1. Export - to generate the excel Job Budget Template

  2. Enter or Copy budget values into the Job Budget Template  (for Advanced Help with linking to an existing Budget spreadsheet See point 5 below).

    Job_budget_template.png

  3. Import - to load budget template back into BI

  4. Save

  5. Advanced Help - Linking to an existing Budget Spreadsheet using VLookup. If you have an existing Budget spreadsheet you are probably not budgeting by every single GL account number so you will need to Link this Existing Budget spreadsheet to the Load Tempate

    To link to your Existing Budget spreadsheets to populate the Load Template 
  • Define Range Name - in your Existing Budget spreadsheet by selecting the area to include the Account Number, Account Name and monthly Budget data then enter the Range Name into the Top left cell as shown below.
  • Add Column numbers 1 to 14 - used by the VLookup formula to find how many columns to the right of the Account number to look to find the data for the corresponding month

Existing_Budget.png

  • In the Budget Template Load spreadsheet Enter the Following Excel Formula  into cell "I2" - see screenshot below
  • =IFERROR(VLOOKUP($G2, RANGENAME, PLAN!C$4, FALSE), "0")   where
    • $G2 = the GL account number to lookup and match to. By placing a $ in front of the "G" you will be able to copy the Vlookup formula across and down the page
    • RANGENAME = the Range Name you have given to the Budget data in your Existing budget spreadsheet above
    • PLAN!C$4 - Refers to the number of columns to the right of the Account Number you are looking up in your Existing Budget Spreadsheet (which here is called PLAN- see above).
    • C$4 will return the number 3 which tells the VLookup to look for the value in the 3rd column across. By putting a $ in front of the "4" you will be able to copy the Vlookup formula across and down the page  
    • Copy the formula in cell I2 across for the 12 months then copy the 12 cells down for all GL Accounts. This should populate the full load template.
    • Save
    • Using the "IFERROR" formula around the Vlookup will place a 0 in any cells where you DO NOT have a corresponding budget for that GL account
    • For help using Excel VLookup formula Click Here

Budget_Template_Vlookup.png

  • To create a Load File that has numbers only and removes the VLookup formula
    • Copy the data from the Load template - ensure you pick up all columns and rows
    • Open a new workbook
    • Paste Special - Choose "Values and Number Formats" 
    • Save
  • In BI Load this new file
  • Save changes in BI

www.BI4Cloud.com

 

Was this article helpful?
1 out of 1 found this helpful

Articles in this section

Ask the BI4 Community
Need help using this report or feature?