All
I have shared before and some of you are using my templates.
Here is the latest updated Blank worksheet.
This worksheet is for figuring small jobs that don’t fit into a category.
I have entered materials and labor for a small 2×4 wall and sheetrock job.
On the materials side you can change contingency, taxes and markup by changing the percentage in the yellow cells.
On the Labor side you can accept the labor rate as figured or you can markup labor by changing the percentage in the yellow cell.
I keep the estimating time at 1 hour and the billing time at 1/2 hour as a default setting. If the job is more complicated than that then you should adjust the hours.
I keep other sheets for decks, garage, window replacement and so forth with a list of the pertinent materials, sequence of tasks and pricing.
Other worksheets can be built off of this simple format.
I deleted this attachement go to post # 10 where I put an updated version.
Rich
Edited 8/17/2009 1:45 pm ET by cargin
Edited 8/17/2009 1:46 pm ET by cargin
Edited 8/17/2009 1:47 pm ET by cargin
Replies
I like it. Short and to the point. ONe of these days I'l convince myself to use a short one like that (or steal yours :) ). I still use my large project 27 page estimator even if it is really small. I guess I'm just used to it.
Drew
I put it up there for people to use. So steal away.
We do alot of things that are unique. So this is the worksheet I use the most.
I have a dozen different worksheets tailored to specific tasks like window replacement, basements and bathrooms.
But this one gets used the most.
I'm glad you liked it.
Rich
I was hoping to see your worksheet for future use I want to be as well prepared as I can, BUT I couldnt open it it says there is an error. would you care to post it again? I think it might be the .xls thats causing the problem maybe? Thanks
danno
Email me and I'll send it to you
Rich
Danno
I'll post it again
With this worksheet you can change the tax, contingency and markup by putting in a different % in the yellow cells.
Same with the Labor change the yellow cells to mark up labor by a %.
If you need more rows, and you will then highlight the number of rows (above the totals) you need and then click on insert, click on rows.
Rich
What does 10 % "conting" mean? I think I asked you that once b/4 but it's been awhile.
Is that like what I'm calling an Administration fee.... I'd ask for for managing each sub on top of my mark up?
Also..how did you do the percentage calc function.
If I wanted to say do 20% Mark ups to each "row" ?
ie row: Plumbing (next cell) $30,000 (next cell) 20% (next cell) $6000
Just took the Dummies book out of the library again so I don't have to keep going back'n forth on the puter looking at instructions...although I could split screens being I'm on Jerralds Mac...hmmm..
View Image
The Woodshed Tavern Backroom
The Topics Too Hot For Taunton's Breaktime Forum Tavern
Andy
10% contingency, sorry I was lazy and didn't know how to spell the word that day.
New sheet below.
I just highlighted c 16 thru c 20 then licked on % in the tool bar. Then I made them yellow to remind me that the percentage can be changed.
Rich
Andy
For that new sheet I posted in post #8.
You realize that the plumbers cost is getting marked up twice. Once in the regular column and then again with the 10% contingency, 7% tax and then 20 % markup.
Rich
All
New sheet below. Sorry for any confusion.
sheet in post # 8 was just to show Andy how to put % in a cell.
Rich
http://spreadsheets.google.com/ccc?key=0AjFOakDmOFH6dFNENm40eWk4UGsxdk8yNVJGSGcxMVE&hl=enHere's what I'm working on. Developing it as I go along.
Each row I added a percentage to I did individually in every row but it seems there should be a way to have that calculation of 20% or whatever it is already entered that I can pull out when needed?
I still have a lot to enter. This needs a lot of work I know but it's a first draft thats not the EXACT sheet I'll be using. It's all the right info(so far...I hope) but I'm using this "copy" so I don't screw up the one I'll actually be using.
In the end I'll go over every cell to be sure I didn't add too many zeros or not enough...lol.
Thats what scares me most not using the legal pad...lolView Image
The Woodshed Tavern Backroom
The Topics Too Hot For Taunton's Breaktime Forum Tavern
Andy
I can't look at the worksheet without loggin on with password.
I would love to look and give advice.
Rich
sent you a PMView Image
The Woodshed Tavern Backroom
The Topics Too Hot For Taunton's Breaktime Forum Tavern
Here's my attempt using Bobs' WB.
SamT
Sam
I like what you did with the worksheet.
You think upside down, with your totals at the top of the page. I may get to like that.
On some of my worksheets I copy a set of totals at the top and bottom of the page for convience.
I like the variables worksheet. It eliminates some unnecessary columns and numbers.
If I want to change the labor rate then I can just change one cell instead of doing fill and putting a number in every cell of a column.
One think I don't like, is labor and materials are one seperate pages.
The reason is that for every material (almost) there has to be a labor task.
In my sheets I like to have the materials on the left and the labor on the right.
Most everyone I know complies a materials list 1st and then does a labor estimate.
I think that is because it is easier to compile a list of materials, and in the process they think thru the tasks of a job.
But I am not opposed to studying new ideas.
I will look this over some more tomorrow.
I have not gotten into naming cells yet. Jerrald has been advising me to do this.
It's been a while since I have spent any serious time rewriting my worksheets. I modify them all the time with tinkering.
Thanks for the post. This is fun.
Rich
Hey....teach me a bit about naming them cells again. I think I'm ready for that step.
Jim
Hey teach me.
I just tried and failed. I read the instructions on MS Help and I still am not doing it right.
Rich
One think I don't like, is labor and materials are one seperate pages.
Just cut and paste, or insert, the column from one sheet to the other.
The real beauty of using names is that C&P won't break the workbook.
If you like doing material on the right and jobs on the left, cut columns B,C,and D from the jobs sheet and paste them into F, G, and H on the materials sheet.
I just tried it and all I have to do now is Insert cells into F1, G1, and H1 to align the Jobs working area below the Freeze Pane line.
Now rename Input_Materials to just Input and delete Input_Jobs.
Everything still works. SamTA Pragmatic Classical Liberal, aka Libertarian.
I'm always right! Except when I'm not.
Sam
I know I can C&P I was just discussing the way that the screen is ladi out in front of the user.
I like using for splitting up framing, bathrooms and roofing.
But I like to see material column close to the labor column.
I like to keep the sheet simple and what will fit on one screen, width wise.
I love the use of the variables.
Rich
Is naming cells the same as linking cells on one sheet to another?http://www.tvwsolar.com
We'll have a kid
Or maybe we'll rent one
He's got to be straight
We don't want a bent one
He'll drink his baby brew
From a big brass cup
Someday he may be president
If things loosen up
NoSamTA Pragmatic Classical Liberal, aka Libertarian.
I'm always right! Except when I'm not.
There are two types of Names; Workbook Names and WorkSheet Names.All the names in Sams_Improved_Bob_Breaktime_Blank_Worksheet.xls are Workbook Names. That means they can be used without condition anywhere in the Workbook. It also means that there can not be two identical names. Being lazy, hereafter I', going to use "SIBBBW.xls" to refer to this Workbook.
A Worksheet Name Has the name of the worksheet before the Name. Ex: Input_Materials!Extended. Note the exclamation mark between the two names. These can be "Duplicated" in the sense that any worksheet can have an "Extended" column, (or whatever.) when refering to a Wroksheet Name you have to use its "full" name, ie. Worksheet_Name!Range_Name.
Spaces are not allowed in Names and when using "Insert/Name/Create," Excel will automatically replace spaces with Low dashes "_". If you're typing in the Name, you can use any convention you like, but I always use the default "_".
You can see that some thought must go into choosing Names before you start making them. I usually layout the entire Workbook before I define any Names. However, when I do get ahead of myself, it's not that hard to edit and I'll go into that later.
Naming Ranges
Excel calls even one cell a Range, so if I use the term "Cell" you know I mean "Range" in Excel-speak.
Creating Names:Select your Cells, Choose "Insert/Name/Create."
View Image
Here I've selected 11 Cells and this will give me a 10 Cell Range named "Named_Column."
Here I've selected 6 Cells and this will give me a 5 Cell Range Named "Named_Range_2."
Editing Names:
View Image
This is the Excel Control used to edit Names.
Above is a Name that I Defined. I selected Cell C5, then chose "Insert/Name/Define" and type in the name I wanted in the top box. Notice that the Name is not the same as the Label in Cell A5. I thought that "Projected Material Cost" was more self explanatory than Projected Materials." Note that the Formula in the "Refers To" box includes the Worksheet Name.
If I wanted to have a Worksheet Name, I would just type the worksheet name followed by an "!"then Projected_Material_Cost
When editing a name to change it to a Worksheet Name, open this Control and select it. Then just copy the Worksheet Name in the "Refers To" box and Paste it in front of the Name in the top box under the "Names In Workbook" Label. Don't forget the "!".
The trick is that you have to "Add" the new Name. This will give you two Names almost alike. Delete the one that does not have the Worksheet Name after it in the main list of Names. All Worksheet names will have the Worksheet Name in a Second column in the list. In order to see the old Workbook name, you'll have to open the Control on another Worksheet. Excel!?!?
Workbook Names show up anytime you open the Names Control, but Worksheet Names will only be seen when you open the Control from the Named Worksheet. If you open the control on Input_Jobs, the Worksheet Name "Input_Materials!Projected_Materials_Cost" won't appear
Named Constants and Formulas
View Image
A Named Constant can be a Workbook Name or a Worksheet Name, but it seems more logical to me to use a Workbook Name.
View Image
A Named Formula, and no, I didn't check to see if the formula is correctly formatted. If I had "Add"ed the Name, Excel would have told me if it was no good.
'Nuf fer now. Any Q's?
SamTA Pragmatic Classical Liberal, aka Libertarian.
I'm always right! Except when I'm not.
Sam
Thanks let me work on it tonight.
Rich
Ahhh soooo... velly good.http://www.tvwsolar.com
We'll have a kid
Or maybe we'll rent one
He's got to be straight
We don't want a bent one
He'll drink his baby brew
From a big brass cup
Someday he may be president
If things loosen up
Named Ranges are really useful for Encapsulation. Huh? Wazzat? Encapsulation is what I did to SIBBBW.xls. I moved all the variables and constants to one sheet, all the intersheet calculations to another, the inputs, and the final estimate to still others.
Encapsulation is keeping all unneccessary items out of a sheet and keeping all similar items together.
Why? It makes things so much easier to maintain and upgrade.
I can now format the Estimate sheet to be printable and never have to worry changing it again, especially if I have a Client Info sheet where I put all his/her personal info. I can create an invoice sheet that ties payments off the Client info sheet to the estimate total and have semi-automatic billing system, again with all calculations performed on a seperate sheet.
If I reformat a sheet, I only have to worry about keeping one cell, the named sheet total, and even then I can rewrite that formula.
Named ranges really really make it easier to understand what a formula does when you look at it. Check out the formulas in cells D2:D5 of the attached "Input Sheet of SIBBBW.xls" Don' even have to figger out which cells it's talkin' 'bout, do ya??
A while back I was working up an Excel accounting system, and even now, without looking, I can tell you that if I wanted to know my total office expenses I can put "=Office_Expenses!Total" into a cell and Viola!
I can even do "=SUM(Office_Expenses!Computors_Total)" to see what one division totaled. All because I used lots of Named Ranges and Named Formulas. That particular formula, Computors_Total, looks like"=Computors_Repairs+Computors_Payments+Computors_ETC."
Each Named Column in the Computors section was listed in the Computors_Total formula. If I had to add a column, I just had to name a totals cell, use that cell to sum the column, then add that name to the formula, no thinking about which numbered range (EH8:EJ65536, or whatever,) was supposed to be there.
Looks like:Computor_Repairs$ 3,456.78 21.34 32.43 43.54
Tips:
A lot of times you want to name a column from just below a label all the way to the bottom of the Worksheet. Click on the Label Cell, then hold down the Shift key and the Ctrl key and tap the Down Arrow key until the column is highlited down to the bottom. To get back to the top, After you've Created a Name, Hold down the Ctrl Key and tap the Up Arrow key. This procedure, holding the CTRL Key, moves the cursor until it hits A) if starting in an empty cell, to the next non empty cell. B) If starting in a non empty cell, to the next empty cell. Holding the Shift Key selects all cells the curser crosses.
You can use Names in a differnt Workbook too:
In the Define Name Control I would set Some_Name to refer to='[Workbook_Name.xls]Sheet_Name'!Named_Range
Note the positions of the single quotes in the formula. You must use them if there are any spaces in the fully qualified name of the workbook and or Worksheet or if any of the names are longer than 8 characters or if the extension, (.xls,) is longer than 3 characters. Ex. index.html
C:Dept_Reports20083rd qtrBudget.xls is a fully qualified file name and would require single quotes in a Range Name because Dept_Reports is longer than 8 characters and because of the space between 3rd and qtr
You may not have to avoid spaces, but I always try to. In an Excel system I'll avoid spaces in Folder names, Workbook names, and Worksheet names and I'll use the single quotes everytime. Belt and suspenders, because it can be a real beach to figure out where an error is.
SamTA Pragmatic Classical Liberal, aka Libertarian.
I'm always right! Except when I'm not.
Edited 8/20/2009 6:44 pm by SamT
Whoa! I'm going to dig into that post. Thanks Sam and welcome back! I was wondering where you had drifted off too.
I gotta go walk a roof before it gets dark.
Sorry, I shoulda said "C&P to anopther sheet..."SamTA Pragmatic Classical Liberal, aka Libertarian.
I'm always right! Except when I'm not.
What do you mean "break the workbook"?
I know I had trouble cutting and pasting some stuff. I learned to "paste special" and change the criteria of the paste operation. Sometimes, I'd have to try every combination. Sometimes, I just gave up and reset all the cells.
Will the name thing help solve that?
If it don't work, it's broken. If you move a cell referencing formula to another sheet, it won't work.
To: All
Lemmee put some cafien down and I'll be back.SamTA Pragmatic Classical Liberal, aka Libertarian.
I'm always right! Except when I'm not.
I finally had a chance to open up bobs spreadsheet and learn how/why it makes sense to name things.
For me, I can quickly see a benifit. I'm dealing with five major roofing manufacturers and they each have shingles available in three primary categorys that we sell. So far, we have mostly sold GAF Prestiques. To calculate my costs, I have aimed my multiplier toward a specific cell that has it's price in there. Instead, I will name that cell GAF Prestique and I'll never have to search for that cell again.
Is that basically how naming cells work?
Is that basically how naming cells work?
Yes
I have aimed my multiplier toward a specific cell that has it's price in there. Instead, I will name that cell GAF Prestique
GAF_Prestique
Since you can Insert/Name/Paste and since the Names list box is organised alphabetically, I would suggest Shingles_GAF_3Tab_Prestique.
Suppose you also used Gafs' Architectual shingle called Premium and their 3Tabs called Zinfandel. Or whatever.
The list box would then show:Shingles_HD_SomethingShingles_GAF_Architectual_ProsperityShingles_GAF_3Tab_PrestiqueShingles_GAF_3Tab_ZinfandelShingles_Lowes_SomethingTarPaper_AceHardware_15TarPaper_AceHardware_30TarPaper_Lowes_15TarPaper_Lowes_30
Note that Prosperity is between Prestique and Zinfandel, alphabetically.It doesn't matter how you organise the list of names and price cells on your SS as long as all the name containing cells are in the same row or column and all the price containing cells are adjacent to the names. Select all of them and from the Menu Bar, choose Insert|Name|Create.
Excel will automatically replace Spaces in the Range Names with Underscores, but I prefer to manually implace them on the SS, as a reminder when I look at them that, "Hey, this string of characters is special!"SamTA Pragmatic Classical Liberal, aka Libertarian.
I'm always right! Except when I'm not.
Sam
Thank you again for doing this.
I spent a little time this morning looking this worksheet over.
I might have to radically change the way I think about excel.
Thanks
Rich
Do you, or Sam, have an example of his excel lesson that you can post? I'm having a hard time understanding it and it might help if I could open up that ss.
Jim
If you are talking about naming cells, then no.
I am working (in the field till 9 PM most nights and I am struggling to stay on top of the estimating and billing.
While Sam's stuff is what I need to change my excel think, right now it can become a BT distraction when I need to get work done.
I will attempt to name cells later. But this morning my mind was envisioning rearranging my worksheets with the variables and the cover sheet (estimate).
Right now I redo everything in word when i want to send a doc to a HO.
Rich
Sams_Improved_Bob_Breaktime_Blank_Worksheet.xls
The original as used in the first lesson with all the pictures (120723.26 )
Input_Sheet_of_SIBBBW.xls
This one is used as an example of how easy it is to understand formulas that uses names. See 120723.29 SamTA Pragmatic Classical Liberal, aka Libertarian.
I'm always right! Except when I'm not.
Thanks for that excel file. I'll poke around in it and reverse engineer it and then go back to your lesson. I think this will be very helpful to my current project.
Sam and All
After reviewing your posts I have been revising my worksheets.
We are a small 3 man operation. Our jobs are 1 day to 1 month.
We bid some jobs but most jobs turn into T&M.
So this is a prototype of worksheet that I want to use as a platform upon which to build all my other worksheets. Originally I called this AA Blank worksheet.
I use it for any project where I have a limited number of items or the items are so unique it is impractical to keep a current price.
The worksheet starts with a Coversheet & Settings tab. Here I list the Job name, address, date and any other pertinent information.
The settings are to adjust markup, labor rate and contingency.
The next worksheet is to figure the estimate. The totals appear at the top of the page and I froze the panes so they stay in view. The range of cells for totals allows for 200 items. This can be expanded.
Then next page is the estimate letter. The totals from the Estimate worksheet generate the totals for the estimate letter and the address is generated from the cover sheet.
I got the original version of this invoice as a free download from MS
http://office.microsoft.com/en-us/templates/TC010184681033.aspx?pid=CT101172551033
Then I modified it. Fold the letter on the blue line and it will work with a window envelope from Wal-Mart.
After I do the job then I do a Bill Worksheet to figure the bill or to compare my actual costs with the estimated costs. That comparision is on the Cover sheet.
The Bill Worksheet generates the invoice and the coversheet provides the Job Name, address and date. The original invoice from MS had an automatic date feature. But then anytime I opened the worksheet in the future then the date moves forward. I want to keep the invoice dated to the time of work.
I need to make a change in the Worksheet so that the date of the estimate and the date of the invoice are independent.
Before this worksheet I did one excel worksheet to Estimate, then Word doc. to mail the estimate.
Then after the job I did a seperate excel Bill Worksheet to figure the bill, then I hand wrote an invoice or did a Word Doc.
With this new platform I can have the whole job from estimate thru the invoice and the comparision in one Worksheet.
Some of the cells are named but not all.
Rich
Edited 9/3/2009 10:21 am ET by cargin
cargin,
You're starting to get the hang of it. That looks pretty good.
I looked over your WB and put a few suggestions on the unused Sheet 3, the last sheet.SamTA Pragmatic Classical Liberal, aka Libertarian.
I'm always right! Except when I'm not.
Sam
You have some markets that do charge a tax on labor sold?
In IA new construction and remodeling labor is exempt from sales tax.
Repairs are not.
New storm door not tax on the labor.
Repair a storm door and there is sales tax on the labor.
Thanks for the input. I'll look it over some more tonight.
Rich
Sales Tax, Labor
(Yes/No)
=Tax_Rate_Labor
=IF(B1='Yes',Taxable_Labor*C1,"")
That IF formula reads: Condition comma result if Condition=True comma result if Condition=False where the double quotes mean "show nothing". You must have both commas. Without the "", I think you'll get a "0".
As this is written, you must enter exactly "Yes" in cell B1, anything else including "yes" or a blank is taken as a "No."
It can be rewritten to accept anything beginning with a "Y" or "y."
With a little bit of study, you can write a Macro, (Sub-Routine,) that will Pop-Up a Control that will give you only the choice of "Yes" or "No."
I would probably put the "Sales Tax, Labor; Required: Yes/No" cells on the Actual Costs WS and reference to it in the Billing WS SamTA Pragmatic Classical Liberal, aka Libertarian.
I'm always right! Except when I'm not.
A macro....yes...thats what I want.
Ill have to email mine to you and see if you can suggest a macro. I can't email right now, I'm waiting for the disc to show up. I was using a demo and I'm locked out.
Thanks for the lessons,SamTawesome! it will take me a while to work with this style...but I sure appreciate what you have done. I can adapt the annotated BBNW and try to figure it out from there. where did you learn this? silver
where did you learn this?
U of Autodidaction.
It's really obscure, no one's ever heard of it.SamTA Pragmatic Classical Liberal, aka Libertarian.
I'm always right! Except when I'm not.
I might stop in at the bookstore or library and look up that excel book that was suggested earlier.
Some of the things Sam said are exactly what I'm trying to accomplish and I guess I should structure the workbook the right way instead of doing it "my way".
Jim
I guess I should structure the workbook the right way instead of doing it "my way".
Let me know when you have "arrived"
Psst You will never "arrive" somebody will always show up with new angle or paradigm shift and alter your thinking.
I have always structured my worksheets like I worked on paper.
Sam is showing me how to break out of that type of thinking.
I suspect that I am just scratching the surface of Excal.
And then there are guys like Jerrald who have moved on to databases because they are more powerful than spreadsheets.
Rich
andy
Remember KISS
And not the rock band either.
Rich
SamTA Pragmatic Classical Liberal, aka Libertarian.
I'm always right! Except when I'm not.
I've been trying to open up
I've been trying to open up the worksheet but the link says page not found?
To: gSSNis9y4s
Which worksheet? There's been a few passed around this thread.
JUST A TEST
huh?!
END TEST