Portfolio Allocation Model

hi I'm Bo and I created this video in order to share a spreadsheet that I made this spreadsheet helps me manage different portfolios I work on whether my own or for my family members we have different levels of risk aversion before I going further I want to tell you that asset allocation is not my top priority when it comes to investing first and foremost I am a business investor I want to find strong businesses and hold them as long as possible like Warren Buffett said I'm looking for great businesses and fair prices this spreadsheet helps me look at asset allocation in three different ways the first is asset type I got this from Edward Jones how they run their clients portfolios just break down my my assets a little bit differently we've got cash I have it saved for a rainy day if we have another 10% correction we'll have ability to put some new money to work right away well not to wait for a next paycheck income debt securities pretty straightforward there Russ - these asset types are common stocks blue-chip are companies that are mature they have slower growth but they're still growing and they have an attractive dividend so big companies in mature industries Johnson & Johnson Procter & Gamble coca-cola growth companies companies that are still growing at a fast pace they might be paying a dividend that might not be but they're growing faster than the blue chips they have a solid brand and a sustainable advantage in their in their sector or against their competitors aggressive companies are those that are emerging businesses or have emerging technology they are experiencing significant growth in their revenues and profits if they have them finally we have the resource utility and real estate companies or funds these businesses or funds are those that are investing in real assets hard assets all these businesses will be those that are less susceptible to increased rates of inflation now the next way I use this spreadsheet to analyze a portfolios allocation is by looking at the sector that a common stock would be and I've got them broken down by super sectors cyclical sensitive and defensive and then also within those super sectors as well and that is all determined by Morningstar spreadsheet also analyzes the portfolio based on market capitalization pretty straightforward big companies all the way on down now what makes this spreadsheet work huh it's a heck of a lot of lookup functions and nested-if functions I've got a few well-placed macros a little conditional formatting to help me read it a little bit better and find positions that are overweight or severely underweight filters also help me find if things and they're also a few of quite a few some if counting concatenate functions so to import data I go to this page and I've got one portfolio in here ready but I'll enter a different one for this example and so run that mackerel I like to keep these portfolios in Google Finance because it's lets me track them on a daily basis this is really easy for me to do because I'm usually logged into Gmail on my personal computer so I can go in see how the portfolio is doing see if any any company is moving so that I can download the spreadsheet here's our data output real easy just copy and paste there on this page I pull this data from the company to add a page in the Morningstar data page company data page is where I classify each company or holding by asset classification and market cap classification I pull the sector information from the Morningstar data page I bring it over to this model page where I can view it a little bit easier column and B will give me a broad overview of what we're trying to do target portfolio so the portfolio target got that set up for me right now but this is my brother's portfolio I've got these all set here we want BJ this is BJ so pull that down and you can see that we changed the target right there you see the portfolio size needs to be changed as well I just round this and I also use that if I want to make an adjustment what I failed to mention earlier was this spreadsheet it's also very useful if I want to project how New Orleans will impact the portfolio and the breakdown and allocation of each criterion so if I'm just going to give this portfolio a check up we can say ok the portfolio's currently valued at forty six six so also after I never entered forty six six there that will impact the target weights for each classification and also the weights that would then be targeted for each holding this just gives me a quick count of the number of holdings in the portfolio including cash and if that forty six sixths was spread out evenly across all forty five positions including cash we'd get a little bit over a thousand dollars so that's just a benchmark or a reference point for me equal weighting is also how I allocate funds across each holding within a asset class it's something that gives me a benchmark so if I'm bullish on Disney and I want to hold twice equal weight position in it that's fine it just gives me a point of reference there's a little return calculation there we've got a cost basis and the value and then the yield as well this is going to come into play when we look at my father's portfolio about how much are we going to be making so the projected you but then the target yield is how much we'd be making if every holding was at the target value to analyze this portfolio by asset classification we look at these cells here are the asset classifications there we've got the number of positions broken down so we know we have more positions in growth stocks than anything else here's the target here's how we are actually allocated so we see we're about ten percent over and growth stocks and we have eighteen column each will tell me how much I need to change the allocation in the portfolio to bring it within target Calma I would say hey this is how much each holding should be if you're going to be within target for the actual allocation across asset classes and then each holding be the same amount within that house of that class if I'm going to allocate 35 percent of a forty six six portfolio to growth stocks it's going to be a total of 16 to buy that by eighteen companies and you get nine hundred dollars that means that each position within the growth asset class will be about two percent of the portfolio and with that we know that the growth asset classification would be about thirty four point eight which is right on in columns end through you we have the other classifications we've got a sector breakdown and market cap it's easy to see you there what I'm looking for here is to identify any major holes or just better understand the portfolio I between two companies that I'd like to add new money to if I don't have a clear-cut favorite I can say okay I've got a clear hole in communication services or we don't have much in real estate if there are two different candidates to add as a position in the portfolio and it was between a technology stock and a real estate fund well we've got a lot of growth and technology stocks already in this portfolio I'd go with the real estate now if you're looking at these three different ways to analyze the allocation of a portfolio and you want to get dig down a little deeper I've got a few different ways I can do that say I want to look at the classification I click the sort by class classification that's a classification that is and it basically sorts column C so we've got resource on top income growth this is just an easy way to look through the portfolio they also sort by sector so here all the technology stocks real estate industrials you can also do that with market cap small cap so they're large giant cash and funds so I've got it broken down like that pretty easy to do or if you're looking for a specific company say I want to find Disney click alpha sort I don't need to think oh gosh which which way are they classified in these I can just click alpha sort will go down my ticker there's Disney I could also just isolate using a filter so we just want to look at the mid caps there we go we take out everything else all we see is the mid caps easy to do clear that puppy you have for any of these different columns we've also got conditional formatting that will tell me hey this is a significant part of the portfolio so the conditional formatting for the overall percentage of the portfolio if that's ten I want to say yeah I think I see you nine so it must be ten if it's ten percent or higher it's gonna pop up and say Oh turn yellow column M below row four fourteen and below will tell me the target allocation for each company again that's of disome benchmark they look at me the target number of shares and the amount I need to subtract from the position or add to the position to bring it within the target allocation the share adjustment as well got money that needs to change number shares need to be sold and bought comp you will return the percentage of the individual asset class that is made up by that particular holding so cash is going to be a hundred percent of cash that turns off yellow there because it's greater than twenty Amazon in this portfolio it was 25% of I think it's aggressive now I've also got conditional formatting I'm the percentage of the adjustment that needs to be made so here we don't have any cash allocated so we have to decrease the amount of cash by one hundred percent of course we're not going to do that maybe we'll change the target allocation later that's drilling down how this useful other than just giving you a check up we could say okay Brandon is going to add I don't know another fifteen hundred dollars to his portfolio so let's say it's gonna have forty eight hundred dollars we know he's got quite a bit over allocation to growth stocks and he's a bit low on aggressive stocks say we don't have any clear-cut favorite that we want to get in the portfolio well we'll just go over to this field company data we will sort by what we want to add to the portfolio so we want to add aggressive stocks these are the companies that are classified as aggressive and candidates that we would like to add to our portfolio let's just pick a few here ambarella Netflix Tesla let's say that we're gonna add these three positions we can see what that would do to the makeup of our portfolio I'll just do a little quick arithmetic oh is that your phone ringing look what happened there it is sold your house quick we're gonna have these positions to the portfolio just like that and see where those to our model now we've got three more companies there we went from nine to 12 percent and we went from 45 to 40 about 44 I don't remember what these were but we can all we can see what they are now I forgot to show you earlier was I have a pivot table set up in here it can easily show me a lot of different data and I can change what I want to look at and it just gives me graphical impression of the portfolio's allocation right now I've got the pivot table set up to have the sectors and super sectors on the rows and I've got asset class going down the columns we've got 13 growth companies let's clean this up we've got 13 aggressive companies for our consumer cyclical 6 technology 1 industrials 1 in communication services that must be a Netflix position if we'd rather look at the some of the overall percentage of the portfolio if I want to switch the asset classification to say market cap we can see that if about 15 percent of our portfolio our large cap technology stocks 18 percent technology 20 percent and consumer cyclical about 16% in large caps six of that in Giants just a real easy way to view information and better understand the portfolio I hope you enjoyed looking at this video hopefully it can give you some ideas understand the way I'm thinking about things maybe give you an idea of what you could do to make your own portfolio better I always enjoy learning and if you have any comments or constructive criticism for me I'd love to hear it please comment below I really enjoy doing this kind of analysis it gives me a better idea of the overall picture of our portfolio if I'm picking companies and I'm only looking at these specific companies I could find myself allocated completely in large technology companies and this helps me take a step back and look at the the forests and not just the trees if you like analogies thanks for watching and have a great day

Channel: Beau Buchmann

Views: 10,137

Rating: 4.9411764 out of 5

Keywords: iMovie

Id: YeWkBOhRyaE

Length: 14min 7sec (847 seconds)

Published: Mon Oct 05 2015