MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Setting up analysis formula involving temperature change

Posted by JB on February 28, 2001 7:35 AM

A piece of outdoor equipment was modified. I need to analyze the savings in utility cost as a result of the changes. What makes this difficult is the outside temperature also affects the utility cost. We have tracked utility usage for 4 months prior and 2 months after the change in equipment. I am attempting to calculate the savings in utility costs as a result of the changes in equipment separate from the cost of varing weather temperatures. Needless to say, the warmer the day, the more electricity required to cool the product. I essentially need to calculate savings excluding those costs attributable to change in outdoor temperatures. Can anyone help?

Posted by GregA on February 28, 2001 8:07 AM


You don't have a lot of data to go with (I am assuming that your utility costs are monthly, so you essentially have 4 data points?).

What you would like to do is identify the relationship between electricity and temperature. You would then calculate the "baseline" cost using the relationship you identified, and compare that with the actual cost. If nothing else changed, the difference would be the impact of the modifications.

However, it will be very difficult, with only 4 data points, to determine the relationship. You could try plotting them on an X-Y graph and see if they make something close to a straight line. If they do, you can use the Tools>Data Analysis>Regression tool to determine the relationship.

THe other thing you need to consider is that with only 4 months of past data, you probably don't know have a full spectrum of temperatures to be able to compare -- the relationship between temperature and cost may be different at summer temps and winter temp ranges.

Your best bet is to try to dig up some additional past data, or contact the equipment manufacturer and see if they can help you.

For one, it is most likely not linear (not a straight line).