A little help with new spreadsheet please?

Voltimax · Feb 4, 2011

Hi all,

I'm working on a new estimating spreadsheet. I've got the nuts and bolts of it together, but I could do with a little help.
Part of the spreadsheet will have ready made tasks or points. What I mean by this is common tasks where all the materials and labour allowances are bunched together. For example a double socket (flush) point. 8-10 mtrs of 2.5mm 6242Y, 2 mtrs 25mm oval (or channel), 1 x 2gang flush metal box, 1 x double socket face plate. You get my point?
Obviously most of these are easy, a single socket point, TV point, switch fuse spur point, cooker point and outlet, 1gang switch point, recessed light point etc.

Other more involved tasks/point would be say an inline shower fan with light and timer. These will have more component parts etc. So since It's hard work sitting here trying to think up all the points already mentioned, could anyone suggest other common tasks I may not think of and list the items.

There will be a reward here. Anyone that helps me out will receive a copy of this. I'll have done all the hard work putting it together all you have to do is make a few suggestions.

Just so you don't think this is pie in the sky, here are some screen shots of what I already have. This is no one page estimate spreadsheet. This is going to be quite big by the time I've finished and even then I'll most likely try to improve it.
Anyway, some screen shots:

Voltimax · Feb 5, 2011

39 views, yet no suggestions.
This reminds me of the last time I offered to share something I was producing.

johnboy6083 · Feb 5, 2011

im just writing things down mate to see if i can help you out (and me too, if i get a copy).do you want ideas for commercial things too, ie tray and unistrut?

johnboy6083 · Feb 5, 2011

CU= grommets, sleeving, testing, blanks, labelling, CU itself, MCB's (if not included), tails and earth

new supply to garage/shed: 4way CU, 1x light fitting, 2/3sockets, 15m 1.0mm for light, 20m 2.5 for sockets, say 30m 6mm2 swa, 1x glandpack(if TTing shed allow 1xrod, 1x adaptable box to seperate earth, and say 5m of 6mm g/y for the connection to rod)

bonding= tenby clamps, say 15m 10mm g/y

tray= for every 3m = 3x brackets, gutterbolts, joiners
unistrut brackets = width of tray x3 for every 3m length (x 4 if light duty or heavy loading) also zebs and penny washers

per rewire add enough on price automatically to cover screws, plugs, sleeving, drill bits ect. another idea would be to add a percent or 2 to cover these things, that would mean the larger the job, the more youve allowed for consumables.

ill keep adding if i think of anything.

Voltimax · Feb 5, 2011

Thanks johnboy, that's the kind of thing I'm after. To answer your question about commercial, at the moment I'm covering only domestic, but no reason commercial stuff can't be added. I see this spreadsheet as something that will get added to over time.
With regard to sundry items, screws, grommets, wall plugs etc, personally I add a very small percentage of the overall material cost to cover this, rather than try to list every last little item, so we're on the same page I think

Festerfly · Feb 5, 2011

great start you've made on that, very impressed!

Have you preset a labour time per item/job on the S/Sheet? If so how would that be affected say if you were doing a job in either a New build with stud walling or a 30's semi with solid Brick etc?

ukpablo · Feb 5, 2011

i already have one that somebody gave me, if u PM me i can send u a copy, may be quite useful to u? has everything on it and u could use some of the stuff off it?????

Voltimax · Feb 5, 2011

Festerfly said:
great start you've made on that, very impressed!

Have you preset a labour time per item/job on the S/Sheet? If so how would that be affected say if you were doing a job in either a New build with stud walling or a 30's semi with solid Brick etc?

Glad you asked, because I thought of that myself last night.
There isn't a screen shot above to see, but one of the sheets calculates your hourly rate based on your overheads and that's what the spreadsheet uses. What I've added in last night is a difficulty factor based on a percentage of the labour time. So for instance say it takes 3 mins to clip 1 mtr of cable, you can set a percentage to add to that based on how difficult you think that task will be. I'm unsure as to whether to have that facility on all individual items, or just as a whole on the summary sheet. What are your thoughts on this? Any suggestions?

ukpablo · Feb 5, 2011

looks good by the way, i have sent the spreadsheet to u! good luck

Voltimax · Feb 6, 2011

I thought there was a strong possibility I already had that spreadsheet and I was right, but thanks for sending it to me pablo.
The spreadsheet I'm making is very similar, the one you have is a spons one that used to be for sale on ebay. Mine will include material prices, which that one doesn't, it's labour only. Must have took the person who made it a long time though, but mine will have even more. It won't be perfect, but it's a step in the right direction I'm sure. The spreadsheet is the amalgamation of many ideas taken from other spreadsheets, including construction and American electrical spreadsheets I've obtained.

ukpablo · Feb 6, 2011

cool, like i said if u need any more help just let me know. i did send off for electrika's free software, but was too complex for what i need. Instead of lets say breaking down each component part for a standard CU change, u would only need a column for the set price u charge as u know what bits u require
ie cu x1 set price £390..........hrs x 8 etc.............then any extra bonding could be added with length in metres, man hrs with installation method etc!
just a thought anyway.

chiplard · Feb 6, 2011

Funnily enough I have just started work on my own spreadsheet. Long way to go before it looks anything like yours i hasten to add!!!!

Have you thought about the cost of diesel getting to the jobs?

With regards to the sundries i have a column that automatically counts up the number of sockets, light switches fans etc etc and adds a price of 50p per accessory. A lighting circuit with wagos would obviously be a lot more than this but a socket/switch would be considerably less.

You have gone into more detail with regards to the labour charge than me. I have just used an hourly rate and guestimated the time it would take to do a certain job. Again, some jobs you will gain and some jobs you will lose but it does average itself out.

Good luck with yours, looking good already!

Voltimax · Feb 6, 2011

Well the sheet that is labeled 'points' will have set tasks. There is no reason why they have to draw from the other sheets. Like a consumer unit as per pablo's suggestion.
The points and Tasks sheet will be the biggest headache for me, that's why I've asked for hep.

Chiplard, that's a good idea for sundry items and can easily be added into what I already have.

Guys, keep the suggestions coming, it's amazing what I can miss in the wee hours *yawn*
I may not be able to do everything that is suggested (No VBA skills in Excel I'm afraid), but I'll try.
Like I said, anyone that contributes will get a copy.

Amp David · Feb 6, 2011

With the comment regards the costs for fuel and trravel, I tend to use 2 or 3 rates for jobs that are local, jobs that are upto 15 miles and then anything upto 30 miles.

So you could include different rates, be it hourly or daily.

Excel gives my brain a good hiding when I use it to do my books, never mind trying to create some as complexed as you're doing. Good luck and hope you manage to finish it.

shakerbess · Feb 6, 2011

hi i think its cool wish i had the brains to do something like that lol could i get a copy off somebody would be great thankyou wish i had an idea to put forward

shakerbess · Feb 6, 2011

well cool mate

ukpablo · Feb 6, 2011

ive pretty much sussed out that around the £3 mark per wagobox with connectors will more than cover it for JB's, diesel u could do 50p per mile lets say, it will keep it simple too.!

SirKit Breaker · Feb 6, 2011

What about a one off box just to cover all the bits and bats that get overlooked, screws, grommets, rawl plugs, junction boxes, wago's, helacons, bonding clamps, plugtop fuses, sealant, expanding foam, cable clips, and other stuff i cant think of. I put on a sundry charge for all this stuff of £25 on a rewire and £3 on a run of the mill job.

Cheers...Howard

SirKit Breaker · Feb 6, 2011

Voltimax said:
Well the sheet that is labeled 'points' will have set tasks. There is no reason why they have to draw from the other sheets. Like a consumer unit as per pablo's suggestion.
The points and Tasks sheet will be the biggest headache for me, that's why I've asked for hep.

Chiplard, that's a good idea for sundry items and can easily be added into what I already have.

Guys, keep the suggestions coming, it's amazing what I can miss in the wee hours *yawn*
I may not be able to do everything that is suggested (No VBA skills in Excel I'm afraid), but I'll try.
Like I said, anyone that contributes will get a copy.

Ive got the City&Guilds computer applications qual, if you struggle with any excel formulas PM me and i will see if i can help.

Cheers...........Howard

beaver74 · Feb 6, 2011

have you got a place where you can up date prices as they increase so as the per item price will increase as materials do

Voltimax · Feb 6, 2011

SirKit Breaker said:
Ive got the City&Guilds computer applications qual, if you struggle with any excel formulas PM me and i will see if i can help.

Cheers...........Howard

Cheers Howard
I'm okay with formulas, done some long and complicated ones in my time. I'ts VBA I'm no good with. When things get really complicated it's time for VBA, so I'm trying to steer well clear of it.

Sticking different rates and fuel expenses in is child's play, no problems there, it can be done. As I've said I'm really looking for set tasks that can be added to the tasks sheet, these can be modified later on by users. Consumer unit replacements, shed supplies, changing an existing light fitting, adding an extra socket etc. Basically these will be tasks you've done before. When I do small jobs, I usually keep a record of what was used and the time it took, so I can refer back when something similar comes along. I wanted this all in a spreadsheet because it's all there in one place.

Something else to mention. I've been looking at open office instead of Microsoft excel. The reason is that it has a facility to tie up a database with the spreadsheets, so alot of info can be stored there instead of on the spreadsheet. This will make the spreadsheet smaller. The other reason is that open office is free to all!
What are your thoughts on this?

Voltimax · Feb 6, 2011

beaver74 said:
have you got a place where you can up date prices as they increase so as the per item price will increase as materials do

If you look at the screen shots you will see all the items on different sheet tabs. The tasks are linked to these sheets, so once you increase or decrease the item prices, the tasks automatically do the same. As I said above though, I'm looking at using open office since I can create a database to hold product info. open office calc (excel) allows you to open a database through the spreadsheet and you can drag and drop products straight into the spreadsheet.

ukpablo · Feb 6, 2011

i have some rewire / survey forms on open office. seems a really good application software.
i will sit down tomorrow and have a good think of some tasks.
ie installing an earth rod for tt system etc, installing down lighters, lifting and replacing insulation in attic, extractor fan ducting etc. also fitting smoke alarms, time for certification etc, it all eats into ur day or evening and is taken for granted by customer and costs money! as already stated, a tick box for consumables per job is a good idea to cover sleeving, screws, gromets, plasterboard fixings, etc.

Von Vardo · Feb 15, 2011

This seems really interesting, I'd love a copy if possible!

Have you thought about including home networking points? Outside sockets, security floodlights, hard wiring swimming pool pump, garden pond pump.

Voltimax · Feb 16, 2011

Hi all, just an update. This is becoming a really difficult project to complete. I've changed the layout a couple of times now, since I'm never happy with it.
I'm trying to find a way of letting people put in their own project costs in, because it's taking too long and their are so many variations to the same job.
Standard points for new build and rewires are doable but beyond that it just gets very complicated and the size of the sheet becomes immense.
I'll keep plodding on with it and keep you posted.

Mark.W · Feb 16, 2011

Voltimax

I will have a quick look, I made something similar a while back now, it is exactly what you are looking for. I will see if I can dig it out!

Voltimax · Feb 16, 2011

Mark.W said:
Voltimax

I will have a quick look, I made something similar a while back now, it is exactly what you are looking for. I will see if I can dig it out!

Thank you very much Mark, that would be a big help. I've sort of done it with a macro, but it's not very stable.

ukpablo · Feb 18, 2011

hope it gets their for u!..........or should i say u get their with it!!! lol

shakerbess · Apr 9, 2011

Voltimax said:
If you look at the screen shots you will see all the items on different sheet tabs. The tasks are linked to these sheets, so once you increase or decrease the item prices, the tasks automatically do the same. As I said above though, I'm looking at using open office since I can create a database to hold product info. open office calc (excel) allows you to open a database through the spreadsheet and you can drag and drop products straight into the spreadsheet.

hi i was hoping would i be able to get a copy when you finished please

Voltimax · Apr 9, 2011

shakerbess said:
hi i was hoping would i be able to get a copy when you finished please

Well the point of this thread was to HELP with this. Since I haven't received much help and it's a lot of work, I wouldn't get your hopes up.
I'm sorry if this sounds harsh, but unless you want to help, you won't be getting a copy. I'm not here to make things for people who don't want to input anything.

ukpablo · Apr 9, 2011

how is it going??
anymore i can do?

gutterball · Apr 10, 2011

have a look at this add on for excel ASAP Utilities - The essential add-in for Excel users. FREE excel tools and macros to save time. Download Excel tools - Excel softwareit might help you out.

baldelectrician · Apr 10, 2011

I tried something along these lines years ago and found it was a waste of time- you would get it all set up and there was a change in price

I just charge labour and materials, and call up the wholesaler just before I invoice to check the prices (if I haven't had the line in the post).
If you give too much detail to customers they will start asking questions like why was this shower only £95 and the last one was £345.
When you break it down to labour and materials it is self explanitory.

For recurring jobs I save the excel spreadsheet for that job to allow me to return to it later on- it also allows me to compare the prices

Voltimax · Apr 10, 2011

As much as I hate to admit, baldy is right. I've made a whole mess of different spreadsheets and there isn't one formula that does everything I'm afraid. I hate to think how many hours I've spent on this and now work has picked up, I just don't have much time to spend on it.
I may come back to it, but for now it's on hold.

ukpablo · Apr 11, 2011

fair play bud, i have just started using sliq tools. only £29 and a free 30 day trial. i have spent a couple of hours inputting basic everyday materials in and it is pretty quick to use. U can also use it to print quotes and invoices from same software and to email the customer with quotes..........all very quick when doing estimates. check it out bud?!

Voltimax · Apr 11, 2011

Cheers pab, but I've been using it now for quite some time. I must admit never for quoting though. The trouble is for me, it's too basic and doesn't give me (not the customer) enough of a breakdown. I guess it's a personal thing.

By the way, thanks for the input you gave. PM me your email and l'll send you a little sheet I use

-

Attachments

-

-

-

-

Festerfly

ukpablo

-

ukpablo

-

ukpablo

chiplard

-

-

-

-

ukpablo

SirKit Breaker

SirKit Breaker

beaver74

-

-

ukpablo

Von Vardo

-

-

-

ukpablo

-

-

ukpablo

-

-

-

ukpablo

-

Similar threads

OFFICIAL SPONSORS

Advert

Daily, weekly or monthly email

Thread starter

Thread Information

Thread Tags

Advert

Thread statistics

Advert