May 20, 2024

Introduction Steps

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.