Excel HW 4: Fox Lake Networking Template

Objective: Create a template to estimate the costof networking Fox Lake Country Club.

Skills: IF, AND, OR, COUNT, COUNTA, and COUNTIFand Conditional formatting

For the past year, members at Fox Lake Country Club have beencomplaining about not being able to access the Internet or slowdata transmission speeds while at the club. Given your knowledge ofspreadsheets, Jeff Lewis, business manager at Fox Lake, has askedyou to create an Excel template for estimating the cost of rewiringthe club’s facilities. Jeff wants the worksheet to contain somebasic input information and automatically calculate an estimatedprice, so that he can quickly estimate the cost of rewiring FoxLake’s facilities. The variables to be considered are asfollows:

• The dimensions of the building to be networked – length inlinear feet
• The condition of existing network equipment, where 0 representsnone, where 1 represents excellent, 2 represents reasonable butwould consider upgrading, and 3 represents poor condition withimmediate updates required.
• Whether or not the facility has existing network equipment (YESor NO)
• Speed of network required

Complete the following:

1. Open a new workbook and save it as“yourlastname”_CostOfNetwork.xlsx. Create a worksheet with thecolumns and data shown in the table below (see page 2 for thecomplete table with column headings). Also, include a meaningfultitle at the top of your worksheet. Ultimately, this worksheet willbe used as a template to evaluate competing bids. List all otherinputs that are needed for subsequent calculations below thefollowing table.

Building	Length in feet	Condition of network equipment	Existing Equipment?	Speed of network required?
Main Clubhouse	850	0	Yes	1 Gbs
Outdoor Patio	625	0	No	1 Gbs
Formal Ballroom	700	1	Yes	100 Mbs
Dining Hall	500	0	No	1 Gbs
Maintenance Shed	305	2	Yes	100 Mbs
Bar and Grill	330	1	Yes	1 Gbs
Men’s Locker Room	155	2	No	100 Mbs
Women’s Locker Room	125	2	Yes	100 Mbs
Outdoor Pool	760	3	Yes	1 Gbs

Fill in the cost per building. Enter all formulas so they can becopied down the column. Remember, your formulas will need to workwhen new quantities are substituted into the data entry area.Hint: create input cells and reference those cells in yourformulas.

2. To the right of the Speed of Network Requiredcolumn, calculate the cost of new network equipment. Only thebuildings with an Existing Equipment value of “No”will require networking equipment. The cost is $40.00 per foot. Ifno networking equipment is required, a value of $0 should bedisplayed. Title this column “Cost of NewCable.”

3. In an adjacent column, calculate the additional cost ofnetworking equipment based on the following criteria:
   1. If the Condition of Network Equipment is 0or 3, then computer equipment (e.g. router, backups, etc)are needed at a cost of $5.00 per foot.
   2. If the Condition of Network Equipment is 2,then computer equipment (e.g. new cables) are needed at a cost of$3.50 per foot.
   3. Otherwise, no new equipment is needed and a value of $0 shouldbe displayed.
   4. Title this column “Additional EquipmentCost.”
4. In an adjacent column, calculate the cost adjustment for theCondition of the Network Equipment based on thefollowing criteria:
   1. If the Speed of Network Required is equal to“1 Gbs” and the Condition of the NetworkEquipment is a 0, then the additional cost of networkingis $4.50 per foot, otherwise there is no additional cost and avalue of $0 should be displayed.
   2. Title this column “High-Speed CostAdjustment.”
5. In an adjacent column, calculate the estimated cost to networkeach building by summing the values resulting from your previouscost calculations (steps 2-4 in the instructions). Title thiscolumn “Estimated Cost of Network beforeDiscount.”
6. Insert a column to the immediate right of the column titled,“Speed of Network Required?.” Enter a formula thatdetermines if (TRUE, FALSE) this is a high-priced facility. Ahigh-priced facility is one that has an Estimated Cost ofNetwork before Discount greater or equal to $6,000. Titlethis column “High Priced Facility?.”
7. Because larger jobs have certain economies of scale in setupand cleanup, a discount is given based on these estimated values tojobs based on their total size. To the right of “EstimatedCost of Network before Discount,” determine the totaldiscounted price, of the job based on the following:
   1. If the Estimated Cost of Network beforeDiscount is less than $6,000, then there is nodiscount.
   2. If the Estimated Cost of Network beforeDiscount is at least $6,000 but less than $17,000, then a7% discount will be given (Hint: multiply the discount % times theEstimated Cost of Network before Discount amount,which is Column J).
   3. If the Estimated Cost of Network beforeDiscount is at least $17,000, then a 10% discount will begiven (Hint: multiply the discount % times the EstimatedCost of Network before Discount amount, which is ColumnJ).
   4. Title this column “Discount.”
8. To the right of Discount, calculate the totalestimated cost of the network for each building. (Hint: you aresubtracting the Discount from theEstimated Cost of Network before Discount). Titlethis column “Total Estimated Cost ofNetwork.”
9. In the row below your calculations, insert a function thatcounts the number of facilities.
10. In the next row, insert a function that counts the number offacilities that received a discount.
11. To the right of these two functions enter the text: “Number ofFacilities” and “Number of Facilities Receiving a Discount,”respectively.
12. Format your worksheet so that it is easy to read and theinformation is clearly identifiable.
13. Be sure to set up your worksheet to include data inputs, sothat your results can be changed quickly when an input is altered.For example, if the discount percent changes from 5% to 7%, yourresults automatically adjust by this change.

Log into Blackboard and Click onAssessments/Lab Assignment 5: Fox Lake Networking. Use yourcompleted spreadsheet to answer the following 10 questions:

1. How many buildings areconsidered "High Priced?"

2. What is the Cost of NewCable for the Dining Hall?

3. What is the Total EstimatedCost of Network for all buildings combined?

4. What is the total Discountgiven for all buildings combined?

5. Which building had thehighest Additional Equipment Cost?

6. Which building received thelargest discount?

7. How many buildings have aTotal Estimated Cost of Network = $0.00?

8. Currently, the cost for nothaving Existing Equipment is $40. What does it need to be for theTotal Estimated Cost of Network for all buildings combined to equal$45,000?

9. What would be the TotalEstimated Cost of Network for all buildings combined if, in Step 2,the cost for not having network equipment is decreased to $35.00per foot?

10. With the cost for nothaving Existing Equipment changed back to $40, what is the totalDiscount for all buildings combined if the discount % dropped from10% to 8.5% for all buildings that had an Estimated Cost of Networkbefore Discount >= $17000?