fct_budgets


Description

The budget aims to provide a structured plan for the income and expenditures of care delivery. There can be multiple budgets per membership, however only one is valid at a given time. There are a few elements associated with a budget. Income: There can be a lot of income sources for a budget. They can be, but are not limited to government subsidies, any unspent funds, income tested fees or any additional supplements or income. Expenses: These are care management expenses relating to the budget. E.g. Packagement Management Fee. Service planning: This represents the agreed spend by service between the client and the service provider.

Columns

Column Type Children Parents Comments
budget_id int8

The unique ID of the budget. E.g. 52.
budget_link text

The link to the budget within Lookout. E.g. https://app.{company}.com.au/admin/memberships/1/budgets/1/edit.
membership_link text

The link to the membership within Lookout. E.g. https://app.{company}.com.au/admin/memberships/1.
start_date date

The date the budgeted period starts. E.g. 2025-1-1.
end_date date

The date the budgeted period ends. E.g. 2025-3-1.
funding_scheme_type varchar

The funding scheme for the budget. E.g. HCP.
funding_scheme_classification varchar

The classification within the funding scheme. E.g. 1.
approved_at timestamp

The date and time that the budget was approved. E.g. 2025-02-18 13:40:04.792430.
is_approved bool

Whether the budget is approved. True or False. E.g. True.
membership_id int8

The Membership ID associated with this budget. E.g. 251.
created_at timestamp

The date and time the budget was created (in UTC). E.g. 2021-03-02 00:12:59.
updated_at timestamp

The date and time the budget was updated (in UTC). E.g. 2021-05-02 06:12:59,
daily_income_cents int8

The calculated daily amount (in cents) for the incomes associated with this budget. E.g. 1500.
daily_expenses_cents int8

The calculated daily amount (in cents) for the expenses associated with this budget. E.g. 1500.
daily_service_plan_cents int8

The calculated daily amount (in cents) for the service plans associated with this budget. E.g. 1500.
total_budgeted_for_services_cents int8

The calculated total amount (in cents) for the services associated with this budget. E.g. 1500.
total_budgeted_for_expenses_cents int8

The calculated total amount (in cents) for the expenses associated with this budget. E.g. 1500.
total_budgeted_for_incomes_cents int8

The calculated total amount (in cents) for the incomes associated with this budget. E.g. 1500.
internal_budget_difference_cents int8

This checks the difference between the budgeted income and costs, for any leftover budget. The equation is `total_budgeted_for_incomes_cents - (total_budgeted_for_expenses_cents + total_budgeted_for_services_cents) E.g. 25000.
internal_utilization numeric

A ratio of the internal budget utilization. A value less than 1 indicates there’s more room for expenses or service plans, a value more than 1 indiciates that the budget is overspent. The equation is (total_budgeted_for_expenses_cents + total_budgeted_for_services_cents) / total_budgeted_for_incomes_cents. E.g. 0.8.
internally_consistent bool

If the internal_utilization is less than 1, the budget is internally_consistent. This column will return True. Otherwise False. E.g. True.
actual_cents_paid int8

The amount (in cents) paid by invoices by the membership during the budgeted period. E.g. 2000.
services_surplus_cents int8

The difference between the total_budgeted_for_services and the actual_cents_paid, in cents. E.g. 20000.
is_services_overspent bool

Whether the service surplus is greater than 0 or not. True or False. E.g. True.
actual_utilization numeric

A ratio of the actual budget utilization. actual is defined by the amount charged to the membership during the budgeted period, as given by the invoices during this timeframe. This is then divided by the total_budgeted_for_services_cents. A value less than 1 indicates there’s more room for expenses or service plans, a value more than 1 indicates that the budget is overspent. The equation is actual_cents_paid / total_budgeted_for_services_cents. E.g. 0.8.
predicted_visit_cost_to_member_cents int8

The total predicted cost to the member for all visits for the budgeted period. E.g. 16000. See fct_visits.predicted_cost_to_member.
predicted_purchase_order_cost_to_member_cents int8

The sum of all purchase order items.gross_amounts for the budgeted period. E.g. 19400.
predicted_cost_to_member_cents int8

The predicted cost for all services rendered to the membership during the budgeted period. The equation is predicted_visit_cost_to_member_cents + predicted_purchase_order_cost_to_member_cents.
predicted_utilization numeric

A ratio of the predicted budget utilization. This is a ratio of scheduled services and budgeted service plans. The equation is predicted_cost_to_member_cents / total_budgeted_for_services_cents. A value less than 1 indicates there’s more room for expenses or service plans, a value more than 1 indiciates that the budget is predicted to be overspent. E.g. 0.8.
predicted_overspend_cents int8

The difference between the predicted costs to the membership over the budgeted period and the total budgeted for services. The equation is predicted_visit_cost_to_member_cents + predicted_purchase_order_cost_to_member_cents - total_buddgeted_for_services_cents E.g. 19300.
services_predicted_to_overspend bool

If predicted_overspent_cents is greater than 0, returns True. Else False. E.g. True.
company_id int8
dim_companies.company_id Implied Constraint R

The company ID. E.g. 345.

Indexes

Constraint Name Type Sort Column(s)
fct_budgets_pkey Primary key Asc budget_id
fct_budgets_company_id_idx Performance Asc company_id

Relationships

Close relationships within degrees of separation