How to Make a Quarterly Debt/Expense Report in Excel
Alright, I told you on Friday that I would be showing you how I made a quarterly report in excel. This quarterly report allows me to compare the three months in the quarter side by side and then see the overall difference of debt from the beginning of the quarter to the end of the quarter. You might be wondering why would I want such a thing and why would I want to subject myself to additional work, after all we created an expense sheet, that should be enough right?
With the expense sheet, I am only focusing on a single month. When I am in that month, I totally agree that is what I should be doing, however, I do want to see what progress I have made. These quarterly reports are exactly what I need as a means of encouragement. It also helps me see the full progress of my elimination of debt, whereas only the expense sheet shows for what and where I spent my money. Now don’t get me wrong, the expense sheet is still a necessity, and it becomes an easy way to create this is to use your Expense sheet that we created awhile ago.
First of all, you want to make sure that you have a complete list of the items that you will be recording. This includes every single piece of your debt. Your automobile(s), credit card(s), student loan(s), as well as the other expenses. Now you can make this as detailed as you want, but I broke it down into the simplest of categories: Auto, Credit, & Student Loan. I then would see how much Total Debt and Total Payments I had for that month before I even touched the other expenses. From there I wanted to also compare how much in other expenses (i.e. gasoline, medical, etc..) I had spent. Then it was Total Expenses, Total Income, and the hopeful Leftovers that would be in my savings and/or checking account.
- In Column A, you will be listing the major categories. So Debt would be the header, then Auto, Credit, Student Loan, Total Debt, Total Payments, Other Expenses, Total Expenses, Total Income, & Leftovers (Savings).
- Column B will be the specifics. This is where I broke the categories down further. For example, I was specific in my Auto make and model, as there may come a time where there are two cars for the family in the future. I also listed each credit card, student loan, various expenses.. etc..
- If you want to tidy this up like I did, I made my titles bold and merged the titles A Column with the B Column. Since there would be no information placed in the title cells, it just allowed me to shorten the width of the A column and not waste so much space.
- Next, widen your three columns the width that you wish them to be. I ended up making them three columns wide as I like having all that space and I’m a bit OCD when it comes to aesthetics. Because of this, I even added very thin columns in between B & C, C & D, and D & E and dyed the background of them a bright orange just to make the comparison factor that much more evident.
At this point, your excel sheet should look something like this:
- Put in your numbers, leaving Total Debt and Leftovers (Savings) blank. The rest, you can easily grab from your expense sheet (even the Total Expenses and Total Income as we already had those off to the right of the previous sheet we created).
- For Total Debt, highlight the box and insert =sum( and then highlight the cells above it from auto down to the last student loan. Press enter and your total debt should now be calculated. Follow this method for each month.
- For Leftovers (Savings), all you have to do is click the corresponding box, insert = Column Letter Total Income – Total Expenses. In this case, it would be =D28 – D27. Repeat for the other months.
Alright, now you might be wondering what exactly are those colorful boxes to the very right. Well, much like I did in the expense sheet, I wanted to see the total debt that I had eliminated, my total income for the quarter, total expenses for the quarter, and finally, the total savings that I hopefully have somewhere in between my checking and savings accounts. These are pretty simple to create as well.
- Again, I made things pretty. I like to see my final numbers in a bigger font, so I merged two rows and five columns together for each category. I also filled the background of these merged cells with color to make it pop better. Red = debt, light green = income, blue = expense, and a darker green = savings. Seemed appropriate for me, but if that doesn’t work for you… well you obviously don’t have to be as picky as me.
- For the change of debt section, this is easy. You insert = 1st month’s Total Debt – 3rd month’s Total Debt. This will show you how much you have eliminated over the course of the quarter.
- Total Income is just the sum of the three month’s incomes so =SUM(D,F,&H Total Income). In this case =SUM(D27+F27+H27).
- Total Expenses is the same as Total Income, only you use the cells labelled for the total expenses of each month.
- Finally, you do the summation formula one more time and add up the total leftovers (savings) for the quarter.
Like I said, real simple. I’m sure there is also a simpler way to put in expenses from the expense sheet in just a simple formula, but I’m not that great at importing from one completely different book to the other. Besides, it also allows me to refresh and reflect on just what I did over that month, so I’m not complaining much.
At the end of the year’s 4th quarter for 2013, I will be adding a block to see the comparison of debt, income, expenses, and leftovers from 2012, and 2013. This will allow me to further see my overall progress on a broader scale.
Hope this inspires you to organize your expenses and debt elimination just a little bit more!
Count those Raindrops!