Daily budget vs expenses

What is the goal of your workflow? What problem does it solve, or how does it help you?

This workbook provides a detailed, transparent comparison between monthly spending and income in an intuitive interface. The tool enables the user to make precise financial decisions when they are faced with tight margins or limited budget. It answers the following questions:

  • When all recurring bills are paid, how much is available to spend each day to break even by the end of the month?
  • What is my margin between spending and income TODAY?
  • How do I know when all the bills, savings, expenses, income and overall balancing tasks are complete each month?

The goal of this worksheet is to obviously answer the above questions in a simple format without getting lost in the details, while still allowing the user to investigate deeper whenever they wish. This spreadsheet helps me to just that, and I am confident it will continue to regardless of my income level. Rest assured, this template has been thoroughly tested. It represents literally decades of evolution; I improved on the proven essential functions that allow me to make decisions, and I cut the clutter and “features” that were not helpful.

How did you come up with the idea for your workflow?

It started from manually balancing my checkbook at age 15. As a teenager I struggled to walk the line between limited income and big dreams. I wanted to buy my first car and do many other things, but my lack of knowledge of my own finances led to overly conservative spending. I didn’t know what I really had available to spend, because looking at a checking account balance didn’t tell the whole story.

Most budgeting tools out there were not useful to me, and to this day they are still not. They were full of features, dashboards and details that looked great but had no impact on my decision-making ability, which was the whole point. Tracking expenses against envelope categories like “groceries” was not important to me, nor was knowing my financial health at the end of the month, when it was too late to do anything about it. I needed financial transparency and simplicity, showing me the relevant details while they were happening. The data was there, it just needed the right lens.

I hopped on excel with a pile of receipts and plotted my expenses cumulatively on a graph that spanned one month. I also plotted my income in a straight line, starting at near zero on day 1 and ending at my monthly income on the last day. I subtracted out my known recurring expenses from both plots. Now I was able to directly compare daily variable expenses vs net daily budget, with the goal to keep the cumulative expenses below the budget line. If it was trending badly, it would be obvious and I could look at the cause as well as what actions I needed to take. By the end of the month, any difference that remained was made up with a final adjustment to my savings account transfer, assuring that the month would break even. That last step proved to be a great buffer against financial volatility. On bad months I put less away, on good months I put away more. But the key is it always balances out and the red and blue lines meet at a singular point at the end of the month.

Back in the excel days, it was tedious to get this tool to work. Tiller’s ability to automagically import transactions into a spreadsheet was a game changer. I no longer have to bug my wife to hang on to her receipts! That automation inspired me to further streamline the rest of my workflow, and the result is the template you see here. Things like data plotting, budgeting of bills that recur at non-monthly intervals, and refreshing the overall page based on the month (instead of a separate tab for every month) really made this a tool that I not only tolerate using, I enjoy it!

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?

The first sheet is “Transactions”. Most Tiller users will be familiar with this tab, as well as the Categories tab. I chose categories for each recurring bill and paycheck, and I also have categories for reimbursable, tax deductible and “everything else”, which includes daily expenses and unexpected income.

The second sheet is “Month” and this is my creation. The first thing you see is a graph and a cell above it that says “Month starting”. You enter the first day of the month in this cell, which sets the stage for the rest of the sheet. Once you do that, filtered data is fed in from the transactions sheet, such that only that month’s transactions are displayed. This starts at row 65. Above this data is a category section which displays all my categories (recurring transactions) along with expected and actual amounts for that month. If I haven’t paid my mortgage yet, I have a placeholder in there for an approximate amount, and once the payment shows up that data gets updated with actual. If it recurs every other month, I divide those figures by 2; once a year, I divide by 12, and so on. Cell colors change so the user has a visual cue if a bill was paid. This is accomplished through a category lookup function. Paychecks are handled the same way, just with a positive instead of a negative.

All these categories get added up and result in a remaining budget or “net budget” in cell H43. This number is always smaller than you want it to be (such is life), but it represents the money available for day-to-day expenses over the course of a month.

Moving up the page, we finally get to the graph and the source data. The x axis is the date of the month. The y axis is dollars. The red line is the net budget expressed as a daily accrual. The blue line comes from totalized transactions that are categorized as tax deductible or “everything else”, in other words, transactions that aren’t already accounted for in the net budget. Blue and red are plotted together on the graph, showing how variable expenses compare with daily budget. Column J gives the user an exact amount that the red line is above the blue line on a given day; positive values are good! Column K gives the user an average daily spending amount they need to attain if they are to break even at the end of the month.

In essence, the Month sheet starts with the top level picture and as you scroll down it gets more and more detailed. So if you have a small screen or just want to see a quick snapshot, it’s front and center and easy to find. But if you want to poke at details, like what caused that spike on July 12th, you can scroll down to July 12th and see those transactions line by line. It’s all there one one page. And if you REALLY want to see what you spent on groceries or entertainment, you can create a column of sub categories and filter them to your liking.

Anything else you’d like people to know?

I added a convenient refresh link at the top of the Month tab so I can log onto my Tiller dashboard and refresh one of my credit card accounts. I guess there’s some authentication problem that requires me to manually key in a code from a text message. I’m told Tiller’s data handler is working on this issue.

Is it ok for others to copy, use, and modify your workflow?

Of course! See that template below. I fabricated the transactions as an example, but feel free to paste in your own.

If you said yes above, please make a copy of your workflow and share the copy’s URL:

https://docs.google.com/spreadsheets/d/108J4cu5PNOOc86qgBdX__aWhok2rMNSI-zYJszvY_9Y/template/preview

This is exploding my brain! It is like having a magic eight ball that is always correct.
Q: Should I get Starbucks?
A: Yes-treat yourself

Q: Should I buy those new shoes?
A: Try Again Later

Q: Should I buy concert tickets?
A: Not if you are saving for a vacation!

MAGIC!