Assignment Task

Introduction Steps

1. Download the attached Excel data file.
2. In the report, create 4 sheets with the following names: Problem 1, Problem 2, Problem 3, Problem 4.
3. The results of each problem should be placed into the corresponding sheets.

Problem 1: Property Analysis

1. Copy the Property Data into the Problem 1 sheet.

   • Sort the records in A-Z order using the Property Type variable and simultaneously by the Sq_ft variable in largest to smallest order.
   • At the bottom of the table for each numeric column, add MIN, MAX, and Average values. Be sure to add labels to identify these values.
   • For each property type, highlight by color the top three and bottom three records.

2. Copy the green table into the Problem 1 sheet and fill it using Excel functions (not manually).

3. On the same sheet, create two histograms of Price for 4 and 8 intervals (bins). Ensure you label the axes and chart titles.

   • Conclusion: What conclusions can be drawn from the above histograms?

Problem 2: Decision Matrix

Suppose you are planning a trip and have five different destinations to choose from. The attractiveness of each destination (rated from 0 to 5) has been assessed by a specialized company.

1. Copy the Decision Matrix template into the Problem 2 sheet.
2. Using Excel, randomly assign weights to each criterion in the range from 0.2 to 1.
3. Using Excel, randomly assign grades for each destination in the range from 0 to 5.
4. Calculate scores for each destination.
5. Explain which destination you will pick and why. Highlight the final answer.

Problem 3: Employee Retirement & Health Plan (1%)

A company contributes to employees’ retirement plans at a rate of 4% of the employee’s annual salary. To be eligible for this benefit, an employee must have full-time status with two or more years of employment.

1. Write the Excel formula to calculate:

   • Retirement Contribution in $$ for each employee.

   • Health Plan Cost ($10K of annual coverage for employees with a family plan, and $8K for employees with an individual plan).

   • Best Solution: The optimal solution will be to use a single logical formula for each task, though multiple solutions are acceptable.

Problem 4: SWOT Analysis

1. Provide a SWOT Analysis for any one problem from the list. Ensure at least 4 factors are used in each table segment.

Final Steps

1. Before saving, ensure all charts have titles and axis names.
2. Save your results in one report file: YourFirstname_YourFamilyname.xlsx.
3. Submit your report to the Dropbox before the deadline.

Note: Only in the case of Dropbox technical issues should you submit your report to my Slate email.