Mixing Maroons - Spend Less, Save More … Get Marooned!

Mrs. Maroon

March 26, 2015

Excel is My Friend – A Peek at My Spreadsheet

Tags: , , , , , ,

Most PF bloggers junkies are self-proclaimed Excel nerds… J$ even coined an acronym for it – SFL (spreadsheets for life). We are no different. Being engineers, the adults in the Maroon household were Excel nerds before we ever even heard the term financial independence. Hell, as our first attempt to combine finances – before we got married even – we agreed on the allocation of money in our budget. Instead, we fought over how to organize the spreadsheet to keep up with it! In the end, I won that battle discussion and now over seven years after that first conversation, I still keep track of everything.

 

Seeing as how tracking your expenses is one of the very best things you can do to take control of your finances, I wanted to share with you how I keep up with ours. Having talked about spreadsheets with Mrs. SSC over at Slowly Sipping Coffee before, we have teamed up to share this greatness with some of our readers. A couple other folks we would have asked to join us in this exercise, namely Debtless in Texas and Tawcan, just recently shared their own Excel posts. So today, Mrs. SSC and I are going to show you how the ladies like to use Excel…

 

First, I will say that everyone has their own style in Excel. To this day, Mr. Maroon and I still create those works of art in very different ways. Most of my preferences came from my very favorite project in my professional career. For nearly a year, I used Excel every day to complete countless calculations in a large project using a series of 20 or so interconnected spreadsheets for 500 individual units. I’d do that project again in a heartbeat!! That being said, here are some of the things I adopted from my experience…

  1. Set up your spreadsheet so that a number has to be entered ONE time and one time only. No matter how diligent you think you may be, at some point you will forget to update the same number in three different places. And now your spreadsheet is worthless 🙁
  2. A piggy back to #1 is to link cells to each other and use formulas in every possible instance. If you will take a little extra time from the beginning to set it up, you will make life so easy for yourself by automating all of your calculations (and no, I don’t mean macros even).
  3. Now that you’re totally on board with formulas and linking cells, how do you make sure you don’t break those links? Use COLORS! In the pictures below, you’ll see a rainbow of colors. My particular convention has always been to use blue for linked cells and green for calculations. Any cell that I’m allowed to type in remains black. Therefore I have rules – If the cell is not black, you better not be touching it!! I also like conditional formatting to make values turn red when they go the wrong direction.

 

And since I’ve laid the groundwork and teased you without showing any pictures at all, let’s dive into the spreadsheet itself. For this example, I’m using our real spreadsheet and budget, though I did alter our salaries (up of course!).

All of the pictures can be seen at full size if you click on them.

 

The main tab I use is similar to a dashboard for the month. From here, I can see what we budgeted for each category (but don’t change it here – see it’s blue!). I also enter the transactions, sum up the transactions by category, and determine if we are over or under the budget we set (red dollar signs are always bad). Each month has an individual tab.

Two of the formulas I use the most are VLOOKUP and SUMIF, specifically related to my categories. To make sure that I don’t make a typo in entering that category (which then means it doesn’t get tallied) the categories are selected from the drop down menu created from the list on the right-hand side.

Finally, as cliché as it sounds, a picture is worth a thousand words (numbers maybe??), so the pie chart for the spending categories is right there. January was such a beautiful chart, thanks to the profits from our semi-investment property – a PacMan as Mrs. SSC even pointed out!

2015.03.24-1

While the dashboard may be the tab I use the most frequently, for us the most important tab is the Summary where we keep the budget. I find it very useful to see the budgeted amounts for each month all in one place. Not every month is the same. For us, four months of the year have five Mondays – which means five weeks of day care. Fortunately two months each year also get three paychecks! Also, Maroon Mutt doesn’t need food every month, but does go to the vet once per year. By creating our monthly budget all in one place, we can see those differences easily.

Like I said before, this is our actual budget – except for the income side. Similarly, I got to budget the Endowment contributions differently from our current budget based on that increased income. We are going to max out both of our 401(k) accounts this year, deducted directly from our paychecks, plus Roth IRA accounts. To get a feel for how much we will have to send to the Endowment, I like to allocate whatever is leftover, leaving ~$100 balance each month. Boy I wish those were the real salaries!!

2015.03.24-2

Because I am compulsive about watching our successes (and hopefully not too many failures), I also have a way to compare the actual spending from every month to each other. Since we are still so early in the year, I haven’t really gotten to appreciate the value here too much yet. But I am eager to watch for the anomalies in spending so that we are better prepared for budgeting our post-FIRE expenses.

2015.03.24-3

It’s no secret that I was determined to reduce grocery spending after the last several months of 2014, so my Type A personality wanted to know where it was all going. What better way to know than to track it! I admit this tab is a bit OCD, but I will keep it for now. I like that I’ll be able to compare the spending month over month.

2015.03.24-4

We like so much detail that we decided to break down the components of each paycheck this year. In the past, we’ve only documented the take-home pay. But that’s no fun when you are really keeping nearly $700 in your 401(k) from each check. So, Mr. Maroon dived into the tax code to figure out our withholdings based on all of our deductions. Wow, wouldn’t those salaries be good!

2015.03.24-5

We also keep track of the insurance and taxes from each paycheck. This tab is pretty boring since it doesn’t change. But we have it, so I’m showing it to you!

2015.03.24-6

Since we are also now determined to rid ourselves of our mortgage before we reach FIRE, we needed a way to figure that part out too. We created our own amortization table. About a month ago, we got smart and decided that refinancing to a lower rate would be a huge bonus towards the goal, so now we have TWO amortization tables! One more perk of doing the refi was getting the house appraised. In only ten months, the value increased by $11,000. Our net worth appreciates it! (pun intended)

2015.03.24-7

Speaking of net worth, one of the main reasons for all of this effort is to make spending choices that allow us to steadily increase both our net worth and our Endowment for retirement income. No spreadsheet would be complete without documenting those numbers, along with a pretty graph. We always want to see this one going up, though Mr. Market may deal us some downs too. But that’s okay – we’re in it for the long haul.

2015.03.24-8

If you’re still with me, congrats! We’ve made it to the end. You now have had an in depth look at the way I choose to track every last cent we spend, plus a whole lot more. Hope you’ve enjoyed it. If you are interested in getting your own copy of my masterpiece, leave a note below or shoot me an email at mrs(at)mixingmaroons(dot)com. Make sure you go check out what Mrs. SSC has shared with us too!!

 

Finally, I must admit that I have a kick-ass setup to be doing all of this work. After convincing a new job (I started one year ago Tuesday!) to let me work from home when we moved to Oklahoma, they outfitted me with a workstation just like I would have enjoyed in the office. Pretty awesome to have just a couple steps from my kitchen!

2015.03.24-9

 

Do you have any favorite Excel tips and tricks you’d like to share?

 

Update: I understand that I can share the file for download through here… Just remember the color conventions. Enjoy!!

MixingMaroonsBudget

~ Mrs. Maroon

 

Like Bills Through the Breeze... This is the Money of Our Lives
February 2015 Update
  1. Comment by Debtless in Texas — March 26, 2015 @ 6:49 AM

    I LOVE IT. Thank you for sharing, your spreadsheet is beautifully detailed and I will be emailing you for a copy of your masterpiece!

    VLOOKUP is one of my favorite functions from my previous life in PPC, probably the most useful function in excel…well that and the pivot table.

    Thanks for sharing and spreading the excel nerdiness!!!!

  2. Comment by Christina @ Embracing Simple — March 26, 2015 @ 9:01 AM

    I love how detailed your spreadsheet is! I love spreadsheets as well, I’m also a bit OCD and totally Type A, so I appreciate how structured this all is.

    I just recently created a spreadsheet for our expenses and am especially intrigued by seeing where our grocery money goes….I created a separate category for “junk food and alcohol” to see exactly how much we are spending on non-essentials like cookies and chips. Should be interesting!
    Christina @ Embracing Simple recently posted…Simplified Cleaning Series: Green & DIY Cleaning ProductsMy Profile

  3. Pingback by Our FIRE calculator – Excel style! | Slowly Sipping Coffee — March 26, 2015 @ 9:39 AM

    […] show off some of the fun budgeting calculations we do with Excel. So, please click on over to the Mixing Maroons to check out how she utilizes […]

  4. Comment by Robin @ The Thrifty Peach — March 26, 2015 @ 9:44 AM

    I love the net worth pun! 😉
    I could really use an excel crash course again. I haven’t used it much since college, and I’m ashamed.
    I love how detailed you guys are! It makes everything you spend intentional.
    Robin @ The Thrifty Peach recently posted…My Dad’s Last $50 – I Need Your AdviceMy Profile

  5. Comment by Mrs SSC — March 26, 2015 @ 10:15 AM

    Wow! I am QUITE impressed – it truly is a work of art! I think it is really interesting to see how everyone has a different focus. I think that is the best aspect of using Excel – is it is fully customizable!
    Mrs SSC recently posted…Our FIRE calculator – Excel style!My Profile

  6. Comment by Frugal Buckeye — March 26, 2015 @ 12:59 PM

    Very impressive. As a data nerd and excel lover I can appreciate the work that went in to making these. I had my own excel spreadsheets for our investments a while back, with vlookups for the different asset classes and accounts. I’ve since transferred all of that monitoring to personcapital and use a less complex spreadsheet to tally things up once a month for net worth tracking.

  7. Comment by Tawcan — March 26, 2015 @ 1:03 PM

    Wow your spreadsheet is way more detailed than what we use. Very impressive. Maybe I need to put on my Excel nerd hat on and tweak our Excel sheet a bit.
    Tawcan recently posted…Financial advice for a debt-ridden Vancouver coupleMy Profile

  8. Comment by Gen Y Finance Guy — March 26, 2015 @ 1:23 PM

    Nice work!

    My tips:

    For VLOOKUP try using the match function instead of identifying the column, it makes the formula way more dynamic.

    Try SUMIFS when you have more than one criteria you need to define what you want added up.

    I work in excel every day for both my professional and personal life. I may have to share one of my own spreadsheets. I am actually thinking about giving it away my financial stats and report summary template as a gift for folks that join my email list.

    Here is something you might enjoy as a fellow Excel Nerd:

    A Functional IMAGINARY Tale

    I’ve been sleeping on a COT for a MONTH NOW because I don’t have any CLEAN SHEETS LEFT. So TODAY I went looking for SHEETS — SUMPRODUCT that offers REAL VALUE for the DOLLAR. The MEDIAN cost varies in different AREAS, so I got on the Internet AND did a SEARCH to LOOKUP SHEETS. There were many to CHOOSE from: ROWS of every TYPE of SHEET you can think of, EVEN in the MID PRICE range. I thought it would take DAYS, but I got lots of INFO in a MINUTE. Excellent WEBSERVICE.

    It wasn’t a WORKDAY, so I decided to visit a local store. I found the ADDRESS AND saw their SIGN. Shopping isn’t something I do with any FREQUENCY, but SUM times, it seems like the RIGHT thing to do.

    I found some SHEETS and told the clerk, LEN FISHER, that I could FIND a LOWER price online. He looked at the CEILING, sighed, and said, “NA, I can’t MATCH it ACOS I’m NOT the manager AND don’t have the PROPER POWER OR RANK. I must CELL it at the EXACT PRICE that’s listed, not a DOLLAR LOWER — AND that’s a TRUE FACT. Can you SUBSTITUTE something else OR must it be the EXACT PRODUCT? Perhaps some rat POISSON? OR a slice of PI?”

    “IRR . . . that makes no sense. Are you for real?” I asked.

    “Yes, sir, IMREAL. Hold on a SEC AND I’ll send a TEXT message using our secret CODE AND try to ROUNDUP the FLOOR manager.”

    “BAHTTEXT!” I wondered: ISTEXT the best method of contacting him?

    Apparently so, because the store manager appeared within a SECOND. He was just ROMAN ROUND, checking out the store. He was a TRIM man with LARGE ABS, a dark TAN, AND a moustache on his UPPER lip. The TYPE who likes to look in the MIRR. Nothing special about his eyes, though. Just STANDARDIZE.

    “My name is MAX T. PEARSON. Is there SUM way I can help you TODAY?” he asked with a smile.

    I showed him the SHEETS AND said, “Hello, I’m KURT N. BESSELY. I just want to buy DPRODUCT, AND your employee won’t YIELD on the PRICE.”

    The manager said, “To you, that ISODD AND probably seems off-BASE, but it ISLOGICAL to me because of our store policies. Be assured, however, that you can COUNT on me. I won’t waste your TIME with FALSE promises, AND I have CONFIDENCE I can help. Buying SHEETS shouldn’t be that COMPLEX, AND I want you to be satisfied to DMAX. Wanting a discount isn’t a SIN. IF you’d like a LOWER PRICE, just ASC.”

    “IF you do me a SMALL favor AND LOWER the PRICE by five PERCENTILE buy the SHEETS,” I offered.

    “No PROB,” he replied.

    Net EFFECT? I give MAXA lot of credit for a better than AVERAGE shopping experience. He FIXED the problem, AND the store has a CONVERT. I spent less than an HOUR shopping, RECEIVED good service, AND I RATE them highly. I’ll be back during their end-of-year sale that runs from OCT2DEC.

    Best of all, I was able to REPLACE my SHEETS, AND I don’t use ACOT. When the FORECAST calls for a drop of 30 DEGREES, I like to be under COVAR with a LOG burning in the fireplace for the DURATION.

    http://spreadsheetpage.com/index.php/blog
    Gen Y Finance Guy recently posted…Anatomy of a Side Hustle: How I made $18,000 at an effective rate of $100/hourMy Profile

  9. Comment by ARBM — March 26, 2015 @ 1:28 PM

    This is great! I am very much an excel nerd myself, but I obviously need to work on my budget spreadsheet. I started quite basic in January (my first budget), but have started adding in some more features as I work through the process and figure out how to use it best. Your spreadsheet has provided me with great inspiration… guess what I’m going to be doing on the weekend!!!
    ARBM recently posted…Decluttering Week #8 – Purses & BagsMy Profile

  10. Comment by J. Money — March 26, 2015 @ 1:40 PM

    Hah! You are hardcore!!

    SFL, baby!

  11. Comment by Vawt — March 26, 2015 @ 2:19 PM

    Don’t forget you can also lock the cells to avoid the formulas being overwritten.

    I prefer to actually separate the final product from the data entry. It results in a cleaner look and less chances to overwrite formulas. It also makes it a breeze to update it to a new year. This involves array formulas, but once set-up take no effort to maintain.
    Vawt recently posted…February UpdateMy Profile

  12. Comment by Taylor Lee @ Engineer Cents — March 26, 2015 @ 4:22 PM

    Don’t really have many spreadsheet tips (don’t use them often) but I am impressed by your thoroughness!
    Taylor Lee @ Engineer Cents recently posted…10 Things I’ve Learned While Attempting To Buy A HouseMy Profile

  13. Comment by Leigh — March 27, 2015 @ 9:09 AM

    I have some very similar spreadsheets to you 🙂 I use the monthly savings amounts to plug into my net worth spreadsheet and forecast what it’ll look like, when the mortgage will be paid off, etc. I have a spreadsheet dedicated to income like yours, but one worksheet per year and then a few to sum up the interest and dividends and then I use that to calculate how many withholdings I should claim and what my tax liability should be. The savings worksheet is directly linked to the budget worjsheet, so if I reduce or increase the budget, I can see directly the impact on the mortgage payoff date.
    Leigh recently posted…Frontloading my 401(k)My Profile

  14. Comment by Fervent Finance — March 27, 2015 @ 11:31 AM

    I’ve never been a fan of a budget for myself, because it is just me I am responsible for. I am also not a spender, so for the most part I’m spending my money on food and some utilities and that’s about it (unless I’m traveling). Whatever is leftover after I pay the bills is what gets deposited to Roth IRA or post-tax brokerage account. I also utilize Personal Capital which has all my accounts linked that I keep an eye on. It is addictive to watch your net worth grow, and is nice to see my cash inflows and outflows. Thanks for sharing your spreadsheets!
    Fervent Finance recently posted…Dream JobMy Profile

  15. Comment by Mrs. Frugalwoods — March 29, 2015 @ 6:41 AM

    Wow, those are some detailed spreadsheets! I’m impressed! Our future homestead projection spreadsheets are very detailed (formulas everywhere!), but we don’t track our grocery expenses as rigorously as you. We just pull all expenses at the end of the month, but I don’t subdivide out the food groups. You are super organized–nice!
    Mrs. Frugalwoods recently posted…Weekly Woot & Grumble: Guests We’d Never Met (Plus A Flat)My Profile

  16. Comment by Elroy — March 30, 2015 @ 1:53 PM

    The best excel tip is to learn to use named references (cells and ranges). Then dynamic ranges. That, and learning how to setup the spreadsheet correctly from the get go.
    Elroy recently posted…GritMy Profile

  17. Comment by Jon @ Money Smart Guides — April 1, 2015 @ 7:26 AM

    I’m an excel junkie too and I LOVE this post. I am downloading the spreadsheet to take a closer look.

    The one thing I’ve learned and you mentioned it, is to make sure you LINK whenever possible. I worked as a tax accountant and in my first year I was getting frustrated because I could never remember where certain numbers where coming from. I wasted so much time trying to find them. Once I stated linking, it make life a breeze – ok, not a breeze but much easier. I now ALWAYS link when I can.
    Jon @ Money Smart Guides recently posted…What To Look For In Mutual FundsMy Profile

  18. Comment by Stefanie @ The Broke and Beautiful Life — April 1, 2015 @ 9:09 AM

    Nice spreadshseet! Formulas are the best for simplifying for sure
    Stefanie @ The Broke and Beautiful Life recently posted…Financial Freedom : It’s Not Just RetirementMy Profile

  19. Comment by Even Steven — April 2, 2015 @ 9:00 AM

    So many things I like about your spreadsheet, I would love to take a look if you could send it my way that would be great.
    Even Steven recently posted…Digit Saving for Paying off Student LoansMy Profile

  20. Pingback by 9 Free Excel Spreadsheet Templates - MoneySmartGuides.com — April 8, 2015 @ 6:36 AM

    […] Mrs. Maroon recently posted how much she is an Excel geek, and I loved reading every last word. She created this in-depth excel budget spreadsheet from scratch and being a fellow Excel nerd, had to check it out. It is definitely amazing. For some it might be overkill, but I still recommend it as you can just use the parts that make sense now. In time, when you want to track more things, you can keep using the same template, since it has the added features. […]

  21. Comment by Sarah Lusby — June 10, 2015 @ 8:34 PM

    You, Mrs Maroon, are an Excel legend! Thanks for the actual spreadsheet 🙂

  22. Pingback by Discombobulated « Mixing Maroons — August 5, 2015 @ 8:38 PM

    […] But (using but is always such a downer), without regular updates shared here, I wasn’t tracking our expenses and tabulating how we spent every penny. That looseness snowballed into a relaxation of our spending […]

Leave a comment

RSS feed for comments on this post. TrackBack URL

CommentLuv badge