Question

Excel HW 4: Fox Lake Networking Template

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

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

For the past year, members at Fox Lake Country Club have been complaining about not being able to access the Internet or slow data transmission speeds while at the club. Given your knowledge of spreadsheets, Jeff Lewis, business manager at Fox Lake, has asked you to create an Excel template for estimating the cost of rewiring the club’s facilities. Jeff wants the worksheet to contain some basic input information and automatically calculate an estimated price, so that he can quickly estimate the cost of rewiring Fox Lake’s facilities. The variables to be considered are as follows:

• The dimensions of the building to be networked – length in linear feet
• The condition of existing network equipment, where 0 represents none, where 1 represents excellent, 2 represents reasonable but would consider upgrading, and 3 represents poor condition with immediate updates required.
• Whether or not the facility has existing network equipment (YES or NO)
• Speed of network required

Complete the following:

1. Open a new workbook and save it as “yourlastname”_CostOfNetwork.xlsx. Create a worksheet with the columns and data shown in the table below (see page 2 for the complete table with column headings). Also, include a meaningful title at the top of your worksheet. Ultimately, this worksheet will be used as a template to evaluate competing bids. List all other inputs that are needed for subsequent calculations below the following 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 be copied down the column. Remember, your formulas will need to work when new quantities are substituted into the data entry area. Hint: create input cells and reference those cells in your formulas.

2. To the right of the Speed of Network Required column, calculate the cost of new network equipment. Only the buildings with an Existing Equipment value of “No” will require networking equipment. The cost is $40.00 per foot. If no networking equipment is required, a value of $0 should be displayed. Title this column “Cost of New Cable.”

3. In an adjacent column, calculate the additional cost of networking equipment based on the following criteria:
   1. If the Condition of Network Equipment is 0 or 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 should be displayed.
   4. Title this column “Additional Equipment Cost.”
4. In an adjacent column, calculate the cost adjustment for the Condition of the Network Equipment based on the following criteria:
   1. If the Speed of Network Required is equal to “1 Gbs” and the Condition of the Network Equipment is a 0, then the additional cost of networking is $4.50 per foot, otherwise there is no additional cost and a value of $0 should be displayed.
   2. Title this column “High-Speed Cost Adjustment.”
5. In an adjacent column, calculate the estimated cost to network each building by summing the values resulting from your previous cost calculations (steps 2-4 in the instructions). Title this column “Estimated Cost of Network before Discount.”
6. Insert a column to the immediate right of the column titled, “Speed of Network Required?.” Enter a formula that determines if (TRUE, FALSE) this is a high-priced facility. A high-priced facility is one that has an Estimated Cost of Network before Discount greater or equal to $6,000. Title this column “High Priced Facility?.”
7. Because larger jobs have certain economies of scale in setup and cleanup, a discount is given based on these estimated values to jobs based on their total size. To the right of “Estimated Cost of Network before Discount,” determine the total discounted price, of the job based on the following:
   1. If the Estimated Cost of Network before Discount is less than $6,000, then there is no discount.
   2. If the Estimated Cost of Network before Discount is at least $6,000 but less than $17,000, then a 7% discount will be given (Hint: multiply the discount % times the Estimated Cost of Network before Discount amount, which is Column J).
   3. If the Estimated Cost of Network before Discount is at least $17,000, then a 10% discount will be given (Hint: multiply the discount % times the Estimated Cost of Network before Discount amount, which is Column J).
   4. Title this column “Discount.”
8. To the right of Discount, calculate the total estimated cost of the network for each building. (Hint: you are subtracting the Discount from the Estimated Cost of Network before Discount). Title this column “Total Estimated Cost of Network.”
9. In the row below your calculations, insert a function that counts the number of facilities.
10. In the next row, insert a function that counts the number of facilities that received a discount.
11. To the right of these two functions enter the text: “Number of Facilities” and “Number of Facilities Receiving a Discount,” respectively.
12. Format your worksheet so that it is easy to read and the information is clearly identifiable.
13. Be sure to set up your worksheet to include data inputs, so that your results can be changed quickly when an input is altered. For example, if the discount percent changes from 5% to 7%, your results automatically adjust by this change.

Log into Blackboard and Click on Assessments/Lab Assignment 5: Fox Lake Networking. Use your completed spreadsheet to answer the following 10 questions:

1. How many buildings are considered "High Priced?"

2. What is the Cost of New Cable for the Dining Hall?

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

4. What is the total Discount given for all buildings combined?

5. Which building had the highest Additional Equipment Cost?

6. Which building received the largest discount?

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

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

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

10. With the cost for not having Existing Equipment changed back to $40, what is the total Discount for all buildings combined if the discount % dropped from 10% to 8.5% for all buildings that had an Estimated Cost of Network before Discount >= $17000?

Answer 1

A1	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	Renowation of network - cost analysis
3	Building	Length in feet	Condition of network equipment	Existing equipment	Speed of network required	Cost per foot of cable	Cost of new cable		Additional Equipment Cost		High-speed cost adjustment		Estimated cost of network before discount		High priced facility		Discount		Total estimated cost of Network
4							Formula input	Result	Formula input	Result	Formula input	Result	Formula input	Result	Formula input	Result	Formula input	Result	Formula input	Result
5	Main clubhouse	850	0	Yes	1 Gbs	$             40	=IF(E5="Yes",0,(C5*G5))	-	=IF(D5=0,($C$25*C5),IF(D5=3,($C$25*C5),IF(D5=2,($C$26*C5),0)))	4,250.00	=IF(F5="1 Gbs",IF(D5=0,(4.5*C5),0),0)	3,825.00	=M5+K5+I5	8,075.00	=IF(O5>5999,"TRUE")	TRUE	=IF(O5<6000,0,IF(O5<17000,O5*$D$21,O5*$D$22))	565.25	=O5-S5	7,509.75
6	Outdoor Patio	625	0	No	1 Gbs	$             40	=IF(E6="Yes",0,(C6*G6))	25,000.00	=IF(D6=0,($C$25*C6),IF(D6=3,($C$25*C6),IF(D6=2,($C$26*C6),0)))	3,125.00	=IF(F6="1 Gbs",IF(D6=0,(4.5*C6),0),0)	2,812.50	=M6+K6+I6	30,937.50	=IF(O6>5999,"TRUE")	TRUE	=IF(O6<6000,0,IF(O6<17000,O6*$D$21,O6*$D$22))	3,093.75	=O6-S6	27,843.75
7	Formal Ballroom	700	1	Yes	100 Mbs	$             40	=IF(E7="Yes",0,(C7*G7))	-	=IF(D7=0,($C$25*C7),IF(D7=3,($C$25*C7),IF(D7=2,($C$26*C7),0)))	-	=IF(F7="1 Gbs",IF(D7=0,(4.5*C7),0),0)	-	=M7+K7+I7	-	=IF(O7>5999,"TRUE")	FALSE	=IF(O7<6000,0,IF(O7<17000,O7*$D$21,O7*$D$22))	-	=O7-S7	-
8	Dining Hall	500	0	No	1 Gbs	$             40	=IF(E8="Yes",0,(C8*G8))	20,000.00	=IF(D8=0,($C$25*C8),IF(D8=3,($C$25*C8),IF(D8=2,($C$26*C8),0)))	2,500.00	=IF(F8="1 Gbs",IF(D8=0,(4.5*C8),0),0)	2,250.00	=M8+K8+I8	24,750.00	=IF(O8>5999,"TRUE")	TRUE	=IF(O8<6000,0,IF(O8<17000,O8*$D$21,O8*$D$22))	2,475.00	=O8-S8	22,275.00
9	Maintenance shed	305	2	Yes	100 Mbs	$             40	=IF(E9="Yes",0,(C9*G9))	-	=IF(D9=0,($C$25*C9),IF(D9=3,($C$25*C9),IF(D9=2,($C$26*C9),0)))	1,067.50	=IF(F9="1 Gbs",IF(D9=0,(4.5*C9),0),0)	-	=M9+K9+I9	1,067.50	=IF(O9>5999,"TRUE")	FALSE	=IF(O9<6000,0,IF(O9<17000,O9*$D$21,O9*$D$22))	-	=O9-S9	1,067.50
10	Bar & Grill	330	1	Yes	1 Gbs	$             40	=IF(E10="Yes",0,(C10*G10))	-	=IF(D10=0,($C$25*C10),IF(D10=3,($C$25*C10),IF(D10=2,($C$26*C10),0)))	-	=IF(F10="1 Gbs",IF(D10=0,(4.5*C10),0),0)	-	=M10+K10+I10	-	=IF(O10>5999,"TRUE")	FALSE	=IF(O10<6000,0,IF(O10<17000,O10*$D$21,O10*$D$22))	-	=O10-S10	-
11	Men's locker room	155	2	No	100 Mbs	$             40	=IF(E11="Yes",0,(C11*G11))	6,200.00	=IF(D11=0,($C$25*C11),IF(D11=3,($C$25*C11),IF(D11=2,($C$26*C11),0)))	542.50	=IF(F11="1 Gbs",IF(D11=0,(4.5*C11),0),0)	-	=M11+K11+I11	6,742.50	=IF(O11>5999,"TRUE")	TRUE	=IF(O11<6000,0,IF(O11<17000,O11*$D$21,O11*$D$22))	471.98	=O11-S11	6,270.53
12	Women's locker room	125	2	Yes	100 Mbs	$             40	=IF(E12="Yes",0,(C12*G12))	-	=IF(D12=0,($C$25*C12),IF(D12=3,($C$25*C12),IF(D12=2,($C$26*C12),0)))	437.50	=IF(F12="1 Gbs",IF(D12=0,(4.5*C12),0),0)	-	=M12+K12+I12	437.50	=IF(O12>5999,"TRUE")	FALSE	=IF(O12<6000,0,IF(O12<17000,O12*$D$21,O12*$D$22))	-	=O12-S12	437.50
13	Outdoor Pool	760	3	Yes	1 Gbs	$             40	=IF(E13="Yes",0,(C13*G13))	-	=IF(D13=0,($C$25*C13),IF(D13=3,($C$25*C13),IF(D13=2,($C$26*C13),0)))	3,800.00	=IF(F13="1 Gbs",IF(D13=0,(4.5*C13),0),0)	-	=M13+K13+I13	3,800.00	=IF(O13>5999,"TRUE")	FALSE	=IF(O13<6000,0,IF(O13<17000,O13*$D$21,O13*$D$22))	-	=O13-S13	3,800.00
14
15	Total no. Of facilities																=COUNT(R5:R13)	9
16	Total facilities receiving discount																=COUNTIF(R5:R13,">0")	4
17
18
19
20	Discount % available	upto 6000	0
21		6000-17000	7%
22		above 17000	10%
23
24	Additional equipment cost per foot
25	If condition 0 or 3	5.00
26	If condition 2	3.50
27	If condition 1	-

When you paste this table into excel and the formulas will get auto calculated (the same are shown for understanding purpose) and will get the same result. Once the formulas have been understood please delete the result columns and the spreedsheet will be ready, on the basis of which the 10 questions can be answered.

1. High priced buildings =COUNTIF(O5:O13,">6000") = 4

2. Cost of new cable for Dining hall = U8 = $ 22,275.0

3. Total estimated cost of networking =SUM(U5:U13) = $ 69,2014.03

4. Total discount given=SUM(S5:S13) = $ 6,605.98