Business Studies

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.