Fall 2015
Statistics
1. Number of buyers who bought a Condo as a primary residence that had a purchase price between $300,000 and $450,000
(inclusive)
2. Average Down Payment for a buyer who is financing, has a Good payment history, and a credit score of at least 620
3. Highest Total Due at Closing for a buyer of a Duplex which is their primary residence that chose either a Golf or Social
membership
4. Total Closing Fees for buyers that have a credit score between 650 and 750 (inclusive) and bought either a Townhome as
their primary residence or Villa which was not
5. Smallest Deposit for a buyer of a Single Family home with an Excellent payment history who financed their home
Assumptions
Buyer ID First Name Last Name Primary Residence Credit Score Home Type Purchase Price
Membership Type Payment History Deposit Down Payment Closing Fees Financing (Years) Total Due at Closing
AB933 Kristopher Abreu No 702 Condo $286,000 Golf Good 30
AD567 Hannah Adkins No 614 Single Family $791,500 Social Good 15
AG609 Quentin Aguilar Yes 723 Duplez $554,500 Golf Very Good 30
AR680 Eldridge Arnett Yes 752 Villa $1,908,000 Golf Fair 30
AR830 Marica Arnold Yes 595 Townhome $565,000 Totd Good 30
SE795 Freida Sechel Yes 600 Condo $270,500 Wellness Fair 30
AR748 Darcy Arroyo Yes 819 Condo $271,000 Golf Excellent 30
BA962 Jerold Baker No 551 Condo $396,000 Golf Excellent 15
DE041 Elena Dees Yes 776 Condo 272000 Social Very Good 30
BA525 Krista Ballard Yes 571 Single Family 929000 Golf Fair None
BA850 Sally Barten No 783 Condo 366000 Social Very Good 15
BA791 Romeo Bartlett Yes 786 Single Family 732000 Social Good 15
BA544 Cyndy Bass No 804 Villa 1851000 Golf Excellent 30
BE780 Murray Benitez Yes 626 Villa 1634000 Golf Fair 30
BE458 Reggie Besemer Yes 733 Single Family 730500 Golf Very Good 15
BE966 Francine Beth Yes 641 Villa 1850000 Senior Fair 30
BL521 Krysten Black No 694 Single Family 753500 Wellness Very Good 30
BL103 Alva Bland Yes 607 Duplex 748000 Wellness Fair 30
BO309 Landon Boeff No 803 Single Family 683500 Social Excellent 15
BO605 Ashlie Boone Yes 694 Single Family 938000 Wellness Very Good 30
BO358 Josh Botton No 845 Condo 305500 Golf Excellent 30
BO868 Lori Bowman No 847 Single Family 1007500 Social Good 30
BR001 Nicolas Bradford No 824 Single Family 1004500 Wellness Excellent
30
BR747 Kenneth Bramhall Yes 785 Villa 2107500 Social Excellent 30
BR227 Gavin Braun Yes 689 Single Family 653000 Senior Very Good None
BR146 Tiara Braunstein No 676 Condo 265500 Senior Fair 30
BR895 Tameka Britt No 803 Single Family 723500 Wellness Excellent None
BR274 Curt Brower Yes 719 Townhome 516500 Golf Very Good 30
BR767 Chuck Bryers Yes 654 Duplex 762500 Tots Good 15
BU135 Harvey Bunker No 600 Duplex 690500 Wellness Poor 15
BU877 Dana Burke No 789 Villa 2170500 Golf Excellent 30
BU777 Diana Burnett Yes 731 Duplex 824000 Golf Good 30
BY460 Abraham Byers No 828 Townhome 684000 Golf Excellent 15
PE636 Theodore Pelletier Yes 574 Condo 275500 Senior Good None
CA680 Florence Campbell No 671 Townhome 724500 Wellness Very Good
30
EN427 Danielle England Yes 715 Condo 282500 Tots Very Good 15
CA448 Lee Carson No 741 Duplex 503500 Golf Very Good 30
CE408 Willa Ceres No 790 Single Family 1072500 Social Excellent 30
CA744 Ignacio Cameron Yes 835 Condo 307000 Golf Excellent 30
CH372 Carrie Charette No 803 Single Family 1077500 Social Excellent 30
CH571 Deidre Chung Yes 710 Townhome 308500 Golf Good 30
CI301 Jed Cifizzari No 697 Duplex 876500 Golf Very Good 30
CL938 Annabelle Clinger No 648 Single Family 913000 Tots Fair 15
CO919 Mary Coffey No 841 Duplex 717500 Tots Excellent 30
CO922 Jenna Coole Yes 572 Duplex 520500 Social Good None
CO758 Dorothy Cornelis No 648 Duplex 512000 Tots Good 30
CO310 Shane Cox Yes 677 Villa 1928000 Wellness Good 15
CR999 Pablo Craven No 652 Townhome 641500 Social Good 15
CR424 Marcus Creelman No 720 Townhome 511000 Social Very Good None
CR618 Tyrell Crook No 675 Single Family 1128000 Golf Very Good 30
CR184 Greta Crook No 639 Single Family 904500 Golf Good 15
FR386 Bradford Francis Yes 795 Condo 319000 Social Excellent 15
DA924 Libby Davenport Yes 638 Single Family 751500 Wellness Fair 30
DA362 Manuel Davidson No 802 Villa 1320500 Golf Excellent None
SA140 Felicity Savage Yes 667 Condo 324500 Social Very Good 30
DE532 Carlo Dembrow No 607 Townhome 462000 Golf Good 15
DE070 Montgomery Derringer Yes 670 Single Family 861000 Wellness Very Good
15
DI859 Jena Diaz Yes 724 Townhome 313500 Golf Fair None
DI897 Dorit Dillard No 742 Condo 299500 Tots Very Good 30
DI421 Jason Diller No 729 Single Family 663000 Senior Good 30
AT483 Roman Atkins Yes 606 Condo 346500 Wellness Fair 30
SN044 Vannesa Snow Yes 581 Condo 374500 Golf Fair 15
FI817 Kesha Fienstein Yes 734 Duplex 643000 Wellness Very Good 15
BA219 Juanita Ballard Yes 586 Condo 380000 Senior Fair None
FR901 Marlo French No 732 Villa 1235500 Golf Good 30
FR876 Emil Fritz Yes 638 Townhome 528500 Tots Good 15
FU309 Eleanor Fuller Yes 731 Villa 1825000 Golf Good 30
FU607 Cristen Fulton Yes 742 Duplex 885500 Senior Very Good 30
GA752 Pierre Gardner Yes 781 Townhome 320500 Social Very Good 30
GA580 Pam Garrison No 683 Villa 1249000 Social Good 15
GE680 Armand Gentry No 676 Single Family 791000 Tots Good None
GE398 Louise Gerrits No 628 Condo 334000 Golf Fair 15
GE730 Isaac Gette No 656 Condo 335500 Social Good 30
DA982 Kirby Dapper Yes 554 Condo 410500 Senior Good 30
GO981 Antone Goldbaum No 760 Duplex 877500 Senior Very Good 30
GO114 Chuck Gordon No 732 Townhome 534000 Social Good None
GR809 Thaddeus Green Yes 800 Duplex 785000 Senior Excellent 30
GU727 Kathleen Guzman No 744 Townhome 728500 Senior Very Good 30
HA920 Kendall Haas Yes 623 Duplex 808500 Social Fair 30
HA728 Donovan Harrison No 660 Single Family 1143000 Wellness Good 15
HA116 Carl Hartley Yes 789 Duplex 689500 Golf Excellent 15
HE440 Hannah Hernandez Yes 825 Single Family 706500 Golf Excellent 30
HI417 Antonio Hicks Yes 843 Duplex 503000 Golf Excellent None
HI627 Donald Hinchcliffe No 603 Single Family 1134000 Social Good 30
HO877 Doyle Hodges Yes 767 Duplex 560000 Social Very Good 15
HO012 Troy Hopkins No 583 Duplex 579500 Golf Good None
HU867 Casey Huber Yes 583 Single Family 983500 Wellness Good None
LE702 Stephan Lemons Yes 638 Condo 413000 Tots Good 30
HU360 Geraldine Humphrey Yes 827 Condo 420000 Social Excellent 15
JA363 Edward Jarvis Yes 819 Townhome 371000 Golf Excellent 30
JE200 Juliet Jennings Yes 626 Townhome 520000 Wellness Good 30
JE720 Garret Jennings No 656 Villa 2349000 Wellness Good 30
JE518 Andrew Jepson No 835 Duplex 531500 Social Excellent 30
JO380 Ryan Johnston Yes 787 Villa 2123000 Social Excellent 30
KI723 Cornelius Kim Yes 827 Villa 1619500 Senior Excellent 15
KI334 Robert Kingston Yes 822 Single Family 786500 Golf Excellent 30
KI981 Sean Kinzer No 825 Single Family 997000 Wellness Excellent 30
KO338 Scott Koenen Yes 633 Townhome 618000 Senior Good 30
LA355 Gary Lacher Yes 562 Duplex 682500 Senior Fair 15
LA816 Lorraine Lanahan No 675 Condo 278000 Social Very Good 30
LA216 Clifford Lang Yes 835 Single Family 895000 Social Excellent 30
LA719 Jeremy Larson Yes 758 Single Family 1019500 Social Very Good 15
HU390 Leo Hurst Yes 562 Condo 421000 Golf Poor 15
LE347 Miquel Leonard No 620 Duplex 865500 Social Poor 30
LE087 Isidore Lester No 747 Duplex 852500 Senior Very Good 15
LI651 Edwardo Livingston Yes 844 Duplex 821000 Golf Excellent 15
FE433 Barry Feazel Yes 612 Condo 424000 Golf Good 30
MA979 Julia Maffai No 786 Townhome 550500 Golf Excellent 15
MA660 Dominick Macon Yes 566 Condo 444000 Senior Good 30
MA771 May Marsden Yes 820 Villa 1745500 Senior Excellent 30
MA344 Joline Matos Yes 799 Townhome 325000 Social Excellent 30
MA426 Howard Mayer No 687 Villa 1635000 Golf Very Good 15
ME517 Boyd Merritt No 599 Duplex 815000 Wellness Fair 30
ME789 Bill Merritt No 778 Single Family 787500 Social Very Good 15
MI778 Benton Miller No 571 Townhome 424000 Golf Poor 30
MO649 Tina Mondale Yes 833 Duplex 875500 Wellness Excellent 15
MO609 Kandice Moore Yes 743 Townhome 395000 Golf Very Good 30
MO934 Paris Moreaux Yes 750 Single Family 1077500 Tots Very Good 30
NA653 Nancy Navarro No 808 Single Family 884000 Social Excellent 30
NO971 Tanya Noble No 684 Duplex 562000 Senior Good 15
OL449 Wilson Olsen Yes 778 Villa 2392000 Golf Very Good 30
OR732 Mercy Ortega No 780 Townhome 405000 Wellness Very Good 15
PA944 Slyvia Pacheco No 644 Duplex 878500 Social Fair 30
PA176 Quinn Palmer No 784 Townhome 599500 Tots Excellent 15
PA540 Randi Payne Yes 770 Villa 1338500 Wellness Very Good 15
CA101 Thomas Campos Yes 579 Condo 454000 Golf Fair 15
PE802 Cori Perez No 626 Townhome 433000 Wellness Good 15
PE899 Jamie Perpall Yes 616 Townhome 699500 Golf Fair 15
PI806 Lawrence Pierce No 570 Single Family 962000 Tots Good 15
PI189 Vince Pierce Yes 793 Villa 1354500 Wellness Excellent 30
PL483 Shane Planes No 583 Duplex 646000 Social Good 30
PO871 Genevieve Post No 720 Condo 349000 Social Very Good 30
PO610 Destiny Potts Yes 729 Villa 2451500 Wellness Good 30
PR388 Benita Priest No 711 Duplex 522000 Golf Very Good 30
RA050 Joline Rapanos Yes 684 Single Family 953000 Golf Very Good None
RE970 Jerome Renes Yes 571 Single Family 1166000 Tots Fair 30
RI848 Ariane Rivers No 677 Duplex 733000 Social Good 15
RO595 Orlando Roach No 706 Villa 2128500 Golf Good 30
RO707 Meg Roberson Yes 719 Single Family 766000 Social Very Good 30
RU970 David Russell No 561 Townhome 307500 Golf Fair 30
RU475 Clara Russert Yes 683 Duplex 522500 Golf Good 30
RY084 Erin Ryan Yes 811 Single Family 1191500 Golf Excellent 15
SA509 Leanne Sandoval No 761 Townhome 455500 Wellness Very Good
None
SA830 Marco Santiago Yes 790 Villa 1189000 Wellness Excellent 15
GI031 Keira Gillespie Yes 688 Condo 473000 Social Very Good 30
SA357 Emmett Savage Yes 816 Villa 1254500 Golf Excellent 15
SC680 Rickie Schultz No 818 Townhome 426500 Wellness Excellent 15
WA886 Mauricio Warren Yes 572 Condo 476000 Senior Good 30
SH747 Vilma Shea Yes 605 Townhome 548000 Tots Good 15
SH588 Christopher Shepard No 839 Townhome 311000 Golf Excellent 30
SI351 Suanne Simmons No 815 Townhome 515500 Social Excellent 15
SI362 Deana Simpson No 710 Townhome 653000 Social Good 30
SL866 Albert Sloan Yes 584 Duplex 896000 Tots Poor 30
SM061 Jaquelyn Smart No 843 Duplex 580500 Senior Excellent 15
SM296 Lela Smith No 560 Townhome 627500 Wellness Good 30
CE137 Georgene Cerro Yes 844 Condo 480000 Tots Excellent 15
SP963 Robert Sparks No 709 Single Family 1169500 Golf Good 15
SP772 Kenneth Spence Yes 650 Single Family 1093000 Tots Good 15
SP965 Alycia Splinter Yes 600 Villa 1603500 Senior Good 30
SQ126 Madeline Squire Yes 800 Townhome 495000 Golf Excellent 15
ST969 Walter Stein No 810 Villa 1021500 Tots Excellent 30
SW867 Sherman Sweet Yes 664 Duplex 852500 Social Very Good 30
SY656 Michael Sykes Yes 580 Single Family 913500 Tots Poor None
TA763 Augustina Tanner Yes 664 Villa 1985500 Golf Very Good None
TH631 Mollie Thompson Yes 701 Single Family 840500 Golf Fair None
VA378 Jerry Valencia No 758 Single Family 1008500 Social Very Good 30
VA369 Zachary Vann No 591 Condo 261500 Senior Good 30
VA277 Libbie Vann Yes 696 Condo 492000 Golf Very Good 30
VA877 Amanda Vazquez Yes 845 Duplex 595000 Wellness Excellent 30
VI790 Katherine Villa No 783 Condo 424500 Wellness Very Good 15
WA541 Zackary Wallace No 566 Townhome 352500 Wellness Good 15
MA484 Jordan Magliery Yes 681 Condo 496000 Senior Good 15
WA768 Malcom Wassall Yes 671 Single Family 711000 Tots Very Good 30
WE740 Karl Wenderoth No 583 Single Family 838500 Golf Fair 30
WI317 Elliott Wille Yes 606 Single Family 799500 Tots Poor 15
WI480 Tamica Willems No 710 Single Family 1081500 Golf Good None
WO584 Emily Wolfe Yes 723 Townhome 396500 Senior Very Good 15
YA265 Alyce Yardley No 593 Townhome 728000 Wellness Good 30
YU556 Darryl Yu No 835 Single Family 1148500 Social Excellent 30
ZA985 Leona Zain No 706 Duplex 697000 Senior Good None
ZA999 Kymberly Zamora Yes 817 Single Family 1050000 Social Excellent None
ZA118 Douglas Zavala No 725 Townhome 703500 Social Good 30
ZU375 Felipe Zuniga No 780 Condo 478000 Golf Very Good 15
1. Build formulas for J27, K27, L27, N27, and O27. Before doing so, you will need to read the bulleted list below and create
the assumption area for the variable data. The assumption area should be created in the space provided (A10:O24). Assumptions
should be used for all variables as well as for all of the criteria or comparison values in conditions that you set up. You
may use as many columns and rows in the assumption area as you need – and leave the remaining blank. Do not delete any
rows/columns/cells, nor should you insert any new rows/columns/cells. Make sure your assumption area is attractive and well
organized, as well as easy to read and understand. After building your formulas, copy the formulas down their respective
columns and format the cells/columns appropriately. Do not use defined names in building the formulas and do not correct any
spelling errors in the table data or you will lose points.
• The assumption area should be titled as such and must be constructed so that it is easy to read and understand. You should
not use abbreviations in your labels, but should ensure that your labels are clear and concise. Enter your labels and values,
but do not format the assumption area at this time. You should wait to format the assumption area until after you have
finished formatting the main body of the spreadsheet. Given that your column widths should be set for the entries in the main
body of the spreadsheet rather than those in the assumption area, you will most likely need to move things around in the
assumption area after you have set your column widths. Other formatting (formatting of numerical entries, changing alignment,
changing the row heights in order to wrap the text of the labels in the cells, merging cells, etc.) should also be done after
setting your column widths. Note that the font size should be kept at 12 points to be consistent with the rest of the
spreadsheet.
• The Deposit (column J) is the portion of the purchase price that is paid when the purchase price is agreed upon and the
contract signed. It initiates the loan approval process for the financing of the property (a single family home, condo,
duplex, townhome, or villa). Note that the new owner does not take possession of the property when the deposit is paid, but
must wait until the closing date for the loan. The deposit is calculated as a percentage of the purchase price and the
current deposit percentage depends on the purchase price as follows:
Deposit
Purchase Price Percentage
Under $400,000 15.30%
$400,000 – $649,999 12.75%
$650,000 – $999,999 10.65%
$1,000,000 – $1,749,999 8.50%
Over $1,749,999 6.45%
• The Down Payment (column K) is the required upfront portion of the total purchase price and it is paid in cash at the time
of the closing. The down payment is calculated as a percentage of the purchase price. The percentage used in the calculation
depends whether or not the home is the buyer’s primary residence as well as on the buyer’s credit information as follows:
• The current down payment percentage is 15.75% for buyers who meet the following qualifications:
• The home is the buyer’s primary residence
• Payment History is Excellent or Very Good
• Credit Score is at least 650
• The current down payment percentage is 18.25% for buyers who meet the following qualifications:
• The home is not the buyer’s primary residence
• Payment History is Excellent or the Credit Score is at least 750
• For all other buyers, the current down payment percentage is 23.55%
• The Closing Fees (column L), which are the same for all buyers, are costs that must be paid in cash at the time of the
closing and include the processing fees (which are a percentage of the purchase price), the appraisal fee, the inspection
fee, and the title search as follows.
Type of Fee Amount
Processing Fee 3.75%
Appraisal Fee: $600
Inspection Fee: $425
Title Search: $275
Please note, that in an effort to promote the sale of certain homes that have not been selling well, the company has decided
to offer a discount on the closing fees (currently $5,000) if the buyer purchases either:
• a townhome or condo, either of which have a purchase price between $400,000 and $600,000 inclusive or
• a villa with a purchase price of at least $1,500,000
• No discount is currently being offered for any other type of purchase.
• The Total Due at Closing (column N) is the purchase price minus the deposit (as it was paid when the contract was signed)
plus the closing fees, the one-time only membership initiation fee, the first month’s membership fee, and the first two
month’s home owner’s fees. Do not subtract the Down Payment at this time as it is part of the total due at closing. It will
be subtracted out of the purchase price when we calculate the amount to be financed for the monthly payment calculation.
• The one-time only membership initiation fee is dependent on the membership type as follows:
• The one-time only membership initiation fee for a Golf membership is currently $50,000
• The one-time only membership initiation fee for a Senior membership is currently $15,000
• The one-time only membership initiation fee for a Social membership is currently $35,000
• The one-time only membership initiation fee for a Tots membership is currently $10,000
• The one-time only membership initiation fee for a Wellness membership is currently $25,000
• The monthly membership fee is dependent on the membership type as follows:
• The monthly membership fee for a Golf membership is currently $900
• The monthly membership fee for a Senior membership is currently $450
• The monthly membership fee for a Social membership is currently $650
• The monthly membership fee for a Tots membership is currently $250
• The monthly membership fee for a Wellness membership is currently $285
• The home owner’s fees are dependent on the type of home as follows:
• The home owner’s fees for a Condo are currently $240 a month
• The home owner’s fees for a Duplex are currently $360 a month
• The home owner’s fees for a Single Family Home are currently $480 a month
• The home owner’s fees for a Townhome are currently $300 a month
• The home owner’s fees for a Villa are currently $550 a month
• The Monthly Payment (column O) is calculated using the following financing information:
• The annual interest rate depends on the buyer’s Credit Score as follows:
Annual
Credit Score Interest Rate
Below 550 4.95%
550 – 624 4.65%
625 – 774 4.15%
775 – 799 3.75%
Over 799 3.45%
• The amount financed is the purchase price minus the deposit and down payment.
• If the buyer is not financing the purchase through Gulf View Homes [as indicated by “None” in the Financing (Years)
column], but is paying the total due at closing in cash at the time of the closing, “All Cash” should be displayed in the
cell.
2. Statistics 1 and 2 (to be entered in O5 and O6 respectively) should be determined using a COUNTIFS, AVERAGEIFS, or SUMIFS
function, as appropriate. Statistics 3 – 5 (to be entered in O7:O9) should be determined using the appropriate database
function. The criteria for all statistics should be created on a separate sheet, with all criteria on the same worksheet and
labeled 1 – 5 so as to indicate the statistic for which they were developed. The criteria for statistics 1 – 2 can be entered
as you see fit, but the criteria for statistics 3 – 5 must be entered in criteria ranges as appropriate for a database
function.
3. Format the entire spreadsheet (assumption area and main body) so that it is professional in appearance, attractive, and
easy to read and understand. DO NOT USE THE FORMAT AS TABLE COMMAND. Follow the general rules for formatting that we have
discussed in class this semester.