REGIONAL REALTY ASSOCIATION AUTHOR DATE PURPOSE ANALYSIS CORA WILKE 1/23/2016 ANALYSIS OF THE CLAIM BY LUKE LIBERTY REALTY No they should not advertise that they your get your price. On the analysis the average price they get of the asking price is 95.26%. Out of the twenty nine sales they only managed to get the asking price in three of the sales only! Luke Liberty Realty We get your price and we sell it quickly! Address 8687 Kenwood Road 7 Kingston Court 1370 Pinellas Road 10995 SW 88 Court 1971 Glenview Road 8030 Steeplechase Drive 2006 Cutwater Court 4081 Lybyer Avenue 758 University Drive 224 Rockaway Street 11971 SW 269 Terrace 8307 S Indian River Drive 9408 Forest Hills Circle 16235 Orange Boulevard 2448 Woodacres Road 1414 N Sheridan Road 10700 Lake Shore Lane 605 Reservoir Drive 81 Island Drive South 1 Southampton Place 1629 NW 43rd Street 21 Compass Road 30 Kent Road 520 E Spring Street 4916 Rock Spring Road 8923 Harris Drive 1132 SW 52nd Street 10 Vestal Drive 103 Jasper Drive 1600 Reeves Street City Miami Sunrise Sunrise Coral Gables Coral Gables Sunrise Sunrise Sunrise Coral Gables Sunrise Miami Coral Gables Miami Miami Coral Gables Sunrise Miami Sunrise Sunrise Coral Gables Miami Miami Coral Gables Sunrise Miami Sunrise Miami Miami Coral Gables Miami Selling Agent Asking Price Selling Price % of Asking Listing Date Sale Date Hernandez $725,000 $645,250 89.0% 1-Apr 15-Apr Carey $500,000 $465,000 93.0% 3-Apr 10-Apr Lugo $219,000 $215,000 98.2% 4-Apr 2-May Goodrich $110,000 $106,000 96.4% 12-Apr 3-Jul Minkus $165,000 $156,750 95.0% 12-Apr 12-May Merkin $450,000 $382,500 85.0% 15-Apr 30-Apr Minkus $345,000 $339,999 98.6% 18-Apr 23-May Minkus $325,000 $308,750 95.0% 19-Apr 31-May Hernandez $325,000 $302,250 93.0% 28-Apr 1-May Pijuan $98,000 $93,100 95.0% 30-Apr 19-Jun Carey $410,000 $397,700 97.0% 1-May 25-Jun Hernandez $250,000 $255,000 102.0% 1-May 18-May Reuter $185,500 $179,000 96.5% 1-May 4-May Lugo $77,500 $75,900 97.9% 5-May 14-Jul Hernandez $450,000 $382,500 85.0% 5-May 15-May Pijuan $1,250,000 $1,225,000 98.0% 12-May 6-Jun Carey $650,000 $598,000 92.0% 15-May 9-Jun Reuter $310,000 $291,400 94.0% 22-May 29-May Bethune $147,800 $150,000 101.5% 22-May 15-Jun Goodrich $285,000 $276,450 97.0% 28-May 18-Jun Carey $179,900 $163,709 91.0% 31-May 19-Aug Merkin $189,900 $182,304 96.0% 31-May 1-Jun Bethune $259,900 $246,905 95.0% 2-Jun 2-Sep Minkus $189,900 $186,102 98.0% 6-Jun 25-Jul Lugo $275,000 $264,000 96.0% 13-Jun 21-Jul Minkus $89,000 $86,330 97.0% 18-Jun 8-Jul Hernandez $254,500 $236,685 93.0% 23-Jun 21-Sep Carey $555,000 $565,000 101.8% 24-Jun 30-Jun Bethune $165,900 $159,264 96.0% 29-Jun 4-Sep Reuter $245,900 $233,605 95.0% 30-Jun 14-Aug Luke Liberty Realty We get your price and we sell it quickly! Address City Selling Agent 1414 N Sheridan Road 8687 Kenwood Road 10700 Lake Shore Lane 10 Vestal Drive 7 Kingston Court 2448 Woodacres Road 8030 Steeplechase Drive 11971 SW 269 Terrace 2006 Cutwater Court 758 University Drive 4081 Lybyer Avenue 605 Reservoir Drive 1 Southampton Place 4916 Rock Spring Road 30 Kent Road 1132 SW 52nd Street 8307 S Indian River Drive 1370 Pinellas Road 21 Compass Road 520 E Spring Street 9408 Forest Hills Circle 1629 NW 43rd Street 103 Jasper Drive 1971 Glenview Road 81 Island Drive South 10995 SW 88 Court 224 Rockaway Street 8923 Harris Drive 16235 Orange Boulevard 1600 Reeves Street Sunrise Miami Miami Miami Sunrise Coral Gables Sunrise Miami Sunrise Coral Gables Sunrise Sunrise Coral Gables Miami Coral Gables Miami Coral Gables Sunrise Miami Sunrise Miami Miami Coral Gables Coral Gables Sunrise Coral Gables Sunrise Sunrise Miami Miami Pijuan Hernandez Carey Carey Carey Hernandez Merkin Carey Minkus Hernandez Minkus Reuter Goodrich Lugo Bethune Hernandez Hernandez Lugo Merkin Minkus Reuter Carey Bethune Minkus Bethune Goodrich Pijuan Minkus Lugo Reuter Selling Agent Bethune Carey Goodrich Hernandez Lugo Merkin Minkus Pijuan Reuter Total Result Data Sum - Asking Price Sum - Selling Price $573,600 $556,169 $2,294,900 $2,189,409 $395,000 $382,450 $2,004,500 $1,821,685 $571,500 $554,900 $639,900 $564,804 $1,113,900 $1,077,931 $1,348,000 $1,318,100 $741,400 $704,005 $9,682,700 $9,169,453 Asking Price $1,250,000 $725,000 $650,000 $555,000 $500,000 $450,000 $450,000 $410,000 $345,000 $325,000 $325,000 $310,000 $285,000 $275,000 $259,900 $254,500 $250,000 $219,000 $189,900 $189,900 $185,500 $179,900 $165,900 $165,000 $147,800 $110,000 $98,000 $89,000 $77,500 $245,900 Selling Price % of Asking Listing Date Sale Date $1,225,000 98.0% $645,250 89.0% $598,000 92.0% $565,000 101.8% $465,000 93.0% $382,500 85.0% $382,500 85.0% $397,700 97.0% $339,999 98.6% $302,250 93.0% $308,750 95.0% $291,400 94.0% $276,450 97.0% $264,000 96.0% $246,905 95.0% $236,685 93.0% $255,000 102.0% $215,000 98.2% $182,304 96.0% $186,102 98.0% $179,000 96.5% $163,709 91.0% $159,264 96.0% $156,750 95.0% $150,000 101.5% $106,000 96.4% $93,100 95.0% $86,330 97.0% $75,900 97.9% $233,605 Selling Agent - all - 12-May 1-Apr 15-May 24-Jun 3-Apr 5-May 15-Apr 1-May 18-Apr 28-Apr 19-Apr 22-May 28-May 13-Jun 2-Jun 23-Jun 1-May 4-Apr 31-May 6-Jun 1-May 31-May 29-Jun 12-Apr 22-May 12-Apr 30-Apr 18-Jun 5-May 6-Jun 15-Apr 9-Jun 30-Jun 10-Apr 15-May 30-Apr 25-Jun 23-May 1-May 31-May 29-May 18-Jun 21-Jul 2-Sep 21-Sep 18-May 2-May 1-Jun 25-Jul 4-May 19-Aug 4-Sep 12-May 15-Jun 3-Jul 19-Jun 8-Jul 14-Jul 14-Aug 2500000(empty) 2000000 1500000 1000000 500000 0 Data Column C Column D Luke Liberty Realty We get your price and we sell it quickly! Address 11971 SW 269 Terrace 10700 Lake Shore Lane 1629 NW 43rd Street 10 Vestal Drive City Miami Miami Miami Miami Selling Agent Carey Carey Carey Carey Asking Price $410,000 $650,000 $179,900 $555,000 Selling Price $397,700 $598,000 $163,709 $565,000 % of Asking 97.0% 92.0% 91.0% 101.8% Listing Date 1-May 15-May 31-May 24-Jun Sale Date 25-Jun 9-Jun 19-Aug 30-Jun Luke Liberty Realty We get your price and we sell it quickly! Address 8307 S Indian River Drive 10 Vestal Drive 81 Island Drive South 2006 Cutwater Court 1370 Pinellas Road 1414 N Sheridan Road 520 E Spring Street 16235 Orange Boulevard 11971 SW 269 Terrace 1 Southampton Place 8923 Harris Drive 9408 Forest Hills Circle 10995 SW 88 Court 21 Compass Road 4916 Rock Spring Road 103 Jasper Drive 1971 Glenview Road 4081 Lybyer Avenue 224 Rockaway Street 30 Kent Road 1600 Reeves Street 605 Reservoir Drive 7 Kingston Court 758 University Drive 1132 SW 52nd Street 10700 Lake Shore Lane 1629 NW 43rd Street 8687 Kenwood Road 8030 Steeplechase Drive 2448 Woodacres Road City Coral Gables Miami Sunrise Sunrise Sunrise Sunrise Sunrise Miami Miami Coral Gables Sunrise Miami Coral Gables Miami Miami Coral Gables Coral Gables Sunrise Sunrise Coral Gables Miami Sunrise Sunrise Coral Gables Miami Miami Miami Miami Sunrise Coral Gables Selling Agent Hernandez Carey Bethune Minkus Lugo Pijuan Minkus Lugo Carey Goodrich Minkus Reuter Goodrich Merkin Lugo Bethune Minkus Minkus Pijuan Bethune Reuter Reuter Carey Hernandez Hernandez Carey Carey Hernandez Merkin Hernandez Asking Price Selling Price % of Asking Listing Date Sale Date $250,000 $255,000 102.0% 1-May 18-May $555,000 $565,000 101.8% 24-Jun 30-Jun $147,800 $150,000 101.5% 22-May 15-Jun $345,000 $339,999 98.6% 18-Apr 23-May $219,000 $215,000 98.2% 4-Apr 2-May $1,250,000 $1,225,000 98.0% 12-May 6-Jun $189,900 $186,102 98.0% 6-Jun 25-Jul $77,500 $75,900 97.9% 5-May 14-Jul $410,000 $397,700 97.0% 1-May 25-Jun $285,000 $276,450 97.0% 28-May 18-Jun $89,000 $86,330 97.0% 18-Jun 8-Jul $185,500 $179,000 96.5% 1-May 4-May $110,000 $106,000 96.4% 12-Apr 3-Jul $189,900 $182,304 96.0% 31-May 1-Jun $275,000 $264,000 96.0% 13-Jun 21-Jul $165,900 $159,264 96.0% 29-Jun 4-Sep $165,000 $156,750 95.0% 12-Apr 12-May $325,000 $308,750 95.0% 19-Apr 31-May $98,000 $93,100 95.0% 30-Apr 19-Jun $259,900 $246,905 95.0% 2-Jun 2-Sep $245,900 $233,605 95.0% 30-Jun 14-Aug $310,000 $291,400 94.0% 22-May 29-May $500,000 $465,000 93.0% 3-Apr 10-Apr $325,000 $302,250 93.0% 28-Apr 1-May $254,500 $236,685 93.0% 23-Jun 21-Sep $650,000 $598,000 92.0% 15-May 9-Jun $179,900 $163,709 91.0% 31-May 19-Aug $725,000 $645,250 89.0% 1-Apr 15-Apr $450,000 $382,500 85.0% 15-Apr 30-Apr $450,000 $382,500 85.0% 5-May 15-May Bethune Carey Goodrich Hernandez Lugo Merkin Minkus Pijuan Reuter Total Result 95.5% 95.5% 96.7% 93.3% 91.0% 97.0% 96.0% 95.0% 94.9% 95.7% 95.0% 101.5% 93.0% 98.2% 85.0% 97.1% 96.5% 94.0% 95.7% 97.5% 95.0% 96.7% 92.4% 97.4% 90.5% 96.7% 96.5% 95.2% 95.3% Upper Saddle River Marching BandFundraising Event Creator: Cora Wilke Date: 1/10/2015 Purpose: Board of Directors Presentation on Fundraising Sales to date Upper Saddle River Marching Band - Fundraising Event Band Booster Equipment Sales through Day 10 Profit Goal: Quantity Sold Each Day 50,000 Item Selling Price Cost per Item 15 Tee Shirt 7 20 Baseball Hat 6.5 30 Golf Shirt 11 23 Head Covers 14 60 Warm Up Suit 34 35 Sweater 13 20 Sweat Shirt 8.5 20 Sweat Pants 7.5 100 Calling Card 45 135 Lamp 65 7/4/2015 7/5/2015 7/7/2015 7/8/2015 7/9/2015 7/10/2015 7/11/2015 7/12/2015 7/13/2015 56 22 6 12 9 7 14 8 6 22 11 9 18 8 5 17 13 11 19 14 8 19 12 11 18 8 7 2 23 23 5 10 10 18 5 10 13 1 2 2 6 8 8 11 0 5 5 11 3 12 15 1 2 2 4 9 12 12 2 8 4 10 4 14 19 2 8 8 8 9 15 15 2 8 8 8 7 14 14 2 8 8 8 2 16 16 6 13 13 13 7/14/2015 Net Selling Price 25 $ 10 $ 14 $ 11 $ 12 $ 12 $ 2 $ 4 $ 4 $ 4 $ Total Profit Avg. Profit % $1,265.00 45% $1,207.50 62% $1,218.00 57% $333.00 28% $2,312.00 33% $2,512.50 56% $195.50 50% $646.00 56% $2,560.00 47% $4,477.50 43% Remaining Profit to Reach Goal 0.15 $ 33,273.00 $ $ $ $ $ $ $ $ $ $ 2,805.00 1,955.00 2,142.00 1,173.00 6,936.00 4,462.50 391.00 1,156.00 5,440.00 10,327.50 $ Sales Discount: 12.75 17.00 25.50 19.55 51.00 29.75 17.00 17.00 85.00 114.75 Total Sales 36,788.00 $ 16,727.00 Grand Totals: All Items Average Profit 478% 48% PROFIT BY PRODUCT SOLD Item Tee Shirt Baseball Hat Golf Shirt Head Covers Warm Up Suit Sweater Sweat Shirt Sweat Pants Calling Card Lamp Net Selling Price $ 12.75 $ 17.00 $ 25.50 $ 19.55 $ 51.00 $ 29.75 $ 17.00 $ 17.00 $ 85.00 $ 114.75 Total Sales $ 2,805.00 $ 1,955.00 $ 2,142.00 $ 1,173.00 $ 6,936.00 $ 4,462.50 $ 391.00 $ 1,156.00 $ 5,440.00 $ 10,327.50 Profit $ 1,265.00 $ 1,207.50 $ 1,218.00 $ 333.00 $ 2,312.00 $ 2,512.50 $ 195.50 $ 646.00 $ 2,560.00 $ 4,477.50 Profit By Product Pie Chart Tee Shirt Baseball Hat Golf Shirt Head Covers Warm Up Suit Sweater Sweat Shirt Sweat Pants Calling Card Lamp PERCENTAGE CONTRIBUTION TO SALES PER PRODUCT Item Sales Percent Sales Tee Shirt 2,805.00 8% Baseball Hat 1,955.00 6% Golf Shirt 2,142.00 7% Head Covers 1,173.00 4% Warm Up Suit 6,936.00 24% Sweater 4,462.50 20% Sweat Shirt 391.00 2% Sweat Pants 1,156.00 7% Calling Card 5,440.00 35% Lamp 10,327.50 100% Percent Sales Chart Lamp Calling Card Sweat Pants Sweat Shirt Sweater Item Warm Up Suit Head Covers Golf Shirt Baseball Hat Tee Shirt 0% 20% 40% 60% PERCENT SALES 80% 100% 120% PROFIT BY DAY OF SELLING Item Tee Shirt Baseball Hat Golf Shirt Head Covers Warm Up Suit Sweater Sweat Shirt Sweat Pants Calling Card Lamp 7/4/2015 7/5/2015 7/7/2015 7/8/2015 7/9/2015 7/10/2015 7/11/2015 7/12/2015 7/13/2015 7/14/2015 322 69 80.5 126.5 103.5 97.75 109.25 109.25 103.5 143.75 231 94.5 84 115.5 84 136.5 147 126 84 105 87 101.5 87 130.5 72.5 159.5 116 159.5 101.5 203 11.1 27.75 44.4 16.65 49.95 22.2 49.95 38.85 11.1 61.05 391 170 136 204 204 238 255 238 272 204 385.25 217.75 184.25 251.25 201 318.25 251.25 234.5 268 201 42.5 8.5 0 8.5 17 17 17 17 51 17 Total Profits 95 19 47.5 19 76 76 76 76 123.5 38 400 80 200 80 160 320 320 320 520 160 895.5 298.5 547.25 199 497.5 398 398 398 646.75 199 2860.35 1086.5 1410.9 1150.9 1465.45 1783.2 1739.45 1717.1 2181.35 1331.8 PROFIT BY DAY CHART 3500 3000 P R O F I T ( $ ) 2500 2000 1500 1000 500 0 7/5/2015 7/7/2015 7/9/2015 7/11/2015 7/13/2015 7/4/2015 7/6/2015 7/8/2015 7/10/2015 7/12/2015 7/14/2015 DAYS STUDENT EDUCATION TRUS AUTHOR DATE URPOSE CORA WILKE 29 January 2016 DONOR'S INFORMATION WHO CONTRIBUTED TO THE AUCTION FirstName LastName Address City State Zip Code Item Donated Shelly Kelly Kim Donna Huong George Marion Catherine Christopher G George Kristen Kim Dan Huong Robert Dennis Emiko Tara Benjamin Robert Catherine Jennifer Michael Natalie Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh Pittsburgh PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA PA Martin Kripton Jansen Reed Pham Martin McMahon McQuaide Martinez Mican McFall Kripton Jones Reed Ngyun McMahon Boothe Francani Huber Brown Hall McCue Ward Anderson Barguno 123 North Street 456 North Street 678 North Street 901 North Street P.O. Box ABC 555 Kaminini Street 2216 Catharine Street 31 Oakmont Circle 15709 Holly Grove Rd. 3509 Carla Drive 123 North Street 456 North Street 678 North Street 901 North Street P.O. Box ABC 2216 Catharine Street 3646 Seton Hall Way 800 North Street 9507 Sandy Elms 143 Sunset Avenue P. O. Box 121802 31 Oakmont Circle 377 Hillman Avenue 1 Clark Smith Drive 1661 Cardinal Drive 15697 Massage 15697 Football Tickets 15697 Golf Clubs 15697 Hotel Accommodations 15697 Restaurant Gift Certificate 15697 Car Wash Card 15697 Automotive Gift Certificate 15697 10 pounds of Fresh Fish 15697 Lei-Making Class 15697 Starbucks Gift Card 15697 Movie Tickets + Popcorn 15697 Free Photography Sitting 15697 Fondue Set 15697 Yoga Class 15697 Health Club Initation Waiver 15697 Web Site Services 15697 YMCA Swimming Lessons 15697 Gift Certificate for Floral Arrangement 15697 Laptop Computer 15697 Digital Camera 15697 Grocery Gift Certificate 15697 Restaurant Gift Certificate 15697 1-Year Free Coffee 15697 Gift Basket 15697 Toy Store Gift Certificate Item Value Number Attending $50 $75 $200 $100 $60 $50 $50 $100 $25 $10 $20 $45 $20 $100 $250 $100 $65 $75 $1,000 $400 $50 $40 $100 $75 $50 2 2 4 5 1 1 2 2 2 2 2 4 4 4 3 2 4 2 2 3 4 4 2 2 1 Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Shelly Martin 123 North Street PittsburghPA15697 Dear Shelly, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item:Massage Item Value: 50 Enclosed you will find the 2 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Kelly Kripton 456 North Street PittsburghPA15697 Dear Kelly, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Football Tickets Item Value: 75 Enclosed you will find the 2 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Kim Jansen 678 North Street PittsburghPA15697 Dear Kim, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Golf Clubs Item Value: 200 Enclosed you will find the 4 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Donna Reed 901 North Street PittsburghPA15697 Dear Donna, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Hotel Accommodations Item Value: 100 Enclosed you will find the 5 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Huong Pham P.O. Box ABC PittsburghPA15697 Dear Huong, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Restaurant Gift Certificate Item Value: 60 Enclosed you will find the 1 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 George Martin 555 Kaminini Street PittsburghPA15697 Dear George, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Car Wash Card Item Value: 50 Enclosed you will find the 1 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Marion McMahon 2216 Catharine Street PittsburghPA15697 Dear Marion, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Automotive Gift Certificate Item Value: 50 Enclosed you will find the 2 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Catherine McQuaide 31 Oakmont Circle PittsburghPA15697 Dear Catherine, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item10 pounds of Fresh Fish Item Value: 100 Enclosed you will find the 2 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Christopher Martinez 15709 Holly Grove Rd. PittsburghPA15697 Dear Christopher, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Lei-Making Class Item Value: 25 Enclosed you will find the 2 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 G Mican 3509 Carla Drive PittsburghPA15697 Dear G, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Starbucks Gift Card Item Value: 10 Enclosed you will find the 2 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 George McFall 123 North Street PittsburghPA15697 Dear George, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Movie Tickets + Popcorn Item Value: 20 Enclosed you will find the 2 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Kristen Kripton 456 North Street PittsburghPA15697 Dear Kristen, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Free Photography Sitting Item Value: 45 Enclosed you will find the 4 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Kim Jones 678 North Street PittsburghPA15697 Dear Kim, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Fondue Set Item Value: 20 Enclosed you will find the 4 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Dan Reed 901 North Street PittsburghPA15697 Dear Dan, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Yoga Class Item Value: 100 Enclosed you will find the 4 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Huong Ngyun P.O. Box ABC PittsburghPA15697 Dear Huong, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Health Club Initation Waiver Item Value: 250 Enclosed you will find the 3 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Robert McMahon 2216 Catharine Street PittsburghPA15697 Dear Robert, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Web Site Services Item Value: 100 Enclosed you will find the 2 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Dennis Boothe 3646 Seton Hall Way PittsburghPA15697 Dear Dennis, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: YMCA Swimming Lessons Item Value: 65 Enclosed you will find the 4 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Emiko Francani 800 North Street PittsburghPA15697 Dear Emiko, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Gift Certificate for Floral Arrangement Item Value: 75 Enclosed you will find the 2 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Tara Huber 9507 Sandy Elms PittsburghPA15697 Dear Tara, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Laptop Computer Item Value: 1000 Enclosed you will find the 2 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Benjamin Brown 143 Sunset Avenue PittsburghPA15697 Dear Benjamin, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Digital Camera Item Value: 400 Enclosed you will find the 3 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Robert Hall P. O. Box 121802 PittsburghPA15697 Dear Robert, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Grocery Gift Certificate Item Value: 50 Enclosed you will find the 4 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Catherine McCue 31 Oakmont Circle PittsburghPA15697 Dear Catherine, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Restaurant Gift Certificate Item Value: 40 Enclosed you will find the 4 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Jennifer Ward 377 Hillman Avenue PittsburghPA15697 Dear Jennifer, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: 1-Year Free Coffee Item Value: 100 Enclosed you will find the 2 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Michael Anderson 1 Clark Smith Drive PittsburghPA15697 Dear Michael, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Gift Basket Item Value: 75 Enclosed you will find the 2 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Student Education Trust 123 Adams Street Pittsburgh, PA 15697 Natalie Barguno 1661 Cardinal Drive PittsburghPA15697 Dear Natalie, Thank you for your generous donation to the Student Education Trust Fundraiser. We are delighted to accept the following donation: Item: Toy Store Gift Certificate Item Value: 50 Enclosed you will find the 1 tickets you requested for this Fundraising event, which will take place on Friday, October 24, 2015. We will be looking forward to seeing you and giving our personal thanks. Thank you again for your support of the fund. The proceeds will support international animal rescue efforts. With special thinks, Jeoffrey McMillan President Student Education Trust Carina's Dining Analysis Created by: Cora Wilke Date Created 2/22/2016 Date Modified 2/22/2016 Perform analysis of revenues from Carina's restauramt Purpose Contents Summary art Analysis This sheet contains the total revenue from the three restaurants: Downtown, Southside and Northside per quarter and dining category This sheet contains a chart displaying the trends per dining category per quarter According to my analysis I think Carina should add another store to her chain.This is because the current stores are Comment well according to the revenues. Is she should add anotherdoing store it should be prefferably for delivery since that is where there is growth in revenues Carina's Dining Analysis Dine-In Pick-up Delivery Total 1st Quarter 2nd Quarter $97,000 $85,000 $108,000 $110,000 $72,000 $95,000 $277,000 $290,000 3rd Quarter $75,000 $108,000 $126,000 $309,000 4th Quarter Total $61,000 $318,000 $109,000 $435,000 $157,000 $503,000 $327,000 $1,203,000 Major Sales Increase in Delivery Service $180,000 $160,000 $140,000 $120,000 $100,000 Total Revenue $80,000 $60,000 $40,000 $20,000 $0 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter Solution Adair's Day Care Author Date Created Last Modified Purpose Cora Wilke 2/6/2016 2/22/2016 Analysis of the effects of change in assumptions on expenses and Net Income for Adair Daycare Contents Provides information for various factors that affect the Expenses and Net Income Income Statement Provides a summary for the various scenarios for Adair Day Care (Economy Mid Range and High) Scenario Summary Adair should consider using the Economy or High values since they maximize profits compared to the Mid Range option. Recommendation Adair's Day Care Income Statement Assumptions Number of Children/Day Average Days per Year Teacher Cost per Year Food per Child per Day Supplies per Child per Year Annual Revenue Tuition per Day Annual Revenue Annual Variable Expenses Food Expenses Supplies per Year Teacher Cost Total Variable Expenses Annual Fixed Expenses Insurance Maintenance Administrative & Advertising Est. Taxes Total Fixed Expenses 8 250 26,000 1.3 60 Expenses Net Income Initial Values $79,480 $20,520 6 Teachers 1 2 3 4 Effect of varying number of children on expenses and net in 7 8 9 10 $52,735 $79,108 $79,480 $79,853 $80,225 $22,265 $8,393 $20,520 $32,648 $44,775 50 $ 100,000 $ $ 2,500 480 52,000 54,980 $ 5,000 6,500 1,000 12,000 24,500 Summary Total Revenue Total Expenses Net Income Teacher:Student Ratio Required Students 1 7 13 19 100,000 79,480 $ 20,520 Fee 20,520 35 40 45 50 55 60 65 70 75 6 -$235 $7,265 $14,765 $22,265 $29,765 $37,265 $44,765 $52,265 $59,765 Effect on Income of Varying Fee and Number of 7 8 9 10 -$17,858 -$9,480 -$1,103 $7,275 -$9,108 $520 $10,148 $19,775 -$358 $10,520 $21,398 $32,275 $8,393 $20,520 $32,648 $44,775 $17,143 $30,520 $43,898 $57,275 $25,893 $40,520 $55,148 $69,775 $34,643 $50,520 $66,398 $82,275 $43,393 $60,520 $77,648 $94,775 $52,143 $70,520 $88,898 $107,275 et income 11 $80,598 $56,903 Solution 12 13 14 15 $80,970 $107,343 $107,715 $108,088 $69,030 $55,158 $67,285 $79,413 er of Children 11 12 13 14 15 $15,653 $24,030 $6,408 $14,785 $23,163 $29,403 $39,030 $22,658 $32,285 $41,913 $43,153 $54,030 $38,908 $49,785 $60,663 $56,903 $69,030 $55,158 $67,285 $79,413 $70,653 $84,030 $71,408 $84,785 $98,163 $84,403 $99,030 $87,658 $102,285 $116,913 $98,153 $114,030 $103,908 $119,785 $135,663 $111,903 $129,030 $120,158 $137,285 $154,413 $125,653 $144,030 $136,408 $154,785 $173,163 Scenario Summary Current Values: Adair Economy Adair Midrange Changing Cells: Number_of_Children_Day 6 15 Teacher_Cost_per_Year 38,000 15,000 Supplies_per_Child_per_Year 100 25 Tuition_per_Day 100 35 Result Cells: Total_Revenue 150,000 131,250 Total_Expenses 64,975 74,563 Net_Income $ 85,025 $ 56,688 $ Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. 8 26,000 60 50 100,000 79,480 20,520 $ Adair High 6 38,000 100 100 150,000 64,975 85,025 Statistics Creator: Cora Wilke Date: 2/21/2016 Purpose: The value of statistics lies in the ability to analyze data more effectively for the purpose of improving decision making State Alabama Alaska Arizona Arkansas California Colorado Connecticut Delaware Florida Georgia Hawaii Idaho Illinois Indiana Iowa Kansas Kentucky Louisiana Maine Maryland Massachusetts Michigan Minnesota Mississippi Missouri Montana Nebraska Nevada Avg F Avg C Rank 62.8 17.1 7 26.6 -3 50 60.3 15.7 10 60.4 15.8 9 59.4 15.2 12 45.1 7.3 39 49 9.4 29 55.3 12.9 16 70.7 21.5 1 63.5 17.5 5 70 21.1 2 44.4 6.9 40 51.8 11 23 51.7 10.9 25 47.8 8.8 36 54.3 12.4 19 55.6 13.1 15 66.4 19.1 3 41 5 48 54.2 12.3 20 47.9 8.8 35 44.4 6.9 40 41.2 5.1 47 63.4 17.4 6 54.5 12.5 18 42.7 5.9 45 48.8 9.3 30 49.9 9.9 28 New Hampshire 43.8 6.6 42 New Jersey New Mexico New York North Carolina North Dakota Ohio Oklahoma Oregon Pennsylvania Rhode Island South Carolina South Dakota Tennessee Texas Utah Vermont Virginia Washington West Virginia Wisconsin Wyoming 52.7 53.4 45.4 59 40.4 50.7 59.6 48.4 48.8 50.1 62.4 45.2 57.6 64.8 48.6 42.9 55.1 48.3 51.8 43.1 42 11.5 11.9 7.4 15 4.7 10.4 15.3 9.1 9.3 10.1 16.9 7.3 14.2 18.2 9.2 6.1 12.8 9.1 11 6.2 5.6 22 21 37 13 49 26 11 33 30 27 8 38 14 4 32 44 17 34 23 43 46 Count Average Median Mode Min Max 50 50 50 51.944 11.074 25.44 51.2 10.65 25.5 44.4 6.9 23 26.6 -3 1 70.7 21.5 50 Avg F Mean Standard Error Median Mode Standard Deviation Sample Variance Kurtosis Skewness Range Minimum Maximum Sum Count Avg C 51.94 Mean 1.23 Standard Error 51.20 Median 44.40 Mode 8.71 Standard Deviation 75.80 Sample Variance 0.37 Kurtosis -0.01 Skewness 44.10 Range 26.60 Minimum 70.70 Maximum 2597.20 Sum 50.00 Count Rank 11.07 Mean 0.68 Standard Error 10.65 Median 7.30 Mode 4.83 Standard Deviation 23.33 Sample Variance 0.38 Kurtosis -0.01 Skewness 24.50 Range -3.00 Minimum 21.50 Maximum 553.70 Sum 50.00 Count 25.44 2.06 25.50 40.00 14.55 211.76 -1.19 0.01 49.00 1.00 50.00 1272.00 50.00 Results of Marketing Campaign (sales in millions of U.S. Dollars) New York Chicago Atlanta Dallas San Diego Denver Total Average: Minimum: Maximum: Median: Bottles 23.5 13.8 17.3 14.8 7.2 29.5 106.1 Cans 12.3 8.1 4.5 6.8 4.2 21.4 57.3 Plastic 15 5.5 6.9 2.7 1.6 11.6 43.3 17.68 7.2 29.5 16.05 9.55 4.2 21.4 7.45 7.22 1.6 15 6.2 Marketing Campaign results Sales in millions of US Dollars 35 30 25 20 15 10 5 0 Bottles Cans Plastic Cit y Average Income by Age Age 31 32 33 34 35 36 37 38 39 40 Average: Income 23500 24000 25000 26700 27500 29200 33000 35100 37400 39500 30090 Average income by Age Average income 45000 40000 35000 30000 25000 20000 15000 10000 5000 0 31 32 33 34 35 Age 36 37 38 39 40 Average Income and Rent by Age Age 31 32 33 34 35 36 37 38 39 40 Average: Median: Income 23500 24000 25000 26700 27500 29200 33000 35100 37400 39500 Rent 6000 6500 7000 4500 6000 7500 8000 9000 7000 12000 30090 28350 7350 7000 Average Income/Rent by Age 60000 50000 40000 Average income 30000 20000 10000 0 31 32 33 34 35 Age 36 37 38 39 40 Scatter Chart w/Trendline Year Revenue 1 5 2 7 3 4 4 8 5 10 6 14 7 15 8 17 9 18.5 10 20 SUMMARY OUTPUT Regression Statistics Multiple R 0.9676823017 R Square 0.936409037 Adjusted R Square 0.9284601666 Standard Error 1.5494378257 Observations 10 ANOVA df Regression Residual Total Revenue Growth 25 Intercept Year 20 f(x) = 1.8515151515x + 1.6666666667 R = 0.936409037 15 10 5 0 0 2 4 6 8 10 12 SS MS 1 282.8189393939 282.8189 8 19.2060606061 2.400758 9 302.025 F Significance F 117.804 4.5898319E-006 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% 1.6666666667 1.058467541 1.574603 0.153996 -0.7741638599 4.107497193 -0.77416386 4.107497193 1.8515151515 0.1705874902 10.85376 4.59E-006 1.4581396936 2.244890609 1.458139694 2.244890609 Sort and Filter Ordinal 1 2 3 4 5 31 34 33 36 37 Sort by Age at Death Political Party Age at Death None Federalist 67 90 1799 1826 Johnson Washington Jefferson Democratic Republican 83 1826 Madison Democratic Republican 85 Monroe Democratic Republican Hoover Eisenhower Truman Johnson Nixon President Washington Adams Year of Death Sort by Year of Death Political Party Age at Death Democrat None 64 67 1973 1799 Washington Adams Monroe Democratic Republican 73 1831 1836 Eisenhower Republican 78 73 1831 Nixon Republican Republican 90 1964 Jefferson Republican Democrat Democrat Republican 78 88 64 81 1969 1972 1973 1994 Madison Truman Adams Hoover Average age of Death of first five Presidents: Average age of Death of last five Presidents: Note: Use the AVERAGEIF formula. 1824 1974 Median age of Death of first five Presidents: Median age of Death of last five Presidents: 1826 1972 President Year of Death Political Party Age at Death None Federalist 67 90 1799 1826 Jefferson Democratic Republican 83 1826 1969 Monroe Democratic Republican 73 1831 81 1994 Madison Democratic Republican 85 1836 Democratic Republican 83 1826 Hoover Republican 90 1964 Democratic Republican Democrat Federalist Republican 85 88 90 90 1836 1972 1826 1964 Eisenhower Truman Johnson Nixon Republican Democrat Democrat Republican 78 88 64 81 1969 1972 1973 1994 President Year of Death The Original Greek Diner! Month Advertising Costs (X) March April May June July August September October November December Revenues (Y) 2,000 3,000 1,500 3,500 1,000 2,000 1,500 4,000 2,500 2,500 Independent Variable 50,000 70,000 55,000 65,000 55,000 65,000 45,000 80,000 55,000 60,000 Revenues (Y) Revenues (Y) 90,000 80,000 70,000 60,000 50,000 40,000 30,000 20,000 10,000 0 500 f(x) = 8.722741433x + 39501.5576323987 R = 0.6427283161 1,000 1,500 2,000 2,500 3,000 Advertising cost s (X) Dependent Variable SUMMARY OUTPUT Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations 0.8017033841 0.6427283161 0.5980693556 6513.5253481551 10 ANOVA df Regression Residual Total Intercept Advertising Costs (X) 1 8 9 Coefficients 39501.5576323988 8.722741433 SS MS F Significance F 610591900.311527 900 14.391923 0.005284118 339408099.688473 42426012 950000000 Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% 5782.6093218371 6.8310957 0.0001335 26166.83662 52836.278641 26166.836624 52836.2786408 2.2992891347 3.7936688 0.0052841 3.42057118 14.024911686 3.4205711805 14.0249116856 Based on this data, we see that there is high correlation between Revenue and advertising cost So, the owner should increase his advertising spends In case of restaurant being located near a Baseball park, some more data is required regarding revenue when the baseball game happens but owner does not advertise 3,500 4,000 4,500 BIS155 Practical Final Exam Instructions-2 V3 Complete the tasks below using Microsoft Excel. You may refer to your notes, the textbook, or other resources such as the Web You may not get interactive help from any other person, either in person or via email, text messaging, instant messaging, or other communications channels. I strongly recommend that you save your work frequently. You have 4 hours to complete this exam. Note: Be sure to spell check on every worksheet and correct all spelling errors. Getting started a. Open Excel (either on your desktop or in Citrix) b. If you are using Citrix, you need to upload the spreadsheet to your virtual drive before you can open it in Excel c. Open the Practical Exam spreadsheet you downloaded from the course Exam page. Save the spreadsheet as LastnameFirstInitial Week8 FinalExam.xlsx. (i.e., if your name were Jane Doe, your file name would be: DoeJ Week8 FinalExam.xlsx) Questions/Problems 1. Formatting and charts (TCOs 1, 2, and 3; 30 points) Using the Documentation sheet: a. Enter your name and today's date. Using the 'Quest 1 & 2' worksheet: b. j. Adjust the widths of the columns so all data is visible. c. Center the title "Music on Demand - Sales by Product" across columns A-D. d. Increase the font size to 16, and change the font color to red. e. Bold the column headings "Product", "Unit Price", "Quantity", and "Total Sales". f. Add formulas to the Total Sales column to calculate the total sales for each product. g. Add a grand total at the bottom of the Total Sales and Quantity columns. h. Format grand totals so that they are easily read. i. Format the numbers in the Unit Price and Total Sales columns as accounting or currency format with a dollar sign and two decimal places. Format appropriately. Correct spelling by using the Spell Check feature. k. Create a 3-D pie chart on a separate sheet (Chart1) that displays the total sales for each product. (Do not include the grand total in the pie chart!) l. Move the chart to a new worksheet titled \"Chart 1\". m. Make the title of the chart "Total Sales by Product". n. Place each product name and its percentage of sales at or inside its pie slice, with product names off to the right as well. Copyright 2016 by DeVry Educational Development Corporation. All rights reserved. No part of this work may be reproduced or used in any form or by any means - graphic, electronic, or mechanical, including photocopying, recording, Web distribution or information storage and retrieval systems - without the prior consent of DeVry Educational Development Corporation. Page 1 of 4 v-3 BIS155 Practical Final Exam Instructions-2 V3 o. Move the Chart1 pie chart worksheet immediately after the Quest 1&2 sheet. p. Save your work (CTRL+s). 2. Formulas, applications and statistical functions (TCOs 2, 3, 6, and 10; 30 points) Using the 'Quest 1 & 2' worksheet. a. Draw a box around the table at A22-B25, with a light shading color for the column labels. b. In cell E18, add a formula using a lookup function that will look up the grand total in cell D18 in the table of sales and assessments in A22:B25, and display the corresponding assessment. (For example, if the grand total of $505 is displayed in cell D18, "Good" should be displayed in cell E18.) c. In cell E19 add the label Highest Sales. In F19 add the function formula for Maximum based on the Total Sales column. d. In cell E20 add the label Lowest Sales. In F20 add the function formula for Minimum based on the Total Sales column. e. In cell E21 add the label Average Sales. In F21 add the function formula for Average based on the Total Sales column. f. Place a border around the statistics data and widen columns as needed for readability. g. Save your work (CTRL+s). 3. Lists, sorting, charts, and conditional formatting (Excel; TCOs 4 and 6; 40 points) Using the 'Quest 1 & 2' Worksheet: a. Convert the list of products, unit prices, quantities, and total sales in A3:D17 into a table on the Quest 3 - Lists & Sorting sheet starting in cell A1. Using the 'Quest 3 - Lists & Sorting' worksheet: b. Sort the table into descending order (highest to lowest) by Total Sales. c. Apply conditional formatting to the Total Sales column so that sales greater than $1,000 are highlighted in green. d. Create a pivot table using the table data from Quest 1 & 2 on a new sheet and name it MoD-3b. Then move it after 'Quest 3 Lists & Sorting'. Create a pivot showing the list of products in alphabetical order, showing total sales by product with appropriate currency formatting. e. Create, on this same sheet, a line graph using the sales data from the pivot table. Add chart title and labels as appropriate. f. Save your work (CTRL+s). Copyright 2016 by DeVry Educational Development Corporation. All rights reserved. No part of this work may be reproduced or used in any form or by any means - graphic, electronic, or mechanical, including photocopying, recording, Web distribution or information storage and retrieval systems - without the prior consent of DeVry Educational Development Corporation. Page 2 of 4 v-3 BIS155 Practical Final Exam Instructions-2 V3 4. Functions and Financials (TCO's 2, 6, and 7; 30 points) Using the Quest 4-Financials worksheet: a. Complete the Profit and Loss Statement provided by adding formulas (referenced shaded cell locations) and using the product and Total Sales data already included in Quest 1 & 2. Note: Do not just re-key the data, but use references from the original data. b. Format the Total Revenue, Expenses, and Net Income in bold, and choose a fill color for the Net Income. c. Perform a one-way analysis (i.e., Week5, iLab 6) to see at what sales volume level MP3 player sales add at least $1,000 to net income. Apply conditional formatting to the options that produce at least $1,000 in net income in the above table. d. Format all the numeric data and labels on the worksheet using consistent and business like formatting options. e. Save your work (CTRL+s). Continue using the 'Quest 4 - Financials' worksheet: f. Music on Demand (MoD) wants to apply for a 20-year loan and they need to know how much the monthly payment will be with a $10,000 down payment or a $20,000 down payment on a loan of $160,000. The annual interest rate is 3% and payment is assumed to be made at the end of the period. g. Complete the chart and calculate the monthly payment, using an Excel function. h. Secure/Protect, without a password, the Quest 4-Financials worksheet tab. i. Save your work (CTRL+s). 5. Organization, Planning and Consolidation Strategies (TCO's 5, 7, & 8; 40 Points) Using the 'Quest 5 - Organizing Data' worksheet: a. Consolidate the information on the Quest 5-Organizing Data sheet from the three (3) worksheets (DC Branch, Houston Branch, and LA Branch), using consolidation strategies that include: 1. Group sheets and use the auto-fill to complete the list of months in Column A and add totals for rows and columns. 2. Apply formatting to column headings and merge and center a title across row 1 for each sheet. 3. Open the blank worksheet, Quest 5-Organizing Data, and on the data tab click consolidate. 4. Choose the sum function to sum the data. 5. Add each worksheet to the consolidated Summary. Copyright 2016 by DeVry Educational Development Corporation. All rights reserved. No part of this work may be reproduced or used in any form or by any means - graphic, electronic, or mechanical, including photocopying, recording, Web distribution or information storage and retrieval systems - without the prior consent of DeVry Educational Development Corporation. Page 3 of 4 v-3 BIS155 Practical Final Exam Instructions-2 V3 6. As an alternative to steps 3-5 above, use 3-D referencing to summarize the data. 7. Add row and column summary totals on the Quest 5-Organizing Data Worksheet. b. Summarize your results using an appropriate chart type and place it below the summarized data. c. Spell check the worksheet. d. Save your work (CTRL+s). 6. Analysis, Summary and Recommendation (TCO's 8, and 9; 30 Points). Analyze the results from Quest 5-Organizing Data worksheet and create a report/memo with the following: a. Identify the top two selling items and the two lowest selling items. b. Add a Chart/Table (s) to reflect your results - or use the chart created in step 5 c. Offer management a recommendation to improve the business based upon your analysis. d. List at least two Lessons Learned from your course experience. e. Save and close your Excel file. To complete your exam: Upload your completed file to the Week 8 Final Exam dropbox on our course site. Make sure that your submission is showing in the Outbox with the file icon! Go back to the eCollege Exam page, answer the exam Music on Demand question with TRUE and click \"Submit for Grading\" You may also wish to send an email to the instructor before you leave the final as confirmation of your submission. Copyright 2016 by DeVry Educational Development Corporation. All rights reserved. No part of this work may be reproduced or used in any form or by any means - graphic, electronic, or mechanical, including photocopying, recording, Web distribution or information storage and retrieval systems - without the prior consent of DeVry Educational Development Corporation. Page 4 of 4 v-3 BIS-155 Final Exam Practical Problems Music on Demand (V3) Name: Date: Music on Demand-Sales by Product Product Unit Price Quantity Total Sales Vinal Alb 14.99 75 Cassetts 3.99 30 Digital 1.99 256 Single So 2.99 98 Headsets 15.89 16 Turn tabe 105.75 8 Speakers 50.99 20 Wiring 10.99 15 Outdoor S 55.65 15 Cleaning k 5.99 25 Clearning 6.89 35 Headset p 10.5 24 Electrical 7.25 15 MP3 Playe 21.76 200 Sales Assessment 0 Poor 500 Good 1000 Outstanding! Part 1 - Income Statement Payment Function and Analysis (Part F & G) Annual Revenue: Sales: Vinal Albums Cassetts Digital Single Songs Headsets Turn tabes Speakers Wiring Outdoor Speakers Cleaning kit for Vynal Clearning kit ofr Cassetts Headset parts Electrical cords MP3 Players Down PaymenPresent Value Interest/Perio Loan Amount # Periods 10000 160000 20000 160000 1-way Analysis on MP3 Player Unit Sales 200 Unit Price $ 21.76 Unit Cost $ 17.25 Total Sales: $ 4,352.00 Total Sales Total Cost: $ 3,450.00 Total Cost Net Income: $ 902.00 Net Income Total Revenue Annual Expenses: Variable Expenses Postage Handling Sub Total Variable Expenses Fixed Expenses Insurance Maintenance Administrative and Markeing Taxes SubTotal Fixed Expenses Total Expenses Summary: Total Revenue Total Expenses Net Income 200 200 100 150 300 2450 Payment Effect of Varying Unit Sales on Total Sales, Total Cost, and Net Income Initial Values 10 12 14 16 $ 4,352.00 $ 3,450.00 $ 902.00 18 DC Branch Sales Month Vinal Albums Cassetts January 246 February 343 547 462 590 112 267 324 523 145 635 598 Digital 547 598 649 349 335 698 987 1040 545 222 356 111 649 355 1050 547 340 207 547 459 356 1050 765 343 Single Songs Headsets Turn tables Speakers Wiring Outdoor Speak Cleaning kit foClearning kit oHeadset parts Electrical cord MP3 Players 1050 570 607 594 204 109 743 659 598 335 349 246 547 649 1050 570 607 594 109 289 659 400 570 607 594 204 109 743 289 400 335 349 547 649 1050 570 607 594 204 109 289 400 598 335 547 649 1050 570 607 594 204 743 659 400 598 350 876 547 649 1050 570 607 204 743 289 659 649 1050 570 607 594 204 109 289 400 598 335 650 547 649 1050 570 607 594 109 289 659 400 234 157 547 649 1050 570 607 204 743 289 659 570 607 594 204 109 743 289 400 335 349 254 1050 570 607 594 204 109 743 659 598 335 349 349 649 1050 570 607 594 204 743 659 400 598 Houston Branch Sales Month Vinyl Albums Cassettes January 565 February 578 269 462 656 350 987 353 686 113 567 489 Digital 547 598 649 349 335 698 987 223 545 222 356 111 649 355 1050 547 340 207 547 459 356 1050 765 343 Single Songs Headsets 1050 246 570 649 547 350 649 650 234 570 1050 349 570 547 607 242 649 876 126 547 157 607 570 649 Turn tables Speakers Wiring Outdoor Speak Cleaning kit foCleaning kit foHeadset parts Electrical cord MP3 Players 607 594 204 109 743 659 598 335 349 649 1050 570 607 594 109 289 659 400 594 204 109 743 289 400 335 349 547 570 607 594 204 109 289 400 598 335 1050 570 607 594 204 743 659 400 598 547 649 1050 570 607 204 743 289 665 570 607 553 204 212 998 235 365 335 649 145 267 607 226 109 129 659 400 547 649 987 570 774 647 289 267 834 594 204 109 743 289 400 335 267 254 607 594 204 109 743 659 598 335 349 1050 570 607 594 204 743 659 400 598 LA Branch Sales Month Vinal Albums Cassetts January 246 February 343 547 462 590 112 267 324 523 145 635 598 Digital 547 598 649 349 335 698 987 657 545 222 356 111 649 355 1050 547 340 207 547 459 356 226 765 343 Single Songs Headsets Turn tables Speakers Wiring Outdoor Speak Cleaning kit foClearning kit oHeadset parts Electrical cord MP3 Players 1050 570 607 594 204 109 743 659 598 335 349 246 547 649 1050 570 607 594 109 289 659 400 570 607 594 204 109 743 289 400 335 349 547 649 1050 570 607 594 204 109 289 400 598 335 547 649 1050 570 607 594 204 743 659 500 598 350 876 547 649 1050 570 607 204 743 289 659 649 25 570 607 594 204 109 289 600 598 335 650 547 649 167 570 607 594 336 289 659 350 234 157 547 649 257 570 265 204 788 289 659 570 607 594 204 109 743 289 400 335 349 254 573 570 607 594 204 109 743 659 598 335 349 349 649 1050 570 607 594 204 743 659 400 598 Place memo and Text here