Now that we're done with the top portion comes the bottom half. We'll start with the pie chart.


1. First you will need to get the total number of families in homes. You can get this by going into stats and looking up families in homes. If you hover over those dots it will give you a number.


2. Take the number towards the end of the quarter you're working off of. For us, we're doing Q2 so it would be the end of June which is 435 families in homes.


3. Next you will find the percentage of families current and the number of families current for this quarter. You can find this by typing "current".



4. Normally there wouldn't be huge gaps missing, skipping over months without data, as seen with the number of families current, so we will manually find the average of families current by adding the percentages of families current from the beginning of your quarter to the end, then dividing by how many you totaled to get the average. If you click on the stat it will put you in list view and you can see the dates and percentage number.


For example, we're doing Q2 so take the first data input from April and add the percentages up until the last one in June, then divide by the number of dates you counted.


  • 83.16+79.52+82.23+75.72+81.31+77.14+78.90+71.28+75.13+74.74+72.43+79.10=930.66


  • 930.66/12=77.5


Now you can use this number for the tear sheet, and also to find out the number of families current.


  • (435*77.5)/100=337.3 which means 337 are current.

 

5. Now you need the total of families 90+ days which you can find by looking up "90"



6. Again, normally there wouldn't be huge gaps missing, so because of this, we'll use a base number of 30, which is about the average. Normally, you'd find the average the same way and use that number. Now you take this number and turn it into a percentage by doing 30 which are 90 days+, multiplied by 100, divided by the number of families total which is 435. You get 6.9%, and you can once again input it into the tear sheet on Adobe Illustrator.


7. Lastly, to find the percentage of families under 90 days, you add both percentages. 


  • 6.9%+77.5%=84.4% then take 100%-84.4%=15.6% which is used for the percentage of families under 90 days.


8. Now we are going to find our best sellers. 

To do this: 

  • Go to trusts
  • Click the x on "Property Status" 
  • Then put it in "Pivot view".


9. Next: 

  • Go to group by 
  • Add custom group "State" 
  • Then click apply 
  • Now click count and the top 3 properties are what you'll put for it on Adobe Illustrator, making the state with more properties the bigger bar.




10. Now we are going to find the AVG. MONTHLY PAYMENT. For this, we will be going on Power BI.


11. Go to the "Page 5" tab and click on an empty part of this chart:



12. Under the fields category: 

  • Click "Loans Extra Pluss Address Matrix"
  • Then "Note Payment" 
  • Then under values, click "Note Payment" then, "Average"
  • Then your final result will look like this: 



13. Now, this may be lower than the actual number, this is due to certain properties being super low that we made exceptions for. So we are going to click under Values, "Count of City" and change it to don't summarize. Click on "Average of Note Payment" on the chart to make it so it's going highest to lowest.



14. Now under "Fields" Click on "Investor" then "Investor". Now, scroll down until you reach the bottom that has numbers.



15. Now under "Filters" under "Investor" click "Require single selection" so it's off then click "Select all". With that done you will deselect these investors: "Sero Sed Serio LLC" "KJAN CORP".


16. Now under "Fields" then "Matrix" click and drag "Purchase Date" to the Filters column. Now click on it, select "Advanced filtering" "is after" then choose the beginning of the month at the end of the quarter of last year. So since we're doing Q2 2021, we would filter it for June 1st, 2020. Now your final number will be located and more accurate.



17. Now input it into Adobe Illustrator and then you're finished, Done!!!