Budget vs. Actual ARM Report: Process Activity

The following activity will walk you through the process of preparing and running an ARM report that compares budgeted and actual amounts.

Story

Suppose that the management of the SweetLife Fruits & Jams company wants to compare the budget for 2023 with the actual figures for this financial year.

Acting as a system administrator, you need to prepare and run the Budget vs. Actual ARM report.

Configuration Overview

On the Enable/Disable Features (CS100000) form, the Subaccounts feature has been enabled. On the Segmented Keys (CS202000) form, the SUBACCOUNT segmented key has been defined according to the company’s business needs, as described in Subaccounts: Implementation Activity.

In the U100 dataset, on the Ledgers (CS202000) form, two ledgers have been defined: a ledger with the BUDGET identifier and the Budget type, and a ledger with the ACTUAL name and the Actual type.

Process Overview

In this activity, you will prepare the actual data for SweetLife by uploading an Excel file on the Journal Transactions (GL301000) form. On the Column Sets (CS206020) form, you will copy the column set of an existing ARM report and update its settings. On Report Definitions (CS206000) form, you will copy the report definition of an existing report and update it. Finally, you will run the SweetLife: Actual vs. Budget (RM000001) report and review it.

System Preparation

To prepare the system, in a tenant with the U100 dataset preloaded, do the following:

1. Launch the MYOB Advanced website, and sign in as an administrator by using the following credentials:
   • Username: gibbs
   • Password: 123
2. On the Company and Branch Selection menu, also on the top pane of the MYOB Advanced screen, make sure that the SweetLife Head Office and Wholesale Center branch is selected. If it is not selected, click the Company and Branch Selection menu button to view the list of branches that you have access to, and then click SweetLife Head Office and Wholesale Center.

Step 1: Preparing the Actual Data of SweetLife for 2023

To prepare the actual data of SweetLife for 2023, do the following:

1. Open the Journal Transactions (GL301000) form, and on the form toolbar, click Add New Record to create a batch.
2. In the Summary area, specify the following settings:
   • Branch: HEADOFFICE
   • Ledger: ACTUAL
   • Transaction Date: 12/31/2023
   • Post Period: 12-2023
   • Description: Transactions for 2023
3. On the Details tab, click Load Records from File on the table toolbar.
4. In the File Upload dialog box that opens, click Choose File and select the HEADOFFICE_2023_Transactions.xlsx file. Click Upload.
5. In the Common Settings dialog box that opens, leave the default settings and click OK.
6. In the wizard that opens, leave all the default settings, and map the columns as the system suggests. Click OK.
7. On the form toolbar of the Journal Transactions form, click Remove Hold.
8. On the form toolbar, click Release.

Step 2: Copying and Updating the Column Set

To copy a column set of an existing ARM report and update its settings, do the following:

1. Open the Column Sets (CS206020) form.
2. In the Code box, select DPLP.

   This is the report definition code of the Profit and Loss report that you are going to copy.

3. On the form toolbar, click Copy Column Set.
4. In the New Column Set Code dialog box, which appears, enter MYPL in the New Code box, and click Copy.
5. In the Description box, enter P&L Actual vs. Budget.
6. On the form toolbar, click Save to save your changes.
7. In the upper table, replace the ='YTD' value in column B with ='Actual'.
8. Replace the ='PTD' value in column C with ='Budget'.
9. In the lower table, find the Data Source row. In column B for this row, click the magnifier button, and specify the following settings in the dialog box that opens:
   • Ledger: ACTUAL
   • Amount Type: Ending Balance
10. Click OK to save the values and close the dialog box.
11. In column C for the Data Source row, click the magnifier button, and specify the following settings in the dialog box that opens:
    • Ledger: BUDGET
    • Amount Type: Ending Balance
12. Click OK to save the values and close the dialog box.
13. On the form toolbar, click Save to save your changes. The following screenshot illustrates the changes to the column set.

    
    
    

Step 3: Copying and Updating the Report Definition

To copy an existing report definition and update its settings, do the following:

1. Open the Report Definitions (CS206000) form.
2. In the Code box, select DPL.
3. On the form toolbar, click Copy Report.
4. In the New Report Code dialog box, which opens, specify MYPL in the New Code box, and click Copy. This closes the dialog box; you are now working with the copied version of the report with the MYPL code.
5. On the form toolbar, click Save to save the changes.
6. In the Report Definition section, specify the following settings:
   • Description: P&L Actual vs. Budget
   • Column Set: MYPL - P&L Actual vs. Budget
7. In the Default Data Source Settings section, specify the following settings:
   • Company: SWEETLIFE
   • Request (right of the Company box): Cleared
   • Request (right of the Ledger box): Cleared
   • Start Branch: HEADOFFICE
   • Request (right of the Start Branch box): Selected
   • Request (right of the Start Period box): Selected
8. In the Site Map section, specify the following settings to add your report to the site map:
   • Title: P&L: Actual vs. Budget
   • Workspace: Finance (copied from the predefined report)
   • Category: Financial Statements (copied from the predefined report)
   • Make Visible on the UI: Selected
9. On the form toolbar, click Save to save your changes. The following screenshot illustrates the changes to the report definition.

   
   
   

Step 4: Running the ARM Report

To run the P&L: Actual vs. Budget report, do the following:

1. On the main menu, click the Finance menu item to open the Finance workspace. Under the Financial Statements category, click P&L: Actual vs. Budget to open the report form of the report you have created.
2. On the Report Parameters tab, specify the following parameters:
   • Start Branch: HEADOFFICE
   • Financial Period: 12-2023
3. On the form toolbar, click Run Report.
4. On the report that is displayed, review the amounts in the Actual and Budget columns of the Profit & Loss report generated for the SweetLife Fruits & Jams company, as illustrated in the following screenshot.