Collaborate on Blank Estimate Template for Corporations with Ease Using airSlate SignNow

Blank estimate template for corporations

I'm going to show you how to create a beautiful  and practical construction estimate template in   Excel this template is based on practical  experience I help my husband who is in the   construction and remodeling field design a  similar one for his own use no matter your   industry this video demonstrates how to create and  format any type of business template open a new   Excel file I like to right away apply my preferred  font to the template I will use Sentry Gothic at   11 Point sides I will give my template a title in  my case construction estimate and make it bold at   18 points the first item that I will want to list  are the hourly rates for the main Carpenter and   his helper I will also create place holders  for markups on labor and material you may add   or edit these variables as they fit your field  I incorporate formatting throughout my design   process the first thing I want to remove are  the grid lines to make the template look clean   to emphasize the rates and markups I will make  them bold and fill with color I will name my   tab summary and before I lose any changes I will  name my workbook and save it on the summary tab   I will want to have a summary of all the trades  in the worksheet starting with prep work for a   construction project to give my data some room I  will add numbers in the First Column and adjust   its size now I'm going to add my hourly rates  for the main and helper Carpenters I am ready   to start adding some detail for each part of  the project the first trade will be prep word   and does the name for my new tab under description  I will write all the tasks that need to be done I   will add numbers on the left up to 25 tasks per  trade now I will add few more Columns of needed   data these include the type of work which is  sometimes used to track different work types   in some construction estimates and the hours for  both the main and assistant Carpenters the next   two columns will be subtotals for the charges  associated with each task now I'm going to   enter some sample hours for each task for both  workers for formatting I will Center the hours   and remove the grid lines in the next step I I  want to set up job description categories so I   can use them repeatedly without having to think  about them each time I will add a categories tab   in my template under the description section I  will begin listing the typical tasks that may   be required for a standard construction project  I will also copy over all the test descriptions   from my prep work and add few more there are some  duplicates and misspellings which I will clean up   shortly if you have a previous project you can  copy and paste all the job descriptions to build   a comprehensive list I will highlight my list  and spell check at first next I will sort them   alphabetically to make it easier to find task  descriptions in drop-down menu finally I will   remove any duplicates I like to highlight the  area that will serve as reference for data   validation leave few extra empty rows in case  more task descriptions need to be added while   working on the project I color code the area so I  can quickly tell which rows are being used under   data validation I will apply the same approach to  categorizing the type of work for now I will label   the categories as General Plumbing or electric  it is good to add a space between the categories   so when you want to sort them they don't affect  each other now I'm going back to my prep work   which I've been setting up as a reference tab to  copy and use for other part of the project to add   the work description categories to a pull down  menu select all the rows go to data validation   and choose the list option Source will be the  colorcoded description that are on the categories   tab I will apply the same approach to categorizing  the types of work to use the dropdowns select each   row and choose an option from the drop- down  menu if you can't find your description start   typing an option that match the initial letters  will appear then press enter last I will add few   more work hours for the main and assistant  Carpenter for the next step in the setup I   like to open a new window of my file this allows  me to easily reference and link between sections   side by side we have hours for our workers but but  now we want to calculate the cost for each of the   tasks under prep work to calculate the sub total  for the main Carpenter I will multiply the hours   required for the task by the hourly rate listed  on the summary tab I kept the hourly rates and   markups on the summary tab so any changes will  automatically flow through the entire template   I will set the reference to the rate as absolute  so that copying formulas won't alter the reference   to cell D2 on the summary tab to copy the formulas  down I can double click on the lower right corner   I will do the same to calculate the subtotal for  the helper carpenter work keep it absolute and   link to the rate on the summary tab another way  to copy the formula is to grab the right corner   and drag it down the current format for the sub  totals is set to currency which displays zeros in   empty rows and looks cluttered I will change the  format to accounting which replaces zeros with   dashes creating a much cleaner look now I will add  a bit of formatting I removed grid lines from the   entire sheet but I will add borders to the areas  with data so it is more readable across Road to improve readability across long columns I will  add a light gray color every two rows to create an   alternating row pattern to paste formats from  one area to another use the paint format icon   to center a title across columns I will use the  merge and center option from the menu and label   my columns under labor for now let's add two more  columns the first will be the total labor cost   which adds the subtotal for the main and helper  Carpenter I will use the subtotal function with   the function number nine for summing to  prevent any double counting now and as I   extend the template later on at this stage the  totals only account for direct costs and do not   include overhead costs like Administrative Office  Insurance utilities marketing equipment licensing   and permit expenses to cover these overhead costs  you need to apply a markup to labor costs I will   go back to the summary Tab and add my markups  when setting pricing for construction projects   or other businesses it's crucial to establish a  markup that that not only covers overhead cost   but also generates a profit simply breaking even  is insufficient as unexpected expenses do arise   do research on your industry but for construction  a good reference is the builder.com website they   recommend markups on labor above 25% and for  materials between 30 and 50% a percentage can be   expressed as a decimal by dividing it by 100 when  we use a decimal greater than one it represents   an increase over the original value for example if  you set up a markup of 1.3 it means the price will   be increased by 30% here's why 1.3 is the decimal  equivalent of 130% the one represents the original   amount and the 0.3 represents an additional 30%  so multiplying by 1.3 adds 30% to the original   amount now I'm ready to use it in my calculations  I will go back to my prep work tab then multiply   the labor cost by the labor markup of 1.3 I will  make the reference absolute I will also apply The   Styling from the other columns to my totals and  extend the labor title now what would be helpful   is to have totals for the hours for each Carpenter  and job costs so I will add a some at the bottom   of the columns to select a range you can use the  keyboard or your mouse to make a selection final   touches is to emphasize the total at the bottom  for each trade starting with prep work I want to   copy the summary calculations to the summary tab  to link them I will bring my other Excel Window   with a Mac shortcut command Accent key or on a PC  use the control+ tab or control+ F6 then link for   the total for labor cost if I make the reference  absolute and copy it over it will stay the same   so I have to do the linking twice once for pre and  once for postm markup we have completed the labor   cost calculations next I will add the materials  cost to keep things organized I will temporarily   hide the labor columns using the free Spains I  will add two columns for materials one for the   actual cost of the materials and another for the  cost with applied markup let me paste the format   from the label columns before I perform the  calculations I will take the material cost and   multiply it by the markup of 1.5 from the summary  tab as an absolute reference then copy the formula   down and apply consistent formatting the last  column that I want to have is a total that will   be given to the customer that's a straightforward  addition of the postm markup labor and material costs now I will go back to my summary Tab and  add the material and total to customer columns   and individually link them as absolute values  to my prep work tab this is a good setup for   a large scale construction project eventually  I will have many different trades in the file   and to move and find them quicker I will create  hyperlinks select the cell right click and go to   hyperlink then choose the correct tab under in  this document when I select the cell now I jump   to the prep work tab perfect once the file becomes  large I would would like a quick way to navigate   back to the summary tab from any other tab so I  will add a hyperlink on the prep work tab as well   I can test this navigation and it works great and  is a quick way to move about a large file at this   point I am done setting up my pre-work tab as  a template for other trades now I am ready to   make a copy of it by writing clicking on it then  going to move or copy and checking the create a   copy option I will give this tab a name demo work  then delete all the sections applicable to prep   work make sure you don't delete any formulas now  I will customize it for demo work by selecting   new tasks under the description and adding  work hours for the main and helper Carpenters   I will also add some example material charges to  make this example more comprehensive currently   the data for demo work is not appearing on the  summary tab so I have to link to it I will make   a copy of the prep work row then select on  that row and go to find and replace I want   to find all instances in the formula in the row  of prep work and replace it with demo work make   sure you don't add any spaces to the name it  has to match exactly to the names of the tabs   in the file that was so quick and much less  work to do I do want to double check to make   sure it linked the numbers correctly the  final thing I need to fix is to change the   hyperlink from prep work to the demo work tab  now I'm going to add another Tab and leave it blank I will also update the titles  on each tab then relink the data as   I done before by finding and replacing  the prep work links with the new work   tree name next I will copy the work three  tab five more times to prepare for future   trades you can add as as many as you need and  always create more later on the summary tab   I will duplicate the first row five more  times and relink them using the find and replace last thing I need to do is update the  hyperlinks and test them the summary will update   as I update more trade data on each tab on the  summary tab I will use the Autos sum option to   sum up all the labor materials and total to  customer columns right now I can't tell what   is my profit margin on this project let's  calculate I will calculate the gross profit   and gross margin once I have determined the  total revenue and the cost for the labor and   material on the project the total revenue is the  total paid by the customer the cost of the labor   and material are the premark up amounts these  are the actual costs gross profit is the total   revenue less the two costs in this example on the  total revenue of 12,000 we made a gross profit of   3,500 this amount must cover overhead costs taxes  and ideal leave some for savings and Investments   dividing the gross profit by the revenue gives  us a gross margin of 29% not bad now we can   easily change the hourly rates and markups to run  different scenarios here is a general rule if you   want your profit margin to be at least 23% you  mark up needs to be 30% or 1.3 if you want your   profit margin to be at least 37% your markup would  need to be 1.6 if you have two variables such as   labor and materials with different markup rates  both will affect your gross margin you can also   add a payment schedule to your template and  calculate the required invoice amount based   on the work completed but that's the topic for  another video let me know in the comment section   if you would like a step-by-step tutorial on  how to do this thanks so much for watching