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)
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
- Export - to generate the excel Job Budget Template
- Enter or Copy budget values into the Job Budget Template (for Advanced Help with linking to an existing Budget spreadsheet See point 5 below).
- Import - to load budget template back into BI
- Save
- 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
- 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
- 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
