Jeff Barr's Blog

Things I Like..

Using Google Docs Spreadsheet to Compute Recipe Costs

My teenage daughter Bianca is hoping to go to Spain next year for a semester of study abroad. The program is kind of pricey and we told her that she would have to raise half of the money herself. Since she’s a very accomplished cook and baker, she’s going to sell some tasty baked goods to friends and neighbors during the holiday season.

Earlier today she started to gather her recipes together and created a brochure using Microsoft Publisher. She asked me how she would go about setting prices and I told her that she could price them based on value to the consumer or as some multiple of her ingredient and labor costs. This particular 15 year old wasn’t quite sure what I was talking about, so we set about to figure out her actual costs.

This turned out to be more complex than I would have ever imagined, but we managed to do it to a high degree of accuracy using a Google Docs Spreadsheet, a few online reference sites for unit conversion, and two online shopping sites (Amazon Fresh and Safeway).

We created a new spreadsheet and set up a pair of tabs, one for an ingredients list and another for recipes:

Then we populated the Ingredients tab with information about the ingredients that she’d need. We had to do a bunch of math to convert the weights and measures of the packaged ingredients into the equivalents used in the recipes. The rec.food.cooking FAQ and the Sugar Conversion Calculator were both very helpful. It is always odd to see how recipes interchange weights and volumes so often. Why is flour sold by the pound and used by the cup? Anyway, here’s what it looked like:

In order to simplify the cost calculations we assigned a named range to each value in the Recipe Unit Price Column::

Then we switched to the Recipes tab and entered a recipe (Martha Stewart’s Pumpkin Cookies With Brown Butter Icing) to try it out:

We then used the named ranges to calculate the cost of each ingredient:

Summing up the column gave us the total cost (we decided to ignore the costs of seasonings and spices for this costing exercise). Bianca was intrigued and decided to compute her unit cost, set some prices, and even figured out her profit. She had never used a spreadsheet before (what do they teach kids in school these days?) but caught on really quickly:

Not bad at all for an hour or two’s worth of work. Bianca now has a very valuable pricing tool at her disposal and she’s going to be better equipped to run a real business of her own someday. Frankly, I was surprised that the ingredients for Pumpkin Cookies With Brown Butter Icing will cost $7.96, but I am relieved to know that this works out to just 13 cents per cookie.

She’s starting out local and isn’t ready to ship goodies, but if you are in Redmond or Sammamish and would like to buy some delicious baked goods for the holidays, drop me a note (via a blog comment) and I’ll send you a brochure.