2022: Week 15 Property Management

Created by: Tom Smith (Twitter: @tSmithyyy)

You work for a Property Management company and your boss has asked you to compile a report detailing how much revenue the business can expect to make from its existing office space rental contracts. The time frame in focus is from 2021 until 2026 and the board would like to see a cumulative total for each month of a contract. An additional requirement is to create a dynamic table that totals rent paid for completed years across all contracts, as well as year to date figures for the current year, which would update as time goes on.

Input files:

1. Rental Contracts

   
   

   
   
   

2. Office Space Prices

   
   
   
   
   

Requirements:

• Input the Rental Contracts data

• Work out the length of each contract in months

• Work out the number of months until each contract expires (imagine today is 13th April 2022)

• Input the Office Space Prices data and join it to the contracts table

• Remove duplicated fields

• Create a row for each month that a rental contract will be live

  - Retain the details for each of the contracts in the new rows

• Edit 14/04/2022: Be careful at this point that the number of rows for each Office ID is equal to the Contract length

• Calculate the cumulative monthly cost of each office space contract

  - Remember we only have one contract per company

• This will create our first output

• Create a table that details total rent paid for completed years across all contracts and year to date figures for the current year, which would update as time goes on

• This will create our second output

Outputs:

Cumulative Cost:

• 15 fields

• Cumulative Monthly Cost

• ID

• Country

• City

• Address

• Company

• Office Size

• Contract Start

• Contract End

• Contract Length

• Months Until Expiry

• People

• Per Person

• Rent per Month

• Month Divider

• 792 rows

EoY and Current Table:

• 2 fields

• Year

  EoY and Current

• 6 rows

You can download the full outputs here. 

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @tSmithyyy, @Datajedininja, @JennyMartinDS14 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!