Using the data compiled on the "Master Data" tab, complete the 3 tables using FORMULAS

Loan Loan Number Seller Name 83449837 Shanna's Loan Headquarters 4120064 Santa's L akers 250559 Shanna's Loan Headquarters 63173857 Shere's Loan Headquarters 51.26213 Santa's L akers 75906141 Shanna's Loan Headquarters 21071019 Sana's Linearters 53651842 Shanna's Loan Headquarters 68573753 Shanra's Loan Headquarters 353.2476 Shanta's Loan Headquarters NMD GENOEG 33275435 Shara's Logn Headquarters 89076715 Shanta's Loan Header 683 Sans LINHALTE E 234376.3 Shanna's Loan Headquarters 634.5670 Shante's Loan Head er 72221710 Sarasin pariters 66901329 Shanna's Loan Headquarters 14201615 Senten ceurs 33495177 Shanna's lon Headquarters Loan Amount Annual Rate Term (Years) Payment Payments Made Current Balance Loan Type Agency Delinquency (Months) Status Foreclosure Foreclosure Comment Foreclosure Step 280,000 39 151,740,841 $194,232.77 VA SNV This Loans in Stoplument 320,000 39 151, 56 S6,256.5 UKIN This Lin is in Stop: ry 2:2000 151521821 SIS4,504.53 USCA Durem N This loan is in Step: $1.7,582.59 VA SNV This loan is in step: 152,392.41 $108,724.40 UCIN This Lin is in Steyn 152 256641 $144,115.13 IOONV This Loan is in Stepa 10 54001.721 $127.065.74 USDA 10 mo 17cm $29.75.29 LDA This ronisin Step:ry's 370,000 152 125.071 $335,782.51 IOONV This Loan is in Stepnya 243,000 24300 151.146 71 $265.6510 UDN FNM This Lonis in Steps 120,000 151490.52 $23,653.50 FHA This Loan is in step: $11,214.67 FMA 100.000 gas 51.165.67 FHA This Logn is in Step Sale $163,207.46 FHA This loan is in step: 29,00 $215,42.5 UCDN 240.000 15L 296241 $:5,331.27 UCON FNV 289,000 151,495.25 $215, 201.63 USDA o Durun N This loan is in step: 5181,151.76 VA This regnis in Step: Feterral 300.000 1525.7.14 Sz52.574. L. USDA 1 30 60 N 200,000 343.000 9291.9 VA This Loan Is In Step: Referral 390.000 $2.0,526,07 10ONY 3 30 N 23,000 SZIK, 65.41 UCUN 6.OGA 5326,583.24 VA This Loan Is In Step: Service This oan is in Stepny's $211,4 LEDA The Lean is in Step: ry 350,000 151.988.68 $308,620.10 VA GNVA Current N This loan is in Stepwa This is in stupiry's 151.500 5 177.15 UDN This can Is In Step:r's 100,000 $79,130.54 VA This Loan is in Step: Sale 2 30-60 N This Linn Step: 253,000 2.7GM 30 151,835 $239,450.67 UCON This lon is in Step: Sale 389,000 154545.601 $373,649.10 VA This loan is in step. Sale SS SSS 501.18 Sena LIHAT 52137527 Shanna's Loan Headquarters 49 Santa's Linearters 67342178 Shanna's Loan Headquarters 51105AG Shana's leaders 39064297 Shanna's Loan Headquarters 9239240525- n un t unters G9205733 Shana's Loan Headquarters 14023128 Saar's Logn head T S & 320,000 0 This Loan Is In Step: Service 95865629 Shanna's Loan Headquarters S22915.6 Sanresantedat 153.618.631 15781.06 53:2,375.03 USDA $17,926.66 FHA 320,000 40,000 Het 20152 721201 30 51,541.751 157269 10 52.48.601 This loan is in Step: ludement This canis in Stup: aferral This Is In Sten: Peter This loan is in Step: Service This unis in Stellen This loon is in Step: 260,000 183,000 OM OSSO This Lion is in Stop: Sarv This Loan is in Step: Service 15625.03 84177304 Shanna's Loan Headquarters 1250 Shere's na tura 5967157 Shanna's Croanlagters 79015075 Shanna's Loan Headquarters 198342) Shan katters 19725073 Shanna's Loan Headquarters 72592476 Shanna's Loan Headquarters 51751139 Santa's Link ers 74930308 Shanna's Loan Headquarters 82625844 Sandstedt A Sana's Linearters 95805855 Shanna's Loan Headquarters 256355 Shanes n atura BRA Sana's leadquarters 8.807153 Shanna's Loan Headquarters 14:14 S- Lin e rs 97257211 Shanna's Loan Headquarters 9419.5050 Samsson headquarters $202,591.05 UCONY $263,929.6 VA Sa. 042.57 UDN $174.142.15 FHA 447,2M VA SIS. 293. U ONN $385,501.50 FHA $116,491.54 USDA $133,733.00 VA $86,751.57 UCON 5201,.42 UTN SS4 114.80 FH4 $125, 7 73 FHA $5304. VA $107, 195.32 UCONY $123,210. UCUN $65.532 45 100KY 213,000 140.000 99,000 mo 200,000 139,000 1230 120,000 10 1931.191 10 151,887.921 This ren is in step:ry's This Loan Is In Step.ludgment 7.25% FNMA This Is In Step: Retral This Loans in Stop:iudgment 158.B.63 $1.11.16 572 18.034.821 23 39 10 FHUMC 90.000 310,000 50- This loan is in Steprva This man is in lupa FINAL REPORT 6a. Using the data compiled on the "Master Data" tab, complete the 3 tables using FORMULAS 6b. Using the data compiled on the "Master Data" tab, recreate tables 1 & 2 using PIVOT TABLES Table 1 Weighted Current Balance Average Rate* # of Loans 38 Sellers Ed's Quality Loan-Mart Ed's Quality Loan-Mart Ken's Discount Value Loans Ken's Discount Value Loans Shanna's Loan Headquarters Shanna's Loan Headquarters *Weighted by Current Balance Loan Rate >=5% =5% =5% 8.01% 8.01% 8.01% 8.01% Total: *Weighted by Current Balance Loan Loan Number Seller Name 83449837 Shanna's Loan Headquarters 4120064 Santa's L akers 250559 Shanna's Loan Headquarters 63173857 Shere's Loan Headquarters 51.26213 Santa's L akers 75906141 Shanna's Loan Headquarters 21071019 Sana's Linearters 53651842 Shanna's Loan Headquarters 68573753 Shanra's Loan Headquarters 353.2476 Shanta's Loan Headquarters NMD GENOEG 33275435 Shara's Logn Headquarters 89076715 Shanta's Loan Header 683 Sans LINHALTE E 234376.3 Shanna's Loan Headquarters 634.5670 Shante's Loan Head er 72221710 Sarasin pariters 66901329 Shanna's Loan Headquarters 14201615 Senten ceurs 33495177 Shanna's lon Headquarters Loan Amount Annual Rate Term (Years) Payment Payments Made Current Balance Loan Type Agency Delinquency (Months) Status Foreclosure Foreclosure Comment Foreclosure Step 280,000 39 151,740,841 $194,232.77 VA SNV This Loans in Stoplument 320,000 39 151, 56 S6,256.5 UKIN This Lin is in Stop: ry 2:2000 151521821 SIS4,504.53 USCA Durem N This loan is in Step: $1.7,582.59 VA SNV This loan is in step: 152,392.41 $108,724.40 UCIN This Lin is in Steyn 152 256641 $144,115.13 IOONV This Loan is in Stepa 10 54001.721 $127.065.74 USDA 10 mo 17cm $29.75.29 LDA This ronisin Step:ry's 370,000 152 125.071 $335,782.51 IOONV This Loan is in Stepnya 243,000 24300 151.146 71 $265.6510 UDN FNM This Lonis in Steps 120,000 151490.52 $23,653.50 FHA This Loan is in step: $11,214.67 FMA 100.000 gas 51.165.67 FHA This Logn is in Step Sale $163,207.46 FHA This loan is in step: 29,00 $215,42.5 UCDN 240.000 15L 296241 $:5,331.27 UCON FNV 289,000 151,495.25 $215, 201.63 USDA o Durun N This loan is in step: 5181,151.76 VA This regnis in Step: Feterral 300.000 1525.7.14 Sz52.574. L. USDA 1 30 60 N 200,000 343.000 9291.9 VA This Loan Is In Step: Referral 390.000 $2.0,526,07 10ONY 3 30 N 23,000 SZIK, 65.41 UCUN 6.OGA 5326,583.24 VA This Loan Is In Step: Service This oan is in Stepny's $211,4 LEDA The Lean is in Step: ry 350,000 151.988.68 $308,620.10 VA GNVA Current N This loan is in Stepwa This is in stupiry's 151.500 5 177.15 UDN This can Is In Step:r's 100,000 $79,130.54 VA This Loan is in Step: Sale 2 30-60 N This Linn Step: 253,000 2.7GM 30 151,835 $239,450.67 UCON This lon is in Step: Sale 389,000 154545.601 $373,649.10 VA This loan is in step. Sale SS SSS 501.18 Sena LIHAT 52137527 Shanna's Loan Headquarters 49 Santa's Linearters 67342178 Shanna's Loan Headquarters 51105AG Shana's leaders 39064297 Shanna's Loan Headquarters 9239240525- n un t unters G9205733 Shana's Loan Headquarters 14023128 Saar's Logn head T S & 320,000 0 This Loan Is In Step: Service 95865629 Shanna's Loan Headquarters S22915.6 Sanresantedat 153.618.631 15781.06 53:2,375.03 USDA $17,926.66 FHA 320,000 40,000 Het 20152 721201 30 51,541.751 157269 10 52.48.601 This loan is in Step: ludement This canis in Stup: aferral This Is In Sten: Peter This loan is in Step: Service This unis in Stellen This loon is in Step: 260,000 183,000 OM OSSO This Lion is in Stop: Sarv This Loan is in Step: Service 15625.03 84177304 Shanna's Loan Headquarters 1250 Shere's na tura 5967157 Shanna's Croanlagters 79015075 Shanna's Loan Headquarters 198342) Shan katters 19725073 Shanna's Loan Headquarters 72592476 Shanna's Loan Headquarters 51751139 Santa's Link ers 74930308 Shanna's Loan Headquarters 82625844 Sandstedt A Sana's Linearters 95805855 Shanna's Loan Headquarters 256355 Shanes n atura BRA Sana's leadquarters 8.807153 Shanna's Loan Headquarters 14:14 S- Lin e rs 97257211 Shanna's Loan Headquarters 9419.5050 Samsson headquarters $202,591.05 UCONY $263,929.6 VA Sa. 042.57 UDN $174.142.15 FHA 447,2M VA SIS. 293. U ONN $385,501.50 FHA $116,491.54 USDA $133,733.00 VA $86,751.57 UCON 5201,.42 UTN SS4 114.80 FH4 $125, 7 73 FHA $5304. VA $107, 195.32 UCONY $123,210. UCUN $65.532 45 100KY 213,000 140.000 99,000 mo 200,000 139,000 1230 120,000 10 1931.191 10 151,887.921 This ren is in step:ry's This Loan Is In Step.ludgment 7.25% FNMA This Is In Step: Retral This Loans in Stop:iudgment 158.B.63 $1.11.16 572 18.034.821 23 39 10 FHUMC 90.000 310,000 50- This loan is in Steprva This man is in lupa FINAL REPORT 6a. Using the data compiled on the "Master Data" tab, complete the 3 tables using FORMULAS 6b. Using the data compiled on the "Master Data" tab, recreate tables 1 & 2 using PIVOT TABLES Table 1 Weighted Current Balance Average Rate* # of Loans 38 Sellers Ed's Quality Loan-Mart Ed's Quality Loan-Mart Ken's Discount Value Loans Ken's Discount Value Loans Shanna's Loan Headquarters Shanna's Loan Headquarters *Weighted by Current Balance Loan Rate >=5% =5% =5% 8.01% 8.01% 8.01% 8.01% Total: *Weighted by Current Balance