Everyday Excel, Part 2 Coursera Quiz Answers – Networking Funda

All Weeks Everyday Excel, Part 2 Coursera Quiz Answers

Everyday Excel, Part 2 Week 01 Quiz Answers

Quiz 1 Answers

Q1. Which of the following conditional formatting rules was applied to cells A1:C3 to result in the formatted range shown below?

Q2. Which of the following formulas could be placed into cell C4 and dragged down through C4:C16 and would provide the cumulative (starting from the top) number of errors that have occurred in each Process?

• =COUNTIF($B$4:B4,$B$4)
• =COUNTIF($A$4:A4,A4)
• =COUNTIF($A$4:A4,$A$4)
• =COUNTIF(A4:$A$4,A4)

Q3. The worksheet below shows the Log in and Log out times for 10 different people to a specific building over the course of the day. Which of the following formulas has been placed in cell F4 that will calculate the total number of people in the building at the Time entered in cell F3? (Ctrl-Shift-Enter was pressed on versions other than Office 365.)

• =SUM((B2:B11>=$F$3)*(C2:C11<=$F$3))
• =COUNTIF(B2:B11,”<=”&$F$3)+COUNTIF(C2:C11,”>=”&$F$3)
• =SUM((B2:B11<=$F$3)*(C2:C11>=$F$3))
• =AND(B2:B11<=$F$3,C2:C11>=$F$3)

Q4. We wish to determine the rows and columns that the 7 is found in an array. In cell E1, we’ve placed the formula shown, and we’ve copied/pasted this formula into cells E2 and E3. Thus, the values in cells E1:E3 will tell us which row the 7 is found in.

A: What formula has been placed in cell C5?

B: What formula has been placed in cell C6?

Q5. We wish to determine which of the items on List 2 are also on List 1. What formula can be placed in cell D2 and dragged down through D2:D15 that will output a “YES” if the corresponding item in column C is found on List 1 (column A) and will be blank if the item is not found on List 1?

• =COUNTIF($A$2:$A$15,C2,”YES”)
• =IF(COUNTIF($A$2:$A$15,C2))
• =IF(COUNTIF($A$2:$A$15,C2),”YES”,””)
• =IF(COUNTIF(C2,$A$2:$A$15),”YES”,””)
• =COUNTIF($A$2:$A$15,C2)

Everyday Excel, Part 2 Week 02 Quiz Answers

Quiz 2 Answers

Q1. The nominal interest rate for a 10-year, $25,000 loan is 4.75%, compounded quarterly. If we make payments on the loan every month, which of the following shows how we would calculate the monthly payment in Excel?

• =PMT(NOMINAL(EFFECT(0.0475,12),4),120,25000)
• =PMT(NOMINAL(EFFECT(0.0475,4),12)/12,120,25000)
• =PMT(0.0475/12,120,25000)
• =PMT(EFFECT(NOMINAL(0.0475,4),12)/12,120,25000)
• =PMT(0.0475/4,120,25000)

Q2. Charlie applies for and receives a $4,000 interest-only loan. The interest rate (compounded quarterly) is 7%. What is Charlie’s quarterly payment? Round your answer to the nearest dollar, omit the $ sign, and present the absolute value of your answer.

• Comment down correct Answers Below

Q3. You wish to have $5,000 in 10 years from now. How much do you need to invest today at an annual interest rate of 3.5% (compounded monthly) in order to accomplish this financial goal? Round your answer to the nearest dollar, omit the $ sign, and enter you answer as a positive number.

Comment down correct Answers Below

Q4. We start a savings account with a balance of $5000. Then, we wish to add $200/month for 5 years. At that point, we wish to withdraw equal amounts of money in 5 equal disbursements taken at 1-year intervals and at the end of each year (at the ends of years 6, 7,8, 9, and 10). The account will be empty after these 5 withdrawals. Which of the following depicts how we could set this up in a single formula in Excel? Interest rate is 5.5% and compounded monthly.

• =PMT(EFFECT(0.055,12),5,-FV(0.055/12,60,-200,-5000),0)
• =PMT(0.055/12,60,0,FV(0.055/12,60,200,5000))
• =PMT(EFFECT(0.055/12,12),5,0,FV(0.055,12,-200,-5000))
• =PMT(0.055,5,0,FV(0.055/12,60,-200,-5000))
• =PMT(EFFECT(0.055,12),5,0,-FV(0.055/12,60,200,5000))

Q5. We take a 30-year loan with an interest rate of 4% (compounded monthly) for $200,000. Of the 180th payment, how much money goes towards the loan principal? Round your answer to the nearest dollar, omit the $ sign, and enter your answer as a positive number.

Comment down correct Answers Below

Everyday Excel, Part 2 Week 03 Quiz Answers

Quiz 3 Answers

Q1. The purchase cost of a piece of equipment is $14,000, its useful life is 7 years, and its salvage value is $3,000. We choose a certain depreciation method and after the 4th year the value of the asset is $5,357.14. What depreciation method have we used?

• Units of production method
• Sum-of-years method
• Straight-line method
• Double declining balance method

Q2. Your company is required to purchase a new safety system, which costs $100,000 to purchase and install, has a lifetime of 10 years, and has no salvage value. It will require an overhaul in five years at a cost of $30,000. Annual Operating and Maintenance costs (O&M) will be $15,000 per year for the first five years, and will increase to $25,000 per year for the remaining life (including the 10th year). Assume that the annual interest rate is 6%.

What is the net present value of this system? Treat yearly depreciation as positive cash flows, and use straight-line depreciation.

• $(190,695)
• $(237,897)
• $(179,901)
• $(203,903)

Q3. Which of the following options has a higher present value? Assume an annual interest rate of 4.5%.

Option A: Your friend will give you $2000 in 2 years from now.

Option B: Your friend gives you $1000 today, $500 in each of the next 3 years (years 1, 2, and 3) but then you have to give them $1000 in 4 years.

Option C: You lend your friend $4000 today, but he agrees to pay you back $6500 in 2 years from now.

• Option A
• Option B
• Option C

Q4. You invest $12,000 in startup costs (today) to start a part time food truck that sells pies. Your projected sales in the next 5 years are: $2000 in year 1, $3000 in year 2, and $4000 in years 3 through 5. What is the IRR after 5 years? Leave your answer as a percentage, omit the % sign, and round your answer to the nearest tenth of a percentage (e.g., XX.X).

Comment down correct Answers Below

Q5. Which of the following are TRUE statements regarding the material in this week? Select all that apply. (HINT: 3 of these are TRUE!)

• In the IRR function, it is important NOT to include any present values inside the IRR function; present values are added on outside the function.
• If the percentage of useful units produced is 10% each year for 10 years, the depreciation per year calculated using the Units of Production depreciation method is exactly the same as the yearly depreciation calculated using straight line depreciation for the same useful life (10 years).
• The future value of an asset does not depend upon the interest rate.
• The double declining balance method results in greater depreciation amount during the first year than the sum-of-years method.
• Depreciation is important because it reduces the amount of taxes that a company must pay.

Everyday Excel, Part 2 Week 04 Quiz Answers

Quiz 4 Answers

Q1. The volume of a spheroid (shown in figure) is given by the following equation. We wish to perform a two-way case study to determine the simultaneous effects that the values of a and c have on the volume of the spheroid.

• Row input cell = $B$1; Column input cell = $B$2
• Row input cell = $B$2; Column input cell = $B$7
• Row input cell = $B$7; Column input cell = $B$1
• Row input cell = $B$7; Column input cell = $B$4
• Row input cell = $B$2; Column input cell = $B$1

Q2. Revisit the wind chill equation that we saw earlier in the week:

T is temperature in deg F and V is wind velocity in mph. If the temperature outside is 20 deg F, what must be the wind velocity (V) in order for the wind chill to be 0 degrees? Enter your answer rounded to the nearest mph (ones place).

Comment down correct Answers Below

Q3. If we wanted to maximize the function f(x,y) shown in the spreadsheet setup below but we also have the constraint that x + y must equal 4, how would we fill out the Solver box?

• We would Set Objective: B5, To: Value of 4, By Changing Variable Cells: B1:B2, Subject to the Constraint: B4 = Max.
• We would Set Objective: B4, To: Max, By Changing Variable Cells: B1:B2, Subject to the Constraint: B5 = 4.
• We would Set Objective: B1, To: Min, By Changing Variable Cells: B4:B5, Subject to the Constraint: B5 = 4.
• We would Set Objective: B5, To: Min, By Changing Variable Cells: B1:B2, Subject to the Constraint: B4 = 4.

Q4. Which of the following statements are TRUE? Select all that apply.

• The Goal Seek tool can be used to find the maximum of a function.
• The Solver tool can be used to solve targeting problems.
• The Solver tool can be used to maximize two objective cells.
• The Goal Seek tool can be used to solve targeting problems.
• The Goal Seek tool can be used to find where a function of multiple variables (multiple input cells) is equal to zero.

Q5. The equations to calculate the volume and surface area of a cone are given below:

Everyday Excel, Part 2 Week 05 Quiz Answers

Quiz 5 Answers

Q1. Which of the following statements below is true regarding linear regression analysis in Excel?  Select all that apply.

• The Trendline tool can be used to create regression equations for polynomial models.
• The Regression tool outputs important statistical information, like confidence intervals and adjusted correlation coefficient.
• The Trendline tool can be used to create regression equations for complex, custom-defined models.
• The Trendline tool outputs important statistical information, like confidence intervals and adjusted correlation coefficient.
• The Regression tool can be used to create regression equations for complex, custom-defined models.

Q2. The following data presents the number of eggs vs. dry weight in the amphipod Platorchestia platensis. The independent variable is Dry weight and the Number of eggs is the dependent variable. Which of the following options show a single Excel formula that we could use in cell F4 to estimate the Number of eggs as a function of the Dry weight in cell F3? Cell F3 has been named “weight“. Select all that apply.

• =SLOPE(B2:B29,A2:A29)+INTERCEPT(B2:B29,A2:A29)*weight
• =FORECAST.LINEAR(weight,A2:A29,B2:B29)
• =SLOPE(B2:B29,A2:A29)*weight+INTERCEPT(B2:B29,A2:A29)
• =FORECAST.LINEAR(weight,B2:B29,A2:A29)
• =SLOPE(B2:B29,A2:A29)*weight+FORECAST.LINEAR(F3,B2:B29,A2:A29)

Q3. We wish to create the following model relating ∆H_valve to flow rate (Q). ∆H_(dead head) is the intercept.

We have set up the spreadsheet above. To perform the regression, what will we place in the Input X Range of the Regression box?

• $A$2:$A$9
• $C$2:$C$9
• $B$2:$B$9
• $B$2:$C$9

Q4. In which of the following types of regression do we specifically limit the output (dependent variable) to between two values. Note that the independent variable does not have to be limited in range.

• Multilinear regression
• Simple linear regression
• Polynomial regression
• Logistic regression

Q5. The table below presents data on the maximum ice thickness in mm (y), average number of days per year of ice cover (x1), average number of days the bottom temperature is lower than 8°C (x2), and the average snow depth in mm (x3) for 13 lakes in Minnesota.  This data set can be found in the attached file entitled “Ice_Thickness.xlsx”.

Note: Answers may be subject to wrong/missing or not available, please contact us on telegram or comment below if you found correct answers to avoid wrong/misguidance. it will help other students.

Get All Course Quiz Answers of Everyday Excel Specialization

Everyday Excel, Part 1 Coursera Quiz Answers

Everyday Excel, Part 2 Coursera Quiz Answers