Calculate the closing cost

Assignment Help Basic Computer Science

Reference no: EM13812017

1. Let's calculate the Closing Cost (in column M) for ALL properties. It is determined based on the Style of the property and is summarized in the look-up table in R24:T26. Note that on th elook-up table, the first column is the style of the property, the second column is the county code, and the third column is the corresponding closing cost. The county code is not used for the calculation. Select ALL of the correct answers below. You will have to get all the answers right in order to receive credit for this question.

a. The correct formula for property #101 is:

=VLOOKUP(M2,$R$24:$T$26,3,TRUE)

b. The correct formula for property #101 is:

=VLOOKUP(E2,$R$24:$T$26,3,FALSE)

c. The correct formula for property #101 is:

=VLOOKUP(M2,$R$24:$T$26,2,FALSE)

d. The closing cost for property #125 is $400.

e. The correct formula for property #101 is:

=VLOOKUP(E2,$R$24:$T$26,2,TRUE)

2. Go to the bottom of the list. The average of all the Sales Price is listed in cell J120. Notice that Propery #218 is listed but not sold yet. How much does Property #218 need to be sold at (to be shown in cell J119) in order for the average sales price to become $100,800? Only enter whole number in the answer box; no dollar sign, no comma, no decimal point.

<Hint: use goal seek to calculate the Sales Price of Property #218 in J119 based on the conidtion given above>

3. Add a Total Row to the table. Display only the properties with 1 bathroom. What is the Average Sales Price of all the 1 bathroom properties? Write your answer in whle number; no dollar sign, no comma, no decimal.

4. Create a PivotTable to calculate summary statistics. Use the following filed as labels: Style, Bedroom, and Date Sold. And use the Sales Price as the value field. Answer the following questions:

1. What is the number of Condo properties with 2 bedrooms that were sold in May 2013? answer: ______

2. What is the average sales price for all the Ranch properties with 2 bedrooms sold in April 2013? answer: _______

Make sure you enter whole number only; no dollar sign, no comma, no decimal. You will need to get both answers right to receive credit for this question.

5. Conditional format the column Sales Price to highlight the bottom 7% of the price in green. How many properties have a green highlighted cell?

a. 11

b. 10

c. 9

d. 8

e. 7

6. Calculate Condo Fee in column O. Only those properties that are condos with 2 or more bedrooms have to pay a condo fee of $150, listed in cell S7. Enter a formula to calculate condo fees for ALL the properties. What is the correct formula (use property #101 as an example)?

a. =IF(E2="Condo",150,IF(F2>=2,0,0)

b. =IF(AND(F2>=2,E2="Condo"),S7,0)

c. =IF(OR(E2="Condo",F2>=2),S7,0)

d. =IF(AND(E2="Condo",F2>=2),$S$7,0)

e. =IF(OR(F2<2,E2="Condo"),S$7,0)

7. To calculate the warranty cost, the following criteria are used (as in R10:T12):

If a warranty is purchased (that is, the value in Warranty purchase is Y), then the warranty cost is determined by the age of the property. The cost is $200 when the age of the property is less than 5 years old, and $300 when it is greater than or equal to 5 years old. When a warranty is not purchased (that is, the value in Warranty purchase is N), the warranty cost is 0.

Use a nested IF formula to calculate the warranty cost in column L for All the properties (remember to autofill). Choose ALL the correct answers below. You have to pick all the correct answers to receive credit for this question.

1. The correct formula (using propery #121 as an example) is:

=IF(D22<5,200,IF(K22>=5,300,IF(K22="N",0)))

2. The correct formula (using propery #121 as an example) is:

=IF(K22="N",0,IF(D22>=5,300,200))

3. The correct formula (using propery #121 as an example) is:

=IF(D22="Y",300,IF(D22<5,200,0))

4. The warranty cost for property #119 is $300.

5. The warranty cost for property #158 is $200.

8. Sort the table in the following order:

Square Feet, largest to smallest

Style, A to Z

Age, smallest to largest

Date Sold, newset to oldest

Make sure you use the exact order with the correct criteria for each. What is the ID number (in column A) for the first property on the sorted list?

a. 128

b. 140

c. 153

d. 217

e. 193

9. Enter a formula in the cell T29 to calculate the average Sales Price for all the properties with 2 bedrooms. Note that the number of Bedrooms is recorded in column F, and the Sales Price is in column J. Write the result shown in T29 in the answer box in whole number; no dollar sign, no comma, no decimal point.

Attachment:- tables.xlsx

Reference no: EM13812017

Questions Cloud

Use a computer to plot the magnetic field : Determine d such that ∂2B/∂z2 = 0 at z = 0 on the z-axis and show that for this configuration, the third derivative of the field with respect to z is also zero at z = 0.

E-crime in china : E-crime in China

Analyze current state of diffusion of virtualization tech : Normal 0 false false false EN-US X-NONE X-NONE Analyze the current state of..

Business term of significance to business : The "boiled frog" phenomenon is a business term of significance to business in general and to strategic management in particular. Based on your research, please describe how this phenomenon applies to business and particularly to strategic managem..

Calculate the closing cost : Let's calculate the Closing Cost (in column M) for ALL properties. It is determined based on the Style of the property and is summarized in the look-up table in R24:T26

Company gain competitive advantage : Select a company with which you are familiar and write a 2 to 3 page paper including the following: Develop three new IT initiatives that can help your selected company gain competitive advantage and explain your choices in detail. Include at leas..

What fraction of the sky is covered by stars in case : What fraction of the sky is covered by stars in case - You can make the simplifying assumption that stars don't overlap.

Discuss four main element needed to bring an innovative idea : Discuss the four main elements needed to bring an innovative idea to the marketplace. What are then some of the difficulties facing successful innovation?

User Account

All Pages