Contents Diagrams Roles Schemas Tables Views Procedures Functions

Gartle Budgeting

Version 2.0, July 20, 2020

Contents

Introduction

Gartle Budgeting

Gartle Budgeting is a budgeting application for small and mid-size companies.

Its design principles:

1. The application allows users to load and save unified budget requests.
2. The application allows assigning various analytic members to budget request rows.
3. The application allows selecting pivot data based on analytic members to build custom reports in Microsoft Excel and other applications.
4. The application contains built-in standard financial reports like Income Statement, Cash Flow, and Balance Sheet.
5. The application allows saving and loading reports to create consolidated statements in Excel based on reports stored in a database.

The key points of these principles:

1. Business users may create planning models in Microsoft Excel themselves.
2. Analysts may create budget reports in Excel using data from a database.
3. Analysts may create consolidated reports in Excel using saved reports from a database.

These ideas differentiate Gartle Budgeting from other budgeting applications that implement planning models, budget reports, and consolidated reports internally.

The main benefits of this approach:

1. Flexibility
2. Short time to first results
3. Low implementation cost
4. Independence on external ERP analysts

Also, Gartle Budgeting differs from budgeting applications built with Microsoft Excel:

1. It stores data in a SQL Server database, not in Excel.
2. It allows managing permissions.
3. It does not use macros.
4. It works very fast.
5. It allows using Microsoft Excel as a client, but also the desktop DBEdit and the DBGate and ODataDB web applications.

So, business users may choose any platform and even view and edit budget requests on tablets and smartphones.

Here is an example of a report:

Planning Application - Form Example

You may download sample workbooks at www.savetodb.com and try the application hosted online.

Also, you may try Gartle Budgeting with the DBGate and ODataDB web applications:

- https://dbgate.savetodb.com/en-us/budgeting-app.htm
- https://samples.odatadb.com/en-us/budgeting-app.htm

Feel free to contact us.
We can deploy a copy for you in the cloud to test and configure the app.
Later we can export your data to deploy it in your environment.

Change History

Version 2.0, July 20, 2020

The application has a new name, Gartle Budgeting.

New Features:

Diagrams

DiagramDescription
Diagram 01Budget Requests
Diagram 02Budget Currency Rates
Diagram 03Budget Opening Balances
Diagram 04Journals
Diagram 05Actual Currency Rates
Diagram 06Actual Opening Balances
Diagram 07Configuration Tables
Diagram 08Report Sections
Diagram 09Reporting Engine
Diagram 10Saved Reports
Diagram 11Member Permissions
Diagram 12Member Relations

Diagram 01. Budget Requests

Budget Requests

This diagram highlights the dbo27.budget_requests table used to store data of budget requests.

The dbo27.budget_requests table has a unique key that includes the following columns: entity_id, category_id, time_id, and row_index.

So, budget requests may have multiple lines with the same members but different row_index.

Use xls27.usp_budget_request1 and xls27.usp_budget_request2 to edit data.

Diagram 02. Budget Currency Rates

Budget Currency Rates

This diagram highlights the dbo27.budget_currency_rates table.

Use xls27.usp_budget_currency_rates to edit data.

Diagram 03. Budget Opening Balances

Budget Opening Balances

This diagram highlights the dbo27.budget_balances table used to store budget opening balances.

Use xls27.usp_budget_balances to edit data.

The current version does not use the subsidiary_account_id values as it uses opening balances for such accounts from the balance00 field of the dbo27.budget_requests table.

Diagram 04. Journals

Journals

This diagram highlights the dbo27.journals table used to store accounting journal data.

Use xls27.usp_journal to edit data.

Diagram 05. Actual Currency Rates

Actual Currency Rates

This diagram highlights the dbo27.actual_currency_rates table.

Use xls27.usp_actual_currency_rates to edit data.

Diagram 06. Actual Opening Balances

Actual Opening Balances

This diagram highlights the dbo27.actual_balances table used to store actual opening balances.

Use xls27.usp_actual_balances to edit data.

Diagram 07. Configuration Tables

Configuration Tables

This diagram highlights the primary tables used to configure the application.

The dbo27.members table contains analytic members grouped by dimensions.

The first dimension is the Accounts. It contains members like Revenue or Expenses.

Most of the member attributes are actual for this dimension.

Analysts may define units and VAT rates for account members. Otherwise, users may choose values themselves in the budget requests.

The dbo27.ledger_accounts table contains ledger accounts used in accounting and financial reports.

The dbo27.members, dbo27.ledger_accounts, and dbo27.report_sections tables have properties used to calculate built-in financial reports like Income Statement, Cash Flow, and Balance Sheet.

Diagram 08. Report Sections

Report Sections

This diagram highlights tables used to configure built-in reports executed using the xls27.usp_budget_reports stored procedure.

The application contains default settings to build standard reports.

Analysts may change the following features:

- Row name text (we recommend using dbo27.translations for this purpose)
- Row format
- Row order (using the section column)
- Row reference number
- Lines before and after
- Row parents

Diagram 09. Reporting Engine

Reporting Engine

This diagram highlights tables used to produce financial reports executed using xls27.usp_budget_reports, xls27.usp_actual_reports, and other stored procedures.

Usually, you do not need to change these system tables.

You use the dbo27.ledger_account_types table to customize the dbo27.ledger_accounts table in the setup phase. Also, you use the dbo27.report_section_types table to customize the dbo27.report_sections table.

It is enough to build financial reports using this engine.

This diagram shows the rs_ prefix instead of report_sections_ and the la_ prefix instead of the ledger_accounts_ one.

Diagram 10. Saved Reports

Saved Reports

The SaveToDB add-in for Microsoft Excel allows saving named ranges to a database, into tables directly, or using stored procedures.

The application uses this feature to save Excel reports to a database through the xls27.usp_saved_reports store procedure.

As a result, users may save and load saved reports from a database to compare reports or build consolidated statements.

The dbo27.saved_reports table contains data of such saved reports.

Diagram 11. Member Permissions

Member Permissions

The dbo27.member_permissions table contains user permissions on members.

Use xls27.usp_member_permissions to check and change permissions.

Note that the application checks these permissions for users who are the members of the budgeting_app_users role.

Members of other roles have access to all members.

Diagram 12. Member Relations

Member Relations

The dbo27.member_relations table allows setting permissions for member pairs.

For example, you may deny the update for the budget of 2020 but allow the update for the forecast of 2020.

Use the xls27.usp_member_relations and xls27.usp_budget_closed_periods procedures to manage such permissions.

Roles

RoleDescription
budgeting_app_accountantsThis role includes permissions for budgeting application accountants.
budgeting_app_adminsThis role includes permissions for budgeting application administrators.
budgeting_app_analystsThis role includes permissions for budgeting application analysts.
budgeting_app_developersThis role includes permissions for budgeting application developers.
budgeting_app_usersThis role includes permissions for budgeting application users.

budgeting_app_accountants

This role includes permissions for budgeting application accountants.

Assign this role to accountants.

See actual database permissions in the dbo27.xl_actions_set_role_permissions procedure.

budgeting_app_admins

This role includes permissions for budgeting application administrators.

Assign this role to users who manage business user permissions.

This role members have permission to execute permission management forms and procedures and do not have permission to execute business reports.

See actual database permissions in the dbo27.xl_actions_set_role_permissions procedure.

budgeting_app_analysts

This role includes permissions for budgeting application analysts.

Assign this role to financial analysts and officers.

This role members have full control over any business-related objects and do not have permissions to manage user permissions and change the application configuration.

See actual database permissions in the dbo27.xl_actions_set_role_permissions procedure.

budgeting_app_developers

This role includes permissions for budgeting application developers.

Assign this role to users who configure or develop the application.

See actual database permissions in the dbo27.xl_actions_set_role_permissions procedure.

budgeting_app_users

This role includes permissions for budgeting application users.

Assign this role to business users.

This role members may have additional restrictions configured at the application level.

Do not assign this role to users that are members of the roles listed above.

See actual database permissions in the dbo27.xl_actions_set_role_permissions procedure.

Schemas

SchemaDescription
dbo27This schema contains data tables and technical views and procedures of the budgeting application.
xls27This schema contains end-user views and stored procedures used in the budgeting application.

dbo27

This schema contains data tables and technical views and procedures of the budgeting application.

Administrators and developers have select permissions on views and execute permissions on stored procedures.

There is no direct access to application tables.

xls27

This schema contains end-user views and stored procedures used in the budgeting application.

Business users have access to a limited set of schema objects.

See default permissions in the dbo27.xl_actions_set_role_permissions procedure.

Contents

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.OK