Schedule and Cost Summary Calculator

About this template

This Cost Estimate and Scheduling spreadsheet provides a lightweight method for learning to estimate time to complete a web design project, and calculating cost for completion. The intended audience is individual freelancers or contractors who need to prepare proposals for potential clients. This may not be an ideal format for the calculation of project team estimates.

The purpose of the document is to teach you to get acquainted with spreadsheets as tools for cost estimation, with the hopes that you will learn to use and modify it to suit your needs. Please note that this is only meant to be learning a tool and must be customized to be useful to you. This is NOT meant to be an out of the box solution.

How it works

This spreadsheet works by breaking your phases into individual tasks. You estimate time to complete tasks and start dates. Estimates are rolled up to show the sum of hours and estimated completion date for all tasks in a phase. Those numbers are then rolled up to show the sum of hours, cost, and completion date for all of the phases. This makes up your cost summary for your proposal.

Calculating time

The spreadsheet calculates time to complete each task based on an estimated low and high number of hours for each task in each phase of work, and calculating the mean or average of those values. This is done so you can enter a range rather than a hard number for safety. The range should be based on your own knowledge of how long it takes to complete each task. Only you will know how long you take to complete these tasks. To better know that number, you may want to work outside the spreadsheet to break each task into sub-tasks that can be estimated in hours.

When a starting date is entered for a task, an estimation of finishing date is calculated based on your value for the number of hours per day you can devote to the project which you enter at the bottom of the spreadsheet, and subtracting any holidays you specify (also at the bottom).

Calculating cost

Cost is calculated by taking the mean hours for a task and multiplying by the rate of pay you select. For this example, I use 3 different rates of pay. You will need to calculate your formulas to the appropriate rate of pay in the Cost column.

Estimating Hours

Coming up with these numbers will depend on the information that you gather in your request for information/statement of work produced with the client. You will llikely do a more broken down estimate of hours based on scope of work. The screen inventory is an example of a document that can be used to break down a task.

Modifying Calculations

Because you may only want to use part of this spreadsheet, e.g. give hard estimates without computing means and using only 1 pay rate, you may wish to edit the formulas included here. The sheet and its formulas are not protected, so you may want to configure your phases and tasks, rates and rate assignments, as well as holidays. You may then protect the document so it can be used for information entry only. Some knowledge of Excel is mandatory for modifying formulas.

Disclaimer

Michael Angeles and Konigi provide no warranty for the use of this spreadsheet. By electing to use this spreadsheet, the user acknowledges that any errors are the sole responsibility of the user, due to data entry, formula creation, calculation, or any use of this spreadsheet on the user's part. Use this spreadsheet at your own risk.

screenshot

Download the Excel Template
This template is free to use, but may not be distributed without permission. If you like it, a donation is appreciated.