Detailed Lists

Now that I’ve covered the basics of how to use a spreadsheet to get a high level overview of something by using graphs, it’s time to go to the other extreme. Spreadsheets can make it very easy to work with a long list of items that have a lot of details that need to be tracked.

A spreadsheet has all of the advantages that any electronic format has. It can be as large as it needs to be, you can search it for the words you want, you can move text around easily, and, of course, handwriting isn’t an issue.

However, a spreadsheet has some additional benefits. It can be organized in ways that most other documents can’t be. Because each detail can be put in a separate column it can be made more clear, and it also becomes more sortable. It can be filtered so you only see the parts you want. It can be automatically formatted, so that cells which fit your desired criteria are highlighted.

Continue reading Detailed Lists

Templates: Projects 1-4

Now that there are a fair number of project posts, I thought it might be good to post templates for each of them.

They’re all available on Google docs. I used formatting to clearly identify various elements.

  1. Yellow background: This cell was/should be used to create the chart.
  2. Bold, italic font: formulas you might be interested in looking at.
  3. Blue background: This cell contains an instructional note, and doesn’t need to be duplicated in your actual project.

The templates are all designed to be self-explanatory, but I’m including some particular notes about each one below.
Continue reading Templates: Projects 1-4

Project 4: Line charts & debt payments

The United States government relatively recently passed some credit card reforms, requiring credit card companies to tell borrowers how long it will take to pay off their debts if they only pay the minimum balance. But what if you pay $1 more than the minimum balance? How much will that affect it?

It’s actually pretty easy to figure out and graph, if you have a spreadsheet. You don’t even need to know any fancy formulas like the online calculators use. All you need to know is what the current balance is, what the interest rate is, and what amount you want to consider paying. The graph below was made with the assumption of a current balance of $1000, an interest rate of 18%, and a monthly minimum payment of $17.50. Look! You’ll have paid off the $1000 in only 11 years! And paid approximately 17.50 per month*12 months per year * 11 years = $2310 total, which means 2310-1000=$1310 in interest. ….uhm, really? Yes, really.

Continue reading Project 4: Line charts & debt payments

Project 3: Pie charts & household budgets

I have a friend who’s not very fond of numbers. I suspect they reside in the same part of her brain as potentially hostile dogs and IRS auditors. She’ll deal with them when she has to, but if there’s too many of them she might end up running away no matter what her original intentions were.

She tries to be responsible with her money, but doesn’t have the time or focus to track daily expenses. So at one point she came to me and asked me to help her figure it all out. She’d tried trimming things as much as she could, and the fixed bills (like mortgage) and common variable bills (like groceries and utilities) were well below their combined incomes, but the credit card balance just kept climbing. I told her to bring all her receipts, bills, and/or bank statements from the last three months to me, and we’d see what we could figure out. In the end, this is the chart we came up with.
Household budget pie chart
Continue reading Project 3: Pie charts & household budgets