Income/Bill Forecasting

Currently I have a Spreadsheet that contains the normal bills I have plus income and lets me know how much I have. Part of this is to tell me how much I can put into my credit card bill for now. I use my credit card for everything. It lets me be even more granular in seeing my trends in purchases and savings. At the moment, I’m tapped and pay everything other than a couple of hundred into the credit card bill each month.

Since the utilities (water, gas, electric, cable) are generally pretty stable and the other bills pretty consistent, there are only a few things now and then that pop up such as yearly vehicle insurance, mortgage payments, county taxes, and some misc stuff. So I can reasonably plan out the upcoming months and even year through this spreadsheet.

The spreadsheet is broken out into 2 week periods coinciding with payday. The header shows every monthly bill title plus the due date. The last line is what’s pending for that two week period. As bills are paid, they are moved out of the pending line and into the two week period. There’s a calculation on the left for that period that gives the Pending total and a second number that’s the final amount left in checking plus the previous 2 weeks’ final amount. This lets me put the regular bills into their slots and for the credit card statement, I can put the remaining in less between $200 and $300. Since it’s a forecasting spreadsheet, I can look ahead months and make sure I’m not over extending later and decrease the amount going to the credit card.

    Date Income Mortgage Auto Electrical Water Cable Gas Credit
    Dec 31 $2,000              
    Jan 1                
    Jan 2                
    Jan 3                
    Jan 4                
    Jan 5                
    Jan 6                
    Jan 7                
    Jan 8                
    Jan 9                
    Jan 10                
  -$1,835 Jan 11                
  $165 Jan 12                
    Jan 13                
    Pending   -$1,000 -$300 -$100 -$30 -$95 -$10 -$300

It sounds complicated but once it’s all working correctly, it’s a reasonably quick process. I just move the pending items up and the calculations are done.

One of the problems though is if I have to add some extra stuff for a block of time. I have to adjust the spreadsheet calculations and if something changes, it tends to muck with the layout.

So I’m rewriting it into a database. This lets me more easily manage the periods plus the ability to add items easily and know they’ll be included.

The requirements: Item Name, Amount, Start Date, Due Date, End Date, Recurring/Single Payment, Autofill

I create a standard form edit and display area. This gives me the data entry point for items. Under the Edit Form display area, I have a link on each recurring item that changes the start date of a recurring item to next month, then loads up the data into the form resetting the recurring portion to single payment.

A second page just takes the data, creates the bi-weekly blocks with the headers and pending amounts. Single Payments are noted. Recurring payments are extrapolated forward including the totals as listed above. So it’ll look similar to the above table but with an easier management of the data.

I have the Edit Form completed and am adding in the data (off to bed shortly). Once the data is in place, I’ll work on the second display page.

This entry was posted in About Carl, Computers. Bookmark the permalink.

Leave a Reply

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