Question Details

[solution] » Question: 1) The Database tab of the workbook contains a

Brief item decscription

Step-by-step solution file


Item details:

Question: 1) The Database tab of the workbook contains a
More:

Question:

1)   The Database tab of the workbook contains a duplicate listing of all of the apartments listed as rental properties. The owners are looking for you to help them filter through all of this data to come up with specific pieces of information. They would like to have answers to the following questions.

  • How many two- and three-bedroom apartments need to be remodeled?
  • What is the value of lost rent income?
  • What is the year of the oldest remodel?

You know you will need to perform an advanced filter and use some database functions to address these interests. Select the Database worksheet to begin. 

  1. Enter conditions in the Specify Criteria area for unoccupied two- and three-bedroom apartments that need to be remodeled. Be very careful with data entry and spelling.
  2. Select cells A2:H4 and give it the Range Name Criteria.  
  3. Insert database functions to determine the database statistics in the range D8:D10.
  4. Complete an advanced filter based on the specify criteria range; filter the database in place. 
  5. Put  the three Rs into practice. Adjust all titles and columns for proper  spacing. Double check data formats and consider grid lines.  



2)   

Summit Ridge Ski owners want to purchase a sixth apartment complex. This decision is under review. Here are the details of their offer. The loan amount is $950,000 with a down payment of $400,000 for 30 years at 5.325%, with the first payment due on January 20, 2017. Please consider the loan calculations and build a loan amortization table on the Loan worksheet. Click on the Loan worksheet to begin. 

  1. Enter the loan details provided above in the Input Area and place formulas to create all calculations in the Summary Calculations. The loan payment is at the end of the period.
  2. Create a loan amortization table. The Payment Date column needs a date function. The Interest Paid and Principal Payment columns require financial functions.
  3. Create a custom footer with your name on the left side, the page and page number in the center, and your professor's name on the right side of each worksheet. Make sure you put the page back to normal view after you insert the footer at the bottom.  


3)  

In addition to adding the new apartments to their financial portfolios, the owners of Summit Ridge Ski Resort would like to ensure that they are paying their employees at market value and want to continue to encourage employees to stay with the company. Another phase of your project is to research all employee salaries to see if they have any effect on job satisfaction. Employee satisfaction surveys allow the company to get a pulse for how content employees are. A voluntary survey was administered to a cross-section sample of all employees in the company. This next bit of work will be on the Employee Satisfaction Worksheet.  

  1. Calculate the average job satisfaction for Administrative Assistant in cell I5. Format the results with the number format and two decimal positions.
  2. Use the fill handle from cell I5 to copy the function down through the range I6:I11. Make certain to consider the appropriate mixed and/or absolute cell referencing.
  3. Calculate the average salary of all Administrative Assistants and place the result in cell J5. 
  4. Use the fill handle from cell J5 to copy the function down through the range J6:J11. Make certain to consider the appropriate mixed and/or absolute cell referencing.
  5. Calculate the number of Sales and Marketing Directors in cell I14 that have a job satisfaction level of 4 or above. 
  6. Calculate the average salary of Sales and Marketing Directors in cell I15 that have a job satisfaction level of 4 or above.
  7. Use a process like that demonstrated in steps E and F to calculate the total number and the average salary of Managers that have a job satisfaction of 4 or greater


4)  

Please continue your analysis and calculate both the salary rankings and the salary quartile thresholds.

  1. Insert a function in cell F4 to determine the rank of the salary in cell D4 against the range of all salaries.
  2. Copy the Salary Rank function down column F. Make certain to consider the appropriate mixed and/or absolute cell referencing.
  3. Calculate the minimum quartile value in the list of salaries. Place your quartile function in cell I22.
  4. Copy the remaining quartile values in range I23:I26. Make certain to consider the appropriate mixed and/or absolute cell referencing.
  5. Insert a function in cell H29 to determine the strength of the relationship between satisfaction and salary. Format the correlation of columns D and E as number format with two decimal positions.


5)

You are almost finished with your analysis. Please create a summary of descriptive statistics using the Analysis ToolPak. The Analysis ToolPak add-in must be active before completing the following.

  1. Go to the Data tab and select Data Analysis. Select Descriptive Statistics, and click OK.
  2. Insert the input range using the salary data in column D.
  3. Select the new worksheet output option to display on a new worksheet named Descriptive Statistics. Put a check in the box to the left of Summary statistics. Click OK.
  4. Create a histogram with the Analysis ToolPak. The salary column D will be the input range and the quartile range I22:I25 will be the bin range. Position the Output range in cell H31. Make sure the Chart output is checked. Click OK.
  5. Format the worksheet professionally.  



Stay a While Longer - Apartments Search

 

Unit # Unit #

 

1101

 

1102

 

1103

 

1104

 

1105

 

1106

 

1107

 

1108

 

1109

 

1110

 

1201

 

1202

 

1203

 

1204

 

1205

 

1206

 

1207

 

1208

 

1209

 

1210

 

1301

 

1302

 

1303

 

1304

 

1305

 

1306

 

1307

 

1308

 

1401

 

1402

 

1403

 

1404

 

1405

 

1406

 

1407

 

1408

 

1501

 

1502

 

1503

 

1504 1209 Apartmen

 

t Complex # Bed

 

Turning Lea

 

1

 

Turning Lea

 

1

 

Turning Lea

 

2

 

Turning Lea

 

2

 

Turning Lea

 

3

 

Turning Lea

 

3

 

Turning Lea

 

2

 

Turning Lea

 

2

 

Turning Lea

 

3

 

Turning Lea

 

3

 

Turning Lea

 

1

 

Turning Lea

 

1

 

Turning Lea

 

2

 

Turning Lea

 

2

 

Turning Lea

 

3

 

Turning Lea

 

3

 

Turning Lea

 

2

 

Turning Lea

 

2

 

Turning Lea

 

3

 

Turning Lea

 

3

 

Piedra Spri

 

1

 

Piedra Spri

 

1

 

Piedra Spri

 

1

 

Piedra Spri

 

1

 

Piedra Spri

 

2

 

Piedra Spri

 

2

 

Piedra Spri

 

2

 

Piedra Spri

 

2

 

Eagles Dom

 

1

 

Eagles Dom

 

2

 

Eagles Dom

 

3

 

Eagles Dom

 

1

 

Eagles Dom

 

2

 

Eagles Dom

 

3

 

Eagles Dom

 

2

 

Eagles Dom

 

2

 

Hickory Rid

 

1

 

Hickory Rid

 

1

 

Hickory Rid

 

1

 

Hickory Rid

 

1 Rental Price $1,050.00 Last

 

Pet

 

Rental Price

 

Occupied Remodel Deposit

 

$

 

765.00

 

Yes

 

2005

 

100

 

$

 

765.00

 

Yes

 

2003

 

100

 

$

 

895.00

 

No

 

1999

 

100

 

$

 

895.00

 

Yes

 

2005

 

100

 

$

 

1,225.00

 

No

 

2007

 

150

 

$

 

1,225.00

 

Yes

 

2005

 

100

 

$

 

895.00

 

No

 

2005

 

100

 

$

 

895.00

 

Yes

 

2009

 

150

 

$

 

1,225.00

 

No

 

2008

 

150

 

$

 

1,225.00

 

Yes

 

2009

 

150

 

$

 

775.00

 

Yes

 

2011

 

100

 

$

 

775.00

 

Yes

 

2010

 

100

 

$

 

875.00

 

Yes

 

2011

 

150

 

$

 

875.00

 

No

 

2011

 

150

 

$

 

1,050.00

 

Yes

 

2005

 

100

 

$

 

1,050.00

 

No

 

2005

 

100

 

$

 

875.00

 

Yes

 

2000

 

100

 

$

 

875.00

 

Yes

 

2000

 

100

 

$

 

1,050.00

 

No

 

2005

 

100

 

$

 

1,050.00

 

Yes

 

2005

 

100

 

$

 

875.00

 

No

 

2008

 

100

 

$

 

875.00

 

No

 

2001

 

100

 

$

 

900.00

 

No

 

2001

 

100

 

$

 

900.00

 

Yes

 

2001

 

100

 

$

 

1,355.00

 

No

 

2005

 

100

 

$

 

1,355.00

 

Yes

 

2005

 

100

 

$

 

1,355.00

 

Yes

 

2011

 

150

 

$

 

1,355.00

 

No

 

2011

 

150

 

$

 

875.00

 

No

 

2005

 

100

 

$

 

950.00

 

No

 

2011

 

150

 

$

 

1,355.00

 

Yes

 

2011

 

150

 

$

 

875.00

 

No

 

2011

 

100

 

$

 

950.00

 

Yes

 

2011

 

150

 

$

 

1,355.00

 

No

 

2002

 

100

 

$

 

975.00

 

Yes

 

2002

 

100

 

$

 

975.00

 

Yes

 

2002

 

100

 

$

 

550.00

 

Yes

 

2011

 

100

 

$

 

550.00

 

No

 

2011

 

100

 

$

 

550.00

 

No

 

2003

 

100

 

$

 

550.00

 

Yes

 

2001

 

100 1505

 

1506

 

1507

 

1601

 

1602

 

1603

 

1604

 

1605

 

1606

 

1607 Hickory Rid

 

Hickory Rid

 

Hickory Rid

 

Cypress Tra

 

Cypress Tra

 

Cypress Tra

 

Cypress Tra

 

Cypress Tra

 

Cypress Tra

 

Cypress Tra 2

 

2

 

2

 

1

 

1

 

2

 

2

 

3

 

3

 

3 $

 

$

 

$

 

$

 

$

 

$

 

$

 

$

 

$

 

$ 850.00

 

850.00

 

850.00

 

1,225.00

 

1,225.00

 

1,355.00

 

1,355.00

 

1,675.00

 

1,695.00

 

1,710.00 No

 

Yes

 

No

 

Yes

 

Yes

 

No

 

Yes

 

No

 

Yes

 

No 2003

 

2011

 

2011

 

2005

 

2005

 

2005

 

2011

 

2011

 

2005

 

2005 100

 

150

 

150

 

100

 

100

 

100

 

150

 

150

 

100

 

100 rch Recommendation

 

No Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

Please Remodel

 

Please Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

Please Remodel

 

No Remodel Please Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

Please Remodel Criteria Range Unit # Development # Bed Rental Price Occupied Database Statistics No. of Apts. to Remodel

 

Value of Lost Rent

 

Year of Oldest Remodel Advanced Filter based on Criteria Range Unit #

 

1101

 

1102

 

1103

 

1104

 

1105

 

1106

 

1107

 

1108

 

1109

 

1110

 

1201

 

1202

 

1203

 

1204

 

1205

 

1206

 

1207

 

1208

 

1209

 

1210

 

1301

 

1302

 

1303

 

1304

 

1305

 

1306

 

1307

 

1308

 

1401

 

1402

 

1403

 

1404

 

1405 Development

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Turning Leaf Circle

 

Piedra Springs

 

Piedra Springs

 

Piedra Springs

 

Piedra Springs

 

Piedra Springs

 

Piedra Springs

 

Piedra Springs

 

Piedra Springs

 

Eagles Domain Road

 

Eagles Domain Road

 

Eagles Domain Road

 

Eagles Domain Road

 

Eagles Domain Road # Bed

 

1

 

1

 

2

 

2

 

3

 

3

 

2

 

2

 

3

 

3

 

1

 

1

 

2

 

2

 

3

 

3

 

2

 

2

 

3

 

3

 

1

 

1

 

1

 

1

 

2

 

2

 

2

 

2

 

1

 

2

 

3

 

1

 

2 Rental Price Occupied

 

$765.00

 

Yes

 

$765.00

 

Yes

 

$895.00

 

No

 

$895.00

 

Yes

 

$1,225.00

 

No

 

$1,225.00

 

Yes

 

$895.00

 

No

 

$895.00

 

Yes

 

$1,225.00

 

No

 

$1,225.00

 

Yes

 

$775.00

 

Yes

 

$775.00

 

Yes

 

$875.00

 

Yes

 

$875.00

 

No

 

$1,050.00

 

Yes

 

$1,050.00

 

No

 

$875.00

 

Yes

 

$875.00

 

Yes

 

$1,050.00

 

No

 

$1,050.00

 

Yes

 

$875.00

 

No

 

$875.00

 

No

 

$900.00

 

No

 

$900.00

 

Yes

 

$1,355.00

 

No

 

$1,355.00

 

Yes

 

$1,355.00

 

Yes

 

$1,355.00

 

No

 

$875.00

 

No

 

$950.00

 

No

 

$1,355.00

 

Yes

 

$875.00

 

No

 

$950.00

 

Yes 1406

 

1407

 

1408

 

1501

 

1502

 

1503

 

1504

 

1505

 

1506

 

1507

 

1601

 

1602

 

1603

 

1604

 

1605

 

1606

 

1607 Eagles Domain Road

 

Eagles Domain Road

 

Eagles Domain Road

 

Hickory Ridge Villas

 

Hickory Ridge Villas

 

Hickory Ridge Villas

 

Hickory Ridge Villas

 

Hickory Ridge Villas

 

Hickory Ridge Villas

 

Hickory Ridge Villas

 

Cypress Trail

 

Cypress Trail

 

Cypress Trail

 

Cypress Trail

 

Cypress Trail

 

Cypress Trail

 

Cypress Trail 3

 

2

 

2

 

1

 

1

 

1

 

1

 

2

 

2

 

2

 

1

 

1

 

2

 

2

 

3

 

3

 

3 $1,355.00

 

$975.00

 

$975.00

 

$550.00

 

$550.00

 

$550.00

 

$550.00

 

$850.00

 

$850.00

 

$850.00

 

$1,225.00

 

$1,225.00

 

$1,355.00

 

$1,355.00

 

$1,675.00

 

$1,695.00

 

$1,710.00 No

 

Yes

 

Yes

 

Yes

 

No

 

No

 

Yes

 

No

 

Yes

 

No

 

Yes

 

Yes

 

No

 

Yes

 

No

 

Yes

 

No Last Remodel Pet Depost Recommendation Last Remodel

 

2005

 

2003

 

1999

 

2005

 

2007

 

2005

 

2005

 

2009

 

2008

 

2009

 

2011

 

2010

 

2011

 

2011

 

2005

 

2005

 

2000

 

2000

 

2005

 

2005

 

2008

 

2001

 

2001

 

2001

 

2005

 

2005

 

2011

 

2011

 

2005

 

2011

 

2011

 

2011

 

2011 Recommendation

 

No Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

Please Remodel

 

Please Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel eria Range Pet Deposit

 

100

 

100

 

100

 

100

 

150

 

100

 

100

 

150

 

150

 

150

 

100

 

100

 

150

 

150

 

100

 

100

 

100

 

100

 

100

 

100

 

100

 

100

 

100

 

100

 

100

 

100

 

150

 

150

 

100

 

150

 

150

 

100

 

150 2002

 

2002

 

2002

 

2011

 

2011

 

2003

 

2001

 

2003

 

2011

 

2011

 

2005

 

2005

 

2005

 

2011

 

2011

 

2005

 

2005 100

 

100

 

100

 

100

 

100

 

100

 

100

 

100

 

150

 

150

 

100

 

100

 

100

 

150

 

150

 

100

 

100 Please Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

Please Remodel

 

No Remodel

 

No Remodel

 

No Remodel

 

Please Remodel Input Area

 

Complex Cost

 

$950,000.00

 

Down Payment

 

$400,000.00

 

# of Pmts per Year

 

12

 

Years

 

30

 

APR

 

5.325

 

1st Payment Date

 

1/20/2017 Payment # Payment Date Summary Calculations

 

Loan Amount

 

550,000.00

 

No. Periods

 

360

 

Monthly Rate

 

0.44

 

Monthly Payment

 

Total Interest Paid Beginning

 

Balance Interest Paid Principal

 

Payment Ending

 

Balance Employee Statistics

 

ID Number

 

10474

 

11025

 

11062

 

11653

 

14731

 

14869

 

15039

 

15190

 

16015

 

16034

 

16754

 

18283

 

22923

 

24168

 

25280

 

27427

 

30047

 

30836

 

31615

 

33143

 

34737

 

36528

 

37828

 

38088

 

38180

 

39483

 

39692

 

42434

 

42694

 

43987

 

45418

 

45849

 

46521

 

47711

 

49542 Position

 

Key Account Manager

 

Sales Representative

 

Key Account Manager

 

Administrative Assistant

 

Key Account Manager

 

Support Specialist

 

Support Specialist

 

Sales Representative

 

Sales & Marketing Director

 

Budget Manager

 

Sales Representative

 

Support Specialist

 

Key Account Manager

 

Sales & Marketing Director

 

Manager

 

Budget Manager

 

Administrative Assistant

 

Sales & Marketing Director

 

Administrative Assistant

 

Key Account Manager

 

Support Specialist

 

Key Account Manager

 

Sales Representative

 

Support Specialist

 

Sales Representative

 

Support Specialist

 

Budget Manager

 

Support Specialist

 

Budget Manager

 

Key Account Manager

 

Manager

 

Support Specialist

 

Budget Manager

 

Manager

 

Sales & Marketing Director Salary

 

Job Satisfaction

 

$35,855.00

 

3

 

$41,708.00

 

2

 

$47,572.00

 

3

 

$30,176.00

 

4

 

$45,883.00

 

4

 

$32,650.00

 

1

 

$32,750.00

 

3

 

$45,670.00

 

5

 

$89,900.00

 

4

 

$55,389.00

 

2

 

$49,617.00

 

3

 

$35,230.00

 

3

 

$44,854.00

 

2

 

$77,846.00

 

3

 

$70,125.00

 

3

 

$62,263.00

 

4

 

$30,982.00

 

2

 

$84,937.00

 

4

 

$32,709.00

 

4

 

$45,983.00

 

5

 

$33,501.00

 

1

 

$48,706.00

 

5

 

$45,927.00

 

2

 

$31,632.00

 

5

 

$49,575.00

 

3

 

$37,404.00

 

4

 

$65,492.00

 

3

 

$35,316.00

 

2

 

$68,365.00

 

2

 

$41,130.00

 

4

 

$72,862.00

 

3

 

$30,701.00

 

5

 

$66,009.00

 

2

 

$74,767.00

 

4

 

$97,159.00

 

5 Salary Rank Position

 

Administrative Assistant

 

Budget Manager

 

Sales & Marketing Director

 

Key Account Manager

 

Manager

 

Sales Representative

 

Support Specialist Summary Information

 

Average Satisfaction Sales & Marketing Directors with >= 4 Satisfaction

 

Count

 

Average Salary

 

Managers with >= 4 Satisfaction

 

Count

 

Average Salary

 

Quartile

 

1

 

2

 

3

 

4

 

Correlation Average Salary

 







About this question:
STATUS
Answered
QUALITY
Approved
ANSWER RATING

This question was answered on: Feb 21, 2020

PRICE: $24

Solution~00066414138.zip (18.37 KB)

Buy this answer for only: $24

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free copy (Deadline assured. Flexible pricing. TurnItIn Report provided)

Pay using PayPal (No PayPal account Required) or your credit card. All your purchases are securely protected by PayPal.
SiteLock

Need a similar solution fast, written anew from scratch? Place your own custom order

We have top-notch tutors who can help you with your essay at a reasonable cost and then you can simply use that essay as a template to build your own arguments. This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student. New solution orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

Order Now