I am confused how I get the results for these problems 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 clubs 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 Lakes 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 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 Mens Locker Room 155 2 No 100 Mbs Womens 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 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 In an adjacent column, calculate the additional cost of networking equipment based on the following criteria 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 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 Otherwise, no new equipment is needed and a value of $0 should be displayed Title this column Additional Equipment Cost In an adjacent column, calculate the cost adjustment for the Condition of the Network Equipment based on the following criteria 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 Title this column High Speed Cost Adjustment 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 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 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 If the Estimated Cost of Network before Discount is less than $6,000, then there is no discount 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) 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) Title this column Discount 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 In the row below your calculations, insert a function that counts the number of facilities In the next row, insert a function that counts the number of facilities that received a discount To the right of these two functions enter the text Number of Facilities and Number of Facilities Receiving a Discount, respectively Format your worksheet so that it is easy to read and the information is clearly identifiable 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 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 a $29 87 b $19 00 c None of these selections are correct d $29 90 e $18 50 1 points QUESTION 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 a $49,774 55 b $49,875 35 c $49,778 89 d $49,878 93 e None of these selections are correct 1 points QUESTION 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 $17,000 a $4,982 64 b $3,882 83 c $5,770 66 d $5,902 51 e None of these selections are correct

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 01, 2024

I am confused how I get the results for these problems. Excel HW 4: Fox Lake Networking Template Objective: Create a template to estimate the

I am confused how I get the results for these problems.

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 clubs 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 Lakes 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:

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
Mens Locker Room	155	2	No	100 Mbs
Womens 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.

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.

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.
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 Equipmentis 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.
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.
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?.
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.
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.
In the row below your calculations, insert a function that counts the number of facilities.
In the next row, insert a function that counts the number of facilities that received a discount.
To the right of these two functions enter the text: Number of Facilities and Number of Facilities Receiving a Discount, respectively.
Format your worksheet so that it is easy to read and the information is clearly identifiable.
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.

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?

a.
$29.87

b.
$19.00

c.
None of these selections are correct

d.
$29.90

e.
$18.50

1 points

QUESTION 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?

a.
$49,774.55

b.
$49,875.35

c.
$49,778.89

d.
$49,878.93

e.
None of these selections are correct

1 points

QUESTION 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 >= $17,000?

a.
$4,982.64

b.
$3,882.83

c.
$5,770.66

d.
$5,902.51

e.
None of these selections are correct