ABC Pool Company

Congratulations, you’ve just started your new job as sales manager for a mid-size pool company operating on the west coast. ABC Pool Company is the leader in supplying and installing quality in-ground pools for residential and commercial customers. One of the first projects you’d like to tackle is to assist your sales staff in providing customers with fast, reasonable estimates for potential pool installations. To do this you’d like to setup an estimating workbook that outlines inputs and costs for the most common pool type/size configuration.  Download the Excel start file to begin your work.  Data given in the spreadsheet is shaded gray. You will be providing proper formulas for the remaining data in the un-shaded cells by answering the following questions using formulas and cell references from this worksheet. You will input your formulas in the spreadsheet, NOT in this file. Remember to start all formulas with an equal (=) sign and to always use cell references.

• All of the basic pool specifications have already been input for you on the spreadsheet.

 
o You will use these inputs to calculate each component cost. See next page. o Your calculations will be in cells A7:C7 and in cells F9:F15. o All the other cells will be input for you.
 
If your calculations are correct, the total pool costs should be $106,591.11 
 
 

1. Pool Dimensions and Features

The data given contains the case you will initially evaluate.  The lengths and widths are given in linear feet (LF) and are the actual size of the pool. All costs associated with the building of the pool are outlined below.

• (0-4 pts) In Cell A7 : Calculate the Pool Area using cell references (length times width cell values are in row 4, columns A and B).
• (0-4 pts) In Cell B7 : Calculate the Pool Perimeter using cell references (2 times the (length plus width) where the length and width cell values are in row 4, columns A and B).
• (0-4 pts) In Cell C7 : Calculate the Pool Volume using cell references. Convert your cubic feet to cubic yards, there are 27 cubic feet per cubic yard (pool area times depth divided by 27 where the pool area is in row 7, column A, and the depth is in row 4, column C).

2. Component pricing

To calculate the cost of each pool option, use the list of construction components detailed below.  Some items are priced based on the square footage (SF) – area of the pool, area of the decking, or entire area (pool + deck).  Some items are priced based on linear footage (LF) – perimeter of the pool area – and some items are priced by unit (each).  You will be calculating the cost of each component using the quantities and pricing given.  USE CELL REFERENCES IN ALL CALCULATIONS UNLESS THE SPREADSHEET DOES NOT INCLUDE A CELL REFERENCE WITH THE VALUE NEEDED FOR THE CALCULATION.

• (0-4 pts) In Cell F9 : Excavation – The cost to excavate the hole required for this pool is estimated based on the pool volume using the pool length * width * maximum depth. It costs $4.00 per cubic yard (CY) to dig this hole. Therefore you will multiply the volume in cubic yards in cell B9 by the cost per cubic yard in cell D9.
• (0-4 pts) In Cell F10 : Pool Structural Framing  The cost per square foot of this framing is $5.00/SF.

Multiply the framing square feet given in cell B10 by the cost in cell D10.

• (0-4 pts) In Cell F11 : Filtration System, Water Hookup and Drainage- The cost of filtration is $10,970. Use cell references in B11 and D11 to calculate the cost in F11 (filtration number times cost).
• (0-4 pts) In Cell F12: Pool Lining – The cost of the pool lining is $68,160. Use cell references in B12 and D12 to calculate the cost in F12 (lining number times cost).
• (0-4 pts) In Cell F13: Deck – The cost of the deck is $11,475. Use cell references in B13 and D13 to calculate the cost in F13 (deck number times cost).
• (0-4 pts) In Cell F14: Diving Board – one type of diving board is offered at the cost of $475/each. Use cell references in B14 and D14 to calculate the cost in F14 (diving board number times cost).
• (0-4 pts) In Cell F15– Calculate the Total cost of all items from cells F9 through F14 (Hint: Use the SUM() function here.).
• (0-4 pts) Rename the sheet Carter Pool Estimate by <Your Initials> where <Your Initials> are the first letters for your First then Last name (Hint: Right click on sheet name and type in new name.)

 

3.  The Smiths (6 points)

You have a potential new customer, The Smiths.  Copy the existing sheet to a new sheet and put it at the end of your current workbook/file (select the worksheet name in the tab at the bottom, then right click and select move or copy and select move to end and also the create a copy button at the bottom then click OK).  Rename the sheet Smith Pool Estimate by <Your Initials> where <Your Initials> are the first letters for your First then Last name.  This new customer would like a pool that is 125 feet long, 50 feet wide, and 10 feet deep.  Change the appropriate values in this new sheet to correspond to their request by changing cells A4, B4, and C4 appropriately.  Note that the Smiths do not have children and therefore do not need a diving board for their pool.  Make this change where appropriate on their worksheet (cell B14).  Note how the cell references used in your formulas cause the necessary cells to change as you change the pool dimensions.

WHAT TO SUBMIT

Input all formulas and make all changes in the Excel start file as stated above.  Rename your excel file WKSHT_M1_<Lastname>.xlsx where <Lastname> is your Last name.  Submit your file to Canvas by the stated due date.