Cost Sheet for Desklib - Direct Material, Direct Labor and Overhead Costs

This spreadsheet model provides details of cost for proving the services. The cost are classified as the Direct material, direct labor and overhead costs. It is easy to use, reliable, effective and less time consuming.

SPREADSHEET MODEL
Cost Sheet

INTRODUCTION
 The spreadsheet is to provide details of cost for
proving the services.
 The cost are classified as the
 Direct material,
 direct labor and
 overhead costs

INSTRUCTION
 The spreadsheet provides details of direct and indirect
expenses that are required for completing the job. In
order to use this spreadsheet effectively the steps that
should be followed are:
1. Go to the data tab of the spreadsheet.
2. Insert the rate per unit of estimated direct material
cost in the table 1.
3. In table 2 insert the estimated hourly labor cost.
4. In table 4, insert the estimated indirect overhead cost.
5. The budgeted labor hour should be included in the
table 4 for calculating budgeted overhead rate.

INSTRUCTION
 The result is obtained in the layout spreadsheet.
 The layout spreadsheet has three tables and a cost
summary.
 These tables include both the estimated cost and the
actual cost.
 In addition to this the resulting differences are also
included in the tables.
 The cost summary provides a bird eye view of the
entire cost structure.

LIMITATIONS
1. It is vulnerable as the cost can be manipulated easily.
2. The result is not 100% accurate as it is affected by
human error.
3. In the spreadsheet model it is very difficult to identify
the problem.

FEATURES
 Easy to use
 Reliable
 Effective
 Less time consuming

INPUT DATA FOR DIRECT MATERIAL
Material Labour
Code Rate Per Unit
` $110
Windows $105
Sensors $80
Brakes $80
Car seat $95
Suspension $90
Wipers $30
Tires $60
Other Components $10
Engine Components $600
Employee ID Annual Salary monthly salary Rate/Hr
Robert D001 $85,920 $7,160 $51
Rajkumar D002 $75,000 $6,250 $42
Peter D003 $65,000 $5,417 $40
Andrew D004 $60,480 $5,040 $35
Kamlesh D005 $51,840 $4,320 $30
total $28,187

INPUT DATA FOR DIRECT MATERIAL
Factory Overhead Budget
Month of May
Rent for Workshop $5,500
Eletricity $1,700
Gas $300
Water $700
Supplies $4,900
Supervisors Salary $6,000
Depreciation of Equipment $1,300
Insurance of Equipment $1,500
Inspection $6,000
Factory Insurance $3,500
$31,400
Budgeted Hours 1020.00
Budgeted Overhead Rate 30.78$
Actual Overhead for May $32,000
Actual Hour Worked 1050
Actual Rate $30.48

OUTPUT LAYOUT DTC 1
Estimate Actual
Material Code
Quantity
Used Rate $ Total $ Emloyee ID
Hourly
Rate $
Hours
Worked Total $
Total
Labour
Hours Rate/DLH $ Total
` 1 $110 $110 Peter D003 $40 10 $400 20 30.78$ 615.69$
Car seat 3 $95 $285 Andrew D004 $35 3 $105
Other Components 5 $10 $50 Kamlesh D005 $30 7 $210
Total $445 Total 20 $715
Cost Summary Price
Cost for the Job $1,775.69
Direct Material $445 Mark up $443.92
Direct Labour $715 Price Charged $2,219.61
Manufacturing Overhead 615.69$
Total Cost $1,775.69
Direct Materials Direct Labour Manufacturing Overhead
Material Code
Quantity
Used Rate $ Total $ Emloyee ID
Hourly
Rate $
Hours
Worked Total $
Total
Labour
Hours Rate/DLH $ Total
` 1 $110 $110 Robert D001 $51 9 $459 19 30.78$ 584.90$
Car seat 3 $95 $285 Andrew D004 $35 3 $105
Other Components 5 $10 $50 Kamlesh D005 $30 7 $210
Wipers 1 $30 $30
Total $475 Total 19 $774
Cost Summary Price
Cost for the Job $1,833.90
Direct Material $475 Mark up $458.48
Direct Labour $774 Price Charged $2,292.38
Manufacturing Overhead 584.90$
Total Cost $1,833.90
Direct Materials Direct Labour Manufacturing Overhead

OUTPUT LAYOUT DTC 2
Estimate Actual
Material
Code
Quantity
Used Rate $ Total $ Emloyee ID
Hourly Rate
$
Hours
Worked Total $
Total Labour
Hours Rate/DLH $ Total
` 2 $110 $220 Robert D001 $51 9 $459 21 30.78$ 646.47$
Brakes 1 $80 $80 Andrew D004 $35 5 $175
Engine Components4 $600 $2,400 Kamlesh D005 $30 7 $210
Total $2,700 Total 21 $844
Cost Summary Price
Cost for the Job $4,190.47
Direct Material $2,700 Mark up $1,047.62
Direct Labour $844 Price Charged $5,238.09
Manufacturing Overhead 646.47$
Total Cost $4,190.47
Direct Materials Direct Labour Manufacturing Overhead
Material
Code
Quantity
Used Rate $ Total $ Emloyee ID
Hourly Rate
$
Hours
Worked Total $
Total Labour
Hours Rate/DLH $ Total
` 2 $110 $220 Rajkumar D002 $42 9 $378 24 30.78$ 738.82$
Brakes 2 $80 $160 Andrew D004 $35 8 $280
Engine Components4 $600 $2,400 Kamlesh D005 $30 7 $210
Total $2,780 Total 24 $868
Cost Summary Price
Cost for the Job $4,386.82
Direct Material $2,780 Mark up $1,096.71
Direct Labour $868 Price Charged $5,483.53
Manufacturing Overhead 738.82$
Total Cost $4,386.82
Direct Materials Direct Labour Manufacturing Overhead

OUTPUT LAYOUT DTC 3
Estimate Actual
Material Code
Quantity
Used Rate $ Total $ Emloyee ID
Hourly
Rate $
Hours
Worked Total $
Total
Labour
Hours
Rate/DLH
$ Total
Sensors 3 $80 $240 Rajkumar D002 $42 10 $420 22 30.78$ 677.25$
Wipers 2 $30 $60 Peter D003 $40 4 $160
Engine Components 1 $600 $600 Kamlesh D005 $30 8 $240
Total $900 Total 22 $820
Cost Summary Price
Cost for the Job $2,397.25
Direct Material $900 Mark up $599.31
Direct Labour $820 Price Charged $2,996.57
Manufacturing Overhead 677.25$
Total Cost $2,397.25
Direct Materials Direct Labour Manufacturing Overhead
Material Code
Quantity
Used Rate $ Total $ Emloyee ID
Hourly
Rate $
Hours
Worked Total $
Total
Labour
Hours
Rate/DLH
$ Total
Sensors 3 $80 $240 Andrew D004 $35 9 $315 21 30.78$ 646.47$
Wipers 2 $30 $60 Peter D003 $40 4 $160
Engine Components 2 $600 $1,200 Kamlesh D005 $30 8 $240
Total $1,500 Total 21 $715
Cost Summary Price
Cost for the Job $2,861.47
Direct Material $1,500 Mark up $715.37
Direct Labour $715 Price Charged $3,576.84
Manufacturing Overhead 646.47$
Total Cost $2,861.47
Direct Materials Direct Labour Manufacturing Overhead

OUTPUT LAYOUT DTC 4
Estimate Actual
Material Code
Quantity
Used Rate $ Total $
Emloyee
ID
Hourly
Rate $
Hours
Worked Total $
Total
Labour
Hours Rate/DLH $ Total
Windows 1 $105 $105 Rajkumar D002$42 9 $378 19 30.78$ 584.90$
Brakes 3 $80 $240 Peter D003 $40 4 $160
Suspension 4 $90 $360 Kamlesh D005 $30 6 $180
Total $705 Total 19 $718
Cost Summary Price
Cost for the Job $2,007.90
Direct Material $705 Mark up $501.98
Direct Labour $718 Price Charged $2,509.88
Manufacturing Overhead 584.90$
Total Cost $2,007.90
Direct Materials Direct Labour Manufacturing Overhead
Material Code
Quantity
Used Rate $ Total $
Emloyee
ID
Hourly
Rate $
Hours
Worked Total $
Total
Labour
Hours Rate/DLH $ Total
Windows 1 $105 $105 Rajkumar D002$42 10 $420 20 30.78$ 615.69$
Brakes 3 $80 $240 Andrew D004 $35 4 $140
Suspension 4 $90 $360 Kamlesh D005 $30 6 $180
Tires 2 $60 $120
Total $825 Total 20 $740
Cost Summary Price
Cost for the Job $2,180.69
Direct Material $825 Mark up $545.17
Direct Labour $740 Price Charged $2,725.86
Manufacturing Overhead 615.69$
Total Cost $2,180.69
Direct Materials Direct Labour Manufacturing Overhead

OUTPUT LAYOUT DTC 5
Estimate Actual
Material Code
Quantity
Used Rate $ Total $ Emloyee ID
Hourly
Rate $
Hours
Worked Total $
Total
Labour
Hours Rate/DLH $ Total
Doors 1 $110 $110 Rajkumar D002 $42 8 $336 20 30.78$ 615.69$
Car seat 3 $95 $285 Andrew D004 $35 4 $140
Suspension 5 $90 $450 Kamlesh D005 $30 8 $240
Total $845 Total 20 $716
Cost Summary Price
Cost for the job $2,176.69
Direct Material $845 Mark up $544.17
Direct Labour $716 Price charged $2,720.86
Manufacturing Overhead 615.69$
Total Cost $2,176.69
Direct Materials Direct Labour Manufacturing Overhead
Material Code
Quantity
Used Rate $ Total $ Emloyee ID
Hourly
Rate $
Hours
Worked Total $
Total
Labour
Hours Rate/DLH $ Total
Doors 1 $110 $110 Peter D003 $40 8 $320 20 30.78$ 615.69$
Car seat 3 $95 $285 Andrew D004 $35 5 $175
Suspension 5 $90 $450 Kamlesh D005 $30 7 $210
Wipers 2 $30 $60
Total $905 Total 20 $705
Cost Summary Price
Cost for the Job $2,225.69
Direct Material $905 Mark up $556.42
Direct Labour $705 Price Charged $2,782.11
Manufacturing Overhead 615.69$
Total Cost $2,225.69
Direct Materials Direct Labour Manufacturing Overhead

OUTPUT LAYOUT DTC 6
Estimate Actual
Direct Materials Direct Labour Manufacturing Overhead
Material Code Quantity
Used Rate $Total $ Emloyee
ID Hourly Rate $ Hours
Worked Total $
Total
Labour
Hours
Rate/DLH $ Total
Windows 3 $105 $315 Rajkumar D002$42 12 $504 23 30.78$ 708.04$
Sensors 4 $80 $320 Andrew D004$35 6 $210
Wipers 2 $30 $60 Kamlesh D005$30 5 $150
Total $695 Total 23 $864
Cost Summary Price
Cost for the Job $2,267.04
Direct Material $695 Mark up $566.76
Direct Labour $864 Price Charged $2,833.80
Manufacturing Overhead 708.04$
Total Cost $2,267.04
Direct Materials Direct Labour Manufacturing Overhead
Material Code Quantity
Used Rate $Total $ Emloyee
ID Hourly Rate $ Hours
Worked Total $
Total
Labour
Hours
Rate/DLH $ Total
Windows 3 $105 $315 Rajkumar D002$51 9 $459 19 30.78$ 584.90$
Sensors 4 $80 $320 Andrew D004$35 3 $105
Wipers 2 $30 $60 Kamlesh D005$30 7 $210
Engine Components 1 $600 $600
Total $1,295 Total 19 $774
Cost Summary Price
Cost for the Job $2,653.90
Direct Material $1,295 Mark up $663.48
Direct Labour $774 Price Charged $3,317.38
Manufacturing Overhead 584.90$
Total Cost $2,653.90

OUTPUT LAYOUT DTC 7
Estimate Actual
Material Code
Quantity
Used Rate $Total $
Emloye
e ID
Hourly Rate
$
Hours
Worked Total $
Total
Labour
Hours
Rate/DLH
$ Total
Item4 3 $80 $240 Robert D001 $51 10 $510 18 30.78$ 554.12$
Item5 5 $95 $475 Rajkumar D002 $42 3 $126
Item9 10 $10 $100 Peter D003 $40 5 $200
Total $815 Total 18 $836
Cost Summary Price
Cost for the Job $2,205.12
Direct Material $815 Mark up $551.28
Direct Labour $836 Price Charged $2,756.40
Manufacturing Overhead 554.12$
Total Cost $2,205.12
Direct Materials Direct Labour Manufacturing Overhead
Material Code
Quantity
Used Rate $Total $
Emloye
e ID
Hourly Rate
$
Hours
Worked Total $
Total
Labour
Hours
Rate/DLH
$ Total
Brakes 3 $80 $240 Robert D001 $51 10 $510 18 30.78$ 554.12$
Car seat 5 $95 $475 Rajkumar D002 $42 3 $126
Other Components 9 $10 $90 Peter D003 $40 5 $200
Total $805 Total 18 $836
Cost Summary Price
Cost for the Job $2,195.12
Direct Material $805 Mark up $548.78
Direct Labour $836 Price Charged $2,743.90
Manufacturing Overhead 554.12$
Total Cost $2,195.12
Direct Materials Direct Labour Manufacturing Overhead

INCOME STATEMENT
Sales $22,921.99
Cost of Goods Sold $18,337.59
Add: Underapplied $853.22
Actual Cost of Goods Sold $19,190.80
Gross profit $3,731.18
Deductions: Administration expences
Monthly salary of employees $28,187
Net Loss -$24,455.49
Income Statement for the month of May, 2018