Select National Properties Group Property Loan Case Scenario

User Generated

Erv008

Business Finance

Description

The Scenario:

Tony Spacken, Sales Manager, has an Excel workbook listing properties for sale. If a property is sold, Select National Properties Group will finance the property loan for 30 years at 7.5% interest. If the monthly payment of a property is greater than $300,000, the company will give the buyer a 1% discount of the selling price. Lavern Gallen is interested in purchasing the Pleasant Properties Warehouse, one of the company’s properties. She lives at 4231 Center Drive, Tempe, TX 76501.

For this project, you will need the following files:

New blank Access database

New blank PowerPoint presentation

Gallen_Letter

Sale_Properties

You will save your files as:

Reifel_Sawyer_Sale_Properties

Reifel_Sawyer_SNPG_Properties

Reifel_Sawyer_Gallen_Letter

Reifel_Sawyer_Sales_Presentation

  1. Open the file Sale_Properties, and then save the workbook as LastnameFirstname_Sale_Properties
  2. Copy the Properties worksheet to a new worksheet,
  3. Rename the worksheet as Payments
  4. In the Payments worksheet, convert the data to a table.
  5. In column G, insert a calculated column using the PMT function to calculate the monthly payment for each of the properties.
  6. In column H, insert a calculated column using the IF function to determine the amount of the if discount
    property qualifies for it.
  7. Determine the totals for columns F:H.
  8. Apply conditional formatting to cells with a selling price between $40,000,000 and $70,000,000.
  9. Copy the Payments worksheet to a new worksheet,
  10. Rename the new worksheet as Pleasant Properties
  11. Filter the table to display only the data for the Pleasant. Insert your name in the footer so that it displays on all worksheets.
  12. Create a blank Access database, and then save it as Lastname_Firstname_SNPG_Properties
  13. Import the data from the Properties worksheet.
  14. Create a query that displays all fields, except the ID field, for properties located in Florida and Georgia.
  15. Save the query as Lastname_Firstname_FL_GA Query
  16. Export the query result as an RTF file
  17. Create another query that displays all fields, except the ID field, for all properties with selling prices less than $1,000,000.
  18. Save the query as Lastname Firstname Price Query
  19. For each query, create and format a report.
  20. Save each report with the name as the query, substituting the word Report for Query.
  21. Create a PowerPoint presentation using a template of your choice; the presentation will be shown to prospective buyers attending a sales conference.
  22. Save the presentation as Lastname_Firstname_Sales_Presentation
  23. Insert the file name in the footer to display on all pages of the notes and handouts.
  24. Modify the slides to help persuade buyers to purchase property from Select National Properties Group:
  25. On one slide, inform the audience that the company sells many properties.
  26. On the same slide, link the cell from the Excel Payments worksheet that displays the total selling price.
  27. On a new slide, insert a SmartArt graphic that lists some of the different types of properties that the company develops and manages.
  28. At the end of the presentation, insert a slide that contains the contact information for Mr. Spacken.
  29. Insert additional text, pictures, and clip art to enhance the presentation.
  30. Format any text or other objects to create a professional appearance.
  31. Open the file Gallen_Letter, and then save as Lastname_Firstname_Gallen_Letter
  32. Insert the file name in the footer.
  33. Format the letterhead for Mr. Tony Spacken.
  34. Insert the current date, and then insert Lavern Gallen’s name and address and a proper salutation.
  35. Following the first paragraph, copy the SmartArt graphic from your presentation to the letter, changing text wrapping and resizing as necessary.
  36. Following the second paragraph, from Excel, on the Pleasant Properties worksheet, link cells F1:G38 (first and second row) to the Word document.
  37. Under the third paragraph, insert the RTF file listing the Florida and Georgia properties.
  38. Format the document to create a professional appearance.

Unformatted Attachment Preview

Property AmberTechnical Training Allen Surgical Center All American Service Industry Fulfillment Archer Square Shopping Mall Ardenna Wood Medical Center Arroh Surgical Hospital Arroun Grande Rehabilitation Hospital Astin Office Solutions Warehouse Austin & Ball Warehouse Ballyhoo Office Building Barton, Oaks & Colina Building Belmar Group Warehouse Bergstrom Industries Warehouse Best Electronics Manufacturing Bloomington Hospital Boulder Community Training Services Broaddus Surgical Center Broatman Hospital Builder Supply Warehouse Cardman Facility Casa Mesa Regional Medical Center Cayeta County Educational Services Cincinnati Career Center Circuits Computing Training Community Hospital West Contrail Engineering Crescent Media Consultants Building Crosswinds Educational Partners Cullen Refrigeration Systems Dallas Training and Educational Center Daytona Automobile Assembly Systems Deaconness Medical Center Fayette Memorial Medical Center Finlay Supply Co. Goshen County Hospital Guyan Valley Medical Center Pleasant Properties Warehouse Hiniston Building Illiana Training Institute Jadon Michels Shopping Mall Javitch Office Park JIT Fulfillment Facilities JM Facilities Koler Whole Foods La Costa Research Industrial Building Lidle Manufacturing Inc. Address 789 W. Main St. 106 E. 1500 N. 1401 Mother Lode Cir. 4980 Hwy. 14 E 306 W. Central Ave. 42 W. 35th Ave. 500 NW Cheyenne 2211 SE 16 St. 712 E. 108th Ave. 4161 N. 1500 W. 1119 NE Thorn Hill Rd. 30 Imperial Way 209 Elice Cir. 8692 Technology Blvd. 843 Fayette Ave. 176 Van Buren St. 145 Tudor Dr. 22161 N. Randolph Dr. 103 Cleveland St. 100 Linden Ln. 926 Camino Blvd. 6921 1st Ave. W. 9653 Jackson St. 900 Tech Dr. Suite 340 33 Herman Ave. 11042 Woodruff Ave. 3718 W. Sardis Rd. 690 Kings Highway Blvd. 806 W. Texas Ave. 533 Lanceford St. 22161 N. Randall Dr. 8465 Skyline Way Blvd. 423 Hazel Dell Blvd. 8444 Helms Ave. 4813 Augusta Cir. 2045 Lynne Blvd. 400 Pivot Rock Blvd. 30050 Walmsley Rd. 860 Brilliance Way 412 E. Frontage Rd. 460 Montagne Way 106 NW Osage 403 Industrial Blvd. 6815 Reseda Blvd. 2600 E. Itetbury Ln. 705 Fargo Ave. City Albuquerque Rugby Port Huron St. Louis Arlington Ulysses Salem Battle Creek Paterson Green Bay Jefferson City Bolivar Littleton Berkeley Amarillo Boulder Waterloo Auburn Elkhorn Rock Springs Inglewood Brookfield Cincinnati Salt Lake City Concord Gresham Colorado Springs Claremont Edmond Dallas Princeton Alameda Wallingford West Mifflin Cambridge Marion Gainesville Forth Worth Evansville Minneapolis Ballinger Pocatello Holyoke Aurora Davenport Bardstown State NM ND MI MO TX KS NH MI NJ WI MO TN CO MI TX CO IA WA WI WY CA WI OH UT MA OR CO CA OK TX WV CA CT PA MN KY FL TX IN MN TX ID MA CO IA KY Littleton Professional Outreach and Training Lyman Test Prep Services Madison Logistics Co. Marco Supply Warehouse Maricopa Medical Center Miami-Dade Employment Training Services Midwest Regional Hospital Milwaukee Health Services Training Mississippi Mentoring Associates Montana Agricultural Supply Mykala Michels Office Building Organic Textiles Inc. Padme Parts Services Parton & Simms Warehouse Peterson Testing Services Pleasant Valley Rehabilitation Hospital Preston General Hospital Riverdale Medical Center San Mateo Mentors Sloane-Reynolds County Hospital Steiner Group Building Tangier Industrial Design Tanglewood After School Services Technology Parts Inc. Telcom Ltd. Phone Systems Office Park Test Prep Training Services Tillecum Metalworks University Hospital of Austin University of Tempe Outreach Center Woodland Daycare Center Zeldman Technology Buiding Zionsville Centre for Health Occupations 68 Brittany Rd. 730 S. Monte Park Pkwy. 208 E. Central Ave. 2445 E. 51st St. 582 Deering Ave. 4245 Carvine Dr. 706 Valencia St. 4211 Cohen Dr. W. 5491 Lynhurst Pkwy. 1545 Park Dr. 2250 Heritage Rd. 8742 Circuitry Way 8851 Skyline Blvd. 1050 Luther Ave. 10302 Heartfield Dr. 50 Tulip St. 36815 Bethesda Blvd. 872 Riverdale Way 412 Valencia St. 2345 E. Sloane St. 801 Hwy. 25 12045 Lakewood Blvd. 4591 Spinnaker Cove Way 3422 Lees Ave. 1 Basildon Cir. 2003 N. 14th St. 581 Hwy. 31 S. 683 Hwy. 36 N. 43 Educational Dr. 320 Lindon St. 1188 Boswell St. 1111 Meadowbrook Rd. Lexington Lake George Cabot Coventry Macon Miami Bay City Milwaukee Coldwater Springfield Detroit Norman Atlanta Wenatchee Oak Forest Nashville Jamaica Plain Montgomery San Mateo Lebanon Camden Grinnell Lincoln Marietta Jeffersonville Miami Blackfoot Quincy Tempe Frankford North Little Rock Burlington SC WI AK RI GA FL MI WI MS MA MI OK GA WA IL GA MA AL CA NH TN IA NE GA IN FL ID IL AZ WV AR VT ZIP 87110 58368 48060 63122 76012 67880 03079 49017 07514 54305 65101 38008 80122 48072 79106 80026 50702 98001 53121 82901 90301 53045 45221 84106 01742 97030 80906 91711 73013 75205 30375 94501 06492 15122 55008 42064 33030 76014 47740 55407 76821 36188 01040 80017 52803 40004 Selling Price $ 604,500 $ 53,999,000 $ 751,500 $ 6,390,000 $ 68,433,000 $ 55,500,000 $ 75,520,000 $ 6,230,000 $ 9,189,500 $ 5,675,000 $ 5,460,000 $ 4,900,000 $ 7,326,000 $ 6,030,000 $ 60,450,000 $ 848,900 $ 68,150,000 $ 47,146,000 $ 6,412,000 $ 3,980,000 $ 81,950,000 $ 597,500 $ 399,900 $ 714,300 $ 56,275,000 $ 584,600 $ 2,560,000 $ 627,500 $ 851,700 $ 523,000 $ 547,600 $ 88,489,000 $ 32,850,000 $ 887,000 $ 47,900,000 $ 59,750,000 $ 5,630,000 $ 5,740,000 $ 552,000 $ 6,470,000 $ 6,489,000 $ 641,700 $ 4,580,000 $ 650,500 $ 4,785,000 $ 410,000 29072 54501 72023 02816 31208 33131 48708 53219 68618 01111 48201 73069 30310 98801 60452 31639 02130 36117 94401 03756 38320 50112 68522 30008 47130 33131 83221 62305 85282 24938 72118 05401 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 643,300 435,000 746,200 637,800 77,080,000 685,000 69,512,000 714,600 713,500 5,410,000 8,570,000 461,300 741,000 4,659,000 675,000 77,143,000 77,135,000 54,350,000 651,200 55,230,000 7,465,000 715,000 550,000 698,000 3,390,000 708,000 576,500 46,750,000 385,000 895,000 7,886,000 479,000 Mr. Tony Spacken 321 North Michigan Avenue, Suite 700 Chicago, IL 60601 312-555-0070 I understand that you are interested in a property that we are selling. I am Tony Spacken, Sales Manager at Select National Properties Group. We are a diversified real estate company that develops, builds, manages and acquires properties nationwide. Our company is focused on quality and commitment to the environment and economic development of the areas where we operate. Listed below are the types of properties in our portfolio. Select National Properties Group will use resources at our disposal to work with you to finance the purchase of the Harvest Properties Warehouse in which you expressed an interest. We have calculated the monthly payment for you based on a 30-year loan financed at 7.5% interest. The selling price and payment are shown below. We understand that you are interested in property in either Florida or Georgia. We have a number of properties in both of these states that might be of interest to you. I have provided a list of the properties which includes the addresses as well as the selling price. Please let me know if you are interested in any of these properties and would like more information on them. I would like to thank you for your interest in the Harvest Properties Warehouse and look forward to hearing from you on how you would like to proceed on this property or any of the other properties in Florida or Georgia. Sincerely, Tony Spacken Sales Manager
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Please find attached the solution. A...

Similar Content

Related Tags