Gain access to exams, certificates, and exclusive content at learnitanytime. com. More information can be found in the video description below. Hi, everybody. Welcome to this lesson where we're going to look at how to create a rate of return sheet inside Microsoft Excel. And first of all, what is a rate of return? It's going to allow investors and business owners to assess the success of or failure of an investment by quantifying the percentage gain or loss over a specific period of time..
So it provides a standardized metric for comparison across multiple investments and asset classes. The idea is that when you compare the expected or historical rates of return, you can make informed choices about where to allocate your capital in a business. Now for us, we're going to be looking at how to calculate single period return, or what's called HPR, holding period return. And again, this is an acronym you will see. The idea with HPR is it calculates an investment's total return over a period of time, like a day, a month, or a quarter, or a year. We're going to be doing a year's worth, a couple of years, actually based on month..
Now for this, you need some historic data. A great place to go to get that is from Google Finance. That's where I will get my data from for the next part of this lesson. If you don't want to get your data from Google Finance, feel free to just use the provided data that's available inside, again, the exercise files. If you open up the file called rates calculator, number one, it will provide you with the data. But the thing we're going to do next is actually go to the finance Yahoo site and show you how to download data of your own for really any kind of company that has stock available..
So let's go ahead and do that next. I've traveled to finance.yahoo.com. You can do this in any browser, and now I'm gonna come up to the search bar and actually look for either the stock initials or again, the name of a company. We're gonna use Disney because it's pretty safe. You'll see right here that you can actually see the stock symbols or abbreviations, and then you can actually select the one that you want. This will take you to a page specifically about that particular organization's finance data. What I'm going to do after I travel to that is come to the headings at.
We're going to go ahead and do monthly for hours, and then we're going to click on apply. This will download the data, and again, when it downloads it, it's going to put it into a CSV file format. All I need to do is select that download file, and it will open it up directly in Microsoft Excel. And that's what we're going to see next. Now again, if you don't want to do this, go into the exercise files, and just get the file called Rates Calculator 1. It will provide some data for you to use..
Hi, everyone. In this lesson, we're going to look at how to actually get the data formatted to prepare it for our HPR calculation. Now, I've imported my data from the Yahoo Finance site. You'll see that it comes in columns. The two data points that I really need to calculate my HPR calculation. holding period return are going to be date and adjusted close. And you'll see that this does include a close column. The reason I'll use adjusted close instead is because it includes split and also dividends in the adjusted close price. All the other columns I'm going to go ahead and delete. So I'm going to come in and just delete those columns out of.
Microsoft Excel because we do not need them to calculate our HPR. Then I'm going to go ahead and actually create a column to hold the HPR calculation. In addition to that, I actually want to bring in the other information that I'll need to actually calculate my HPR by creating a little summary area. This will include my average mean return and my geometric mean return, and we'll be using two functions in Excel to do that with. Now, as you're doing these formatting changes, if you would like a prepared sheet where this is already done for you, feel free to.
Go in and open up the exercise file called rates calculator number two. In the meantime, please remember that you know how to format a finance sheet. So feel free to do that inside of this one and then get ready to do the next part of the activity where we're actually going to calculate the HPR. So I'm ready to actually calculate my HPR, and I finished reformatting the spreadsheet. In my HPR column, I'm going to come to the second, again, set of data for the second month, and I'm going to go ahead and type in my HPR calculation..
Now what this calculation does is it takes the ending value minus the beginning value, and then divide it by the beginning value again. So it's a little bit different, but it works. I'm going to do an equal sign and put the first part of the calculation in parentheses because I need it to happen first. So again, I'm going to take the ending value and I'm going to subtract it from the beginning value and close that in parentheses. And then I'm going to use the backslash to divide that by the beginning value again. This will calculate, again, my HPR. Then what I'm going to do is take that and use the fill handle.
Posts Related:
- New Logo Mockup PSD Files Free Download For Photoshop Sheri Sk Photoshop Tutorial
- Create This Amazing Contact Manager In Excel To Manage Leads & Prospects FREE DOWNLOAD
- How to Design Raffle Ticket in MS Word Cash Prize Raffle Draw Office
To drag it down to the bottom. Now at this point you're going to see a lot of decimals. This is another great keyboard shortcut that you can use inside of Excel.
If you do Shift Control the 5 key on your keyboard, it will actually take those, and if you highlight them, it will format them as parentheses. So again, Shift, Control, Shift, and then the number 5. Thanks. This will take them and turn them into, again, a percentage rather than a decimal. And it makes it a little bit easier to understand what's happening in the spreadsheet at this point..The next thing we need to do is figure out the average mean of return for all the values that we just calculated the HPR for. Very simple function, used in Excel all the time. I'm going to click next to the average mean return, and remember, if you want to just have a practice file to use, open up rates calculator number 3. I'm going to come in and click in cell G2 and start my AVERAGE function. I just need to type in an equal sign and then go ahead and use the AVERAGE function that's available right here. Again, the formula calculate list. Then I'm going to go ahead and click in the first HPR calculation and I can.
Actually do another keyboard shortcut right here, which is CTRL SHIFT DOWN ARROW to include the entire range of. I don't need the closing parenthesis, I can just hit enter, and it completes the current average based on those values. Always remember that even though it's a percentage here, if you want to include a couple of extra decimals, you can go up and use the increase decimal depending on the formatting. Now the next thing we need to do is calculate the geometric mean return. This is another Excel function that's built into the function library of Excel that we'll use to calculate this..
Next thing we want to do is make sure that that percentage for our average mean includes decimals. Couple different ways to do this, but an easy one is to right click on our average, go down to format cells and in the number tab, just make sure to add two decimal places. This will give you a little bit more of an average read because remember that it rounds things if you don't have decimals. Now the final step we need to do is to calculate our geometric mean return. Now quickly, what is a geometric mean? So the geometric mean is going to be an array or range of positive data..
You can use GeoMean to calculate average growth rate given a compound interest with variable rates. It works great for what we're trying to do. It is an array formula, so it's best used with Excel 2016 or higher. Keep that in mind, but there are keyboard shortcuts that will allow you to use it in older versions of Excel. The first thing you need to do is click in the cell that will put the geometric mean return in. I'll click there and type my equal sign and then start typing in the word Geo. You'll see again Microsoft Excel's formula autocomplete list come up. GeoLean is one of the formulas there..
I'll double click in it. The next thing I need to do is include my range of values that I'll be finding the GeoLean for. So I'm going to come in and go ahead and click in cell C3. And then do control shift down arrow to get all the values selected. Now, because this is an array formula, we're also going to include a plus one to the end of that formula so that it will calculate correctly. And then at the end, we're going to subtract the whole thing by one. Then we'll hit the enter button. Now again, you'll see that it's formatted as a decimal. So what I can do here to help again, get it to be formatted correctly is.
Come up, click on the percentage button. And then again, if I want to go in and format the cells to include two decimal places, this will give me again, a more Easy to read geometric mean of return, and this, my friends, is how you can actually create your own rates of return calculator inside Microsoft Excel. Please remember for this final activity, if you want to use the course or exercise file called rates calculator number three, you can use it to help you get a little bit further ahead on the practice activity..
Hi, everyone. Welcome back. We are ready to start building our own three statement financial model from scratch. Now, first of all, what is the three statement financial model? And why is it so important? It allows organizations to forecast and it's based on three core elements, income statements, balance sheets, and cashflow statements. Now, all of these require that you gather data ahead of time before performing any of your financial modeling. And there are several steps to building a three statement financial model. That's what we're going to be doing over the next..
Several lessons. Now with each of these three different financial models, we want to talk about them briefly before we start creating them. The first one we're going to look at is called the income statement. Sometimes it's called the PNL or profit and loss statement. The income statement shows the revenue a company earns and the expenses involved in its operating activities. So it's basically the difference between revenue and expenses, and it represents the company's net profit for a specific period of time. Another one of the financial model. Statements that will be creating is the balance sheet..
Now, the balance sheet is usually the first type of statement that people create, but it provides a snapshot of the company's financial
Position at a given point in time. It shows the company's assets, liabilities, and the shareholders equity. The third statement that we're going to be looking at is called the cash flow statement. And it basically shows how much cash enters and leaves your business over a set period of time. It begins with net income and then it goes to net statement and subtracts any non cash expenses. So go ahead, open up the practice file we'll be using for this..It's called the three statement model part two. It's in the exercise files and we're going to get started with building our first income statement. Hi, everyone. We're ready to start building the first portion of our three statement financial model, which is the income statement. Now, there's a practice file you can use for this that will save you a tremendous amount of time. It is called three statement model dash part two. Feel free to use it because there are a lot of informational pieces that you need to build your income statement, and this will already be provided for you. When you open it up, you'll see that we're building our income statement.
Based on a mail order cookie company. Basically, you put an order in and you get your cookies through the mail. So let's look at the assumptions that I've created so that we can get started with our income statement. As you come in, you'll notice that I have it broken down into three three major categories. RAT revenue is in cell A6 and then we have COGS or cost of goods sold in A12 and then operating expenses. And then these are broken down into four different years. Now, anything that I've assumed is showing up in a bright blue in the cells, this is common formatting and financial modeling that you put any assumptions that aren't.
Formula based that are hard coded in cells, you put those in a bright blue. So you'll see that under revenue, I have my first category of new customers. Then I have my average order value, then refunds as a percent of revenue and discounts, and we can see those amounts for each of the four years on the right under cost of goods sold, we have our materials cost to create the cookies. Then fulfillment cost. Merchant services would be things like bank. And credit card processing fees coming down. We have operating expenses. We have HR, so personnel costs, marketing costs, miscellaneous..
You always need a little bit of cushion. And then we have depreciation and interest on any debt in the organization. These two are marked as other model. And that's. That's important because we're not going to calculate them quite yet. The very final item at the bottom down in cell A25 is our tax rate. What we've done here is just assumed an average tax rate that is common in the US right now, which is about 22%. We will be using this information in the next lesson to help us actually begin calculating the income statement. Welcome back..
We're ready to start creating and calculating our income statements. Now I'm in a new practice file. This one is called three statement model hyphen part three. Again, feel free to use it to help save yourself time at the top. You'll notice there are some new inserted rows, and then also we've added some new categories that we'll use as we begin calculating the actual income statement. All right, we need to actually calculate our gross revenue. The next part of the income statement is pretty fun because we're just going to be using some of our good old fashioned Microsoft Excel basic formulas for gross price..
We need our gross revenue. We need to go in and take the number of customers or new customers times our average order value. Very simple multiplication application. So when you hit my equal sign and go down and take my price. First new customers, which is sell in this case, be 16. We're going to use the asterisks and multiply that by the average order value. And then we'll go ahead and hit enter. Now this is actually a currency. So after I do that, I'm going to go ahead and come up and format it as a currency. And then I can actually drag this over. To the right using the fill handle and remember if you get.
The number signs inside of the cells, just use the double click. You can widen out all the columns or you could select all the columns and double click and they would widen out next. We're ready to calculate our refunds. Now. This is basically just going to be our gross revenue. multiplied by our refunds down in cell A18, which is 5%. But a couple of changes I want to make briefly. I'm going to come into my gross revenue and remove the extra decimal places that are there. We actually don't need them. Then I'm going to click in cell B7 and start my refunds calculation..
Again, this is a very simple multiplication problem. We're going to do a couple tweaks to it. The first thing I'm going to do is hit my equal sign, and then I'm going to come up and click on my gross revenue, which is in cell B6, but I'm going to make it negative. So it calculates correctly. Now, the other thing I'm going to do that will make this a lot easier is I'm going to make the six fixed reference by putting in a dollar sign in front of it. And again, I can either do this with F4 or I can just type it in. Then I'm going to do my asterisks and come in, and this is going to be down in again, my refunds amount, which is the 5%. We can see that cell B18 very simple formula..
I'm going to hit enter. It calculates. Notice if I don't want the decimals in it, I can remove the decimals up here again, but I'm going to go ahead and drag this amount over to the right. Now, the thing I want you to see is that as I drag this, because I put the dollar sign in front of the row, it means that row is fixed. So I'm going to drag it over and I can also drag it down as well. And this is going to allow me to copy the formula and save a significant amount of time. But again, what is the value of the dollar sign in front of the six? It's a fixed reference. So what that means is no matter where I drag the formula, it will always be.
Fixed on row six, which is going to be again, the amount that's my gross revenue. The next thing that we're going to look at is calculating our net revenue. Good news. Next thing is easy. Net revenue is just a sum function. So I am going to come in, do an equal sign. But before I do that, I'm also going to come up to my refunds and discounts and remove the extra decimals again. We just don't need them. So we're going to come in to cell B9, do an equal sign, start our sum function..
You can either type it in or use your formula auto complete. I'll just be summing B6 through B8. And then we're going to come in and hit enter. Again, this is a formula that I can drag over. And while I'm doing that, I also would like to add a top border so that they can see again where the net revenue starts. So I'm just going to click on the borders button and go ahead and just add a top border so that they can see that. The next thing that we're going to do is get ready to calculate our cost of goods sold amount. Welcome back..
You should be in a new practice file. This one is called three statement model Four. And again, I filled in an additional few rows at the top where we're going to start calculating the cost of goods sold or the COGS amounts for our financial sheet. So, or our income statement. So we're going to start right here by clicking in cell B12 to calculate our product cost. For this, we're going to again do a simple multiplication problem. We're going to do an equal sign. We want to take our net revenue, not gross revenue. So I'm going to go up and click on it..
But I also want to do another absolute reference on the row and a great keyboard shortcut to do absolute references or fixed references is F4. Notice if I hit it a couple of times, you'll see that it toggles between just doing the absolute reference on the column. or just on the row or on both, and we need it on the row. This will allow that row to again, be fixed in place. Then I'm going to come in and do an asterisk, and I'm going to multiply this down below under my assumptions for my product percentage. This is again, the percentage of my materials cost, and I'm going to hit enter. And once I get this calculated, I can copy this formula..
And the great thing about that formula, Fixed reference again on row nine is that when I copy the formula, it allows me to copy it over and copy it all the way down. And I'm using the fill handle to do this again. You can also use other keyboard shortcuts, but it really helps to simplify your calculating inside the spreadsheet. Now that I've got those calculations done, I want to add a bottom border. So I'm just going to highlight the cells. Do remember you can use keyboard shortcuts for this, but to keep things simple, we're just going to go up and use the borders button..
I'll be adding a bottom border, but anytime you're using the mouse, you are using precious time. And that's why keyboard shortcuts can save a tremendous amount of time when you're doing any kind of financial modeling. Okay. Now we need to sum our COGS accounts. So we're going to come in and do a good old sum function. Again, I'm going to click in cell B15 and. If you want, you can go up and use your auto sum button. Remember, it's on the home ribbon tab. You just go up to the editing group, double check the formula that it's correct. And then of course we can use the fill handle to drag it over..
And now we've completed the sum of our COGS accounts. The next thing we're going to do is continue building again, our income statement. Don't forget, we need to actually say what this total is. We're just going to come in to cell 815 and say, this is our total COGS. And again, I'm going to bold this just so that we can see that it's a total amount inside our income statement. Hi, let's continue working with our income statement. We're ready to calculate gross margin. Gross margin is pretty easy. I'm in the practice file called three statement model hyphen part five..
So please use it to follow along. We're going to be clicking in cell B17 to calculate our gross margin. All we need to do here is do an equal sign. Now, gross margin is basically your revenue minus your cost of goods sold. So I'm going to go up and click in cell B9 and subtract that using the hyphen from my total COGS or cost. Cost of goods sold, and that will calculate my gross margin. After that, we're going to go ahead and do a percent of gross margin. This is basically just going to be our gross margin divided by net revenue..
So to calculate our gross margin percent, we're going to take gross margin and divide it by net revenue. The reason this is important, it helps us to know what percent at the product level we're making profit in terms of our revenue. So to do this, I'm just going to click in sell B18. I'm going to do an equal sign. And again, I'm going to take my gross margin and use the forward slash to divide that by my net revenue. And we'll see that it calculates at a percent. And again, if it doesn't format as a percent, go up and click on the percent symbol..
The other thing I'm going to do is go up to my gross margin and just put a top and bottom border on this. And if you want to get fancy, you can do the top with the double bottom border, totally up to you. But again, this is something that I can come in and I'm going to copy this over to the right. with the formula. Notice this will also include the formatting. And then I'll do the same thing with my gross margin percent as well. Now we're ready to calculate our operating expenses. So what we want to do is start with our cost for HR or personnel expenses. I'm clicking in cell B21 for this..
Now, what we're going to do is take net revenue and multiply it by RSI. Assumption for the percent of cost for HR. If you come down, you'll see that it's 25%. It's down in cell B43. So to do this, I'm going to go up and click in cell B21, hit my equal sign, and then I'm going to go up and click on my net revenue. And then I'm going to go ahead and multiply this using the asterisks. So we're going to go ahead and make sure we get all these amounts correct. So again, equal sign, go up, click in your net revenue, then use your asterisks to get the assumption amount. I'm going to come down to my operating expenses and click on the.
Assumed percentage for our HR costs, which is going to be about 25%. Now, we have to remember that to make this easy in our model, I'm going to put the dollar sign in front of the row so that when I copy the formula, it will do as it's already done, make it a lot easier so I don't have to redo this formula for the other operating expenses. Now, ways to do that, we can hand type it in or we can use the F4 key again. Just remember that you only need the fixed reference or absolute reference on your row. Then we'll hit enter. Now, again, this is one of those formulas that you can copy. So either using your keyboard or your, uh, formula autocomplete,.
You can use the fill handle to drag it over and then drag it down. And notice again, it's going to be formatted as a currency. The next thing that we're going to do is talk about depreciation. So now it's time to talk depreciation. Now, the interesting thing about depreciation is it represents the expenses of things that have been purchased prior to the time that your income statement's being made. It's calculated using the balance sheet and the CapEx schedules, and we actually haven't calculated those yet. So for the time being, we're not going to work with depreciation yet..
So what I'm going to do to represent this is I'm just going to highlight the cells where my depreciation will go. Those are going to be cells B24 through E24. Put them in a light yellow so that later on I can come back and we'll again follow up with depreciation once we get our balance sheet and CAPEX schedules set up. Hi everyone. We're ready to calculate our total OPEX or operating expenses, our operating income and talk about interest. So for this lesson, I'm in the three part model hyphen part six.
Lesson file or exercise file. Feel free to open it up to use it. We're going to go ahead and start by clicking in cell B25 to calculate our total OPEX. This is a simple sum function. Again, it won't be quite accurate because we don't have depreciation calculated yet, but we'll have it all set up so that once we do have depreciation put in, it will be correct. All I'm going to do is use my fill handle to drag this over to the right to get my total OPEX for each of those four years. Now we want to come down and do operating income. So operating income will not be correct because we're still missing.
Depreciation as has already been mentioned, but we'll get it later on. Operating income is going to be your gross margin subtracted by your total opex. So I'm gonna do an equal sign, go up and click and sell B 17 again. Do a hyphen and subtract that from my total opex, which to start with will be sell B 25. Then I'm gonna go ahead and again drag this over to the. And remember, interest is basically our debt and we don't have this calculated yet. So just like it did with depreciation, because this will be coming from another.
Model, maybe right here, I'll put that in parentheses just so I don't forget that I still need to get it filled in. This will just be my reminder that this is something that I'll come back and get later. Again, I won't normally have this in bold, but for right now we'll keep it in bold so I don't forget. And we'll go ahead and highlight it in the same color as I did with my depreciation. In fact, this is a great chance to use my format paintbrush. As a reminder that we'll come back and get it once we get our balance sheet and capex calculated. All right, we're ready to take on our NI or net interest before taxes..
Make sure that you're in the practice file three statement model hyphen part seven for this activity. If you're following along with me in the practice files. I'm going to click in cell B31 and exactly why is net interest before tax is important? We got to remember that you actually pay taxes on net income after interest. So it's an incentive to take on more debt. And remember that interest is a protection from taxes in its own way. So to calculate this, we're going to take and take our operating income and we're going to subtract it from our interest..
So I'm clicking in cell B31, I'm going to hit my equal sign, and then I'm going to go up to my operating income, and I'm going to subtract that from my interest. Now we haven't yet calculated our interest, so again, it won't be working yet, but it will once we complete the rest of our balance sheet and our capex. So this is something that we'll come back to later. I'm going to copy that formula over, and then we're going to get ready to do our next segment of the income statement. Take care. All right, now we're ready to actually calculate our again, taxes. Remembering that because some of the things are not quite complete yet, our taxes will be more correctly calculated later. But I'm going to come into the spreadsheet and click in cell B33..
