How to visualise the costs per unit of several energy providers?

StephenBall

New Member
Joined
Nov 24, 2013
Messages
2
Hello,

energy providers usually charge a monthly fixed rate and a flexible consumption-based rate. Some only charge the later. This has implications for the overall costs based on projected consumption.

I want to enter the the fixed and flexible rate into excel and then get a graph that show for much consumption I would be charged which amount.

For example, one provider charges:
10,- EUR/month & 5.26 ct/kWh

Another provider charges:
0,- EUR/month & 7.13 ct/kWh

I want to see two lines in the graph representing each provider and how the overall price increase in line with increasing consumption. Let's say from 0-15000 kWh.

How do I have to configure excel in order to get such a graph?

Many thanks for your help.

Please excuse my use of non-technical language but I am new to excel and don't have the vocabulary to describe it in a better way.

Anyway, your help is much appreciated.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
One way:

A​
B​
C​
D​
E​
1​
Provider A​
Provider B​
2​
Cost per montk
$ 10.00​
$ -​
3​
Cost per kWh
$ 0.0526​
$ 0.0713​
4​
5​
Usage​
Price​
Price​
6​
400​
$ 31.04​
$ 28.52​
B6 and across and down: =B$2 + $A6*B$3
7​
600​
$ 41.56​
$ 42.78​
8​
800​
$ 52.08​
$ 57.04​
9​
1000​
$ 62.60​
$ 71.30​
10​
1200​
$ 73.12​
$ 85.56​
11​
1400​
$ 83.64​
$ 99.82​
12​
1600​
$ 94.16​
$ 114.08​
13​
1800​
$ 104.68​
$ 128.34​
14​
2000​
$ 115.20​
$ 142.60​
15​
2200​
$ 125.72​
$ 156.86​
16​
2400​
$ 136.24​
$ 171.12​
 

StephenBall

New Member
Joined
Nov 24, 2013
Messages
2
Thank you for this excellent suggestion. Is there away to turn this numerical representation into visual graph with $ on the vertical and kWh on the horizontal axis and then to have lines or bars (I prefer lines similar to a stock exchange).

As provider A has high fixed costs (120/year) there will come a point when provide a will become cheaper than provide B. I would like to be able to see it as the point where the lines of both providers cross. However, I don't know how to create such visual outputs with excel.

Many thanks for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,719
Messages
5,446,127
Members
405,383
Latest member
mccordscvs

This Week's Hot Topics

Top