Splitting Costs between half hourly time periods

During each half hour, you will be using energy for heating, lighting, other equipment etc., so is it fair to factor all that in to your calculations for the production of your widgets?

Also, it would help if you supplied a sample workbook (the FAQ describes how to attach a file to a post), so we can see how your data is laid out. I presume you have the half-hour data on one sheet and are using another sheet for your calculations. The cost of electricity for such supplies usually involves a different rate for day and night usage, plus standing charge, plus capacity charge, plus reactive power, plus CCL, plus VAT etc, so you will need to supply the rates for each of these. I think you will need more than one column to arrive at the cost.

Hope this helps.

Pete

Hi,

I've already done the calculations for all the different rates, standing charges, CCL and capacity charges and included this in the total cost per half hour.

I agree that there are other costs incurred for heating, lighting etc however the actual product we make consumes a lot of energy and the majority of the cost is attributable to the main production area.

Once I have this calculation sorted I will be able to split out the energy usage by department/machine so this is not an issue yet.

I've now attached a copy of the basic data I am working with.

Thanks,

It is more usual for HHD to have the half-hours going across the sheet and have the dates on different rows - then when you get data for another month it can just be added below the previous data.

Also, in your example, the costs cover September, but the production data is for October.

Pete

I agree that data is usually formatted like that however I have been playing around with different layouts - if it makes the calculation easier then I can easily change the sheet layout.

As I mentioned above Pete, I just whipped a very basic spreadsheet together for this post to give an example. Just assume all the dates are October to make it easier.

Okay, will all the production runs be of about 30 minutes, i.e. less than than 1 hour? That makes it easier if it is, especially if it only spans 2 successive HH periods.

I'll play about with it and see if it is better in the format you have at the moment compared with HH across and dates down.

Pete

Yes, no product should take longer than 1 hour. If it does, I can manually calculate it as it would be a rare instance. I would prefer it in the format I have sent to you but if it is easier to adjust the format then please do so.

Thanks Pete,

Well, one thing I've just noticed is that your times are discontinuous, as you have put the night-rate times at the bottom of the sheet so that the sheet starts at 7:00am. Of course, your first production run starts at 6:45am, so this causes some problems. I'll probably just move the the rows so they are in the correct sequence.

Incidentally, are you likely to have a production run which spans midnight, i.e. starts in the period 11:30 to midnight one day and finishes between midnight and 00:30 the next day? If so, this will have to be treated as a special case.

Pete

Sorry, I should have mentioned that we operate a 24/7 operation. We have a 2-shift pattern each day, the first shift starting 07:00 until 19:00 and the second shift 19:00 until 07:00. We class the start of a day as 07:00 and the end being the following day at 07:00.

So in answer to your question, yes we are likely to have a production run which spans midnight.

With regards to the 06:45 run, that product will have been handed over on the shift change.

I am happy to treat any product runs that pass through midnight as a special case - there should only be one a day which will be minimal effort.

Thanks,

If you have the data set up as a discontinuous range, then there would be two special cases - if it starts in the period 6:30-7:00am then you need to consider the next period on the same day (but at the top of the column), whereas if it starts just before midnight you have to consider the next period on the following day (which will be part-way down the next column). Both could be done via formulae (i.e IF... IF ..., ...), but the expressions will be quite complex so it would be better not to introduce this complexity and to have the times starting at 00:00am.

Another consideration is that if you have a production run of, say, 45 minutes, then this could span 3 periods, as you might have 5 minutes in one HH period, then the whole of the next period, then 10 mins in the third one. Obviously, a run of more than 60 mins could span 4 periods by the same logic.

I think I'll lay the data out in the more conventional way, i.e. times going across starting at 00:00 and dates going down, and then put something together to suit that arrangement.

Pete

OK Pete, do what you need to do to get the formula to work. As I said, the layout is not particularly important and can be easily modified.

Thanks,

I think the attached file does what you were after. I've made the adjustments to the data layout that I mentioned before, i.e. having the HH periods going across starting with 00:00am, and having the dates going down the sheet in column A.

I've split the calculation into separate helper columns for each time period, with another helper column (L) identifying the first time period. This way I could easily account for a fourth period for longer production runs, so you should not need to do any manual adjustments for these. You will still have to adjust for runs close to midnight, however, but I think a relatively easy way to cope with this is to copy, say, the first 3 periods to the right of the HH data, offset upwards by one day, so that effectively the day continues into the next day (I've done this for midnight 1/09/12). Then all you will need to do to carry out the adjustment is to manually type in the appropriate value for the part-run after midnight - shown in red on a yellow background in the example.

Anyway, give it a test run and see if it works with some real data.

Hope this helps.

Pete

Pete,

That is great! Just what I was looking for! I've received your mail and you should be able to post the final formula now!

Thank you for all your help!

Well, it isn't actually a formula - it is to do with how to cope with run-times that span midnight. All you need to do is to ensure that the finish time is entered as 24:15 (in the example, do this for cell H14) rather than as 00:15. The cell will still show 00:15, but now the formulae calculate the correct values post-midnight (well, as long as you copy the first 3 HH costs from the next day, as advised above). So, the workbook that I attached on Friday night will now cope with production runs that span up to 4 HH periods, as well as runs which span midnight.

If you now think that the problem has been solved, please mark it as such (the FAQ describes how). Also, you can pass on your thanks more directly by clicking on the "star" icon in the bottom left corner of any post that has helped you.

Hope this helps.

Pete