+ Reply to Thread
Results 1 to 4 of 4

advice for construction estimating spreadsheet

  1. #1
    Registered User
    Join Date
    01-18-2012
    Location
    St. Louis, MO
    MS-Off Ver
    Excel for Mac 2011
    Posts
    2

    advice for construction estimating spreadsheet

    Hey folks,

    I'm a basic excel user here, nothing too advanced. I use Excel for MAC 2011. I'm trying to do a project for work which requires some advanced (to me) functionality. I think I'm pretty good at self teaching, I just need to know where to start looking to learn how to do this type of stuff. I'm hoping you guys can point me in the right direction.

    Here's what I want to do:

    I work for a specialty construction contractor as an estimator/project manager. I currently use excel to put my estimates together, but it only has some basic formulas for calculation.

    I'm hoping to create a workbook that will allow me to efficiently put together pricing for installation of traffic signage. Every quarter, we get a catalog from our supplier with costs for all the components that go into a sign (sign panel, post, concrete footing, fasteners, etc...). I want to create a spreadsheet that allows me to use drop down menus to build a sign out. The drop down menus would reference the current pricing info from a database I create

    I know how to do the basic formulas (i.e. take material cost and generate profit margins). What I don't know how to do is create a database of items and costs and then have excel reference this database

    For example, I would like my worksheet to look like this:

    ---------------------------------------------------------------------

    SIGN TYPE: <user types in sign name> example: ""30'x30' Stop Sign into landscape"

    QUANTITY: <user selects quantity numer> example: "3"

    MATERIALS:
    POST TYPE: <drop down menu of post types> example: "Green Channel Post - 8' "
    PANEL 1 <drop down menu of sign panel types> example: "30"x30" STOP

    PANEL 2 <drop down menu of sign panel types> example: "6" x 18" PEDESTRIAN CROSSING"

    ADD 1 <drop down menu of general add-ons> example: "Concrete Footing"

    ADD 2<drop down menu of general add-ons> example: "Fastening brackets"



    LABOR PER SIGN - MAN HOURS <user types numerical value>

    COST: <value collected from adding together above selections>
    MARGIN <takes costs and calculates profit margins at various percentages>
    ---------------------------------------------------------------------

    What do you guys think? Is this beyond what excel is capable of doing?

    Thanks!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,209

    Re: advice for construction estimating spreadsheet

    Excel is pretty versatile. In terms of accessing a "database", you could use Data Validation for the drop down boxes and VLOOKUP to return the relevant prices.

    Your best bet is to rough out what you want it to look like.

    I'd recommend that you keep the data input area separate from the calculation area ... and from any summaries and reports you might want to create.


    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook question. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results..
    To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
    To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

    On this page, below the message box, you will find a button labelled 'Manage Attachments'.
    Clicking this button will open a new window for uploading attachments.

    You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
    Alternatively you can click the Attachment Icon to open this page.

    To upload a file from your computer, click the 'Browse' button and locate the file.

    To upload a file from another URL, enter the full URL for the file in the second box on this page.
    Once you have completed one of the boxes, click 'Upload'.

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  3. #3
    Registered User
    Join Date
    01-18-2012
    Location
    St. Louis, MO
    MS-Off Ver
    Excel for Mac 2011
    Posts
    2

    Re: advice for construction estimating spreadsheet

    Thanks for the response TMShucks

    I've attached a rough version of what I'm looking for. It's pretty straightforward. Right now I have 2 sheets; the sign estimating on sheet 1 and the pricing table on sheet 2. I'm thinking I should just be able to reference the pricing table on sheet 2 for all the information on sheet 1?

    The items marked with a red asterisk are the items I'd like to have a drop down menu with their corresponding fields from the table on sheet 2. This is really the only function I dont' know how to do, or if it's even possible (I think it is). Everything else is just simple formulas and I can handle that.

    I'm thinking this is probably a no-brainer. I'm not expecting anyone to do my work for me here, I'm just hoping someone can point me towards the functions that I need to research to achieve what I'm looking for. Also, Is there an example you could point me towards to illustrate your idea of keeping data input separate from calculation?

    Thanks for all your help! This forum is great!

    sign pricing demo.xlsx
    Last edited by emt8q5; 01-19-2012 at 12:37 PM.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,209

    Re: advice for construction estimating spreadsheet

    See the attached updated example.

    I've added some Named Ranges, Data Validation for the drop down lists and VLOOKUP formulae to return the costs.

    See if this helps you move forward ... or generates more questions.


    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1