I’m trying to show our budget (both past and future) and I’m struggling. All of the budget examples I found have a single date for their payments while I have a start and expiration date for scheduled on-going payments.
I’m attaching a sample of my data. I work in real estate and I have rent streams for each of our properties. A rent stream consists of a few key elements including (per the columns in the
attached .xls file)
- The site unique ID
- The amount of the payment
- The reason for the payment
- The frequency of the payment (0 is a onetime payment on the commencement date, 1 is a monthly payment, 12 is an annualpayment - the frequency represents the number of months in the period)
- When we started making the payments (commencement date)
- When the payment are scheduled to end (expiration date)
Per my attached tableau workbook I have been able to graph my data at the year level, but it’s not quite right on 2 accounts:
- The results per year are based on the monthly cost of the rent steams in that year… however, I don’t know how to take month timing into account. For example, I have a rent streams on CO001499 starting on 11/1/2007 totaling $2,660 per month. I would like to show that in 2007 we paid $5,320 (November and December at $2,660 each). I don’t know how to make this adjustment without impacting the whole rent stream (via tableau’s “Running_Sum” for future years (eg: all of those rent streams are active in 2008 so we paid $31,920 that year - $2,600 * 12 months). This same issue is in reverse for when leases expire.
- I’m not quite sure how to deal with frequency… I think it needs to fold into the same equation as will be used for monthly payments
I had considered using these formulas to come up with a monthly sum. But again, these recalculate the whole rent stream and thus impact each of the years (not just the start / stop year).
For when leases start: ([Amount]/[Frequency])*(13-month([Commencement]))
For when leases end: ([Amount]/[Frequency])*month([Expiration])
FYI: I modeling my work on this article…
Any help would be greatly appreciated.