In the tutorial you will know the way to use Excel to construct a spreadsheet for calculating assignments for the small service provider. The final outcome is an extremely vital, however useful calculating guide. All the instruction about the task is in one worksheet.
Open an Excel spreadsheet. Make columns for Materials, Price, Quantity and Total, task, number of hours, rate, and Total. These are the columns. Increase the column width for Materials, Task and Total columns.
Then enter the materials below the Materials column, and their prices below the Prices column, and the quantities required in the Quantity column. Once you have entered the materials, their prices and quantities below their related columns, please go to the cell Total column and then make a formula for Price multiplied by Quantity (Price*Quantity).
Set the formula and drag for the remaining cells below Total column that will automatically show amount calculated by multiplying price with quantity. Create subtotal by adding all the cells (containing amount of price into quantity) below Total column.
Calculations for contingency, sales tax, markup and total materials –
Then set contingency column and enter the contingency percentage. Contingency is equal to Subtotal multiplied with contingency percentage. Sales Tax is equal to the sum of subtotal and contingency multiplied with sales tax percentage.
Markup is equal to the sum of subtotal, contingency and sales tax multiplied with markup. A total material cell is equal to the sum of subtotal, contingency, sales tax and markup.