Hello!
To get the data you need to update a Case Study, you'll need access to the following:
PowerBI: Shows investor portfolios
Excel: Has formulas where you need to input
VTIPX: Stock info
GoogleDrive: Houses all Case Studies. You need to save everything as both a PDF and an .AI file to here.
Adobe Illustrator: Should already be installed, if not check with VP of Div2. You'll update all info into visuals here, just make a copy of the Case Study you are trying to update, rename it to (Name of CS, Quarter #, Date) and edit it.
Step 1. Update the stock.
The VTIPX (link above) is where you will find this information.
You will need to find the price of the stock per the quarter that you are working on; when on the site, hover over the last date of that month and choose the closing date.
So for the first Quarter of the year (the end of March), the closing number for VTIPX is $25.39
Next, go into the excel sheet and put that figure into the Price column.
No math needed, drag the last figure from each column down and excel will fill in the rest! Once you have the fund for each investor, copy the column and go to the investor's page, right click on the first line that has numbers under "FUND" and select special paste and then only values. For example, Copy Marion Lambert's column (H, shown in the pic above) , and paste it into the FUND column (C) on her page in the excel sheet, shown below:
Step 2. Update the REIT.
You will need to input the investor's original investment into column 1 A, shown below:
To find the Initial Investment, go to the page on the Excel sheet for the case study you are editing and look under the E&H column shown here:
Or you can look on the VTIPX page of the excel sheet and it will be the first figure under the investor name column shown in blue:
Once you have that figure, go back to the REIT page and enter the number- everything will update for you because excel has the formula. Copy and paste everything in the column over to the appropriate Investor, so if you are looking for the REIT for Marion Lambert, input her initial investment into the REIT page in excel, copy the whole column, and go back to Marion's page in excel, and paste it.
Step 3. A lot of math (the E&H column on each investor page).
Now you will use PowerBI.
At the top of the investor tab, you will see a list of properties. These are the properties you will be using to add up their earnings for the month in the particular quarter you are updating to the figure at the end of the quarter of the previous year.
Example, Marion Lambert.
Click in the empty cell for the Quarter that you are updating. In the section for formulas, type =(Cell of December of previous year)+ This is to set up what you will be adding.
Next, go into PowerBI, click on "My workspace". Find Marion Lambert, and click.
Click on the "Overview*" tab under Pages.
Make sure that the year you are updating for is correct.
Scroll down a little to see the properties the investor has in their portfolio- Marion Lambert's is easy because she only has three- you can see that those are the same three in the excel sheet but you will need to do each home for other investors by clicking on each address.
Add the number on top of the bar graph for each of the houses to the formula section that you set up earlier. *Waiting for March data, but in this example you would add in the top number in March 2022.*
Once those numbers are added, you will see the graph on the right side change.
Step 4. Cap Rate.
To find the cap rate, go into the formula section in the excel sheet and enter: =(click on the cell for the current date/year you are working on) - (click on the cell for December for the previous year) / (the cell that contains their initial investment).
So for example, the cap rate for 2021 was 12.02%.
Now you can go into Illustrator and update the .AI file!
Hints: For Granduer Capital, their props are found in 3 different places in PowerBi- look under Granduer and these two: