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.

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?

The taxi and takeoff time for commercial jets is a random variable x with a mean of 8.5 minutes and a standard deviation of 3.1 minutes. Assume that the distribution of taxi and takeoff times is approximately normal. You may assume that the jets are lined up on a runway so that one taxies and takes off immediately after the other, and that they take off one at a time on a given runway.

(a) What is the probability that for 34 jets on a given runway, total taxi and takeoff time will be less than 320 minutes? (Round your answer to four decimal places.)


(b) What is the probability that for 34 jets on a given runway, total taxi and takeoff time will be more than 275 minutes? (Round your answer to four decimal places.)


(c) What is the probability that for 34 jets on a given runway, total taxi and takeoff time will be between 275 and 320 minutes? (Round your answer to four decimal places.)

It's true — sand dunes in Colorado rival sand dunes of the Great Sahara Desert! The highest dunes at Great Sand Dunes National Monument can exceed the highest dunes in the Great Sahara, extending over 700 feet in height. However, like all sand dunes, they tend to move around in the wind. This can cause a bit of trouble for temporary structures located near the "escaping" dunes. Roads, parking lots, campgrounds, small buildings, trees, and other vegetation are destroyed when a sand dune moves in and takes over. Such dunes are called "escape dunes" in the sense that they move out of the main body of sand dunes and, by the force of nature (prevailing winds), take over whatever space they choose to occupy. In most cases, dune movement does not occur quickly. An escape dune can take years to relocate itself. Just how fast does an escape dune move? Let x be a random variable representing movement (in feet per year) of such sand dunes (measured from the crest of the dune). Let us assume that x has a normal distribution with μ = 10 feet per year and σ = 2.7 feet per year.

Under the influence of prevailing wind patterns, what is the probability of each of the following? (Round your answers to four decimal places.)

(a) an escape dune will move a total distance of more than 90 feet in 7 years


(b) an escape dune will move a total distance of less than 80 feet in 7 years


(c) an escape dune will move a total distance of between 80 and 90 feet in 7 years

Mr. Sanchez is a Colombian economist living in Bogota. He is currently analyzing the costs of production. He observes the marginal-cost and average-total-cost curves for a typical firm. He wonders why the curves have the shapes that they do and why they intersect where they do.

Explain!

Kitchen Supply, Inc. (KSI), manufactures three types of flatware: institutional, standard, and silver. It applies all indirect costs according to a predetermined rate based on direct labor-hours. A consultant recently suggested that the company switch to an activity-based costing system and prepared the following cost estimates for year 2 for the recommended cost drivers.

In addition, management estimated 7,400 direct labor-hours for year 2.

Assume that the following cost driver volumes occurred in January, year 2:

Actual labor costs were $16 per hour.

Required:

a. (1) Compute a predetermined overhead rate for year 2 for each cost driver using the estimated costs and estimated cost driver units prepared by the consultant. (Round your answers to 2 decimal places.)

(2) Compute a predetermined rate for year 2 using direct labor-hours as the allocation base. (Round your answer to 2 decimal places.)

b. Compute the production costs for each product for January using direct labor-hours as the allocation base and the predetermined rate computed in requirement a(2). (Do not round intermediate calculations.)

c. Compute the production costs for each product for January using the cost drivers recommended by the consultant and the predetermined rates computed in requirement a. (Note: Do not assume that total overhead applied to products in January will be the same for activity-based costing as it was for the labor-hour-based allocation.) (Do not round intermediate calculations.)

Schultz Electronics manufactures two large-screen television models: the Royale which sells for $1,600, and a new model, the Majestic, which sells for $1,300. The production cost computed per unit under traditional costing for each model in 2020 was as follows.

Assign overhead to products using ABC and evaluate decision.

In 2020, Schultz manufactured 25,000 units of the Royale and 10,000 units of the Majestic. The overhead rate of $38 per direct labor hour was determined by dividing total expected manufacturing overhead of $7,600,000 by the total direct labor hours (200,000) for the two models.

Under traditional costing, the gross profit on the models was Royale $552 or ($1,600 − $1,048), and Majestic $590 or ($1,300 − $710). Because of this difference, management is considering phasing out the Royale model and increasing the production of the Majestic model.

Before finalizing its decision, management asks Schultz’s controller to prepare an analysis using activity-based costing (ABC). The controller accumulates the following information about overhead for the year ended December 31, 2020.

The cost drivers used for each product were:

Instructions Please use an Excel spreadsheet for your answers, clearly marking the answers to each requirement (a) – (c)

a. Assign the total 2020 manufacturing overhead costs to the two products using activity-based costing (ABC) and determine the overhead cost per unit.

b. What was the cost per unit and gross profit of each model using ABC?

c. Are management’s future plans for the two models sound? Explain.

After reviewing the new activity-based costing system that Nancy Chen has implemented at IVC's CenterPoint manufacturing facility, Tom Spencer, the production supervisor, believes that he can reduce production costs by reducing the time spent on machine setups. He has spent the last month working with employees in the plant to change over the machines more quickly with the same reliability. He plans to produce 106,000 units of the Sport model and 43,000 units of the Pro model in the first quarter. He believes that with his more efficient setup routine, he can reduce the number of setup hours for both the Sport and the Pro products by 31 percent.

Cost Drivers and Cost Driver Volumes—CenterPoint Manufacturing Facility

Third Quarter Unit Cost Report, Activity-Based Costing—CenterPoint Manufacturing Facility

Required:

a. Compute the amount of overhead allocated to the Sport and the Pro drones for the first quarter using activity-based costing. Assume that all events are the same in the first quarter as in the third quarter except for the number of setup hours. Assume the cost of a setup hour remains at $900.

3. Describe the twofold cost of sex, the cost of males, and the cost of recombination.
4. What are the differences between sexual and natural selection? Define and explain each (including the basic principles of evolutionary changes and natural selection) with examples from the readings and class.

please explain in about 3-4 paragraph so that I could understand clearer please.

In what sense is the cost of capital an opportunity cost? An opportunity cost to whom? And, Is it possible that there could be sources of financing that have a zero cost?