Category: Finance

Referencing other Google spreadsheets

I’m more and more impressed with Google spreadsheets as I get into them. Today’s new trick that I learned, as I was helping my sister figure out how to do it, was how to pull in data from one Google spreadsheet into another — and the data will theoretically update automatically in the second spreadsheet when you update the first.

I can’t think of immediate applications yet, so I’m posting this for my own reference in case I need to do it in the future!

Using ImportRange to pull in a range of cells from one spreadsheet to another:

Inside your new spreadsheet, select the top-left cell of the area where you want to pull in data. Then type this formula:

=ImportRange(“spreadsheet_key“,”sheet_name!range“)

  • spreadsheet_key: The “key” variable in the Google spreadsheet link. For example, the bold text here is the spreadsheet key: http://spreadsheets.google.com/ccc?key=abcdefghijklmnop&hl=en
  • sheet name: The text that is in the bottom tab of the spreadsheet.
  • range: This can be a single cell or a range of cells – for example, B2 (single cell) or A3:A5 (part of a column) or A2:E10 (a block of cells).

Another seemingly roundabout way to do this is to use ImportData.

First, publish your original spreadsheet. In the Publish tab is a link for “more publishing options.”

Change the File format to CSV, pick the specific sheet you want to import, and type the range of cells. Then click the Generate URL button.

Use this URL in the ImportData(“published_url“) formula.

I’m not really sure what the pros and cons are of one method over the other, although ImportRange seems simpler if you’re working with your own spreadsheets as you don’t need to mess with publishing.

Anyway – it takes Google a minute or so to refresh new data from the original spreadsheet, but I think it’s pretty cool!

Smart Strip

Smart StripI’m excited because I recently received my Smart Strip and plugged it in. My Smart Strip is a tan color, not the white that the product pictures show, but it works just fine!

The basic concept is that you can use the Smart Strip for one main appliance and related peripherals. Common uses would be for your computer/printer/drives/etc. or a home entertainment center. When the one main item is powered off, all the other peripherals have the power shut off to them as well, so you avoid electricity drain and save money! (The Smart Strip does include three plugs that are “always on” for things that you don’t want to be powered off — those are the red plugs.)

I first read about the Smart Strip at The Simple Dollar and finally tried to get one when we moved. Our local Office Max staff looked at me blankly when I asked about it, so I ended up ordering mine off of Amazon. Of course, a few days later, I saw them available at Ace Hardware along with other “green” products! Go figure.

Freezer shortcuts

The freezer can be a great tool for saving time in the kitchen or for saving money… or both! I like to buy in bulk and also pre-prep some foods, store it in the freezer, and pull things out when I need them. Here are some of the things I like to do:

Meat freezer tricks

  • Buy ground meat in bulk. Split it up into smaller packets, wrapped in plastic wrap or butcher paper, label, and freeze. You can use a food scale (or a postal scale, which is what I do!) to get 1/2 lb. or 1 lb. packets — all ready for a recipe!
  • Same thing works when you see any kind of bulk meat or meat on sale: Buy a lot of meat all at once, split it up into recipe-sized portions, and freeze. I do this with large fillets of salmon, for example.
  • Buy whole chickens (which are usually cheaper than individual parts). If the butcher can’t cut it up for you, it’s actually kind of fun to do it yourself! I usually de-skin and de-bone the breasts as I’m cutting up the rest of the chicken, and use a cleaver to chop up the back into smaller pieces to use for homemade chicken broth. Divide the parts into different plastic bags (or butcher paper) according to what you might usually use them for. For example, I’ll wrap the chicken breasts individually, put the wings, thighs, and legs together, and take all the excess skin, fat, bone, and chopped-up pieces of back into a big Ziploc bag for homemade chicken broth. Everything gets labeled and stored in the freezer, ready for dinner later on.
  • If you like bacon occasionally or if you like to buy bacon in bulk, try this trick: Pull out two slices of bacon at a time and roll them up together. Put the bacon rolls side-by-side in a freezer bag (it’s okay if the rolled sides touch each other) and freeze. It’s very easy to break apart the bacon rolls and pull out as much bacon as you want to have in the morning or to use it in a recipe.

Produce freezer tricks

  • Chopped green onions are a staple in my kitchen — I use them in soups, fried rice, in omelets, and more. So, instead of buying a bunch of green onions, using one or two stalks for a meal, and letting the rest rot in the fridge, I wash, dry (with a paper towel), and chop all the onions at once and throw them in a freezer bag. The frozen onions are great with cooked foods; however, they tend to look a bit wilted if you try to use them “fresh” (as a garnish or topping, for example)
  • I do the same thing with parsley and celery, which I am rarely able to use all at once. Chop them up and freeze them, and they’re great for adding to cooked foods later on!
  • We have a friend with a very productive lemon tree. I took part of an afternoon to juice the multiple bags of lemons that she gave us (an electric juicer would have been handy, but I used a normal hand-held juicer) and poured the lemon juice into ice cube trays. The lemon juice was a bit more difficult to pop out of the trays (I had to use a butter knife to encourage some of the cubes to come out), but once they did, I was able to put them all in a freezer bag. You can do this with any home-squeezed juice — lime, orange, etc. I’ve also frozen the base for blackberry limeade (blackberries and water) when I bought a ton of blackberries on sale, although in a ziplock bag, not in ice cube trays.
  • Although this isn’t produce-specific, ice cube trays work nicely for freezing small portions of homemade pesto, broth, or any sauce or liquid!

Other freezer tricks

  • I usually only need a tablespoon or two of tomato paste. When I open a new can of tomato paste, I use the can opener to open up both sides of the can. Pushing against one of the metal discs, I slide the tomato paste out onto a sheet of plastic wrap, then discard the can top/bottom. When I need tomato paste, I just eyeball the frozen chunk and use a knife to cut off the approximate amount that I need.
  • I have to admit that I don’t like the heels of loaves of bread. We used to just let them sit and collect in the cupboard until they molded. Now, I’ll tear them up into chunks and pulse them in my food processor to make fresh breadcrumbs. I store the breadcrumbs in the freezer and use them in recipes. (Actually, the freezer is a great place to temporarily store those leftover slices of bread so that you can make a big batch of breadcrumbs all at once! Just make sure that they thaw before trying to pulse in the food processor.)
  • The food processor is also a great tool for making your own shredded cheese. Buying a big block of cheese is sometimes more cost-efficient than buying pre-shredded cheese. I’ll often shred the cheese myself and then freeze portions of it in Ziploc bags which I can pull out and thaw as I need them.
  • Another cheese trick — Steve loves provolone cheese, which I’ll buy in bulk at Costco. I divide the provolone into packets of 5 slices (one for each weekday) and freeze. On Sunday, I can pull out one of my packets and thaw it in the fridge to use in Steve’s lunch sandwiches that week.
  • After making homemade chicken broth (usually about 2 qts. worth from the back pieces, leftover skin, fat, and bones of one chicken), I’ll pour two or four-cups of broth into Ziplock freezer bags, seal them up, and store in the freezer for later.
  • We have a local company that makes these wonderful fresh flour tortillas, but because there are no preservatives, they go bad faster than we can eat them. I’ve started layering the tortillas with waxed paper and freezing the stack of tortillas, which allows me to pull them out easily one at a time, defrost quickly in the microwave (15-20 seconds at high), and use for a quick quesadilla.
  • After making Chinese steamed pork buns, which we love — but can only eat so many for a few days in a row, I’ll let them cool and then freeze them in a plastic freezer bag. I can then pull out one or two at a time and microwave them to eat for a quick snack or meal.
  • Finally, when Steve and I make a huge batch of potstickers/dumplings (note to self: will have to post recipe at some point), we’ll lay them out on cookie sheets (before cooking them) and freeze them, then transfer them into a Ziplock bag for storage. We then have lunch or dinner all ready to go; I like to either boil them or fry-steam them in a skillet, which you should do straight from the freezer so that they don’t thaw and stick together.

What freezer tricks do you have up your sleeve? Please share in the comments!

Month-long food expense tracking

I mentioned earlier in December that I was in the middle of a month-long food expense tracking experiment and had so far been able to see first-hand how much money you can save by cooking meals instead of eating out. I kept Very Detailed Spreadsheets of exactly what we were spending money on for food (including eating out and individual grocery items like “three onions”) and how much money each meal cost. The purpose of this experiment: To see how our grocery budget was impacted by purchasing mostly organic and/or locally-produced food.

The month is over, I’ve tallied up the numbers, and here are the results:

  • Grocery costs: $298.11
    • Includes atΒ  least $235.55 spent on organic/locally-produced foods
    • Does not include items that were already in the pantry or freezer
    • Does not include food purchased for Christmas gifts (we made potstickers for friends, for example)
    • Includes money spent on food that was used to feed other people — potlucks, Christmas brunch, inviting friends over for dinner, etc.
    • January – November 2007 11-month average of monthly grocery costs: $322.12 – $24.01 less than average
  • Eating-out costs: $102.13
    • I don’t have an 11-month average of eating out costs because my budget tracking lumps all “fun” activities into one category, including eating out. However, I’d guess that this is a typical amount.

I was initially surprised that we spent less on groceries on average. Purchasing conventional, non-organic foods seems cheaper, after all:

  • Organic milk is more than twice as much as conventional milk, and we’ve been buying more milk than usual because I’ve been having to drink a certain amount for my glucose-intolerant diet.
  • The free-range eggs we buy at the Farmer’s Market are twice as much as normal eggs on sale, which is what we used to get.
  • Organic cheese is also significantly more expensive.

But performing some additional analysis helps to bring this into perspective so that it makes sense:

  • We purchased and ate less meat overall. Usually I buy meat in bulk and freeze it, and we usually would have meat for dinner every day. However, the sticker shock of free-range and/or organic meats meant that I ended up purchasing and cooking far less meat than we usually would eat. This in itself probably saved us a lot of money.
  • We obtained in-season produce at the Farmer’s Market. My old method of menu planning was to pick out recipes that sounded yummy, generate a shopping list, and pick everything up from the store. This month, I found myself making a loose menu plan but revising it as I saw what produce was available at the Farmer’s Market, sometimes even building a menu plan on the fly while walking the stalls. Some of the produce was very cheap — for example, I got 50-cent heads of cabbage several times which made Very Cheap Potluck side dishes and could be used in stir fry, fried rice, soup, and more. In contrast, my former method of menu planning involved purchasing lots of out-of-season foods, most of which were probably imported and/or processed and therefore more expensive.
  • We were more aware of our money. Tracking every penny spent on food helped to motivate me to really make the most of the produce I bought. I’m guilty of buying produce, using a tiny bit in a recipe, and letting the rest rot in the fridge. Because I was keeping track of the food I bought and how much was used in each recipe, I found myself becoming creative with leftover bits of produce and using every bit up. For example, a remaining half of a bell pepper got sliced up with some onions into an egg scramble for a few breakfasts, as did leftover cabbage. I think we only ended up with one moldy orange, one rotten apple, half a bunch of cilantro, and a few leaves of lettuce that had to be thrown out!

If you’re curious about the gory details of the food expense tracking and cost of meals, I’ve published the Google Doc workbook here: 12/1/2007 Food expense. Here are a few notes of explanation:

  • The first spreadsheet, “Expenses,” has the breakdown of all the money spent on food in December.
    • For simplicity, I tallied only foods purchased at the Farmer’s Market and our local food co-op as “organic/locally-produced.” The actual amount spent on organic foods is probably higher because I think some of the things we bought from Safeway and Costco were also organic.
    • I categorized items as “groceries” for general grocery items that we’d eat throughout the day that would be hard to keep track of, “meals” for grocery items that I could keep track of by meal, “fun” for eating out, and “gifts” for items that were used to make Christmas presents. “Gifts” were not included in my grocery total.
  • The second spreadsheet, “Food,” shows the food we ate each day and the cost per meal.
    • The actual cost column only includes the cost of groceries bought that month. I did make notes in the last column if I used food from the pantry or freezer and if I knew approximately how much it was.
    • I calculated the cost to make an actual dish, so if there were leftovers on subsequent days, I didn’t calculate the cost for those days. However, you can get a sense of how many servings each dish provided — and I started putting numbers to indicate how many servings were served that day.

So in conclusion, while I never want to keep such detailed track of food expenses ever again, I think this was a very valuable experiment. It’s one thing to read or hear from someone else about how eating out is more expensive and another thing to personally calculate the cost yourself and compare the numbers. It was also encouraging to see that we aren’t spending any more on groceries (and perhaps even less) by purchasing “more expensive” organic, locally-produced foods. Given the health, environmental, and sociological benefits of doing so, we’re going to continue along this path!

Appendix: Related blog posts

These blog posts relate to the topic of organic, local, sustainable food and the cost of food:

The cost and benefits of homemade vs. store-bought chicken broth

While writing up yesterday’s post about eating out vs. making meals at home, I realized that I’d been making a lot of soup lately. For some of the recipes, I used store-bought chicken broth; for others, I made homemade chicken broth using chicken backs and necks that I had in the freezer from past chickens I’ve chopped up and chicken breast bones that I’ve de-boned. Which makes me wonder — what’s cheaper? What’s better?

Analysis

Pacific Organic Free-Range Chicken BrothPacific Foods has organic free-range chicken broth, normal or low-sodium, available at our local grocery store or food co-op. Regularly $3.09; on-sale at the Co-op this month for $1.89. Each box has approximately 4 cups (1 quart) of chicken broth.

My homemade chicken broth recipe involves 2-3 lbs. of chicken parts (backs, necks, bones, skin, etc.) that would otherwise be unusable, 1 medium-to-large onion, salt, and two bay leaves. It makes approximately 2 quarts (8 cups) of chicken broth.

The obvious variable in the chicken broth recipe is how much you’re paying for the chicken parts. The local food co-op offers prepackaged chicken backs and necks at $1.39/lb., although I’m not sure if those are free-range chickens or just “natural” chickens. Last month, I bought a $15.07 free-range chicken (approximately 5-6 lb. chicken), which, after deboning the breasts and taking off the legs, thighs, and wings, yielded enough chicken parts for a batch of broth. I’m going to somewhat arbitrarily set the price for the leftover chicken parts at a higher value of $1.50/lb., or $4.50 out of the $15.07 I spent on the whole chicken.

Back to my calculations: $4.50 for free-range chicken parts + $0.50 for a medium-large organic onion + minuscule pennies for salt and bay leaves = $5.00 for 8 cups (2 quarts) of chicken broth, or $2.50/quart. I save $0.39 on homemade broth for normal-price chicken broth, but lose $0.61 when chicken broth is on sale.

Notes:

  • The natural, free-range chicken I used isn’t “certified organic” but comes from a local, sustainably-run farm. Pacific Foods is based in Oregon and is labeled USDA Organic.
  • You can make homemade broth even cheaper with conventional chicken; I used to purchase bone-in chicken breasts or whole chickens on-sale for as little as $0.99-$1.39/lb. and freeze the leftover parts/bones until I had enough for making broth. However, that defeats my purpose of trying to purchase more organic, locally-produced foods.
  • A Cooks’ Illustrated issue ranked Pacific Foods free-range chicken broth as one of the lowest in their taste testing. Swanson’s Organic topped the list, but I don’t know how far the food travels before it arrives at my local store. I haven’t done my own taste-testing (and frankly, I’m not sure if I’m discerning enough to tell with premade broths!). However, I know for sure that homemade broth’s taste and flavor far surpasses ANY pre-made broth that I’ve had so far!

Summary

So, as I don’t care too much about the fact that Pacific Foods free-range chicken broth was ranked low in taste tests, I bought a few boxes of broth on-sale for convenient use in dishes where the chicken broth flavor isn’t the main taste.

However, I’ll probably stick to trying to make my own broth whenever I can for the following reasons:

  • The cost is about the same
  • The flavor is a ton better
  • I’m supporting local farmers by doing so
  • The food is traveling less to get to me
  • The satisfaction level of cooking from scratch is much higher

Comments or questions? Post them below!

The cost of eating out vs. making meals

The Simple Dollar did a couple cost-comparison (and time-comparison, too!) studies a little while ago about making hamburgers at home vs. purchasing them at a fast-food place. While I’ve always generally “known” that making my own food is cheaper than eating out, I’ve never really bothered to break down the numbers.

One of my friends, curious about our organic-and-local food attempts, asked how our grocery budget was faring near the end of last month. While I had general numbers for our grocery budget, I really had no concept of what percentage was represented by organic, locally-produced foods. As an experiment, I’ve been trying to keep Very Detailed Track of the food we buy and what I use it for in December.

As I seem to love spreadsheets as much as I love lists, I made a workbook with two spreadsheets:

  • Expense tracker: This breaks down every penny that we spend on food this month. I note the date, the specific item purchased, the category, the cost, and where it was purchased.
    • Categories break down into “fun” (which is shorthand for “eating out”), “meals” (items that I can track to specific meals that I make), and “groceries” (general items like milk and snacks that I can’t keep track of as well).
    • Example of a line from the spreadsheet:
      • Date: 12/3/2007
      • Item: Daikon radishes for soup
      • Category: Meals
      • Cost: $1.00
      • Where: Farmer’s Market (i.e., local and usually organic)
  • Food tracker: This lists the food that I use for each meal and calculates the cost of the meal. I note the date, which meal, what I made, the cost (hand-calculated, usually), and another column for notes.
    • Example of a line of data from the spreadsheet:
      • Date: 12/3/2007
      • Meal: D(inner)
      • What: Asian-style chicken soup with oyster mushrooms, daikon radish, cabbage, served with rice.
      • Cost: $2.88
      • Notes: $1 from radishes (local/organic), $0.10 of cabbage (1/5th of a 50-cent head, local/organic), $1.78 from oyster mushrooms. The chicken was bought in November ($15.07; local/free-range) so doesn’t count towards the meal cost for this month; I used about $5 worth for the soup, using the thighs/legs and wings. (Actual cost $7.88) Rice was purchased a long time ago and would count in regular grocery money anyway so I’m not keeping track of it.

(Keeping track of this is enough work that I doubt I will continue after this month, but I’m motivated enough by curiosity for now to keep going!)

After just 12 days of keeping up these detailed spreadsheets, I was really surprised by how cheap it is to cook your own meals, even when trying to purchase organic, locally-produced foods. That $7.88 batch of chicken soup that I made was good for 7 individual servings, or about $1.13 per serving. And that’s with expensive free-range chicken that costs about 3 times as much as “conventional” chickens!

Here are some other examples of super-cheap meals that I’ve made so far this month:

  • Indian Curry Tuna and Indian-Spiced Cabbage, served with rice
    $2.08 total = 3 servings, or $0.69/serving
    $0.30 for another 3/5 of the head of cabbage, $1.78 for a can of tuna. Spices and rice not included in cost.
  • Homemade pizza – one with pineapple and ham, the other with chicken, spinach, and bell pepper
    $5.09 total = 4 servings, or $1.27/serving
    $1.43 accounts for half a can of organic pineapple, $0.79 for a partial bunch of local/organic spinach, $0.37 for the quarter of local/organic bell pepper used, and $2.50 for the chicken. Ingredients for tomato sauce and crust were already in the pantry and not included in the cost.
  • Chickpea, potato, and spinach soup
    $4.98 total = 5 large servings, or $0.99/serving
    $0.75 for the rest of the bunch of spinach, $1.09 for a can of garbanzo beans from local distributor, $1.00 potato (local/organic), $1.89 for premade organic free-range chicken broth. Seasonings/spices not included in cost.

In contrast, here are some samples of times that we’ve eaten out this month:

  • Dinner at local Thai restaurant – two appetizers, main dish, rice, Thai iced tea (Steve), hot tea (Corrie)
    $35.49 including tip = 4 servings, or $8.87/serving
    We ate the appetizers and drinks the first night, but had enough main dish leftovers for two smallish lunch servings.
  • Breakfast at local crepe restaurant – shared one large crepe which came with side of potatoes
    $8.74 including tip = 2 servings, or $4.37/serving
    We were still a little hungry afterwards and ate more after running errands and going home.
  • Pizza and mocha freeze from Costco
    $5.58 = 2 servings, or $2.79/serving
    Steve had the mocha freeze, I had water; we shared the two “slices” of pizza.

Even the “cheap” food at Costco is twice as much as one of the meals I’ve made at home (which are far healthier, with the added benefit of using organic and locally-produced ingredients).

So far, our three categories are about even (“fun,” “grocery,” “meal”), if you don’t include the large case of soda that we got from Costco ($20!). Our grocery category includes organic milk, which is expensive compared to conventional milk, fruit, cheese for snacking, sandwich rolls, and other hard-to-track items like cereal and butter. Surprisingly, so far we haven’t been spending any more than we used to spend on groceries when purchasing conventional, “cheap” food.

I may update with another post at the end of the month after looking at the final numbers. For now, I’m definitely motivated to continue to enjoy cooking meals at home after seeing the price tags! We do have a “fun” budget which gets spent mostly on eating out — which we usually exceed — so this has been a good motivator to stay within our fun budget and instead have fun with the challenge of making foods that are nutritious and cost-effective.

Frugal heating system

Our house is older, not well insulated, and has big windows. We haven’t turned on the heat yet, so it’s quite cold in the mornings and evenings. As I work, I usually have a big mug of hot tea to help warm me up. But hot tea only stays hot for a limited time, and I can only drink so much of it!

We do have a space heater, and although I haven’t worked out the numbers, I think that running it all morning, every day, would defeat the frugalness of having the house heater off in the first place.

Yesterday, I filled a gallon-sized ziplock bag half-full of white rice (I get rice in large quantities so it’s pretty cheap) and microwaved it for two minutes. Then I sat with it in my lap. Ahhhhh.

The rice was so hot, I had to move it around. It became a nice soothing back pillow for a little while.

I was warm for the rest of the morning.

The 4 Hour Workweek: 80/20 Analysis

One of the applications from The 4 Hour Workweek by Tim Ferriss is to do a regular 80/20 analysis (Chapter 5: The End of Time Management).

According to Pareto’s principle, 80% of results come from 20% of the efforts. In business-speak, 80% of your income comes from 20% of your clients. The ratio can vary slightly, such as 90/10, 95/5, or even 99/1, but you get the idea. In his book, Tim Ferriss shares his personal story of how he realized that he was spending 95% of his time pursuing those clients who weren’t placing orders (95% of his customers), while his bulk of his income came from steady, low-maintenance clients who simply placed orders without bothering him (3% of his customers). By choosing to stop expending energy on those people who only ordered occasionally (and even “firing” the two customers who gave him the most grief), he was able to go from working 80 hours a week to 15! (This worked for him because he had a product business model, where most of his time was spent on sales calls and customer support.)

The personal application derived from this principle is to look at your income and determine your 80/20 ratio — which of the small percentage (20% or so) of clients, pursuits, or projects are contributing to your income. You can also determine whether or not most of your energy is being expended on the other 80% and make decisions to divert your energies and efforts. Finally, you can analyze the common factors between the 20% and pursue more projects/clients/etc. that fit those commonalities.

So, this past weekend, I created a few reports in Quicken and some spreadsheets to perform my own 80/20 analysis.

In Quicken, I printed out two reports:

  • Year-to-date report of Itemized Payees. I collapsed the “Expenses” and “Transfers” sections so that only the income “payees” were listed. This report showed all the places where we received income in 2007 — including bank interest income, Steve’s paycheck, but also my individual clients.
  • The same report with a customized date to include numbers from 2006 (1/1/2006 – current date).

I also created reports to find my total business income for year-to-date and my customized date.

Now for the analysis:

  • On my itemized payee reports, I ignored all non-business payees (as this report includes ALL sources of income).
  • For each business payee, I took the amount * 100 / total business income to find the percentage of income for that time period. The way Quicken exports reports to Excel- or spreadsheet-ready formats was a bit too detailed for me, so I calculated percentages the old-fashioned way with a calculator. (I used my old high school/college Texas Instruments scientific calculator, which allowed me to store my total business income as a variable so that I didn’t have to keep retyping it.) I wrote these percentages down on my printout.
  • I quickly typed the list of payees and percentages into a spreadsheet.
    • Column 1: Payee name
    • Column 2: Percentage – last 22 months (since 1/1/2006)
    • Column 3: Percentage – last 10 months (since 1/1/2007)
  • Adding another column (Column 4), I made some notes about clients that were most likely one-time — i.e., I made their web site and that was the end of it. I also noted the projects that I had especially enjoyed working on and the projects that were especially stressful to work on.
  • I added another column (Column 5) to jot down some notes about who these clients were, using common keywords:
    • referred by family – client was referred by family member (or IS a family member)
    • referred by friend – client was referred by friend
    • referred by writing – client was referred by my blog or other articles
    • developer – client is mainly a developer who came to me for visual design work
    • middleman – client is a design house or similar middleman contracting me for their own client work
    • small biz – client came to me for work related to their small business
    • frequent updates – client comes to me for web site maintenance at least once a month

I made a few copies of the worksheet:

  • Sorted by 22-month percentage – This shows me the top clients for the past 22 months.
  • Sorted by 10-month percentage – This shows me the top clients for the past 10 months.
  • Projected 10-month percentage – I readjusted some of my numbers; as they were based on pure cash flow, I hadn’t taken into account some of the invoices that were going to be due.

What did I find?

  • 80% of my income came from “middlemen” — companies or design houses that have their own clients and contract me for design and/or production work. (This includes work that I do for PixelMill, my main contract, and also includes royalties that I’ve received from products, so the number isn’t exactly accurate but I didn’t feel like figuring out the breakdown between royalties and work-payment. Maybe later.)
  • 5% (22 month)/10% (10 month projected) came from a one-time writing project.
  • 15% (22 month)/10% (10 month projected) — came from miscellaneous projects for small businesses, non-profits, and people referred by family or friends. About 2.5% of this comes from two businesses who need regular updates; the rest were pretty much one-time projects.
  • There were 3/23 miscellaneous one-time projects that were a joy to work on; my two regular update clients are also very positive relationships. 3/23 were extremely stressful. The others had their highs and lows. While I don’t have specific numbers, I know from experience that these clients required more hand-holding and communication overall.

Some observations and thoughts:

  • My “middleman” work and developer work has generally been enjoyable and positive and provides about 80% of my income.
    • I think a big part of this is because they can appreciate my skills and express their appreciation, which makes me feel good. For example, I’ve been called a “CSS wizardess” by one and complimented on my “inhuman speed” by another. πŸ™‚
    • These relationships generally require less foundation work; I don’t need to convince them of my skills, I don’t need to hand-hold them through the basic concepts of having a web site, I can use industry terms without explaining them.
    • These relationships do generate more interruptions, however, either with emails, IM, or phone calls to talk about projects.
    • The main downside of middleman work is that it’s often not very creative work; I’m either working on a small part of a project or doing HTML/CSS production work.
    • Bottom line: Based on pure numbers, I should continue to work with these companies and be open to working in similar situations. However, these are mainly hourly-contracted jobs. Am I working myself into a situation where my income is purely dependent on how many hours I work? I want to recommit to producing more products where I make a royalty and also consider raising my hourly rate for new clients to take into account my main strength of speed and efficiency.
  • While I enjoy the end product of my one-time web projects, the process of wooing the client, educating the client, probing the client for useful feedback, discussing things with the client, etc., etc., can be exhausting.
    • With a few exceptions, the most exhausting projects were with small businesses looking for their first web site.
    • A big part of the process with the clients was developing trust. The projects that I did for friends, family, or those referred by friends/family generally went much quicker.
    • I like the relationship-developing aspect of working with clients: learning about their personalities, learning how to communicate with them, and getting a little more personal despite having a “business” relationship.
    • There are a couple clients who take up a lot of communication time — very wordy on the phone, requiring in-person meetings, etc.
    • Bottom line: I really do enjoy the client process and the final product, but I’m sensing that I’m starting to burn out in this area — and again, based on numbers, it’s not “worth” my time to continue to pursue this type of work. I’m going to set some boundaries for myself so that I’m only working with a certain number of custom clients at a time. I think this will be better overall as I can then really focus on the one or two that I work with and develop the one-on-one relationship that I enjoy. So, one of my immediate goals is to complete and close out as many of my open projects as I can.
  • A big chunk of income this year was from a one-time writing project.
    • I really enjoyed this project, and not just because it paid me a lot. πŸ™‚
    • This was an opportunity that fell in my lap; I didn’t go looking for it. The person that found me, however, had read other related articles that I had written and thought it would be a good fit.
    • Bottom line: Continue writing and look into other opportunities for paid writing.
  • Other action items:
    • I will have to rework my web site content. Right now corriehaffly.com is very targeted at small business owners, from my home page “why hire a designer” content to my educational “what goes into a web site” content to my portfolio pieces. If I want to work more with developers and middlemen, I should redo my web site to reflect the services and skills that I can offer.
    • I will figure out my royalties to see what the actual percentage is. This will help me to decide how much more time I should invest in creating digital products or looking into more writing opportunities.
    • Finally, I’ll think more about applying other principles from The 4 Hour Workweek about cutting down interruptions and time is spent on communications so that I can be more productive.

Overall, this has been a very valuable exercise! It only took about 10 minutes to print out the reports, calculate the percentages, and create the initial spreadsheet data, and another 20 minutes to sort the data and jot down notes. I did more analysis while writing up this blog which took quite a bit longer, but your analysis process could probably be more efficient than mine. πŸ™‚ If you have a spare half-hour or hour, I’d highly recommend that you follow Tim’s advice to perform an 80/20 analysis.

This is my first full post with thoughts about The 4 Hour Workweek. View other posts related to The 4 Hour Workweek.

Spreadsheet for Tracking Roommate Expenses

The scenario: You live with roommates and share some expenses. For example, one person is in charge of writing the rent check, another person pays utilities, another person pays for internet access. You share occasional grocery and household items. Let’s make this hypothetical scenario more complicated: Three of you share milk, but the other person doesn’t. How do you keep track of it all without writing 800 checks to each other at a time?

When I lived with four other roommates in college, I developed a roommate expense tracking spreadsheet. The spreadsheet was complicated enough so that it not only tracked items we all shared, but also allowed for tracking items that some people shared but not others. This allowed us to visibly see what we were spending money on, equitably divide expenses, and consolidate paybacks. Instead of writing a check for each item someone purchased, we settled our accounts monthly.

Now that we live with housemates again, I’ve resurrected the spreadsheet with the power of shared Google documents. In the old days, my roommates had to save, mark, and initial receipts, which I would enter into the spreadsheet and then print out each month. With a shared Google spreadsheet, each of us can go to Google docs and update the spreadsheet ourselves with receipts.

The rest of this post describes how to use the spreadsheet and includes links to view my open Google spreadsheet example and to download an Excel version.

View and Download

Hopefully my server doesn’t crash again with the Excel download. If you’re willing to mirror the download, please let me know!

  • Roommate Expense Tracker – published Google spreadsheet
    This spreadsheet includes a template and four example spreadsheets that go along with the diagrams above.
  • Roommate Expense Tracker Excel spreadsheet download – shared-expenses.xls

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 License.

Step by step through the spreadsheet

Click on screenshots to open a larger version in a new window.

Setup

If you’re using a Google spreadsheet, you can share the spreadsheet by going to the Share tab and adding your roommates’ email addresses as collaborators. This allows them to access the spreadsheet and edit it with their expense line items.

If you’re going to be using an Excel spreadsheet on a single workstation, then your roommates will have to be diligent about giving you their receipts. For easier entry, they should write their name at the top of the receipt, circle the shared items, and note if any items are shared between specific individuals (but not everyone).

My sample spreadsheet is set up for four roommates. If you have more or less people, you’ll want to adjust the spreadsheet by inserting/deleting columns and copying the formulas. You’ll also want to change the column names to more accurately reflect the names of your roommates (unless you happen to be named Bob, Joe, John, and Larry)!

You should make a copy of the template worksheet each time you are settling expenses (monthly, biweekly, etc.). The worksheet is meant as for “one time use,” not as an ongoing thing. You can date it at the top.

1. Enter receipts.

Shared expenses diagram 1

For each item:

  1. Enter the item description (“milk” or “utilities”).
  2. Enter the amount paid. Don’t forget to include sales tax if applicable.
  3. Enter in /# how many people are splitting this expense. In most of the line items, the item is being shared by everyone, so the dividing number is 4. In this example, milk is only shared by three people, and printer cartridges are shared by two. (The screenshot shows the notes I’ve added to those specific rows.) Note: If something is divided unevenly, such as “rent,” then you can leave this blank.
  4. The each column automatically calculates how much each person would owe (amount/#).
  5. Enter the name of the person who paid for this item in paid by.
  6. Now, manually type the each amount under the appropriate name. For example, Bob, Joe, and Larry split milk (but not John). The milk amount ($4.99), divided by 3, is approximately $1.66. So, $1.66 is entered under Bob, Joe, and Larry. John remains blank. Note: For uneven amounts such as “rent,” you can just type the amount under each name. In this example, Bob has a bigger room and pays more rent than the others.

Since I haven’t figured out if it’s possible to sort selected rows in a Google spreadsheet, you may find it easier to group items together that were paid by the same person.

Also — don’t worry about the “approximate” amount. The rounding issues will be dealt with in the next step!

2. Make adjustments for rounding.

Shared expenses diagram 2

As noted above, there may be some small rounding problems, resulting in some very slight differences (on the order of pennies) between what was actually paid and what people owe collectively.

The bottom of the spreadsheet accounts for these differences. The blue total shows the actual amount that was paid. The people totals in italics show how much each person owes, and that sum allocated is in italics under the blue total. The difference takes the difference. In this case, the allocated amount is 3 cents less than what was actually paid.

Some roommates won’t care about the leftover pennies. However, my roommates happened to be very detail-oriented, so we had an additional adjustment row. Taking the small difference, divvy it up amongst the roommates in a sort-of-fair way. In this case, since Bob (oops, this should have been applied to Joe!) took the brunt of the expenses by paying the rent, he’s exempt from the additional 1-cent adjustment applied to everyone else (reference the yellow highlighted cells in the diagram).

3. Indicate how much each person contributed.

Shared expenses diagram 3

Now, sum up the amount each person paid in the Paid row (highlighted yellow in the diagram). You can do this by typing “=” to start a formula in the cell, then highlighting the specific amount cells relevant to each person. This is where grouping people’s expenses comes in handy, as you can just highlight an entire block instead of individual cells.

In this example, you can see that since Bob (oops, that should be Joe) paid rent, he paid the most out of everyone.

After doing this, check your totals. The grey boxes on the right side of the spreadsheet guide you through making sure the numbers all work out. Breaking it down:

  • $1230.52 Check totals – This row takes the sum of the actual amount paid — same formula as the blue total.
  • $0.03 Does this equal difference? – This is the sum of the Adjust row. It should equal the green “difference” amount.
  • $1230.52 Does this equal total? – This is the sum of the Owed row. It should equal the blue total amount.
  • $0.00 Is this 0? – This sums up the Pay row. It should theoretically be zero, as the positive (people who owe money) numbers should equal the negative (people who are owed money) numbers.

4. Add helpful notes for payment.

Shared expenses diagram 4

The final step is to add helpful notes for payment. The negative pay amount means that this person paid the most, and other people owe them money. You can add a note in the bottom row for who the check should be made out to. If there is more than one person with a negative amount, then you can add a note for how to split the check between each person who is owed money.

That’s all!

And there you have it — a very fair way to track and consolidate shared household expenses! This spreadsheet certainly isn’t for everyone (such as those of the “who cares” variety), but for some, having a expense tracking worksheet can be a great way to avoid financial roommate conflict.

Organizing my cell phone to optimize frugality

I now have my very first cell phone ever.

(I could tell a long story about why I haven’t had a cell phone, why we finally decided to get cell phones, etc., etc., but that would make this post way too long and uninteresting for most people. Maybe I’ll do that the next time I’m in a storytelling mood.)

Steve and I signed up for the AT&T (formerly Cingular) FamilyTalk Unity 700-minute plan. The “unity” part of the plan means that we can call any AT&T phone line — home land line, business land line, or cell phone — without using our minutes. That is one of the main reasons why we signed up for this plan; AT&T is the primary provider in our area and most of our friends and family have at least one AT&T phone line.

AT&T allows you to check mobile phone numbers and check land line phone numbers to see if they are in the AT&T network.

So, here’s how I organized my cell phone to optimize frugality:

  • As I entered in my contact numbers, I looked them up to see if they were part of the AT&T network.
  • If my friend’s home phone and cell phone were part of the network, I added an exclamation point after their name (e.g. Steve !)
  • If only the home phone was part of the network, I added “!H” after their name (e.g. Mom !H)

Now I can quickly scan my contact numbers and incoming calls (from people already in my contact list) to see if this is someone that I can talk to for a long time without worry, or if I should be careful with my minutes.