Creating a Spreadsheet Spending Projection
June 10, 2011 Off the Top of My Head No CommentsEach month I track my spending very closely in a spreadsheet I’ve created. Over 5 years of spending I have organized different categories of spending so that I may view where savings may be discovered as well as simply having an understanding of where most of my money goes to. As a month of spending progresses I review the ratio of spending to income (simply referred to as my “ratio”) to get a quick understanding of if I spent more than I made or if, overall, I saved money that month. The ratio has become my most important quantity in my Spending Spreadsheet and after a while I began coming up with a formula that would give me a projected “end of month” ratio. The following formula is what I use to calculate out my end-of-month spending and thus able to project the monthly ratio:
Avg. Day Spending * Days Left in Month + Current Month Spending = Projected Spending
This is a simple approach but the “Avg. Day Spending” has a specific definition. The “Avg. Day Spending” is the amount I spend per day but excludes any monthly overhead such as Rent, Utilities or Insurance. This is important since the overhead costs are generally static month-to-month and allows me to see where extraneous monthly spending occurs.
I am always interested in trying to more accurately preview my end of month spending and I have attempted several other algorithms in the past with varying accuracy. My current approach I like the most since it is dependent only on the current month spending and does not include any previous month averages. I find that previous month averages vary too much for them to properly predict the current month. Some people would likely disagree.