I need a good explanation this excel presentation
ela4u
spreadsheet_concepts-group_project.xlsxDocumentation Sheet
| C:\Users\Abbas\Downloads\[Spreadsheet Concepts-Group Project.xlsx]Documentation Sheet |
| 8/16/14 0:00 |
| Spreadsheet Administrators |
| Overview: |
| This spreadsheet was designed to organize the data necessary to present to potential homebuyers. The mortgage payments are shown (assuming it is sold at listed price) . It also includes best-case and worst-case scenarios in regards to potential selling price of the home, updating the monthly payments. The database information is filtered to show results from condos in Toronto. |
| Workbook Specifications |
| Throughout Worksheet |
| 6 Worksheets |
| Relative, Mixed, and Absolute References |
| Documentation worksheet |
| Link between worksheets |
| Collected and Entered Data |
| Used AVERAGE, MAX, MIN and COUNT functions |
| 2 Pivot Charts |
| Assisted other group members |
| Used a FINANCIAL FUNCTION(PMT) |
| Used SCENARIO MANAGER |
| Used Conditional Formatting |
| Assisted other group members |
| 1 Pivot Chart |
| PivotTables |
| Included a list and applied DATABASE FUNCTIONS to the list |
| Assisted other group members |
Real-Estate data
| GTA Real-Estate | ||||||||
| Property Type | Agent's First Name | Agent's Last Name | Location | Number of Bedrooms | Listed Price | |||
| Condo | Zia | Abbas | Toronto | 2 | $ 249,900 | Average | $ 819,418 | |
| Condo | Zia | Abbas | Toronto | 1 | $ 414,900 | Max | $ 4,395,600 | |
| Condo | Zia | Abbas | Toronto | 3 | $ 4,395,600 | Min | $ 134,900 | |
| Condo | Zia | Abbas | Toronto | 0 | $ 219,900 | Count | 33 | |
| House | Zia | Abbas | Stouffville | 5 | $ 2,899,999 | |||
| House | Kathryn | Stewart | Mississauga | 4 | $ 1,299,850 | |||
| House | Kathryn | Stewart | Brampton | 4 | $ 659,850 | |||
| House | Kathryn | Stewart | Mississauga | 3 | $ 1,188,850 | |||
| House | Kathryn | Stewart | Toronto | 4 | $ 1,349,850 | |||
| House | Kathryn | Stewart | Mississauga | 4 | $ 1,699,850 | |||
| Condo | Kathryn | Stewart | Toronto | 1 | $ 898,850 | |||
| House | Kathryn | Stewart | Mississauga | 4 | $ 1,649,850 | |||
| House | Kathryn | Stewart | Mississauga | 5 | $ 2,499,850 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 359,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 409,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 2 | $ 579,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 399,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 2 | $ 675,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 339,800 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 285,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 275,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 459,000 | |||
| House | Allan | Tarkmeel | Vaughan | 4 | $ 878,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 385,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 489,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 0 | $ 219,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 0 | $ 230,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 2 | $ 418,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 0 | $ 225,000 | |||
| Condo | Nadeem | Ahmed | Toronto | 1 | $ 259,900 | |||
| Condo | Nadeem | Ahmed | Toronto | 1 | $ 319,000 | |||
| Condo | Nadeem | Ahmed | Toronto | 1 | $ 269,800 | |||
| Condo | Nadeem | Ahmed | Toronto | 2 | $ 134,900 |
Mortgage Information
| Mortgage | 25 | Rate | 8.75% | |
| Interest Rate | 8.75% | Payments per year | 12 | |
| Down Payment | 20% | Type | 0% | |
| Listed Prices | Down Payment | Monthly Payments | ||
| $ 249,900 | $ 49,980 | $ 1,644 | ||
| $ 414,900 | $ 82,980 | $ 2,729 | ||
| $ 4,395,600 | $ 879,120 | $ 28,911 | ||
| $ 219,900 | $ 43,980 | $ 1,446 | ||
| $ 2,899,999 | $ 580,000 | $ 19,074 | ||
| $ 1,299,850 | $ 259,970 | $ 8,549 | ||
| $ 659,850 | $ 131,970 | $ 4,340 | ||
| $ 1,188,850 | $ 237,770 | $ 7,819 | ||
| $ 1,349,850 | $ 269,970 | $ 8,878 | ||
| $ 1,699,850 | $ 339,970 | $ 11,180 | ||
| $ 898,850 | $ 179,770 | $ 5,912 | ||
| $ 1,649,850 | $ 329,970 | $ 10,851 | ||
| $ 2,499,850 | $ 499,970 | $ 16,442 | ||
| $ 359,900 | $ 71,980 | $ 2,367 | ||
| $ 409,900 | $ 81,980 | $ 2,696 | ||
| $ 579,900 | $ 115,980 | $ 3,814 | ||
| $ 399,900 | $ 79,980 | $ 2,630 | ||
| $ 675,000 | $ 135,000 | $ 4,440 | ||
| $ 339,800 | $ 67,960 | $ 2,235 | ||
| $ 285,000 | $ 57,000 | $ 1,874 | ||
| $ 275,000 | $ 55,000 | $ 1,809 | ||
| $ 459,000 | $ 91,800 | $ 3,019 | ||
| $ 878,000 | $ 175,600 | $ 5,775 | ||
| $ 385,000 | $ 77,000 | $ 2,532 | ||
| $ 489,900 | $ 97,980 | $ 3,222 | ||
| $ 219,900 | $ 43,980 | $ 1,446 | ||
| $ 230,000 | $ 46,000 | $ 1,513 | ||
| $ 418,900 | $ 83,780 | $ 2,755 | ||
| $ 225,000 | $ 45,000 | $ 1,480 | ||
| $ 259,900 | $ 51,980 | $ 1,709 | ||
| $ 319,000 | $ 63,800 | $ 2,098 | ||
| $ 269,800 | $ 53,960 | $ 1,775 | ||
| $ 134,900 | $ 26,980 | $ 887 |
Scenarios
| Mortgage | 25 | Rate | 8.75% | ||
| Interest Rate | 8.75% | Payments per year | 12 | ||
| Down Payment | 20% | Selling Price | 100% | ||
| Toronto Real-Estate (Under $400,000) | |||||
| Most-Likely Scenario | |||||
| Location | Number of Bedrooms | Listed Price | Selling Price | Down Payment | Monthly Payment |
| Toronto | 4 | $ 1,349,850 | $ 1,349,850 | $ 269,970 | $ 11,098 |
| Toronto | 3 | $ 4,395,600 | $ 4,395,600 | $ 879,120 | $ 36,138 |
| Toronto | 2 | $ 675,000 | $ 675,000 | $ 135,000 | $ 5,549 |
| Toronto | 2 | $ 579,900 | $ 579,900 | $ 115,980 | $ 4,768 |
| Stouffville | 5 | $ 2,899,999 | $ 2,899,999 | $ 580,000 | $ 23,842 |
| Mississauga | 4 | $ 1,299,850 | $ 1,299,850 | $ 259,970 | $ 10,687 |
| Brampton | 4 | $ 659,850 | $ 659,850 | $ 131,970 | $ 5,425 |
| Mississauga | 3 | $ 1,188,850 | $ 1,188,850 | $ 237,770 | $ 9,774 |
| Toronto | 2 | $ 418,900 | $ 418,900 | $ 83,780 | $ 3,444 |
| Mississauga | 4 | $ 1,699,850 | $ 1,699,850 | $ 339,970 | $ 13,975 |
| Toronto | 2 | $ 249,900 | $ 249,900 | $ 49,980 | $ 2,055 |
| Mississauga | 4 | $ 1,649,850 | $ 1,649,850 | $ 329,970 | $ 13,564 |
| Mississauga | 5 | $ 2,499,850 | $ 2,499,850 | $ 499,970 | $ 20,552 |
| Toronto | 2 | $ 134,900 | $ 134,900 | $ 26,980 | $ 1,109 |
| Toronto | 1 | $ 898,850 | $ 898,850 | $ 179,770 | $ 7,390 |
| Toronto | 1 | $ 489,900 | $ 489,900 | $ 97,980 | $ 4,028 |
| Toronto | 1 | $ 459,000 | $ 459,000 | $ 91,800 | $ 3,774 |
| Toronto | 1 | $ 414,900 | $ 414,900 | $ 82,980 | $ 3,411 |
| Toronto | 1 | $ 409,900 | $ 409,900 | $ 81,980 | $ 3,370 |
| Toronto | 1 | $ 399,900 | $ 399,900 | $ 79,980 | $ 3,288 |
| Toronto | 1 | $ 385,000 | $ 385,000 | $ 77,000 | $ 3,165 |
| Toronto | 1 | $ 359,900 | $ 359,900 | $ 71,980 | $ 2,959 |
| Vaughan | 4 | $ 878,000 | $ 878,000 | $ 175,600 | $ 7,218 |
| Toronto | 1 | $ 339,800 | $ 339,800 | $ 67,960 | $ 2,794 |
| Toronto | 1 | $ 319,000 | $ 319,000 | $ 63,800 | $ 2,623 |
| Toronto | 1 | $ 285,000 | $ 285,000 | $ 57,000 | $ 2,343 |
| Toronto | 1 | $ 275,000 | $ 275,000 | $ 55,000 | $ 2,261 |
| Toronto | 1 | $ 269,800 | $ 269,800 | $ 53,960 | $ 2,218 |
| Toronto | 1 | $ 259,900 | $ 259,900 | $ 51,980 | $ 2,137 |
| Toronto | 0 | $ 230,000 | $ 230,000 | $ 46,000 | $ 1,891 |
| Toronto | 0 | $ 225,000 | $ 225,000 | $ 45,000 | $ 1,850 |
| Toronto | 0 | $ 219,900 | $ 219,900 | $ 43,980 | $ 1,808 |
| Toronto | 0 | $ 219,900 | $ 219,900 | $ 43,980 | $ 1,808 |
PivotTable
| Average Listed Price | Column Labels | |||||
| Row Labels | Brampton | Mississauga | Stouffville | Toronto | Vaughan | Averages |
| Allan | $ 383,473 | $ 878,000 | $ 414,381 | |||
| Kathryn | $ 659,850 | $ 1,667,650 | $ 1,124,350 | $ 1,405,850 | ||
| Nadeem | $ 245,900 | $ 245,900 | ||||
| Zia | $ 2,899,999 | $ 1,320,075 | $ 1,636,060 | |||
| Averages | $ 659,850 | $ 1,667,650 | $ 2,899,999 | $ 570,588 | $ 878,000 | $ 819,418 |
| Minimum Listed Price | Column Labels | |||||
| Row Labels | Brampton | Mississauga | Stouffville | Toronto | Vaughan | Minimum Price |
| Allan | $ 219,900 | $ 878,000 | $ 219,900 | |||
| Kathryn | $ 659,850 | $ 1,188,850 | $ 898,850 | $ 659,850 | ||
| Nadeem | $ 134,900 | $ 134,900 | ||||
| Zia | $ 2,899,999 | $ 219,900 | $ 219,900 | |||
| Minimum Price | $ 659,850 | $ 1,188,850 | $ 2,899,999 | $ 134,900 | $ 878,000 | $ 134,900 |
| Row Labels | Count | |||||
| Brampton | 1 | |||||
| Mississauga | 5 | |||||
| Stouffville | 1 | |||||
| Toronto | 25 | |||||
| Vaughan | 1 | |||||
| Number of Estate | 33 |
Charts
Average Listed Price
Brampton Allan Kathryn Nadeem Zia 659850 Mississauga Allan Kathryn Nadeem Zia 1667650 Stouffville Allan Kathryn Nadeem Zia 2899999 Toronto Allan Kathryn Nadeem Zia 383473.33333333331 1124350 245900 1320075 Vaughan Allan Kathryn Nadeem Zia 878000Houses Per Location
Total
Brampton Mississauga Stouffville Toronto Vaughan 1 5 1 25 1 Brampton Allan Kathryn Nadeem Zia 659850 Mississauga Allan Kathryn Nadeem Zia 1188850 Stouffville Allan Kathryn Nadeem Zia 2899999 Toronto Allan Kathryn Nadeem Zia 219900 898850 134900 219900 Vaughan Allan Kathryn Nadeem Zia 878000
Database
| GTA Real-Estate | ||||||||
| Property Type | Name | Agent's Last Name | Location | Number of Bedrooms | Listed Price | |||
| Condo | Zia | Abbas | Toronto | 2 | $ 249,900 | |||
| Condo | Zia | Abbas | Toronto | 1 | $ 414,900 | |||
| Condo | Zia | Abbas | Toronto | 3 | $ 4,395,600 | |||
| Condo | Zia | Abbas | Toronto | 0 | $ 219,900 | |||
| House | Zia | Abbas | Stouffville | 5 | $ 2,899,999 | |||
| House | Kathryn | Stewart | Mississauga | 4 | $ 1,299,850 | |||
| House | Kathryn | Stewart | Brampton | 4 | $ 659,850 | |||
| House | Kathryn | Stewart | Mississauga | 3 | $ 1,188,850 | |||
| House | Kathryn | Stewart | Toronto | 4 | $ 1,349,850 | |||
| House | Kathryn | Stewart | Mississauga | 4 | $ 1,699,850 | |||
| Condo | Kathryn | Stewart | Toronto | 1 | $ 898,850 | |||
| House | Kathryn | Stewart | Mississauga | 4 | $ 1,649,850 | |||
| House | Kathryn | Stewart | Mississauga | 5 | $ 2,499,850 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 359,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 409,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 2 | $ 579,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 399,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 2 | $ 675,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 339,800 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 285,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 275,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 459,000 | |||
| House | Allan | Tarkmeel | Vaughan | 4 | $ 878,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 385,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 489,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 0 | $ 219,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 0 | $ 230,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 2 | $ 418,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 0 | $ 225,000 | |||
| Condo | Nadeem | Ahmed | Toronto | 1 | $ 259,900 | |||
| Condo | Nadeem | Ahmed | Toronto | 1 | $ 319,000 | |||
| Condo | Nadeem | Ahmed | Toronto | 1 | $ 269,800 | |||
| Condo | Nadeem | Ahmed | Toronto | 2 | $ 134,900 | |||
| Criteria Range | ||||||||
| Property Type | Name | Agent's Last Name | Location | Number of Bedrooms | Listed Price | |||
| Condo | Toronto | Summary of Condos in Toronto | ||||||
| Average | $ 538,119 | |||||||
| Output Range | Max | $ 4,395,600 | ||||||
| Property Type | Name | Agent's Last Name | Location | Number of Bedrooms | Listed Price | Min | $ 134,900 | |
| Condo | Zia | Abbas | Toronto | 2 | $ 249,900 | # of Condos | 24 | |
| Condo | Zia | Abbas | Toronto | 1 | $ 414,900 | |||
| Condo | Zia | Abbas | Toronto | 3 | $ 4,395,600 | |||
| Condo | Zia | Abbas | Toronto | 0 | $ 219,900 | |||
| Condo | Kathryn | Stewart | Toronto | 1 | $ 898,850 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 359,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 409,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 2 | $ 579,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 399,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 2 | $ 675,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 339,800 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 285,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 275,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 459,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 385,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 1 | $ 489,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 0 | $ 219,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 0 | $ 230,000 | |||
| Condo | Allan | Tarkmeel | Toronto | 2 | $ 418,900 | |||
| Condo | Allan | Tarkmeel | Toronto | 0 | $ 225,000 | |||
| Condo | Nadeem | Ahmed | Toronto | 1 | $ 259,900 | |||
| Condo | Nadeem | Ahmed | Toronto | 1 | $ 319,000 | |||
| Condo | Nadeem | Ahmed | Toronto | 1 | $ 269,800 | |||
| Condo | Nadeem | Ahmed | Toronto | 2 | $ 134,900 |
