<>

Harnessing the Power of Spreadsheets

In class on Monday we took a quick look at how spreadsheet applications like Excel can make quoting easier and more responsive to “market volitility“. Unless you use equations and structure your spreadsheet carefully, the only benefit your deriving from these applications is penmanship.

I’ve made a sample of a spreadsheet using Google Doc’s Spreadsheets application that details production costs and profit margins for a hypothetical product. I haven’t distinguished between retail and wholesale in this example, but this spreadsheet not only tells you how much it costs to produce a good and how much profit can be expected, it also provides insight as to what is expensive about this product, what is inexpensive, and where costs might be cut. The relationship between profit and production costs are also offered in two ways: one has the retail price of the item determined as a factor of profit x production cost, the other assumes a target retail price and figures a profit margin based on the production cost relative to the price. I’ve also created a pie chart on this spreadsheet that displays cell values in a graphical breakdown of the production costs.

I’ve shared the document with you all, so you can edit the document and see how the different variables affect the results. You should also be able to “save a copy” as a starting point for your own use. Look for the little yellow triangles in the top right corner of cells for an explanation of what the various tables do and how they function. Keep in mind that many of the cell values are equation driven. One useful equation is to equate the value of one cell with another. For example: If I have an hourly rate of $20 per hour, I can make one “master” cell that can be linked to from multiple other cells. Every cell that I need to input my hourly rate is linked to this master cell rather than inputting the value directly. When I make a change to this master cell, all linked instances reflect this change.

Here is the editable copy of the spreadsheet I’ve shared with everyone (you might need to sign into your risd google apps account to view it):
https://docs.google.com/spreadsheet/ccc?key=0AjS3fbMAPm9WdDk5ZHNKZjAxNjFqQkFncG1RTGt0MVE#gid=1

Here is a public copy that can not be edited (in case you are having trouble accessing it):
https://docs.google.com/spreadsheet/ccc?key=0AjS3fbMAPm9WdG9tQjhTbG15VzQ3S0poVGpOdVpuSEE&hl=en_US

One response to “Harnessing the Power of Spreadsheets”

1. Matthew Kulp says:

   Oct 23, 2011 at 11:03 pm

   Thanks Chris. This spreadsheet is a really informative document. Thanks!

   Log in to Reply