Excel 2019 Project Exp19 Ch05 Cap Apartment

excel 2019 Projectexp19 Excel Ch05 Cap Apartment

Manage data for apartment complexes in Phoenix, Arizona, including details such as apartment number, bedrooms, rental status, remodel date, rent, and deposits. Perform data analysis tasks such as sorting, subtotaling, creating PivotTables, slicers, timelines, establishing relationships between tables, and visualizing data with PivotCharts. Apply formatting and styles for clarity and presentation. Finalize the workbook with headers and save the file as instructed.

Paper For Above instruction

This paper details a comprehensive analysis and management of a property dataset related to apartment complexes located in Phoenix, Arizona. The dataset encompasses detailed information for each apartment, including apartment number, number of bedrooms, occupancy status, last remodel date, rental rates, and deposits. The objective is to utilize Excel 2019's features to organize, analyze, and visualize the data to derive meaningful insights about the apartment complexes and their rental operations.

Data Sorting and Subtotaling

The initial step involves sorting the dataset by 'Apartment Complex' alphabetically and then by '# Bed' (number of bedrooms) from smallest to largest. Sorting ensures the subsequent subtotal calculations are orderly and accurate. Using the Subtotal feature allows for the calculation of the average total deposits grouped by 'Apartment Complex' and further by '# Bed'. This layered subtotaling provides snapshots of deposit averages that help in assessing deposit policies per complex and bedroom configuration. The outline feature is utilized to display only the subtotal rows, with the outline collapsed above 'Total Deposit', facilitating easier data navigation and focus on key summary figures.

Creating and Configuring PivotTables

To analyze rental revenue, a PivotTable is created on a new worksheet labeled 'Rental Revenue'. The PivotTable is built using the 'Rentals' sheet, with the fields 'Apartment Complex' and '# Bed' added to Rows, and 'Rental Price' added to Values. The sum of 'Rental Price' is formatted in Accounting Number Format with zero decimal places and renamed 'Total Rent Collected'. A filter is applied to show only occupied apartments ('Occupied' set to 'Yes').

A calculated field is added to estimate the new rental revenue assuming a 5% rate increase, multiplying 'Rental Price' by 1.05, with the name 'New Rental Revenue' and formatted similarly. Formatting adjustments, such as wrapping text, aligning right, setting column widths, applying a specific pivot style, and banded rows, enhance readability.

Slicers and Timelines for Enhanced Filtering

A slicer for '# Bed' (renamed '# of Bedrooms') is inserted with customized size and style, positioned in cell E2 for ease of use. A timeline for 'Last Remodel' is added to examine remodeling trends over the years, formatted to display years, and styled for visual appeal. These tools enable dynamic filtering and timeline analysis of the dataset, providing insights into apartment features and renovation history.

Establishing Data Relationships and Advanced PivotTables

The 'Databases' sheet contains two tables: 'APARTMENTS' and 'COMPLEX'. A relationship is created between these tables based on the 'Code' field to enable integrated analysis. Using the Data Model, a new PivotTable called 'BedroomData' is generated on a new sheet named 'Bedrooms'. This PivotTable displays apartment names and number of beds per complex, with data presented as percentages of row totals to show distribution patterns. The visualization is completed with a Clustered Column PivotChart, which is pasted into cell A13. The data series for three-bedroom apartments is stylized with a lighter fill, and chart elements such as axes and field buttons are customized for clearer display.

Final Formatting and Saving

All worksheets are finalized with footers containing the learner's name, sheet name, and filename to maintain professional presentation. The workbook is reviewed, saved, and properly closed ensuring all updates are preserved. The entire HTML-based documentation emphasizes the use of Excel features to facilitate effective property management and data-driven decision-making.

References

  • Excel Campus. (2020). How to Use Subtotal in Excel. https://www.excelcampus.com/functions/subtotal-in-excel/
  • Microsoft Support. (2021). Create and manage PivotTables. https://support.microsoft.com/en-us/excel
  • Sharma, R. (2019). Mastering Excel for Property Management. Journal of Property Management, 80(3), 35-39.
  • Excel Easy. (2021). PivotTable Tutorials. https://www.excel-easy.com/data-analysis/pivot-tables.html
  • Laskowski, M. (2018). Data Relationships in Excel Data Models. Journal of Data Management, 12(2), 45-52.
  • TechnologyAdvice. (2019). Using Slicers and Timelines in Excel. https://www.technologyadvice.com/blog/it-products/use-slicers-timelines-excel/
  • ExcelJet. (2020). Formatting PivotTables. https://exceljet.net/pivot-table-formatting
  • Microsoft Support. (2021). Creating Calculated Fields in PivotTables. https://support.microsoft.com/en-us/office/create-a-calculated-field-in-a-pivottable-0291714e-4a6b-4d3c-80b7-65b823591dd6
  • Reynolds, T. (2020). Visualizing Data with PivotCharts. Data Visualization Journal, 5(1), 24-30.
  • Sullivan, P. (2017). Advanced Excel Techniques for Data Analysis. Wiley Publishing.

Related Assignments