How to Set Up a Budget

[phuebs]

I want to set up a budget table as part of a databse for our church.

There are several main categories: Office Expenses, Building/Utilities, Staff, etc. etc. But the specifics aren't always known (for example, some months I'll buy paper for copying and some months I won't)

So is it best to set up a repeating field for each major category? Or are repeating categories too hard to use with calculations and all that? My inclination is to use repeating fields in that way each month and then to use summary reports to show monthly and yearly totals.

Any better or other suggestions on how to set up a very large and complicated budget?

Thanks!

[Ender]

I haven't setup such a system, but I won't let that stop me (I need to figure out my own budget anyway, so this might be useful for me )

First, stay away from repeating fields for this. They won't give you the flexibility you'll need later for building reports. Instead of repeating fields, use a portal of related records.

I'd imagine you'd have a list of items for each month, and each item would be assigned a single main category.

It sounds like you also have a set of annual expenses that are also assigned to main categories. Would it make sense to pull out the annual budget items into a separate list, or do you want those assigned to a specific month? Structurally, I'd think it'd be easier to assign things to specific months. But it's good to keep an open mind and try to think through the business processes first.

Are there different budgets for different departments, or is it one general budget?

[phuebs]

Problem is I don't completely understand how portals work yet....

I do have one large budget number for the year. It is subdivided into categories such as I suggested above. The reason I thought of repeating fields was because the individual expenses would often change from month to month. One month under Office Supplies I would list ink, toner, paper, folders. Another month I might have none of those but list CDs, a computer, and pencils.

I had in mind to have each record be a month of the year. Then in that record I would have several repeating fields for each major category of our budget.

Is there a better suggestion--explained more thoroughly--that I can try?

[Vaughan]

"I do have one large budget number for the year."

No you don't.

You have a whole heap of *Expenses* which are made on a particular date. Each Expense record should have a Description field, an Amount field, and a Date field. If you likethe Descriptions can be put into a value list so they can be later gropuped and summarised.

[comment]

I thought 'budget' was the plan, with 'expenses' being the execution steps of the plan.

I don't have much experience with this, but I think I would start by defining BudgetItems as a related table, with a record for each category (in a budget year). Each item would be marked as either 'monthly' or 'yearly', with a calculation field providing a yearly figure based on that. The aggregate sum of those should equal, more or less, the yearly budget figure.

[Lee Smith]

I don't understanding why are you wanting to do this in FileMaker. Unless you have a compelling reason that hasn't been stated here, I would look at some of the cheap accounting packages that are out there.

For what you have described so far, I think can be done in one of the Quicken products. Last time I looked, they do forecasting, and budgeting.

HTH

Lee

[phuebs]

This is going to be part of a greater church management database, so that's why I'm using FM

[phuebs]

But if I keep making every little expense a record...wouldn't I end up having hundreds, or even thousands, of records after 2 or 3 years?

[comment]

But if I keep making every little expense a record...wouldn't I end up having hundreds, or even thousands, of records after 2 or 3 years?

Yes, you will. Why do you think that poses a problem? If you want to track your expenses, you need to record them somewhere - so the amount of data will be the same, using any method. But only by breaking the data into separate records you will be able you to view and analyze the data from any aspect you want.

[phuebs]

I guess I understand. Then it would be sort of like a big business check binder anyway with all my check stubs for expenses. I was just hoping for less pages to flip through when looking for a record, but I guess that's what the Find is for right?

So how do you suggest I keep it all straight? With several fields, maybe in a drop down, that can be chosen as the category for each record or expense? Should I have just a drop down calendar for date, or should I have separate month and year fields? It shouldn't really matter when generating summary and sub summary and grand totals on reports right?

[comment]

I was just hoping for less pages to flip through when looking for a record, but I guess that's what the Find is for right?

Yes and no - you should have a good navigational system in place. Such system can have scripted finds and/or filtered portals to show only the data of interest.

So how do you suggest I keep it all straight? With several fields, maybe in a drop down, that can be chosen as the category for each record or expense?

Hold on: are we now talking about tracking individual expenses, or about budget breakdown? These are two separate things. If tracking expenses, you should have (at least) a Date field, a Category field (using the same values as the budget categories) and the Amount.

Should I have just a drop down calendar for date, or should I have separate month and year fields? It shouldn't really matter when generating summary and sub summary and grand totals on reports right?

A field of type Date is best for this. How you enter the data into it is less important. You can use a drop-down calendar or type the actual date - whatever you prefer.

[phuebs]

"Hold on: are we now talking about tracking individual expenses, or about budget breakdown? These are two separate things. If tracking expenses, you should have (at least) a Date field, a Category field (using the same values as the budget categories) and the Amount."

I meant on individual expenses, or individual records (same thing). I figured I would have a drop down category field (eg. office supplies, staff, school, promotional, etc.)

That way I could create reports based on each category if I wanted too.

I hope that helps/makes sense

[comment]

Yes, it makes sense - I just wanted to make sure we are on the same page. Your budget breakdown would be kept in a separate table. Now, if you relate your budget items to the expenses, using category and year as the matchfields, you can watch the actual spending in each category vs. the allocated figure.