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:
LawWhite_Frederique_Sale_Properties
LawWhite_Frederique_SNPG_Properties
LawWhite_Frederique_Gallen_Letter
LawWhite_Frederique_Sales_Presentation
Open the file Sale_Properties, and then save the workbook as LawWhite_Frederique_Sale_Properties
Copy the Properties worksheet to a new worksheet,
Rename the worksheet as Payments
In the Payments worksheet, convert the data to a table.
In column G, insert a calculated column using the PMT function to calculate the monthly payment for each of the properties.
In column H, insert a calculated column using the IF function to determine the amount of the if discount
property qualifies for it.
Determine the totals for columns F:H.
Apply conditional formatting to cells with a selling price between $40,000,000 and $70,000,000.
Copy the Payments worksheet to a new worksheet,
Rename the new worksheet as Pleasant Properties
Filter the table to display only the data for the Pleasant. Insert your name in the footer so that it displays on all worksheets.
Create a blank Access database, and then save it as Lastname_Firstname_SNPG_Properties
Import the data from the Properties worksheet.
Create a query that displays all fields, except the ID field, for properties located in Florida and Georgia.
Save the query as LawWhite_Frederique_FL_GA Query
Export the query result as an RTF file
Create another query that displays all fields, except the ID field, for all properties with selling prices less than $1,000,000.
Save the query as Lastname Firstname Price Query
For each query, create and format a report.
Save each report with the name as the query, substituting the word Report for Query.
Create a PowerPoint presentation using a template of your choice; the presentation will be shown to prospective buyers attending a sales conference.
Save the presentation as LawWhite_Frederique_Sales_Presentation
Insert the file name in the footer to display on all pages of the notes and handouts.
Modify the slides to help persuade buyers to purchase property from Select National Properties Group:
On one slide, inform the audience that the company sells many properties.
On the same slide, link the cell from the Excel Payments worksheet that displays the total selling price.
On a new slide, insert a SmartArt graphic that lists some of the different types of properties that the company develops and manages.
At the end of the presentation, insert a slide that contains the contact information for Mr. Spacken.
Insert additional text, pictures, and clip art to enhance the presentation.
Format any text or other objects to create a professional appearance.
Open the file Gallen_Letter, and then save as LawWhite_Frederique_Gallen_Letter
Insert the file name in the footer.
Format the letterhead for Mr. Tony Spacken.
Insert the current date, and then insert Lavern Gallen’s name and address and a proper salutation.
Following the first paragraph, copy the SmartArt graphic from your presentation to the letter, changing text wrapping and resizing as necessary.
Following the second paragraph, from Excel, on the Pleasant Properties worksheet, link cells F1:G38 (first and second row) to the Word document.
Under the third paragraph, insert the RTF file listing the Florida and Georgia properties.
Format the document to create a professional appearance.
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
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
Purchase answer to see full
attachment
