Re: Accounting P&L in filemaker

In article <2006021807035516807%MRevenaugh@aolcom>,
Matthew Revenaugh <MRevenaugh@xxxxxxx> wrote:

I have a dilema/enigma. I am using FM to develop an accounting solution
for a client. I've been using FM since version 1.0 and have full
confidence in creating anything that comes accross my desk. Until I
started to do the Profit and Loss portion of the accounting database.

Here's an over simplified setup. I have a Journal table that contains
the following fields:

Date
Distribution Account Number
Account Type (Credit/Debit/Taxable)
Credit Amount
Debit Amount

To simplify, on a Profit and Loss, you summarize all the credits and
then all the debits in separate sections. Then you calculate a Gross
Profit (credits-debits). Then you summarize all taxable entries, and
calculate a Net Profit (gross profit-taxable).

Sounds straight forward, but I can't get FM to do it.

I sort by Account Type, that lets me summarize the credits, then the
debits. Now I need to show a Gross Profit. Then continue with the
Taxable accounts and summarize. Finishing off with a Net Profit.

Here is a brief layout:

---
My Profit & Loss
---Header---

[Account Number] $[Total of Amount]
---Sub-Summary (By Account Number) ---

[Account Type] $[Total of Amount]
---Sub-summary (By Account Type) ---

$[Net Profit Amount]
---Title Page Footer---

---


As you can see, there is no way to break the report after an arbitrary
number of Account Types, and print the Gross Profit, that should only
appear once on the report not after each sub-summary.

I guess there may be some really ugly ways of getting what I need, but
I'm looking for something elegant (as usual).

Any ideas or experience with how other solutions may have done it. I've
searched the web for other FM based accounting solutions. Lots of
theme, None include P&L. Suprised?

The Cash Flow report is also in the works, but it breaks and totals
easily with FM,


Matt Revenaugh
The Software Man

email: matt (at) thesoftwareman.com

You need to set up your solution to use standard double-entry
bookkeeping. I think you are not doing that.

Standard account types should be Asset, Liability, Income, Expense, and
Equity.

The income (P&L) statement should be a layout in the Journal table.
Parts should inclue subsummary when sorted by Account Name and
subsummary when sorted by Account Type, and a Trailing Grand Summary.

Fields in the parts should be:

In the subsummary by name: Account name, Summary amount

In the subsummary by type: Type, summary amount

In the Trailing Grand Summary: summary NetCredit

The calculation of the Amount for each journal record would depend on
the type of account: Debit for Asset and Expense accounts, Credit for
Liability and Income accounts.

NetCredit in each Journal record is Credit - Debit

to generate the Income statement, do a find for Journal records where
Account::Type = Income or Expense, and the transaction:Date is within
the desired range. Then sort first by Account::Type and second by
Account::Name. Do the sort by type based on a value list that lists
Income before Expense.

Now your Income statement should work.

To do a Balance ***, use the Accounts table. Set up a somewhat similar
layout. Do a Find for Asset, Liability and Equity accounts, and sort by
Type, using a value list that shows Asset, Liability and Equity in that
order.

Variations and refinements are certainly possible, but that is the basic
idea.

The foregoing is based on using three tables: Account, Transaction and
Journal. Journal is related to Transaction by TransactionID, and to
Account by AccountID. Transaction and Account are related directly to
Journal but not directly to each other.

Transaction records store date of transaction, identifying documents,
payee/payor, memo, and sums of related credits and debits from the
Journal table. it is also useful to have a calculated transaction
balance warning that shows unless Sum Debit = Sum Credit, to help assure
debits and credits balance on each transaction.

Journal records store the related TransactionID and AccountID, debit and
credit numbers, reporting amounts which are calculated based on the type
of related account, and summaries of debit, Credit and Amount, for use
in reporting.

Account records store the Account ID, Name, Type, other information of
interest, and sums of debits and credits from the related Journal
records.

Addition of Cleared and Cleared Amount fields to the Journal and of a
Sum Cleared Amount files to Account allows reconciliation to bank
statements.

A layout of Transaction should have a portal to Journal to allow
creation of Journal records when you make a new transaction.

A layout of Account should have a portal to related Journal records to
show the condition of the account and to allow bank statement
reconciliation.

Let me know if you want more details.

Bill
.

Loading