Electricity Acc. Formula

[country rose]

Hi Guys

I have an electricity invoice which I would like to set up - the invoice is as follows :


No of days = 31days
Prev. Reading = 99966 Current Reading = 101418 Difference(Consumption) = 1452


Network charge @ 3.52 p/day for 31days = 109.12
Energy charge(0-600kwh) 600kwh @ 0.8208 = 492.48
Energy charge(> 600kwh) 852kwh @ 1.296 = 1 104.19
Sub Total 1 705.79
Vat at 14% 238.81
Total 1 944.60


The tricky part is Energy charge(0-600kwh) 600kwh @ 0.8208 as if the energy consumption was below 600kwh, lets say 500kwh, then the invoice would need to calculate 500kwh on the line with the (0-600kwh) and if it is more the 600kwh, as shown above, then the calculation needs to take the consumption figure and reduce it by 600 and then show the difference on the line(> 600kwh) and work out the amounts.

Would anyone like to have a crack at working out a formula for the above.

Many thanks
Dave

 

[FormR]

One possible way:

Excel Workbook
	A	B
1	No of days	31
2	Prev. Reading	99966
3	Current Reading	101418
4	Difference(Consumption)	1452
5
6
7	Network charge @ 3.52 p/day for 31days	109.12
8	Energy charge(0-600kwh) 600kwh @ 0.8208	492.48
9	Energy charge(> 600kwh) 852kwh @ 1.296	1104.192
10	Sub Total	1705.792
11	Vat at 14%	238.8109
12	Total	1944.603
Sheet1

 

[country rose]

Thanks for the well set out reply , if i can ask one more question :

Basically I have to read 8 different electricity readings for 8 tenants so would like to create an invoice that will minimise the errors and workload, what would help a lot is if I could enter the monthly data into two cells which could give the end Total of the invoice - the two fields that change on a monthly basis are :

The Reading Dates(Current) Reading dates are : 2014/02/08(Previous) - 2014/03/11(Current) Number of days = 31

The Meter Reading Consumption figures(Current) Previous meter consumption is 99966 - Current meter consumption is 101418

In the above example could one have a formula that would work out the number of days if the current date is inserted, so the formula must be able to use the previous dates to do this - the Consumption figures would also need to be worked out in the same way

So would be great to have the total amount of the invoice after inserting the Current reading date and the Current meter reading.

Many thanks
Dave

 

[subrosumon]

if you use a defferent cell for different rate, vat and otters it will better for you
net unite= current reading - last trading

 

[FormR]

It's not all that clear to be honest, but maybe something like this:

Also on dropbox:

https://www.dropbox.com/s/hpzlkwj1cw1l4je/766760-electricity-acc-formula.xlsx

Excel Workbook
	A	B	C	D	E
2	Month	Jan	Feb	Mar	Apr
3	Previous Reading Date	07/01/2014	08/02/2014	11/03/2014
4	Current Reading Date	08/02/2014	11/03/2014
5	No of days	32	31	0	0
6
7	Prev. Reading	98466	99966	101418	0
8	Current Reading	99966	101418
9	Difference(Consumption)	1500	1452	0	0
10
11	Network charge @ 3.52 p/day for 31days	112.64	109.12	0.00	0.00
12	Energy charge(0-600kwh) 600kwh @ 0.8208	492.48	492.48	0.00	0.00
13	Energy charge(> 600kwh) 852kwh @ 1.296	1166.40	1104.19	0.00	0.00
14	Sub Total	1771.52	1705.79	0.00	0.00
15	Vat at 14%	248.01	238.81	0.00	0.00
16	Total	2019.53	1944.60	0.00	0.00
Sheet1

 

[country rose]

Hi, thanks very much for the info, will try this out and test it.
Dave

 

[country rose]

Hi
On testing the formulas in cells C5 and C9 it will not accept the formula saying the Formula Contains an Error.

Amsure it is the , and "" contained in your formula - any suggestions on how to rectify?

Thanks
Dave

 

[FormR]

Hi, did you try downloading the example file from dropbox?

Also on dropbox:

https://www.dropbox.com/s/hpzlkwj1cw1l4je/766760-electricity-acc-formula.xlsx

 

[country rose]

Hi
Have downloaded from dropbox, the following error comes up in C5 #VALUE! or else all the other formulas are working.

Any reason why, sorry to be a nuisance but my knowledge of Excel is very basic.

Thanks
Dave

 

[FormR]

the following error comes up in C5 #VALUE!

That would suggest that you have not entered a valid date in cell C4.