Paul Matsushima

Updated Budgeting System – Interfaces, Program Budgets, and More!

By Paul Matsushima | Published on October 3, 2022

Built on Airtable.com, this ledger has various views for financial reports, including Income Statement (P&L Report), Balance Sheet (shows cash on hand), and monthly expense reports.

View Ledger on Airtable


What’s New in this Base?

This budget and ledger is an updated version of my previous base, Financial Budget & Ledger for Accounting and Bookkeeping. Here’s a couple of new features in this Base:

3 Built-In Interfaces!

Interface 1: Annual P&L Report (Income Statement) – shows how much your company has spent and earned over a specific time period.

 

Interface 2: Spending Report – shows how much your company has spent over a month (or months). This interface has a date selection filter to easily select a different month or months.

 

Interface 3: Income Report – shows how much your company has earned over a month (or months). This interface has a date selection filter to easily select a different month or months.

 

Multi-Year System

My previous base was designed to house financial transactions for one year at a time (meaning once the year was up, you had to duplicate and start a new base). This new base can house multiple fiscal years, meaning there’s more data and potential insights to see spending and earning trends over larger periods of time!

Better Monthly Insights

A new table has been added, called Monthly, which allows you to associate each transaction with a different month across multiple fiscal years. This gives better data on your monthly spending and earnings, which links up nicely with the “Spending” and “Income” interfaces mentioned above.

Program Budgets for Teammates
  • Another new feature is the addition of a new table called Program/Project Budgets. This is perfect for any budget manager of a small company whose teammates or direct reports have to manage their own program/project budgets but don’t need to know the larger company financial picture.
  • Each financial transaction can be associated with the “Program Budgets” table to see how much a program/project lead has budgeted for the year, how much they’ve spent, and how much they have remaining.

Types of Information this Database Can Include:

*Click on any of the links in the table to jump to the appropriate information below on the page.

Information Definition
Transactions Individual monetary Transactions such as purchases, payments, deposits, etc.
Contacts Individuals and sometimes associated organizations (aka Accounts).
Expense Codes 5-digit expense codes (ex: 52361 – Business Meals)
Fund-Orgs 7-digit cost-centers that house money for individual projects, funds, or departments.
Program/Project Budgets Individual program/project budgets for team members who manage their own program/project budgets but don’t need to know the larger company financial picture.
Monthly Associate each transaction with a month (across multiple fiscal years) for better insights on monthly spending and earnings.


Transactions

Definition: Individual monetary Transactions such as purchases, payments, deposits, etc.

Examples: deposits, withdrawals

Transactions show all information associated with each individual financial transaction, such as Contact (who made the transaction), date of submission, which Fund-Org it belongs to, which Expense-Code it belongs to, amount, whether it was a debit or credit, and stage.

Contacts

Definition: Individuals and sometimes associated organizations (aka Accounts).

Contacts displays an individual’s contact information (email, address, phone, etc.) and associated transactions.

Expense Codes

Definition: Expenses by line items, listed as 5-digit expense codes (ex: 52361 – Business Meals). Expense codes can be easily changed depending upon your needs.

Expense codes shows each line item, first by income and then by expenses. You can also create columns for each fund-org (included here is each fund-org’s budget, actuals, and the variance between the two). It also displays the total budgeted, actuals, and variance across all fund-orgs.

Mini-Financial Report: P&L Report (aka Income Statement)

Annual P&L Report (aka income statement, statement of profit and loss) is pre-built into the Airtable interface.

Fund Orgs

Definition: 7-digit cost-centers that house money for individual projects, funds, or departments. 7-digit codes can be easily changed based upon your needs.

Fund-orgs show a description of what the cost-center is used for, the amount of income, expenses, and cash available (variance), as well as a list of individual transactions and grant-deliverable line items.

Mini-Financial Report: Simplified Cash on Hand Report (aka how much money your company has in the bank.)


Program Budgets

Definition: Individual program/project budgets for team members who manage their own program/project budgets but don’t need to know the larger company financial picture. This is similar to how Tags work in Mint.com or other similar financial software. This allows you to classify each transaction in two ways: 1) as an “Expense-Code”, and 2) as a “Program Budget”.

Similar to how “Expense Codes” works, you can list out the various Program Budgets you have, and then add columns of budgeted, actuals, remaining, and a year-to-date percentage of what is left to either raise or spend.

Monthly Spending and Income

Definition: Associate each transaction with a month (across multiple fiscal years) for better insights on monthly spending and earnings.

The monthly expense report shows how much you have spent each month, based on different transaction categories (like by gifts, grants, earned revenue, etc.).

Learn More!

If you liked this demonstration, you can find the base I created on Airtable Universe. Click the link below!

View Ledger on Airtable

Leave a Comment

Your email address will not be published. Required fields are marked *