Table Redundency Question - Cashflow

[Kavy]

Good Day all,

I am creating a budget database. I have a table called [Transactions] which contains all of my banking transactions. It has many fields, including but not limited to: Vendor, Date, Amount, Account and BudgetAccount (BAccount).

The [BAccount] field tracks which Budget Account each transaction gets assigned to, some examples would be rent, spending money, gas, etc. Currently, each budget account has a given amount with a frequency, so my cell phone might be $50 per month, I have created a query which forecast's the amount of payments till my budget end date (all budget accounts share the same end date, the end of the budget period). So if the budget end date is Aug 31, 2011, the query shows 1 expected $50 transaction for each month till the end of Aug. The problem is, my cashflow is based off earned dollars vs spent.

My database knows which date my cell phone bill is due, and when the date passes, my cashflow will give me the earned money I budgeted for that bill, anything I spend over that amount is considered a loss. However, if I pay the bill 3 days earlier, the database knows that transaction is affiliated with the cell phone budget account, but it doesn't know it is affiliated with the cell phone bill for that SPECIFIC month, and therefore my cashflow will show a incorrect loss until the cell phone due date has passed. Once the cell phone due date has passed, the databased will adjust correctly.

I would like to change this so it doesn't reflect an incorrect loss due to an earlier payment. Originally I had a second table called 'Budgeted Transaction', where the future cell phone bill payment was placed, allowing me to tract transactions against actual budget transaction records, opposed to the query method I am currently utilizing. This created redundancy issues so I tried to place future transaction right on my transaction table and considered them [open], but this becomes a tracking nightmare.

Does anyone have any ideas of how to proceed with this issue?

Any ideas, tiny tricks to major overhauls are more than welcome!

Thank you

Kavy

 

[xenou]

It seems to me that your measurement of variances is cash flow is too tightly wired to daily transactions. Can you run your comparison as (amount spent to date) - (amount due as of the end of the current period)?

Another possibility would be to generate with each transaction entry a "fiscal period" or ("year" and "month"). Then you run your reports not against "days" but against "periods". You can actually query this way even if you don't store the fiscal period in a distinct field (I'm a bit used to having them in the tables since my work databases generate these field values automatically).

 

[Kavy]

It seems to me that your measurement of variances is cash flow is too tightly wired to daily transactions. Can you run your comparison as (amount spent to date) - (amount due as of the end of the current period)?

Another possibility would be to generate with each transaction entry a "fiscal period" or ("year" and "month"). Then you run your reports not against "days" but against "periods". You can actually query this way even if you don't store the fiscal period in a distinct field (I'm a bit used to having them in the tables since my work databases generate these field values automatically).

Xenou

Thank you for the reply. I think thats a good idea, as for the fiscal period, it actually flows nicely with how my database is set-up - I scan and upload monthly financial statements to update my database, so that could be a fiscal period.

I think my thinking has been bouncy around this idea, but I tend to over think and over complicate things, and I think you put me back on the right track.

I think this is right way to do it, my only issue I can see right now is, what if a transaction is late (and this could be me being lazy and I may need to just come up with my own solution). For example, I forecast budget transactions to occur based on fiscal frequency (i.e monthly) and start dates, what if my cell phone company charges me 4 days late and misses the cut off for the fiscal period?

Thanks for putting me back on the path!

Kavy

 

[xenou]

Cash flow and expenses follow different tracks. If you are running on a cash basis, you don't have any expense until you get billed. If you are running on an accrual basis, you can record the expense but the cash flow still doesn't (need) to show a cash outflow until the payment is made. It *might* be two separate things - one, to budget for expenses, and another, to budget for cash flows. So your phone expenses (might) be $X.xx in the month of April. But your cash flow for phone expenses is $X.xx in early May (when billed ... or even, when paid).

Are you running a cash basis or accrual basis accounting system? Is this for business or personal budgeting?

 

[Kavy]

Thank you for all your comments, I appreciate it! My database is for personal budgeting. Unfortunately, it is based off the 2 accounting courses I took in school, - i.e limited knowledge! I would like it to follow accounting rules, but it is tough!

I uploaded an interface I create with excel which my database in Access is based off showing my cash-flow and balance sheet (with fake numbers). If you got a sec and don't mind, tell me what you think!

http://tinypic.com/r/1z6dpnc/7

I am trying to create two different statements, one is cash flow where it shows pretty much how I am doing against my budget to date. I think it should only include costs have I have paid. The problem I am running into with this one is tracking costs. I have 3 types of costs, forecast ones, running ones and budget ones. The forecast ones are causing me problems - I forecast a cell phone bill to occur on a certain date, but it may come a few days earlier or later. If I pay it before the scheduled date, my database looks at it like a loss since it didn't expect it to occur yet, the query which tracks these costs only looks at costs which were suppose to have occurred. I think this is where they fiscal periods you were referring to earlier may help me and solve this issue.

The running costs are things like spending money and gas. I have a set amount over the entire budget period, and it backwards calculated the $/day in order to calculate how much I should have spent budget to date (BTD) and then cross compare against the actual.

The budget costs are things in the future, like planned trips, I don't plan on showing these on the cash-flow statement, because I am not over or under budget until I spend the money or go on the trip.

I then have a balance sheet which sums up the entire budget, including the budget costs.

 

[xenou]

I think you're getting too granular. Generally, you will budget your costs by month for ordinary expenses (rent, gas, food). Then each year you update expected costs that occur more irregularly (education, dental or vision, vacation, insurance if you pay it only semi-annually, etc). It's probably not necessary to plan things like phone bills to the day - that should be simply a monthly figure.

Once this is done, your actual expenses (which are entered daily or during the month as they occur) are rolled up to a monthly total. So at the end of the month you compare monthly actuals and monthly budgeted and that's your variance.

It may be complicated basing this on a billing date. Just lay out a grid of expenses and fill them in 12 months out - that's your budget.

You probably want to use cash basis accounting for home budgeting (this is may also be how you are taxed so it ties better to taxable income too). The only exception might be if you want to track depreciation on major items such as a car. But even this is "off the books" really as its not really a cash item - just a way to keep track of the value of an asset that is slowly losing value over the years. You also might want to accrue for expenses that are coming due in the future so it doesn't hit all at once (for instance, if a loan is due at the end of the year and you record it "as if" you are paying it off month by month so that the expense is evenly spread through the year - which is nice when it comes time to pay the bill and you don't see your "net savings" on the year suddenly drop by a few thousand dollars).

I'm just thinking out loud really - no worries if you still have more ideas to try. I was surprised to see your budget doesn't show how well you are doing in more detail (such as over/under budget on gas or on food or chewing gum). Maybe that is somewhere else?

 

[Kavy]

xenou,

Thank you for the response and sorry for the delay in my reply, I have changed careers and have been busy! Your right, I have future plans to show the over under on such things as gas and rent - that sheet is suppose to be a high level summary.

My dependency on a billing date is due to the way I upload data to the database - it converts my bank statements and uploads them. I do this to save time so I don't have to manually enter each line item. I think you hit my biggest flaw here on the head, this is what is causing me problems.

I currently have accrued expenses set up I believe through my budget accounts where I budget for things like future trips (and now car maint.).

I think I am over-thinking this, as you have laid out before. I haven't had a lot of time to dive back into it, that is probably the best approach for me to try things out and see if they work again.

Thanks for all the advice and ideas, and any future ones!

Kavy

 

[xenou]

No problems. I built a home budget last year so it's interesting to see your take on it. But I built mine primarily in Excel plus some VBA (always intending to move the data storage to Access ... some day...).

 

[Kavy]

I started this pet project to learn how to build a database, but I think Excel is definitely is the way to go for home budgeting, uploading and storing data can be harder than access, but excel is much more manageable and easier to use.

 

[xenou]

Agreed. I may also bring in the Access piece for the experience of a new project ... but maybe not. It is working tremendously well now all in Excel.