In a perfect world we would have every single piece of IT gear we own in a database with it’s own footprint and power consumption rates.  This program would easily calculate what our total power consumption is.  With some additional data, the cost per Kilowatt hour, it would tell us what we spend every month on our power bill.  Well this is a pretty simple spreadsheet we are creating so it won’t do all that for us, but we can get pretty close to the real numbers.

The first thing we need to do is establish our asset inventory.  To do this we will add another sheet to our spreadsheet.  As you will recall from previous lessons, we can right click on the bottom tabs of our workbook to bring up a menu.  We want to use this menu to add a new sheet, or if you still have an unused sheet 3 to rename that to something like “Asset Inventory”.

Once the sheet is added or renamed, we need to set up our column headings.  You can add any additional columns for data that you want to track but the minimum are listed on the sample screen shot.

I know this seems like a lot of data to add for each asset, but if you have a lot of devices, you are probably already using some sort of asset tracking system, in which case you can export from that and copy it into your financial model.  If you are not using a system already there are tools that can help you capture this data.  If you don’t have a huge number of assets it should not take to long to input all the necessary data.  The important thing to note is this: The More Accurate the data you input, the more accurate your financial model will be! There is no getting around this.  The reason we base our finances on an asset inventory is that we gain extra flexibility and granularity in our modeling.  Also, any good financial management package will track this way and as I have said before, we can import our spreadsheet into whatever package we use.

So now let’s enter some assets.  I will just enter a few to get started then we will move on to the power calculation step.

Remember the power entires are in Kilowatts so a value of .5 is 500 Watts.  Most of these specifications can be found on manufacturers websites or in the product manual.  I suggest going with the fully loaded system value as that is the maximum the system will draw when loaded with disk, memory and cards.  This will create a worst case cost scenario.  Another option is to connect an induction meter to the power lines and check the actual draw.  If you have a newer system and the proper management tools, you can get an accurate read on the power from there.

Once we have our assets in the spreadsheet we can do some simple math on the column to get a total.  We will also set up a section the will multiply the total to figure out the cost per day and cost per year.  Then we will add another total to add in the overhead for air conditioning.  The following screen shots will show the cells with the formulas.

So far so good.

Next we add our cost of power and a total daily cost.  The cost of power can be found  on your power bill.  If you don’t have access to it, you can find some information for your state and area on the Department of Energy website at www.doe.gov.  Searching there will lead you to all sorts of useful power related information.

Next we will add the total annual cost, which is cell F10*365.  And finally we will add the total with Air Conditioning.  The easiest way to figure out the AC cost is to multiply the total times .65 (65%) as this is a commonly accepted number to use.  The next screen shots will show this.

The last thing we need to do is add this detail to our summary sheet.

and heave the spreadsheet calculate the monthly total for us.  This is once again by dividing the annual cost (cell D5) by 12.

That’s it for today.  We now have a summary sheet that displays two of our recurring costs, staffing and power.  It is interesting to note, if you calculate the numbers for the long term (for example multiply the annual total by 5 for a 5 year look) the cost of powering and cooling a device starts to approach the cost of the device itself.  Of course these become our opportunities to find savings.