DECISION MAKING SCENARIO

The purchase of a house usually entails some exchanges of prices between buyers and sellers. The realtor is there to facilitate the transactions. The seller has an asking price but typically settles for less. The commission paid to Victory Realty is 9% commission on the actual selling price for the first $200,000, and a further 2.2% for the remaining selling price if the selling price is above $400,000. A flat 6% is charged for selling price $400,000 & below. The realtor in turns gets 40% of the total commission paid to Victory Realty.

Victory Realty wishes to encourage its realtors to try to sell the house as close to the asking price of the sellers as possible. To do that Victory Realty will pay additional bonus for house sold at a certain percentage of the asking price. The distribution of extra bonus is calculated as follows:

A extra 1.0% bonus of the actual selling price is paid to the realtor if he/she is able to sell the house at 92% or more of the asking price.

A extra 0.75% bonus of the actual selling price is paid to the realtor if he/she is able to sell the house sold at 88% or more of the asking price.

A extra 0.5% bonus of the actual selling price is paid to the realtor if he/she is able to sell the house sold at less than 88% of the asking price

No extra bonus will be paid for houses sold at less than 82% of the asking price.

Victory Realty revenue is calculated from the commissions paid by the seller minus the commission and extra bonus paid to the realtor. Calculate the percentage of $ earned for each home based on the actual final transacted price. Develop a worksheet to be used by the CEO of Victory Holdings Group of Companies to allow him/her to identify the key profit earned at Victory Realty.

Also use pivot tables to organise Salespersons commission by month with total commission for the first half of the year for each salesperson. Also in another separate pivot table, organise your data to reflect the net profit by house and by month.

REQUIREMENTS

You are required to create on the 1^st spreadsheet (Commissions) a spreadsheet-based decision support model that allows the CEO of Victory Holdings Group of Companies to understand how the economic climate will affect the revenue earned at Victory Realty to compare different scenarios (e.g. Varying percentages collected from owners: 7%, 9%, 11% for the 1^st $200,000; the 2.2% is fixed for all scenarios). Rather than having a separate model for each scenario, you are expected to design a single model and employ scenarios to change only those aspects of the model that varies with the scenarios being considered. At the same time, he is able to use the same workbook to forecast income for his group of companies.

Use advanced MS Excel built-in functions (IF, HLOOKUP, VLOOKUP, etc.) in your spreadsheet wherever you see fits. Also use pivot tables to organise Realtors commission by month with total commission for the first half of the year for each realtor. Also in another separate pivot table, organise your data to reflect the net profit by house and by month.

Address of House	Date Sold	Asking Price	Final Transacted Price	Realtor
2567 Mica Place	17-Jan-22	$1,000,000	$1,225,000	Eric
1288 Pinetree Way	23-Jan-22	$1,323,000	$1,195,000	Eric
204 #81 Elm Street	20-Feb-22	$1,128,000	$925,000	Eric
#101 800 Schoolhouse Ave	11-Mar-22	$415,000	$395,000	Sam
#501 1290 Greenway Pl	24-Feb-22	$990,000	$850,000	Sam
#409 999 Como Lake	4-Apr-22	$888,000	$959,000	Carol
#111 122 Gilmore Ave	15-Apr-22	$412,800	$389,000	Sam
2388 Sugarpine Ave	23-Jun-22	$2,000,000	$1,825,000	Carol
1634 Diamond Cres.	06-Jun-22	$1,029,000	$999,000	Eric
123 Holdom Ave	05-May-22	$875,000	$799,900	Carol
582 #14 West 13th St	27-Feb-22	$450,000	$385,000	Sam