I enjoy charting how our finances are doing. Seeing the totals of how our spending is going over time pleases me.
I have a spreadsheet that includes a page for credit card charges for me, and one for my wife; a page for checks we've written; and a page for withdrawals from or charges applied to our ATM account. Each page is segmented and summarized by month. When I put the entries in (usually a cut and paste from the relevant web site, except for the checks), I run an excel macro that scans for the name of the entity and either assigns a category - HOME, MEDICAL, etc - or flags it as UNCATEGORIZED. Each month, I take that month's expenses, along with the categories, and add it to a running summary, removing the corresponding month from the top of the summary. That summary feeds an excel pivot table, which charts the expenses by month and by category, giving me a total by month and a grand total.
As I say, I like it. But one thing has stumped me. I add a column to the right to say what the percentage of the month's expenses was for each category. I do the same thing for the Grand Total - what was the percentage of each category over time. I do that so I can see not only what the big hitters were each month, but also whether, over time, we're spending about the same percentage. Its nice. One thing that throws it off, though, is one time expenses that are fairly large. For example, our chart for April of last year showed thirteen thousand dollars for a one-time remodel to our bathroom. As you might expect, that threw the percentages for that category right out the window for that month, and it was big enough to throw the percentages off for the whole year. I don't know how to compensate for that other than simply pulling that charge -- and then I don't get an accurate representation of what we actually spent. I know, this is why 'real' accountants have things like Business Expense and Capital Expenses, just to keep things from skewing each other. But every month, I mull it over....
I never wanted to be an accountant -- detail irritates me, unless I've bought into the reason for it. -- but this, I like.
2 comments:
I wish I knew Excel better as I would do the same. I do chart expenses and have since we retired, but I am a month behind and I still write checks for most instead of doing it online. Must work on that. You could have a separate category for one-time major expenses.
I tried that, and the problem was that then I viewed them as 'oh, thats not going to happen again'. Which, at that level, won't, but routinely? Yeah, routine one-or-rarely charges happen all the time. One of my great epiphanies was when I realized that we ALWAYS have a substantial chunk of money going to something that did not reoccur -- taxes this month, car repair last month, etc.
I think I just need to bite the bullet and modify the 'percentage' line to not include the truly one-time events, but still leave them in the totals column, doing that each month until the massive item rolls off into history.
As for excel, I can send you details on how I do it, if you want. Its not that hard. The fun part was writing the macro to do the automatic categorization, and even that wasn't terrible.
Post a Comment