Property Analysis (40 marks) Display the property sale sheet a. Freeze the headers so that when a user scrolls down, they will still be able to see the titles. (5 marks) b. Create a table named profit analysis for the range of your data (from A1 to E201). (5 marks) C. Set the table style to White, Table Style Medium 1 (Hint: turn off the Filter Buttons to be able to work with your data later) (5 marks) d. Add a new column after Sale price and name it price per sq 2. use a structured reference to create the formula to calculate the price per square feet of a property (Hint: sale price divided by square feet) (5 marks) e. Develop a process to provide a list of all the data that match the following criteria: (15 marks) Do not do it manually. It must be a process that allows user to change the search criteria and get the results accordingly. Location: Surrey House Type: Detached f. Set the Print Area to the results of your search using absolute field references. (5 marks) Data Page Layout Home Insert Formulas Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit fix A B Location House Type Burnaby Condo Maple Ridge Condo Maple Ridge Duplex Vancouver Detached 5 Surrey Detached 7 Maple Ridge Detached 8 Surrey Duplex 9 Surrey Detached 10 Maple Ridge Townhouse 11 Vancouver Duplex 12 Coquitlam Duplex 13 Mission Townhouse 14 Maple Ridge Duplex 15 Maple Ridge Detached 16 Coquitlam Condo 17 Surrey Detached 18 Mission Detached 19 Coquitlam Duplex 20 Burnaby Condo 21 Vancouver Duplex 22 Mission Duplex 23 Vancouver Duplex 24 Burnaby Duplex 25 Maple Ridge Duplex 26 Maple Ridge Detached 27 Coquitlam Condo 28 Vancouver Detached 29 Surrey Detached 30 Mission Duplex 31 Surrey Condo 32 Mission Detached D Sale date Square Feet 43687 1154 44443 1154 44391 1161 44350 1175 44239 1180 43982 1207 44463 1210 43821 1217 44530 1220 44498 1221 44623 1236 43839 1237 43629 1238 43848 1241 43549 1243 44127 1274 44006 1282 43872 1289 44071 1306 43549 1307 44204 1315 43764 1321 44356 1327 43978 1327 44332 1330 44242 1341 43598 1346 44399 1351 44529 1357 44582 1367 44185 1368 E Sale Price 1143358 1543363 1115041 766051 820737 1745296 689104 765988 1245250 1300480 741997 1333351 1358422 1447729 871240 1136536 752521 829768 1535362 1107157 1337524 1053628 584344 976093 662497 1385863 1783111 580438 1023124 848629 1252606 33 COOL Pronerty sale Cost-volume Fire damage C D 43596 1369 1385 1410 1423 1431 1441 1458 1483 1523 1527 1543 1545 1557 B Surrey Townhouse 4 Surrey Detached 5 Vancouver Condo 6 New Westminste Detached 57 Burnaby Duplex 38 Maple Ridge Detached 39 New Westminster Townhouse 40 Coquitlam Condo 41 Maple Ridge Condo 42 Maple Ridge Duplex 43 Vancouver Duplex 44 Mission Condo 45 Coquitlam Townhouse 46 New Westminster Townhouse 47 Mission Townhouse 48 Mission Detached 49 Mission Detached 50 New Westminstei Detached 51 Mission Townhouse 52 Vancouver Duplex 53 Maple Ridge Duplex 54 Coquitlam Condo 55 Burnaby Condo 56 Surrey Duplex 57 Maple Ridge Detached 58 Maple Ridge Condo 59 Mission Condo 60 Maple Ridge Condo 61 Surrey Townhouse 62 Mission Condo 63 Vancouver Townhouse 64 Burnaby Townhouse Property sale Cost-volume 44143 43814 43543 44449 44244 44154 44214 44166 44337 44409 43852 44068 43690 43716 44135 43909 44106 43823 44210 43634 43909 43986 43793 44357 43930 44163 44087 44175 43697 44480 44278 1562 1575 1584 E 599584 1056211 1058272 1126999 1835044 1535545 1249384 1730872 1499251 870820 1408903 932059 1200787 653710 659323 754660 1719355 1597114 1670140 1454152 1550584 1582135 1011067 1595818 733600 844519 656890 1308760 897628 1033411 1165339 1402075 1589 1596 1632 1633 1636 1645 1648 1684 1699 1711 1715 1717 1724 1726 1727 1738 LALO - Fire damage 44118 43738 65 Coquitlam Townhouse 66 Vancouver Townhouse 67 Maple Ridge Duplex 68 Mission Duplex 69 Burnaby Detached 70 Burnaby Detached 71 Mission Townhouse 72 Maple Ridge Townhouse 73 Coquitlam Detached 74 Burnaby Detached 75 Mission Duplex 76 Maple Ridge Townhouse 77 Vancouver Townhouse 78 Surrey Duplex 79 Coquitlam Condo 80 Mission Condo 81 Vancouver Detached 82 Maple Ridge Duplex 83 Maple Ridge Duplex 84 Vancouver Detached 85 Coquitlam Townhouse 86 Surrey Detached 87 Surrey Detached 88 Vancouver Detached 89 New Westminste Detached 90 Vancouver Detached 91 Maple Ridge Duplex 92 Maple Ridge Detached 93 Maple Ridge Detached 94 Coquitlam Detached 95 Burnaby Duplex 96 Surrey Detached Property sale Cost-volume 44470 44547 43946 44608 44053 44046 44077 43626 43922 43976 44144 43829 44104 44580 44337 43552 44135 44150 44066 43772 44084 44115 43931 44596 43864 44130 44269 44006 44419 44468 1739 1741 1745 1769 1781 1795 1815 1816 1832 1853 1856 1862 1873 1884 1913 1915 1940 1943 1957 1958 1965 1966 1968 1990 2007 2011 2029 2033 2034 2041 2051 2053 1437472 1122994 1535536 1672918 1270753 28096 1002688 1151800 1184593 677056 1433434 603394 1418026 1394509 1559797 1352878 1135366 808348 1610824 1576624 1499863 1638355 871603 1419193 1257496 812344 964660 1591381 1200460 971185 835945 1002241 A A A Fire damage Property Analysis (40 marks) Display the property sale sheet a. Freeze the headers so that when a user scrolls down, they will still be able to see the titles. (5 marks) b. Create a table named profit analysis for the range of your data (from A1 to E201). (5 marks) C. Set the table style to White, Table Style Medium 1 (Hint: turn off the Filter Buttons to be able to work with your data later) (5 marks) d. Add a new column after Sale price and name it price per sq 2. use a structured reference to create the formula to calculate the price per square feet of a property (Hint: sale price divided by square feet) (5 marks) e. Develop a process to provide a list of all the data that match the following criteria: (15 marks) Do not do it manually. It must be a process that allows user to change the search criteria and get the results accordingly. Location: Surrey House Type: Detached f. Set the Print Area to the results of your search using absolute field references. (5 marks) Data Page Layout Home Insert Formulas Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit fix A B Location House Type Burnaby Condo Maple Ridge Condo Maple Ridge Duplex Vancouver Detached 5 Surrey Detached 7 Maple Ridge Detached 8 Surrey Duplex 9 Surrey Detached 10 Maple Ridge Townhouse 11 Vancouver Duplex 12 Coquitlam Duplex 13 Mission Townhouse 14 Maple Ridge Duplex 15 Maple Ridge Detached 16 Coquitlam Condo 17 Surrey Detached 18 Mission Detached 19 Coquitlam Duplex 20 Burnaby Condo 21 Vancouver Duplex 22 Mission Duplex 23 Vancouver Duplex 24 Burnaby Duplex 25 Maple Ridge Duplex 26 Maple Ridge Detached 27 Coquitlam Condo 28 Vancouver Detached 29 Surrey Detached 30 Mission Duplex 31 Surrey Condo 32 Mission Detached D Sale date Square Feet 43687 1154 44443 1154 44391 1161 44350 1175 44239 1180 43982 1207 44463 1210 43821 1217 44530 1220 44498 1221 44623 1236 43839 1237 43629 1238 43848 1241 43549 1243 44127 1274 44006 1282 43872 1289 44071 1306 43549 1307 44204 1315 43764 1321 44356 1327 43978 1327 44332 1330 44242 1341 43598 1346 44399 1351 44529 1357 44582 1367 44185 1368 E Sale Price 1143358 1543363 1115041 766051 820737 1745296 689104 765988 1245250 1300480 741997 1333351 1358422 1447729 871240 1136536 752521 829768 1535362 1107157 1337524 1053628 584344 976093 662497 1385863 1783111 580438 1023124 848629 1252606 33 COOL Pronerty sale Cost-volume Fire damage C D 43596 1369 1385 1410 1423 1431 1441 1458 1483 1523 1527 1543 1545 1557 B Surrey Townhouse 4 Surrey Detached 5 Vancouver Condo 6 New Westminste Detached 57 Burnaby Duplex 38 Maple Ridge Detached 39 New Westminster Townhouse 40 Coquitlam Condo 41 Maple Ridge Condo 42 Maple Ridge Duplex 43 Vancouver Duplex 44 Mission Condo 45 Coquitlam Townhouse 46 New Westminster Townhouse 47 Mission Townhouse 48 Mission Detached 49 Mission Detached 50 New Westminstei Detached 51 Mission Townhouse 52 Vancouver Duplex 53 Maple Ridge Duplex 54 Coquitlam Condo 55 Burnaby Condo 56 Surrey Duplex 57 Maple Ridge Detached 58 Maple Ridge Condo 59 Mission Condo 60 Maple Ridge Condo 61 Surrey Townhouse 62 Mission Condo 63 Vancouver Townhouse 64 Burnaby Townhouse Property sale Cost-volume 44143 43814 43543 44449 44244 44154 44214 44166 44337 44409 43852 44068 43690 43716 44135 43909 44106 43823 44210 43634 43909 43986 43793 44357 43930 44163 44087 44175 43697 44480 44278 1562 1575 1584 E 599584 1056211 1058272 1126999 1835044 1535545 1249384 1730872 1499251 870820 1408903 932059 1200787 653710 659323 754660 1719355 1597114 1670140 1454152 1550584 1582135 1011067 1595818 733600 844519 656890 1308760 897628 1033411 1165339 1402075 1589 1596 1632 1633 1636 1645 1648 1684 1699 1711 1715 1717 1724 1726 1727 1738 LALO - Fire damage 44118 43738 65 Coquitlam Townhouse 66 Vancouver Townhouse 67 Maple Ridge Duplex 68 Mission Duplex 69 Burnaby Detached 70 Burnaby Detached 71 Mission Townhouse 72 Maple Ridge Townhouse 73 Coquitlam Detached 74 Burnaby Detached 75 Mission Duplex 76 Maple Ridge Townhouse 77 Vancouver Townhouse 78 Surrey Duplex 79 Coquitlam Condo 80 Mission Condo 81 Vancouver Detached 82 Maple Ridge Duplex 83 Maple Ridge Duplex 84 Vancouver Detached 85 Coquitlam Townhouse 86 Surrey Detached 87 Surrey Detached 88 Vancouver Detached 89 New Westminste Detached 90 Vancouver Detached 91 Maple Ridge Duplex 92 Maple Ridge Detached 93 Maple Ridge Detached 94 Coquitlam Detached 95 Burnaby Duplex 96 Surrey Detached Property sale Cost-volume 44470 44547 43946 44608 44053 44046 44077 43626 43922 43976 44144 43829 44104 44580 44337 43552 44135 44150 44066 43772 44084 44115 43931 44596 43864 44130 44269 44006 44419 44468 1739 1741 1745 1769 1781 1795 1815 1816 1832 1853 1856 1862 1873 1884 1913 1915 1940 1943 1957 1958 1965 1966 1968 1990 2007 2011 2029 2033 2034 2041 2051 2053 1437472 1122994 1535536 1672918 1270753 28096 1002688 1151800 1184593 677056 1433434 603394 1418026 1394509 1559797 1352878 1135366 808348 1610824 1576624 1499863 1638355 871603 1419193 1257496 812344 964660 1591381 1200460 971185 835945 1002241 A A A Fire damage