Calculating Cost in Excel

Calculating Cost In Excel

Directions: A local builder has contacted you and needs an excel spreadsheet designed to help the business calculate the total amount of cost and labor that is being used to construct various products. Below are the items that are commonly used at the business and need to be entered into the spreadsheet (see example below for excel setup help). Make sure to include the formulas that will calculate the totals, taxes and amount due.

Lumber

2x4x8' TOP CHOICE TREATED Board $3.29 each

2x6x8' TOP CHOICE TREATED Board $4.49 each

2x2x8' TOP CHOICE TREATED Board $2.69 each

1X2X8' C & BETTER PINE Board $3.78 each

1X6X8' C & BETTER PINE Board $14.38 each

1X8X8' C & BETTER PINE Board $16.97 each

1X2X8' OAK BOARD $7.18 each

1X2X4' OAK BOARD $5.62 each

Plywood

4X8 ¾ EXTRIORHARDWOOD PLYWOOD $10.78 each sheet

4X8 ¾” Oak Plywood $134.00 each sheet

4X8 ¼” Oak Plywood $77.06 each sheet

Other Supplies

Grip-Rite 3-1/2" 16D Common Nail $3.24 per lb.

Grip-Rite 1-1/2" 4D Common Nail $3.37 per lb.

Owens Corning Supreme Estate Gray Asphalt Shingle $23.95 bundle

Star Drive Stainless Steel Wood Screws 1-1/4” $6.97 per 45

Bookshelf Pins $.25 each

Wood Glue $7.27 quart

Minwax 3.75 Oz. Wood Putty $2.97

Minwax Quart Polyshades Wood Stain Oak $11.87

Labor

Standard Labor = $18.50 per hour

Directions 2:Create 4 Excel Spreadsheets to display cost for particular items that the local builder constructs (to do this first save your work, next in the bottom left hand corner right-click on the tab that says Sheet 1. Next select Move or Copy, next in the Before Sheet Section select sheet 2 and place a check mark in the Create Copy check box. Click OK. This will add a new sheet with the same data as you have on sheet one). Repeat this process until you have 4 sheets with the original spreadsheet data. Next rename your 4 sheets(to do this right click on the sheet tab in the lower left hand corner and select rename, type in the following names: Picnic Table, Dog House, Chair, Bookcase).

Now using your excel spreadsheets fill in the following information within the 4 sheets that you have renamed. Use the materials list from below to help with this step. We will compare results and check values once you have all 4 sheets finished.

Picnic Table Material List:

13 - 2X6X8(treated) used for the seats and top of table

2 - 2x4X8(treated) used for the braces on the table

2lbs of 16D Common Nails

2 hours standard labor

Medium Dog House Material List:
1 - 4'x8'x3/4” Exterior Grade Plywood (to frame in the dog house)
2 - 2"x4"x8'(treated) (for the foundation of the dog house)
1 - 2"x2"x8'(treated) (for the front porch of dog house)

1 – Bundle of Owens Corning Shingles (for the roof)
1lb. - 16d common nails

1.5 hours of standard labor

CLASSIC ADIRONDACK CHAIR

4 - 1x2X8 board Pine

3 - 1x6X8 board Pine

2 - 1x8X8 board Pine

75 - 1-1/4" Star Drive Wood Screws

2 hours of standard labor

Bookcase

1 - 4' x 8' SHEET OF 3/4" THICK OAK PLYWOOD

1 - 4' x 8' SHEET OF 1/4" THICK OAK PLYWOOD

2 - 1x2x8 OAK BOARD

1 - 1x2x4 OAK BOARD

8 - SHELF PINS, 1/4" DIAMETER SHANK BY 3/8" LONG

1 quart - WOOD STAIN Oak

1lb - 1-1/2" 4d Common Nail

1 quart - WOOD GLUE

3.75oz. - WOOD Putty

3.5 hours of standard labor

Insert a picture on each of the sheets that relate to the item being constructed.

Total Points = 160 (40 points per completed sheet)