Budget Trend Analysis
Learn about Maica's Budget Trend Analysis logic.
Overview
The Budget Trend Analysis component displays funding and expenditure on a quarterly basis, aligned with the budget periods defined in a Care Recipient’s Plan under Support at Home. This helps you track approved funds, projected expenditure, and remaining balances for each quarter.
The component displays one tile per quarter.
Each tile contains:
Available Funding– total approved funding for the quarter.Estimated Expenditure– projected expenditure for the quarter, rolled up from Service Agreement Items.Remaining Funding– available funding minus estimated expenditure.
Attributes
The Budget Trend Analysis component relies on two primary attributes to perform quarterly calculations. These attributes define how expenditure and available funding are derived and applied within the component.
Estimated Quarterly Expenditure
Estimated Quarterly ExpenditurePurpose: Rolls up planned expenditure per quarter from Service Agreement Items.
Calculation:
For each Agreement Item:
Map service occurrences into quarterly budget periods based on service dates.
Calculate the number of occurrences using frequency and service dates.
Multiply by cost per occurrence.
Aggregate across all Agreement Items to determine total expenditure for the quarter.
Component Behaviour: Used by the Budget Trend Analysis component as the projected expenditure figure for each quarter.
Available Funding Quarterly
Available Funding QuarterlyPurpose: Represents the amount of approved funding available to the Care Recipient for the current quarter.
Data Source: Derived from the parent Plan record, rolled up from active Plan Budgets.
Component Behaviour: Used as the approved funding figure for each quarter and combined with Estimated Quarterly Expenditure to calculate Remaining Funding.
Fields
The accuracy of the Budget Trend Analysis component also relies on a set of supporting fields across the Plan, Service Agreement, and Agreement Item objects. These fields provide the values that are surfaced in the component and ensure calculations are performed consistently at the quarterly level.
Plan
Total Approved - Active
Rollup Summary
Summarised Object: Plan Budget
SUM Approved Amount
Filter Is Active = TRUE
Represents the SUM of the Approved Amount from all related Plan Budget records where Is Active = TRUE. Use this to view the total approved funding from currently active Plan Budget items.
Total Remaining - Active
Rollup Summary
Summarised Object: Plan Budget
SUM Remaining Amount
Filter Is Active = TRUE
Represents the SUM of the Remaining Amount from all related Plan Budget records where Is Active = TRUE. Use this to monitor how much approved funding is still available within the active Plan Budgets items.
Service Agreement
Available Funding - Active
Formula (Currency)
Plan → Total Approved - Active
Represents the SUM of the Approved Amount from all related Plan Budget records where Is Active = TRUE. Value taken from the roll-up summary field on the Plan record.
Remaining Funding - Active
Formula (Currency)
Plan → Total Remaining - Active
Represents the SUM of the Remaining Amount from all related Plan Budget records where Is Active = TRUE. Value taken from the roll-up summary field on the Plan record.
Agreement Item
Estimated Expenditure Current Quarter
Currency
Stores the projected expenditure for the current quarter at item level.
Calculated by mapping service dates into the current quarter, multiplying the number of occurrences by the cost per occurrence, and storing the total.
Calculation Logic
Once the data model fields are populated, the component applies the following calculations:
Estimated Expenditure (Quarterly)
Taken from Agreement Item → Estimated Expenditure Current Quarter.
Represents the rolled-up total of projected costs for that quarter.
Available Funding (Quarterly)
Taken from Service Agreement → Available Funding – Active.
Represents the approved quarterly funding available to the Care Recipient.
Remaining Funding (Quarterly)
Formula:
Remaining Funding = Available Funding – Estimated ExpenditureWorking Examples
Agreement Item One
Start Date
1 July 2025
End Date
1 July 2026
Rate
$100.00 per service
Service Frequency
Weekly
Service Duration
1.00
Schedule Count
1
Current Month
July 2025
Current Quarter
Q3 (July – September 2025)
Weekly frequency = 1 session per week (Friday)
Schedule Count = 1 → only one service occurrence per week.
1 service x 1 hour x $100.00/hour = $100.00 per service
July 2025 Fridays:
Fridays fall on: 4th, 11th, 18th, 25th → 4 Fridays
Q3 2025 (July, August, September) Fridays:
July: 4 Fridays
August: 5 Fridays → 1st, 8th, 15th, 22nd, 29th
September: 4 Fridays → 5th, 12th, 19th, 26th
Total: 13 Fridays
Estimated Expenditure (Current Month – July 2025):
4 Fridays × $100.00 = $400.00
Estimated Expenditure (Current Quarter – Q3 2025):
13 Fridays × $100.00 = $1,300.00
Final Answer
Estimated Expenditure (Current Month)
$400.00
Estimated Expenditure (Current Quarter)
$1,300.00
Agreement Item Two
Start Date
1 July 2025
End Date
1 July 2026
Rate
$87.16 per service
Schedule Count
1
Service Frequency
Weekly
Schedule Day
Tuesday;Wednesday;Thursday
Current Month
July 2025
Current Quarter
Q3 2025 (July – September)
Since there are 3 scheduled days per week, and 1 session per scheduled day per week, we get 3 sessions per week
July 2025
There are 5 Tuesdays, 5 Wednesdays, and 5 Thursdays:
Total sessions in July = 5 × 3 = 15 sessions
Q3 2025 (July–September):
August 2025: 4 Tuesdays, 4 Wednesdays, 4 Thursdays or 4 × 3 = 12
September 2025: 5 Tuesdays, 4 Wednesdays, 4 Thursdays or 13 total
Total sessions in Q3 = 15 (Jul) + 12 (Aug) + 13 (Sep) = 40 sessions
Rate per session = $87.16
✅ Estimated Expenditure (Current Month):
15 sessions × $87.16 = $1,307.40
✅ Estimated Expenditure (Current Quarter):
40 sessions × $87.16 = $3,486.40
Final Answer
Estimated Expenditure (Current Month)
$1,307.40
Estimated Expenditure (Current Quarter)
$3,486.40
Last updated
Was this helpful?