"Excel for Finance and Accounting Full Course Tutorial (3+ Hours)" - Transcript | YTScribe

[Music] welcome to learn it training the exercise files for today's course are located in the video description below don't forget to like And subscribe hi everyone welcome to learn its Excel for Finance and Accounting part one course my name is Elissa Smith and I'm an IT facilitator with over 25 years of experience teaching professionals smart ways to use Microsoft Excel during this course we're going to show you everything from the basics up to very Advan adved Concepts on how to make Microsoft Excel your best tool when it comes to Finance and Accounting in this course

we're going to explore everything from the basics of data entry in Microsoft Excel to using conditional formatting to help make values pop out we'll also be exploring basic formulas and even more advanced formulas like vlookup and if statements we will also spend time in this course looking at how to use Financial formulas like the ppmt and the ipmt formulas to help you as you begin your journey with financial formulas in Microsoft Excel looking to support our Channel and get a great deal become a member today to unlock ad free videos that's right your favorite courses

without a single ad interested in a specific video purchase one of our ad free courses individually looking for even more gain access to exams certific ific Ates and exclusive content at learnit anytime.com more information can be found in the video description below hi everyone in this lesson we want to overview how to navigate in Excel and get you familiar with some of the vocabulary that you'll actually see used in the platform Microsoft Excel is a data analytics program that's been around for over a quarter Century it's really the main application that people use to calculate

and analyze their data in any business setting now the first thing to know about Microsoft Excel is that everything is contained in cells hence the name Excel you'll see here that in my current spreadsheet I have these little boxes they're called cells they allow me to designate data to a specific spot within this grid system it's fantastic when you're trying to analyze and calculate data you'll see that columns are known as letters rows are known as numbers and the way you locate a specific cell is to click in it and it will be known by

its column letter and its row number now if you look up at the top of Microsoft Excel it uses the same interface as the other Microsoft Office applications like PowerPoint and word so you'll see that there are ribbons that you use to access different buttons and when it comes to navigating you click in a cell to locate the cell you want to type in you can also select rows and columns by clicking on column letters or row numbers and if you ever need to know what cell you're in you can come up and look in

the top leftand Corner you'll see again the cell name box that will tell you the name of the cell that you're currently clicked inside of you can select multiple cells this is called AR range by using your mouse or your keyboard and you can select non-adjacent ranges by selecting one group of cells then holding down your control key on your keyboard to select a different group now the other critical thing to know about a spreadsheet is it also has sheet s down in the bottom left hand corner you'll see that my current workbook has three

sheets in it the reason for sheets are to allow you to organize data by sheet it's a great way to take large amounts of data and split it up in an organizational fashion all you do is click on the sheet you want to go to and that sheet will come forward you can also insert more new sheets as well now as always if you need a practice file we have practice files for you to use during this course just click in the link in the description and it will take you to the practice files the

practice file I'm in right now is called practice one and you can use it as you explore Microsoft Excel the next thing that we'll talk about is how to do data entry in Excel so join me for the next lesson hey everyone I'm ready to look at how to do data entry inside Microsoft Excel I just have a blank workbook open and if you're trying to follow along with me feel free to open up Excel as well and do this as we talk about it now now all you need to do to begin doing data

entry in cells is click in a cell and start typing I'm going to click in cell B2 remember your cells are known by their column letter and row number when I click in cell B2 I can simply start typing as soon as I hit enter you'll notice that the data entry is complete now another way to do data entry in Excel is to use the formula bar it is used for two things creating formulas and doing data entry the formula bar is located right below the ribbons of Microsoft Excel it can be turned off but

by default it should be turned on you'll see this long white bar where you can do data entry so I've clicked in another cell C2 this time I'm going to go ahead and type in the topic that I want entered into my formula bar you'll notice that as I type that whatever I type here in the formula bar gets entered in the cell below once I hit enter you'll notice that that information gets put into the cell so I can do data entry by clicking and typing or by using my formula bar now let's chat

for just a minute about keyboard shortcuts when you're navigating inside an Excel workbook you can use your scroll bars but they can be a fairly slow way to navigate so I just want to make a few suggestions one of them is you can actually use the cell name box next to your formula bar for fast ways to navigate if I know that I need to get down to a cell quite a ways down in my workbook I could scroll but scrolling is one of the slowest ways to navigate so instead I'm going to click in

the cell name box and enter in the column letter in this case the column letter is M the row number is 500 and it doesn't matter if your column letter is uppercase or lowercase I'm going to hit enter and notice that my computer in Excel takes me all the way down to cell M500 now how do I get back to cell A1 quick I love the keyboard shortcut control home this is on a PC on an Apple computer you'll need to use the command button for this but what you're going to do is look for

the page up key on your keyboard the home button is always located right next to it hold down your control key and then your home key and it will take you back to sell A1 in any workbook this is a fast way to use your keyboard as opposed to using the scroll bars because sometimes the scrolling will take longer than using those keyboard shortcuts so just remember get a new workbook open and practice typing in cells and navigating you'll get the hang of it very quickly join us for the next lesson welcome back we're ready

to show you how to use rows and columns in your workbook to edit the data in your Excel spreadsheet I'm using a practice file right now called practice 2 please remember you can use these practice files to follow along they're available in the link in the description below the video now what I want to do is go into my workbook and I'm going to click on column F when you click on the letter for a column you'll notice that it selects everything in the column below now once everything is selected if I hit the delete

key on my keyboard notice everything in that column will get deleted now if I come up and click on the undo button remember top leftand Corner up on the toolbar the quick access toolbar it will bring it back what if if I wanted to insert a new column into my workbook I'm going to come into the column and rightclick on column G on a PC when you rightclick on a column letter you'll get a secondary menu that gives you opportunities to do things like insert new columns or delete columns please remember that if you delete

a column notice that everything including the content in the column will be deleted I'm going to undo that in this case I want to insert a new column so I'm going to rightclick by default when you insert new columns they always insert to the left so you'll see that the current column moves over the one that I had selected and I get a new blank column I'm going to go ahead and undo that rows work the exact same way in this case I want to selct more than one row all I need to do is

come in and I'm going to highlight rows 7 through 11 you'll notice that I can select all the content in those rows just by left dragging on the row numbers now that I have these selected I'm going to rightclick and say insert now I currently have several rows selected when I insert new rows they'll go above and the same number of rows that I have selected will be inserted into my workbook so you can see that by again working with rows and columns you can manipulate large amounts of data in your workbook without having to

individually select sections of the workbook with your mouse because the columns and rows allow you to do it all at the same time feel free to use the practice files to play with rows and columns in a workbook of your own welcome back everybody let's talk about printing saving and opening spreadsheets now right now I have the practice file called practice 5 open so feel free to use it as we explore these functionalities when it comes to printing I need to go up to my file ribbon to access the print option I'm going to come

to the top leftand Corner because the file ribbon is the very first one on the ribbon tabs you'll notice that when you go to the file ribbon it takes you to a part of excel called The Backstage view where the rest of your spreadsheet is covered up all the functionalities run down the left hand side print is right below save as when you click on print it takes you to a combination print task paint on the left and a print preview on the right now I love this view because everything I need to preview and

also update my print is right here you'll see that you can do things like decide the number of copies you want want to print the printer that you're printing to the portion of the workbook that you're going to print and even do things like update your orientation and I can view these changes as I make them and even update things like my margins right here inside the print dialogue box when I'm ready to print I just click on the print button and my worksheet will suddenly be available on paper do note that the default is

only to print the current sheet that you have selected now to exit print preview or the print dialogue I'm going to come to the top left hand corner and click on the back button when I'm back in my workbook you'll notice that changes that I make in print do not update the look of the workbook the only thing you may notice is that you're going to suddenly see Dash lines in your workbook representing where page breaks are and this happens after you go in and print a workbook now what about saving this workbook let's say

that I've come in and made a few changes and I'd like to make sure my workbook is saved we're going to come up to the file ribbon tab again go down to save or save as save is commonly used the first time that you save a workbook save as allows you to take an existing workbook navigate to where you're saving it give it a name and when you click on Save I'm going to type in my updated name that workbook file will be saved and you can see the new name at the top but saving

in Excel is exactly the same as saving in for example PowerPoint for a presentation or word for a Word document finally how do I open up a file well we're going to go back to the file ribbon tab again and come into open this will take you into your computer where you can see different locations where you can save and open files I'm going to browse locate the file I want to open either double click on it or select it and say open and then that file will open and I'll see its name up here

at the top as always remember if you have multiple files open a great way to switch between files is to come down to your windows taskbar locate the Excel icon and then you can switch between the different files by just clicking on them and again try this out in Excel on your own so you feel comfortable opening saving and printing files hi everybody let's talk about formatting inside Microsoft Excel now what's really important in a spread sheet is what's inside the cells these are what we sometimes call your cell contents formatting is made to highlight

or make it easier to understand the contents inside your cells right now I have practice file format One open feel free to use it to follow along as we try out these different formatting tools there are three main things we want to talk about here applying color alignment and cell borders now first of all to apply any formatting you need to select the contents of your cells in this spreadsheet I'm going to select cells B3 over to G3 most of the formatting tools will be located on the home ribbon tab in the font and Alignment

groups or if you rightclick you'll notice that you get a toolbar that has a lot of the same Tools in it it for this video I'm going to be using the ribbon options let's start with color I've selected my cells and the first thing I want to update is the color inside the cells this is what we refer to as font color for this I'll go to the font group and look for the capital letter A on the bottom row and I'll see that when I click on the Arrow next to it there are different

colors I can apply to the writing of my cell it's really important that when you apply font color you ensure that the color you select is still going to allow people to easily read the contents of the cells what about the background color of the cell this is what we call fill color for this I'm going to highlight A4 down to a13 and directly next to the font color button you'll see a paint cam this is called fill color this allows you to update the color inside the cells again making sure that whatever fill color

you pick does not make it difficult to read the text inside the cells now alignment has everything to do with how things are aligned in the cells for this I actually want to go between rows four and five and make the row taller the reason I want to do this and notice that I'm hovering over row four and five my mouse pointer turns into an arrow that points up and down and I can drag down this will make my row taller each cell is like a box so you actually have a left Center and right

alignment and also a top middle and bottom what I'm going to do is I'm going to highlight row four by just clicking on the number four remember this will select C everything in that row through the entire sheet that I'm on then I'm going to come to the alignment group on the alignment group you'll see that on the bottom I have left Center and right alignments that I can use I'm going to put the numbers in the center and then I have top middle and bottom and I can use these to align something directly in

the center of the cell now in addition to that you can also do alignments that are at a tilt for this I'm going to actually go in and make Row three taller by dragging it down then I'm going to highlight again cells B3 through G3 up on that same alignment group directly to the right hand side of the bottom alignment you'll see a lowercase A and B button with an arrow pointing up and to the right this allows you to actually tilt text inside the cells and put them at an angle it's kind of fun

but again make sure your cells or your row is tall enough and the cells are wide enough for this to happen finally cell borders now it looks like I have borders turned on in my spreadsheet but these are actually grid lines and they don't print by default to apply borders I need to come in and highlight the cells I want to apply the borders to and then directly to the left of the paint can or fill color I'll see the borders button when I click on it it provides me with different borders that I can

apply I just select the Border I want click on it and then when I click away I'll see that border applied to the cells this is a border that we print as always try this out in one of the practice files or a workbook welcome back let's talk about formatting values or numbers inside of cells and also using one of my favorite tools to copy formats called The Format painter or the format paintbrush now right now I'm in practice file format 2 feel free to use it to follow along let's talk about formatting values or

numbers first of all you'll see that in any spreadsh sheet values are always at the right now remember that values include dates and up here in this spreadsheet in cell B2 I actually have a date and if I make the column a little bit wider we'll notice that again it aligns at the right text which means any combination of letters and values together or just letters themselves will always align at the left this is a tool to help you be able to view automatically a number as opposed to something that is not a number now

how would I for example take these values and make them look like a currency because that's what they actually are well first thing you need to do is highlight them so I'm going to highlight cells B4 to G4 then on my home ribbon tab I'm going to come to the number group now Microsoft has conveniently put a dollar sign right on the second row of this group on the ribbon when you click on it it will automatically apply a dollar sign and two decimal places to all those values again if you don't want the decimal

places notice that that on the right hand side of the group you have the ability to both increase and decrease decimals just be careful with this because if your values do have decimals and you remove them the values will round up I also want to apply this same format to my cells in B7 all the way over to G13 so again I'm going to select the values and go up and click on the dollar sign and apply a currency format to all those values in my spreadsheet now what about dates right now if I look

at B2 I've made it a little bit wider the column to see the full date right now this is called the short date format can I update this absolutely I'm going to come in and go right to the drop- down arrow next to the current field where it says date because Excel recognizes this and you'll notice that if I come in it's already showing me a short date format that I currently have but I can update this to a long date format now what's common when you do this is that you're going to see number

sign inside the cell anytime there are numbers in a cell and Microsoft Excel cannot show you all the information it just shows you number signs because it never wants to show you incomplete information a quick trick to fix this is to come right up and double click between the columns where you see those number signs and it will autofit the content so that you can see it so now I have the long date format now what if I have a format for example these cells here don't have decimal places and I'd like all the cells

below to also not have decimal places this is a great time to use the format painter or the format paintbrush the first step is to select the cells that have the format that you like by going up and clicking on the format painter then I'm going to come in and I'm going to highlight the cells and notice when I do this it makes my cell or my mouse pointer in this case look like a paintbrush I'm going to highlight the cells that I want to copy the format 2 when I release my mouse it will

have copied the formatting for me this is a tool that you can use also in Microsoft Word and PowerPoint and I use it all the time if you don't want it to turn off doubleclick on it now as always try this in a spreadsheet of your own by again updating number and date formats and also playing with the format painter welcome back let's talk about conditional formatting and merge and center right now I have practice file format 3 open feel free to use it to try this as we go first of all up in cell

A1 I'd like to merge and center that title I'm going to come in and highlight all the cells that I'd like to make into one so in this case sells A1 through G1 one of my very favorite buttons the merg and center button is on the home ribbon in the alignment group right under the wrap text or next to the wrap text button when you click on this button it takes cell A1 and merges it so all the other cells B1 through G1 become part of it and it's centers the content that was in cell

A1 it's a fantastic way to Quick quickly Center your title at the top of a workbook now the next thing that I want to do is I want to apply conditional formatting conditional formatting is a way to make things pop inside of cells with formatting I'm going to come in and highlight cells B7 all the way over to G10 basically all the expenses in the spreadsheet then to apply a conditional format for my home ribbon t tab I'm going to come to the conditional formatting button now there are different kinds of conditional formats we're going

to try out a couple the most common type is called highlight cell rules this allows you to have something formatted in your spreadsheet based on a criteria for example is something greater than less than between or equal to you can even do conditional formats that are based on text contained in a cell we're going to start with greater than I'm going to come in and say any value that is greater than 5,000 you type that in then click on okay you'll see that the conditional format turns the text and also the fill color in the

cell red now can you do multiple conditional formats you can but let's try a different one for this I'm going to go down to row 13 and highlight cells B13 over to G13 I'm going to go back up to the home ribbon tab in the Styles group and select conditional formatting this time I'm going to come down and show you a data bar this is a conditional format where based on the data inside the cells the values the color will go further over and you can see that there are different colors that you can pick

so in this case I'm picking the green gradient fill you'll see that for a cell that has a higher value the color goes further over now how do you edit a conditional format or clear it I'm going to re highlight the cells in row 13 where I applied the conditional format go back to conditional formatting and come clear to the bottom of the menu to manage rules here in this box I'll see the current conditional format that I selected if I wanted to edit it I could select it and click on edit rule to clear

a conditional format the easiest way is to highlight the part of the spreadsheet where the conditional format is located go back to the conditional formatting button and come down to clear rules just be careful that you select clear rules from selected cells because if you pick clear rules from the entire sheet we all know what's going to happen you're going to lose all your conditional formats in the workbook as always try this out it's a great way to make things pop or highlight things in cells welcome back let's talk about how we can easily see

the top of our screen with the bottom in a large workbook this practice vile I've opened is called navigate one feel free to use it to follow along now one of the challenges of excel is how big a workbook can be this particular workbook if I scroll down in this sheet that I'm on they're only about 200 rows but I would still spend a significant amount of time scrolling between the top and bottom if I'm trying to remember what my column headers are very common issue in Excel well Microsoft has two different ways to deal

with this issue the first one is to split your screen to do this we're going to go up to the view ribbon this ribbon is all about adjusting the view of your workbook sheet that you're in and come to the window group on the right hand side top of the window group you should see a button that says split now what this does is it puts these gray lines in your workbook basically what you're doing is splitting your workbook into what are like window screens there's a vertical line that you can adjust by just resting

your mouse pointer on it and dragging it to the right and left and a horizontal line as well now what you'll see now is that you have two sets of vertical scroll bars and two sets of horizontal scroll bars so what I can do here is stay at the top in this top portion of the split and scroll to the very bottom and I can also be at the far left in one of the splits and the far right in the other so I'm able to align the spreadsheet and see it in different portions if

you don't want one of the splits for example if you don't need the horizontal split you can rest your mouse pointer on it drag it down into the bottom of your screen holding down your left Mouse Button as you do it and you can basically just get rid of a split you don't want to turn the splits off you go back up to the ribbon and click on the split button and it will remove the split now I prefer tool called Freeze panes this allows you to either freeze the top row on your workbook sheet

or the top left column or pick an apex point of a cell for me I'm going to go up and click in cell C2 I'm currently at the very top of the sheet and then I'm going to go back to the view ribbon tab back to the window group and look for freeze paints there's actually a little snowflake on the button when you click on it it'll give you three different choices now in this case I'm not trying to freeze the very top row even though I am but I'm not trying to freeze the very

far left column I'm actually trying to freeze from cell C2 above and to the left of that point so I'm going to pick instead the option at the top that says freeze paints this means anything above and to the left of the cell that I have selected will be frozen in place now it's kind of hard to tell that this has happened but notice if I look at the right hand side of the spreadsheet I see a thin black line that goes above row two and also if I look at everything to the left of

column C I can see the same thing now to really see this in action you need to start scrolling down so as I scroll down I'm going to see that everything above row two stays fixed in place but the exciting part is when you start scrolling to the right because again I didn't say the First Column I said everything to the left of cell C2 so when I start scrolling column A and B stay in place but everything else is scrollable so it's like you've frozen the spreadsheet in place to turn it off go back

up to the freeze paines button again and say unfreeze pains and it turns off as always try this out in your own workbooks these are tools that you'll use every day hey welcome back let's talk about some ways to save you time inside Microsoft Excel for this I have a practice file called navigate 2 open feel free to use it as you follow along I want to start by talking about the quick access toolbar shortcuts I use these all the time and don't forget that you can customize these to add shortcuts of your own now

the quick access toolbar is actually one of the final toolbars still left in Microsoft Excel it's important because on mine this is where the undo and redo buttons are remember that if you go to the right hand side of the quick access toolbar you'll see a line with an arrow pointing down when you click on this button you'll see frequent shortcuts that you can add I'm a big fan of shortcuts like the print preview and print and the spell check also undo and redo are usually pinned to your quick access toolbar for you if you

see a shortcut that you want or I should say if you see a shortcut that's not here feel free to come down to more commands and add it all you do to add the command is left click on it and you'll see the button get added and these commands will always be here no matter what workbook file you open to remove them you just go back to the same Arrow again come in and left click on the particular command you want to take off but it's a great way to again add your own shortcuts to

excel don't forget also that the quick access toolbar can be located above the ribbons or you can come in and say show below the ribbon and in this case it'll be placed right above your formula bar I like to keep it in the top left corner that way it doesn't get hidden but it's up to you where you put it now what other options do you have we already talked about this but I highly recommend learning a few critical keyboard short CS to help you in Excel I just want to share a couple of my

favorites one of the first ones is to be able to select the entire sheet and all the data that's on it to do this I'm going to do contrl a on my keyboard and you'll notice the cell I was clicked in stays white but everything else gets highlighted another way to do this is to go between column A the letter and row number one you'll see a rectangle and this does the same thing it selects all the content in the workbook another great keyboard shortcut that we already talked about on a PC is to do

control home and remember the home button is located directly next to the page up on a Windows PC keyboard this will always take you back to cell A1 another great keyboard shortcut is control end now the End Key end D like the end of a book is located by the page down button this will take you to the last point where someone typed inside your sheet that you're on so contrl home and control end can take you between the top and the bottom of your spreadsheet one more good keyboard shortcut to know is how to

select all the data where there's typed information in a row or column I'm going to come to the top of column G and click in cell G1 now if I scroll down this goes all the way down to row 200 it takes quite a while to scroll down to it sorry because I'm making you seasick while I scroll to it but in this cell I'm now going to click and or on my keyboard I'm going to hit contrl shift down arrow control shift down arrow will select everything in that column to the last cell where

something was typed it's a fantastic shortcut that will save you a lot of time having to drag and select to unselect the content you just click the same thing will work for rows if you click in a Cell I'm going to select cell in this case A10 and then do control shift arrow to the right it selects everything inside a row so again those keyboard shortcuts are crl a control home contrl end and then control shift use your arrow keys to select all the content in a row or a column where there is typed information

try these out in a workbook of your own because these few keyboard shortcuts will save you hours of time now are there more there are many so feel free to explore more Excel keybo board shortcuts because they will save you so much time hi everyone let's take a couple minutes and review the most basic formulas or functions of excel so a formula is a type of calculation that Microsoft Excel knows how to do and there are many they're called functions now in my spreadsheet that I have open it's a practice file called formulas one I

want to review some of the most basic functions that are part of excel the first one we want to do is a sum function sum means to add in this case I want to add up cells B6 down to B9 and place the answer in cell B10 when you create a function you always start at the end so I've clicked in the cell where I want the answer to go to help me create the functions I'm going to use one of my most favorite buttons in Excel called the auto sum button now the auto sum

button is located in a couple of places but one of the most common ones is on the formulas ribbon since we're creating a formula that's a good place to go you'll see the auto sum button is the second button in from the left and it looks like a sigma now when you're just doing a sum you can actually click on the top half of the button and it automatically assumes you're creating a sum function now let's look at the syntax of the function you'll see that every function begins with an equal sign the name of

the function and then in parentheses the range of cells that are being calculated you'll always see the first cell and then a colon and the last cell you'll also see the the formula put up in the formula bar I'm going to hit enter and we'll see that it's added up the cells B6 through B9 now because this formula is complete I can click on it go to the bottom right hand corner and you want to be cautious here because you don't want your mouse pointer to look like a white plus sign or four black arrows

pointing up down left and right it needs to look like a Crosshair like you're aiming at something this is called the autofill button I'm going to hold down my left Mouse button and drag that formula from cell B10 over to G10 the fill handle copies what's in a Cell well in cell B10 I had a formula so it's copying the formula but the exciting part is that if I click in these new formulas that have been created and look at the formula bar I can see that as I've copied the formulas over they're now copying

relative to their position so they're updating to a new range of cells let's try another common formula this is an average I'm going to come in and click and sell B12 now remember to find an average you add up all the values and divide by their number it's not fun math so let's let Excel do it for us the auto sum button can also help us with averages what I'm going to do is click on the Arrow that's either under or next to the auto sum button after I've clicked in cell B12 because that's where

I want my resulting answer to go and I'll pick average the only problem we're going to see is that the range of cells is incorrect because I only want my range to be B6 through b B9 so what I can do while it still has the Box open I can highlight the correct cells and update to the correct range and hit enter and I'll see that it will now give me the average again I can now click on Cell B12 where I see my answer go to the bottom right hand corner get the Crosshair and

drag it to the right let's try a couple more common functions I now want to look at the Max function or maximum its goal is to look at a list of values and return the highest value you can also use the auto sum button for this so I'm going to click in cell B13 go up and click on the Arrow of the auto sum button and pick Max again it's probably going to select the wrong set of cells for your range so highlight B6 through B9 and then hit enter again this is one that you

can use that fill handle to drag over to sell in this case G13 now the Min function that's located in cell b14 does the opposite of the max function it finds the lowest value so again I'm going to go up and click on the Arrow next to Auto sum and this time I'll pick Min now it might select the wrong range so highlight cells B6 through B9 to correct the range and enter in your formula then go to the bottom right hand corner and drag it to the right to find the lowest value in that

range of cells the final function we want to try out is the count function this will just take a list of values or even text and tell you how many things there are so if you have four cells selected the answer will be four but it's still very commonly used in many functions so I'm going to go up again after I've clicked in cell B15 click on the Arrow next to Auto sum and this time pick count numbers then I will select the correct range which is b63 B9 and hit enter and again I had

four cells selected so the answer to the count function is four but I can copy that formula over so remember with these five basic functions what we're doing is reviewing what a function looks like what a range looks like and how you can use the Autos sum button to help you create these functions as always be careful with the Autos sum button because it's very common for it to select an incorrect range but while you're using it you can correct that range using these tools you can start introducing yourself to the basic functions of Microsoft

Excel welcome back let's talk about one of my favorite types of functions in Excel an if statement there's a practice file for this called if functions hyphen practice feel free to open it up and use it now in this spreadsheet you'll see that I have months sales reps and their totals and I need to calculate their bonus however there is a threshold they only get the bonus bonus if they made more than $7 million in sales if you look at my spreadsheet I have a few sales reps that did not make that threshold and I

only want to calculate their bonus if the threshold was met this is the perfect use case for an if statement because an if statement needs to have a logical test that can be set to true or false I'm going to click in my spreadsheet and sell O3 and to help me do my if statement I'm going to use a tool called the insert function button but you can get to the insert function Box by going up to the formula bar and on the left hand side you'll see a small FX this is the doorway to

the insert function box now the first thing I need to do is go into Microsoft excel's library of functions and locate the IF function the top portion I'm going to type in the name of the function I'm looking for on the right there is a go button that I'll click on and it will take me into the library below and find any functions that have the word if in them or close to the spelling I've used as soon as I see the function I want I'm going to select it and then come down to the

bottom of the box and click on okay so now that I've selected the IF function out of excel's function Library it shows me the three different portions of the formula that I need the first thing is a logical test well in this case my logical test is was the amount the sales total in cell N3 greater than or equal to 7 million now it's really important that I count the correct number of zeros for this because if I have too many zeros it won't calculate correctly I don't need to use dollars and cents because it

will automatically format those for me now if that's true then I need to take the sales total that's in cell N3 and multiply it by 5% you can use that decimal if you prefer here but I like percentages if that's false I don't want it to calculate a bonus and I also just want it to tell the person no bonus so anytime you use a text string inside a formula you need to come in and enter it in quotes so I'm just going to put in the words no bonus included in quotes so again my

logical test has three arguments or I should say my if statement The Logical test is their sales amount over 7 million if that's true then multiply that by 5% if it's false just put in the words no bonus in the cell I'm going to click on okay and based on my function now if you look at the formula bar you can see the function how it starts with an equal sign if and then includes the logical test is N3 greater than or equal to 7 million then a comma followed by what to do if that's

true and what to do if that's false and it looks like this person sold over $8.3 million in product so they did meet the threshold for the bonus and we see it now is this a formula that can be copied it is so I'm going to going to click in cell 03 go to the bottom right hand corner get the black Crosshair and drag it all the way from 03 down to in this case 027 and what I want you to see is anytime a sales rep had less than 7 million the formula goes to

the false portion and just puts the words no bonus in the cells these are a super fun type of function and it's very common in an if statement where you see the false portion of the formula for another entire if statement to be nested in there up to several levels so that your formulas can be very complicated hey welcome back let's try out two of my favorite functions in one and it's actually two different functions sum if and average if these are a great way to only sum or average content based on the matching criteria

that's also in the workbook now there's a practice file for this called sum if hyphen average if practice feel free to open it up and use it to follow along I'm going to start by clicking in cell K2 this is where my first function will go the sum IF function I'm also going to suggest that you use the insert function box the first time you do this formula because it does have a couple of different pieces so I'm going to go up to the Le hand side of my formula bar and click on the FX

button this will take us into the insert function box at the top I'm going to type in the name of the function I want which is Su if no words or no spaces in between the two words and I'll click on go on the right hand side make sure you pick some if and not some ifs when you select the function name double click on it and it takes us to the second portion of the insert function box which is the function arguments I'm starting with a range this is the group of cells that I'm

going to be highlighting and you'll see right here that in my case it's column e I'm not going to include cell E1 because it's a column heading to select the cells I'm going to click in cell E2 and then do the keyboard shortcut contrl shift down arrow this will select all the cells to the bottom of the spreadsheet that are in that column where my criteria is my criteria is going to be based on the fact that I only want to sum those destinations that are to Cancun Now what is my criteria my criteria is

that the destination is Cancun because it's text I have to contain it inside quotes So for my criteria in quotes I will type the word Cancun and I will make sure I spell it correctly my third line in the function arguments box is my sum range this is the column that I will be summing in this case it will be column H I'm not going to include cell H1 because again it's a column header and it's not a value so I'll click in cell H2 and on my keyboard I will select cells H2 down to

h200 using the keyboard shortcut contrl shift down arrow so here we can see the three arguments of the function my range which contains my criteria so it sells E2 to E200 and then what I'm summing which is H2 to h200 now I can already see that the formula will work because in the bottom left hand corner of the function arguments box it tells me my formula result then I'll click on okay we'll see right here that it's showing me that if I were to sum all the Cancun destinations and here I can see Cancun because

I've sorted the destination column if I come over and also select the totals in column H and then just highlight them I can see that down on my again status bar if I come over and look at the auto calculate it shows me that yes that would equal 2952 now let's do the same thing in cell M2 but rather than a sum IF function we will do an average if so I've clicked where I want the formula to go and then I'll come up and use the insert function box this time the name of the

function is average if all one word no spaces I'll click on the go button once I've located average if down in the select a function list I'll double click on it this one is very similar to the sum IF function you need the range where your criteria is located this time it's going to be Boston so again it will be the same range I used I'll use control shift down arrow to select E2 through E200 not including E1 because it's a column header now my criteria is that it's Boston so in quotes on the criteria

row I'll type Boston as the destination that I want averaged then in my average range again I'm going to average H2 down to h200 so I'm using the control shift down arrow keyboard short cut to select that range and in the bottom left hand corner I can see if the function's going to work when I click on okay you'll see that there are quite a few decimals included with this particular formula answer so I'm going to come up and just format this as a currency to get rid of some of those decimals now it looks

like my answer is $530 I can again come into the spreadsheet highlight all the Boston destinations and go over and highlight the total row as well and then just highlight the ones that will be for Boston if I come in and look at the average for that group of cells you'll see that down on the status bar it rounds it up to 531 but that is very close to 530 because my status bar doesn't do decimals these are two fantastic functions where you can only sum based on criteria or average based on criteria and they

combine some of our very favorite functions sum if and average howdy I want to show you some of my favorite functions for reforming data in a cell when you need to take just a portion of information in a cell to use somewhere else these functions are called Left Right and mid and there's a practice file with that same name left hyphen right hyen mid that you can use to follow along now I'm going to click in cell D2 you'll notice that to the left I have a customer location and code it's the first four characters

of that cell that I need to put over in cell d too and the left function is a great way to do this to start I'm going to hit an equal sign and type the word left now you'll see that as soon as I come in and double click on the formula auto complete it tells me that I need two things I need the cell with the characters in it which is cell C2 then a comma and the number of characters it's four and then I don't need that closing parentheses I can just hit enter

and we'll see it's captured the first four characters in this case it's a code that give me the customer location code now this is a function so if I click on Cell D2 go to the bottom right hand corner and get that black Crosshair I can drag it down and you'll see that it brings the customer location code in for me and that's the left function now the right function does the same thing but rather from the far left side of the cell it does the right so I'm going to go to column F this

time to the office code cell you'll see that in column E I have office names but I also have a code at the end of each of those so here I'm going to type in an equal sign in cell F2 and type in the name of the function which is write again you'll notice the same thing comes up with the formula autocomplete I'm going to double click on it to get my opening parentheses and here underneath you're seeing that it's telling me again I need to provide the cell that has the text in it that

I need to return characters from so I'm going to click in cell E2 and then I need to do a comma and tell it the number of characters from the right hand side of the cell that I want to return it's four we don't need that closing parentheses because there's only one step set so I'll hit enter and you'll see in this case it's returned the four digits from the right hand side of the cell which is 13 43 this is again a function so if I click in cell F2 come to the bottom right

hand corner get the black Crosshair I can drag it down now the final function I want to show you is called the mid function for this one I'm going to go over to column L under customer rate now in column okay I have some pretty complicated decimal places that represent the customer's satisfaction rate I want to simplify that so what I'm going to do is I'm going to click and cell L2 and use the mid function this function we're just going to type in mid after the equal sign allows you to select a cell and

it can contain values or characters either one you need to give it a little bit more information on this so I'm now going to go up and actually select the FX button so that you can see the two additional ARG arguments we need to tell it where the characters need to begin for what we're doing I want to capture stuff after the decimal place so I'm going to tell it as soon as the decimal place starts after two characters in I'd like it to return three characters for me and then I'll click on okay and

you'll see based on that it's doing exactly what I said go to the second character in and return three characters after that point this is also a function so I'll drag it down and now I'm getting a little bit easier information about what the customer satisfaction rate is with fewer decimal places now these are three functions but they are all again functions I want to show you another tool that actually works a little bit faster and can accomplish some of the same things I'm going to drag over to the right in column B I have

my customer names now what if I just wanted the first name of the customer and not the last name with it I'm going to right click on column C and insert a new column that I'm just going to call first for the first name we'll call it first name then underneath I'm going to type in the first name that I see next to again in cell B2 which is Antonio now I need to create a pattern here for Excel to understand this is using a tool called flashfill it's also important that you spell it exactly

as it appears in the cells to the left now it looks like right now my computer is not picking up on this pattern so to force it to recognize the pattern I have two options I can do CR e on my keyboard or if I go up to the data ribbon Tab and come to the data tools group I can also click on the lightning bolt button which is called Flash Fill and this helps Excel to recognize the pattern that I'm asking it to take out the first names from each of the cells to the

left and put them in column C for me so that I don't have to use a function to accomplish this so this will allow you to combine information and also separate it as I've just showed you try this out because sometimes it's faster than trying to use a function welcome back let's talk about the count function and how you can combine it with the IF function to make it really useful the count function by itself is probably the simplest function in Excel it does basically what it sounds like it counts how many of something are

in cells we want to combine it though to count based on criteria this is where you take the count function and an if statement and put them together I'm in a practice file right now called count if and count ifs please open it up to follow along I'm going to click in cell K2 my use case is I need to count how many sales rep reps have received a commission I can do that if I only do the count if Cell I or anything in column I has the word yes so it's a great use

case for count if we are going to use the insert function button to help us with this so I've clicked in cell F2 then I'm going to go over next to my form formula bar and click on the FX button now I need to type at the top the name of the function that I want to use which is count if all in word click on the go button and then for this first one make sure you use count if and not count ifs there's only one letter difference now again my range are going to

be cells I2 to the bottom so I'm going to click in cell I2 and then use my control shift down arrow to get everything down to the bottom selected now for my criteria it's if the word yes is located in the cell so I'm just going to type the word yes and you'll see right here it's telling me that there are 102 I'm going to click on okay and based on that yes being found in certain cells and no in others it's done account for me now for the commission no I want to do the

same thing this time though again I'm going to come in and type in count if for my function name the first thing I'll need is my criteria and again that will be column I I sells I2 down to the bottom so I'm using that control shift down arrow this time I'm not using the insert function button then my criteria this time is if no commission was paid in quotes I'll type the word no then because the formula only has one set of opening and closing parentheses I'll hit enter and we'll see that there's a 97

and really this is correct now if I come to sell M2 it gets trickier I I only want it to do a count if the location or the destination in this case is St Louis and a commission was paid so you can see here that I've got two different things going on and as I scroll through the different locations we'll see as we come down that St Louis is one of the destinations that's in column e so to do this one I'm going to come in and use the insert function button so I've clicked in

cell M2 I'm going to go up to my FX button and this time I'm going to type in the word count if s it's really important you get that S at the end because this is what allows you to have two criteria in your formula so the first thing I need to do is come in and select my first criteria which in this case is going to be whether a commission was paid or not so I'm going to come in and do the I2 down to the bottom again and type the word yes now you'll

notice that as soon as I do my first criteria range it opens up to give me an additional one for this one it's going to be again is the location like we talked about or the destination St Louis so for this one my criteria range will be cells E2 down to the bottom and I'm using that control shift down arrow again to get that now my criteria is going to be St Louis I need to put it in quotes and I also need to be sure it's spelled the same way it is in the spreadsheet

and I can double check that right here I can see the spelling then I'm going to go ahead and click on okay and you'll see that based on what I did right there if I come down and look at St Louis if I were to again have the spreadsheet sorted in ascending order which I do I could come over and I could actually check this and count how many St Louis yeses I have and we'll do it 1 2 3 4 five six so if I look at my function answer it's correct so again it's

only counting if yes they had a commission and yes the destination was good old St Louis these are super fun functions and you'll see them used a lot in conjunction with other functions to only count when certain criteria is set thank you so much for joining us for this part one of our Excel for Finance and Accounting in this course we've started at the very beginning by how to navigate in Excel and then we jumped into formatting basic functions like sum average min max and even count and then we jumped into more advanced functions like

being able to use sum if average if and even count if and the round function join us for the next portion of this course the part two where we're going to explore more advanced functions like vlookup xlookup and even functions that you will use as you start creating your own financial statements and some of the specialty charts that come with Microsoft Excel hello welcome to learnet Microsoft Excel for Finance and Accounting part two course my name is elisssa Smith and I am an it facilitator with over 25 years of experience teaching users like you how

to maximize their productivity using tools like Microsoft Excel now in this part two course we're going to spend our time exploring more of the functions that help you become a financial genius in Excel just kidding but we are going to explore things like the date and time functions that are part of Microsoft Excel we'll also look at how to validate data in cells using data validation now some of the functions we're going to look look at our vlookup and xlookup we'll also be exploring some of the scenario management tools like scenario manager and goalseek and

then when it comes to financial functions we're going to spend a great deal of time looking at those including the PMT function npv irr the ipmt and others as we begin to teach you how to make your own loan schedules we're also going to explore some of the templates that come with Microsoft Excel that you can personalize and make into your own loan schu schules as well so join us for this course hi everyone let's talk about the date time functions that are part of Microsoft Excel they're actually an entire category and today I'd like

to introduce you to two of the most basic ones keep in mind that dates are very critical to reporting financial information so having knowledge of a few of these datetime functions can be very useful I'm in a practice file called date-time feel free to open it up and use it to follow along as we we do this activity I'm going to start by clicking in cell C5 this is a very basic expense report and I'd like to put the current date into this cell there are a couple of different ways to do that one of

them is a function I'm going to hit an equal sign and type the word today now if I don't even know what today's date is as soon as I type today with an opening parentheses Excel will get the current date off of my computer and when I hit enter it puts the current date into the cell now it's important to note that this is a date that will ow that will update every time you open the file so keep that in mind it will change what if you need to know the date and the time

because the exact time can also be useful to know for this we're going to do an equal sign and type the word now indicating the date with the time I have an opening parentheses I don't need to do the closing parentheses because Excel will capture the current date and time off of my computer now you're going to see the date with the time it does use a 24-hour time clock but please remember you can always click on this date that is again because of the formula go up to your number group on the home ribbon

tab click on your different number formats come down to more number formats and then from the format cells box you can come to date and you'll see that if you go through this list there are some date and time styles that include a 12-hour time clock that you could reformat that particular function answer too and remember these are both dates that will update now is there a way to insert a date that will not update there is a keyboard shortcut that I'm a big fan of that you can use to insert the current date into

a cell I'm going to click in cell A8 and then on my keyboard I'm going to do the following keyboard combination for a PC computer the control key with the semicolon now the semicolon button is usually located next to the letter L as in lettuce on most laptop keyboards and normal keyboards so I'm going to do control semicolon you'll see the current date is inserted into the cell now when I click on this cell you'll notice that this date is not a formula when I look at the formula bar it's just a date so this

is a keyboard shortcut that will capture the current date off of your computer if you'd also like to include the time I'm going to double click after I've put in the date and double click right after the date and hit a space bar to get my cursor in that cell then to do the current time on my keyboard I'm going to do control shift semicolon so three keys all at once control shift semicolon this will insert the current time in the cell this date and time keyboard shortcut does not update so the next time I

open this spreadsheet the date and time that are located in cell A9 will stay there they will not update so keep these in mind when you start recording financial information in your spreadsheets you can either use functions that will up date with equal sign today and equal sign now or you can use keyboard shortcuts like control semicolon to insert dates that don't update welcome back let's talk about data validation now first of all what is it it's a way to verify that what's being entered in your cells is correct data validation can be added to

blank cells or existing cells if you add validations to existing cells the contents of the cells will be grandfathered in meaning the data validation will not apply to them but if you go over the top and enter in something then the data validation will come into play right now I have a practice file called Data validation open feel free to use it to follow along now I'm going to click in column H this is where I'd like to enter my validation my validation is that I don't want incomes under 15,000 entered into the spreadsheet so

they need to be 15,000 or greater for them to be validated in column H now I'm going to start by highlighting this cells that I'd like to apply the validation to I'm not going to include cell H1 but I'll highlight cells H2 down to h19 then I'm going to come up to the data ribbon Tab and come over on the right hand side to the data tools group and locate the data validation button it's usually right next to remove duplicates if I click on the button it's going to open up the data validation box I

want to start on the first tab at the left which is settings now right now you'll notice that it tells me that my validation allows for any value this means there are no validations but I want to come in and tell it that I only want to allow whole numbers in these cells then I need to pick the criteria in this case they need to be whole numbers that are greater than or equal to then I'll type in 15,000 you don't need to worry about decimals or dollar signs because those are just formatting now it's

important to let your users of your spreadsheet know what the validations are so to do that I'm going to come and fill out the other two tabs that are part of the data validation box we're now going to come to the middle tab which is input message just like this name implies this is a message that will come up as soon as a user Clicks in the cell to let them know what they need to enter so I'm just going to put an income amount and we're just going to open the box up again and

then below I'll type in the text to help let them know what they need to type you must enter an income amount if 15,000 or higher very simple now what if they do enter the wrong thing that's what the third tab is for error alert with the error alert tab there are three styles of error alerts but let me warn you that the only one that actually keeps them from entering an incorrect thing is the first one which is the default the stop Style again I need a title which I'm going to call incorrect income

amount and then an error alert you have entered an incorrect income amount it must be 15,000 or higher now to try out my data validation I'll now click on okay the first thing I'll see is if I click in any of the cells in column H where I applied the data validation again the input message comes up now if I go to a cell in that again column H and start typing in a value that is not going to meet the data validation criteria it'll allow me to type it in but as soon as I

hit enter that's when I'll see the error message come up and notice here I can retry it I can also type the wrong thing again I can hit cancel but it will not allow me to save an incorrect value in the cells until I put the right thing in to turn off a data validation you highlight the where the data validation has been applied go back up to data validation in the bottom leftand corner you will see a clear all button this will remove all the validations so the validation rule will be cleared the input

message and the error alert will all be taken off as soon as I click on okay you'll notice that all those things are gone so again it's a great way to ensure that the data that's being entered in your cells is validated hi everyone I want to introduce you to one of the the most famous functions in Microsoft Excel called vlookup which stands for vertical lookup it's been around for a long time and it is used all over the place here's the concept based on a column at the far left hand side of my data

set I can use the vlookup function to find something on the far-left column and then go over on the same row and locate another piece of information and return it hence the name vertical lookup now why are these so popular because you can use these to quickly update information in several places from shipping cost for example to quantity number to even a customer number vlookups are used all over well let's do a very basic example I'm in a practice file right now called vlookup here's my situation in column A of my table I have customer

numbers I would like to be able to type in a customer number and sell M2 and have it return the annual cost of tickets that my customers are spending from column I because I don't want to have to do the eyeball work to look it up this is a good basic scenario for a vlookup function so what I want to do is click in cell N2 please remember you can use the practice file it's called vlookup if you'd like to follow along now this is one that the first time you try it it's a great

idea to use the insert function button to help step you through the different arguments so after clicking in cell N2 I'm going to come up to the insert function button on the far left hand side of my formula top formula bar which of course is the FX button now to get this function I need to type in V lookup all one word at the top portion of the insert function box and click on okay and again it stands for vertical lookup but they've cut it down to just V to help save space in the name

Now vlookup function by default has four different arguments you'll see that the top three lookup value table array and column index number are all bolded the fourth argument isn't which means it's extra you don't have to do it or optional but I want to show you why it's important to remember so the first thing I need is a lookup value so in cell M2 I've actually entered what the customer number is that I'll be looking for so I'm going to click in cell M2 is my lookup value the second thing I need is the table

where my lookup value and again what I want returned is located in this case it's the table to the left cells L1 all the way over to j23 so I'm highlighting that you could also use keyboard shortcuts to select it does this table array have to be in the same workbook file it does not it's very common for the table array to be in a separate workbook file now the third thing I need is a column index number notice right here that if you look at the description in the function arguments box it tells you

that it has to be matching value that will be returned but also that it has to be a value it cannot be a letter A lot of people will say well just put letter I because that's where you need to return the information from the problem with that is that you can't use a letter it has to be a value so therefore what we're going to do is count over the number of columns 1 2 3 4 5 6 7 8 9 and return that it's the ninth column of data we need returned based on

the match that's how you get a column index number now this fourth value or fourth line in the function arguments box is range lookup and like I said it's optional but here's the important thing to know about range lookup if it's omitted that means that Excel will assume you're trying to do an approximate match that means that if your lookup values have decimals or you might have similar text you can get again not an exact match so if you need to absolutely match the right customer number and make sure it's on the correct row then

it's very important right here that you type in the word false so that it always has to do an exact match on your V value so I'm going to click on okay now based here on the fact that I typed in customer 12 for the number I'm going to come in and look for customer 12 it's Peter boggins then I'm going to count over nine columns and notice the value it's returning 1686 if I come in and I type in a different customer number like 19 which is Susan Scott I can see how it's updating

again my yearly cost sell based on that so we look at the formula again what you have is function name then you're going to have your lookup value your table array your column index number and because we want it to be an exact match we have the word false and that my friends is a very basic introduction to the vlookup function welcome back I want to show you a type of function that can replace a vlookup one of the challenges of vlookup functions is that you can only look up a value from left to right

this means that the column containing the value you look up should always be located to the left of the column containing the return value that's not always how our data is set up so what Microsoft has are two other functions that you can combine to basically make an improved vlookup this is called the index and match function now I have a practice file for this called index and match feel free to open it up and use it to follow along we're going to look at both functions separately and then combine them together I'm on the

index sheet of the workbook to start with if you click in cell A2 you're going to see an example of an index function and then we'll make one of our own now the goal of an index function is to retrieve the value at a given location in the range so you'll see right here my goal is to return this number one but let's look at the function first I'm going to double click on sell A2 you'll see the function has three major arguments so equal sign index and then the range or the table array that

I'm looking through for the location of a specific value then I'm providing a row number so I count down four rows then I'm going to go over six columns and whatever is in that position in the table will be returned in this case it's a number one let's try one of our own I'm going to click in cell B2 again it starts with an equal sign and then the word index feel free to use the formula autocomplete to help you along the first thing I need is again my range of cells or table array so

I'm going to select those cells by using keyboard shortcuts or left dragging it's A4 through g58 then I'll do a comma in this case I want to return the 845 that's in cell G8 so that means I need to go down five rows so I'll type in a five I also need to go over seven columns notice I'm not doing a column letter I'm doing a column number then I'll hit enter and we'll notice the answer is 845 now there's another again function that you can use called a match function and it's a little bit

different to see this example go to the match sheet in the workbook you'll see that I have an example of a match function in cell D2 now first of all what is a match function design designed to do it has one purpose and that is to find the position of an item in a Range so its answer is always going to be a vow value because it's telling you at what point that particular item is located in a list now my goal is to find where this 25 is located in this count of different products

so if you double click on Cell D2 there is a match function for you to look at again it starts out with the function name of match and then the lookup value which is 25 then your list range which is B2 through B5 and then right here you see this zero this is the match type and this is a little bit interesting because if you need to exactly find where that value is you'll put a zero you can also do a negative one or a positive one now if you do a positive one it will

find the largest value less than or equal to your lookup value if your list is in ascending order and if you do a negative one it will find the smallest value greater than or equal to your lookup value if your list is in descending order but you can see based on the information I've provided in the function the location of the 25 is in first position now if you combine these two functions together you're able to create a function that's not based on the vlookup problem of having everything be from left to right so you'll

see an example when you go to the index and match combined sheet and if you click in cell H2 let's take a peek at this function and then we'll create one it's two functions combined you'll see in this example that I've started with the index function remember its goal is to retrieve a value at a given location its range is A1 through d11 but where I would normally have the row portion of my index function I start my match function which again its goal is to just retrieve the value at a given location I should

say its goal is to find the position of the item in the range so in this case the match function is locating Chicago for me in the list this is telling me that if I go into column B B1 through b11 it's going to help me know where Chicago's located then after the match function I provide the column number for my index function and by doing this I get the location of the Chicago customer total now let's try this with Phoenix again same function but trying to find the Phoenix customer total and if we look

it's right here in cell D6 so I'm going to hit an equal sign and start my function which is index again you can use the formula auto complete to help you along now the first thing I need to do is provide my table array so it's going to be cells A1 to d11 then I'm going to hit a comma Now normally I would have a row number at this point to have it look down but instead I'm going to start my nested function which is the match function it replaces the row number now I'll get

another opening parentheses and now I need to provide what I'm looking up in this case it's the City Phoenix now I need to provide the table array or the list for the match function it's going to be cells B1 down to b11 then I'm going to hit another comma what type of match do I need to do this is letting you see the zero the positive one or the negative one I'm going to do a zero because I want to do an exact match followed by a parentheses now because this is still not done I

need to provide the column number of my index function I need to do another comma that comma is really important by the way and easy to forget in this case I needed to find the location of again where Phoenix's customer numbers are in column four and so that's what I'm going to include and then I'll do another print parthey to finish out the entire function and I'll hit enter So based on the information I've provided what it's doing is looking through the array but then it's going into the match function and finding Chicago once it

finds Chicago and we know its position then it goes back out to the index function I should say it's finding Phoenix apologies guys and it's going over to the fourth column in the table array and returning what's in that cell so it's more complicated than a vlookup however like I said it doesn't have the constraints of a vlookup and that's why these are so popular try one out and again you have the practice file so that you can play with it on your own there hey everyone do you ever get frustrated with vlookup and overwhelmed

with index and match well Microsoft has created a new and improved function that does very similar things called xlookup let's take a peek at it there is a practice file for this called xlup please feel free to use it to follow along I'm going to come into my spreadsheet and click in cell L5 and then I'm going to go up to the FX button just so we can see the different parts of this function then we'll try one out you'll see that the xlookup function has five potential arguments but only three are required the first

part of the function is the lookup value this is the last name that's located in cell K5 the second portion is my lookup array this is where again my lookup value is located you'll see over here it's A5 through a60 then the return array this is what I want return that's next to my lookup value that's in my lookup array in this case it's columns d and e another great thing about X lookups is they can actually return more than one item as opposed to vlookups that can only do one now there are two additional

arguments with this function that we're not using because they're not required but I want you to see what they are the first one is if not found this means if there's no matching value found based on the other parts of the argument you can actually have a message here or you leave it blank the fifth option is match mode now this is again where I think X lookup is way better than vlookup because remember with vlookup we have to tell it false if we're doing an exact match otherwise it will do an approximate match right

here you'll see that the great thing about X lookups is they default to an exact match so you don't have to put anything here otherwise you can use values like negative 1 and positive 1 to find approximate values that are either larger or smaller than the Val that you're putting in or what you're looking for now let's try one of these on our own I'm going to come in beneath cell K5 and in k6 I'm going to type in my lookup value this time which is the last name of bco and I want it to

return the sales and the location for my last name of Bilco so I'm going to create again an xook up function let's go up and click on our insert function button and open up the insert function box this function's name is X look up all one word after I type it in I'm going to click on go it it'll find it for me in the library and I'll double click on it so the first thing I need is my lookup value this is of course cell k6 second thing is my lookup array well these are

my last names I'm not going to include cell A4 because it's just a column heading but I can use control shift down arrow and get the range which is A5 through A6 now the return array is the part again of my data that I want returned in this case it's the sales and the location column and to select this area you can either left drag or your keyboard shortcuts I'm going to click inell D5 and then I'm going to use my control shift down arrow arrow to the right and then arrow down to select D5

through E60 so I won't only return the sales but also the location for bco now we know that we don't need match not found because I should say if not found because we know there's a match for Belco and match mode if you leave it blank is set to an exact match which is what we want we'll click on okay we'll see that it's returned to sales and to Harbor's location let's check it out it's finding billco for me then it's going over to column d& and it's returning two pieces of information so again why

is this better than index and match and vlookup in my opinion number one it's simpler number two it can return more than one item as we're seeing here it's returning both sales and location number three it defaults to an exact match so those are just a few of my thoughts but please try it out I think you'll find that you can use it in a variety of settings maybe maybe you don't have to be so dependent on index and match hi everyone what do you do in Excel when you need to arrive at a specific

answer or compare information well on the data ribbon tab in Excel there is a toolkit that helps you do that it's called what if analysis we're going to basically explore two of the options under the what if analysis today scenario manager and goal seek you can also follow along with me by using the practice file for this it's called goalseek and scenario manager remember there's a link to the practice files inside the description of the course what we want to do is go up and select the cells that we want to do a scenario for

I'm on the scenario manager sheet right now now what the scenario manager allows you to do is plug different sets of values into the same cells in your spreadsheet and then actually create a report that compares them so I'm going to start by highlighting the cells that I want to be able to plug different values into it's going to be cells A4 B4 and C4 then I'm going to go up to the data ribbon Tab and go to the wh if analysis button it's in the forecast group on the right hand side of the ribbon

I'm going to click and then come down to scenario manager it's the very first option in the menu and you'll see that right now there are no scenarios so I need to start by clicking on the add button at the top right you name each of your scenarios I'm going to call this first try you can see the cells that it's going to allow me to change and then I'm going to click on okay now right now it's taking the values that are already in the cells which is great for me so I'm going to

go ahead and click on okay and now you'll see that the scenario manager Box is open again I'd like to add another set of values so again I'm going to click on ADD and call this second try so you can continue adding more scenarios the next thing I need to do though is type in what I want my new updated amounts to be so I can type in a different amount that I'm borrowing a different again term and also a different interest rate you can use decimals or you can use percentages whatever you're comfortable with

then I'll click on okay again let me make sure I get the right term in there now I want to do one more this will be my third try again same set of cells and I'll type in again a different amount that I'm borrowing followed by a different term or I could maybe use a similar term to the one I already have and a different interest rate making sure that I have enough zeros up here now once I'm finished I'm going to click on okay I'll see all three of my scenarios in the box now

these save with your files so you don't have to worry about having them be deleted if you close the file let's say that I want to see how the second try is going to impact the amount that I'm borrowing and my monthly payment I'm going to select the scenario that I want and at the bottom of the Box there's a show button this will allow me in the same set of cells to see the different scenarios let's try the same thing with the third try and then I'll click on show so this is allowing me

to toggle between all three scenarios without having to copy and paste anything now the other neat thing is that you can create a report that compares all of them at once in the scenario manager box there is a summary button when you click on summary you have two different choices the first one is to do a scenario summary report or you can do a pivot table we're going to do the top one also we know that cells A4 through C4 are changing D4 through F4 so I can see the resulting cells in my report so

I'm going to highlight those three cells D4 E4 and F4 notice again those absolute values on the cell references then I'll click on okay my scenario summary report is going to be placed in a separate sheet in my workbook and what's really great is you see the current values each of your scenarios along with the resulting CH sales and how they've been changed and you can compare them side by side one additional thing you'll see here as well is that there's outlining that allows you to expand and collapse different levels of the report at the

row and column level by clicking on the letters sometimes the outlining is more value than others but it is a way for you to condense the report but your scenario manager will always be back on the original sheet you started in and when you go back to the what if analysis button you'll see the scenarios are always stored inside the spreadsheet there's a limitation to how many scenarios you can save a certain group of cells but it is a great way to allow you to plug the same values or different values into the same cells

now let's look at one more again what if analysis tool that's available called the goal seek this one a little bit simpler I'm going to go to my goal seek sheet in my practice file now again it's the same information but this time I'm trying to arrive at a monthly payment of $2,000 but I don't know what again I can borrow based on that so I'm going to let the goal seek help me to find the answer first of all what I want to do is come into the what if analysis button again and go

to goal seek it's the second option down now what cell am I trying to set well it's cell D4 so if you haven't already clicked D4 do so then the value I want to set it to is 2,000 now the cell I'm going to change is going to be the amount I'm borrowing so you can see here that you can set one cell and change another and they all have to be part of the same formula then I'll click on okay now based on that and it will show me in the goal seek status box

it reports back that if I set D4 to 2000 then it sets my term to 257 965 so it's a very simple tool but it does provide a helpful option when you're trying to arrive at a specific answer in a formula so as always try it out welcome back let's explore two more Excel functions the present value or PV function and the future value or FV function now I'm in a practice file called PV Ampersand FV it's in the practice file so feel free to use it to follow along and I'm starting on the present

value hyphen PV sheet now first of all what is the p PV function the PV function can be used to find the present value of a loan so it we return the principal amount of a loan based on a steady interest rate regular payments and a set number of period for the payments so what I want to do is come to the sheet and I'm going to click in sell notice right here E2 and that's where my PB function will go this is a great time to use the insert function box as well because this

is a newer type of function so I'm going to type PV for present value locate the function and then click on okay now there are five different Arguments for this function but the bottom two are again extra let's start with rate well this is the interest rate so I'm going to click in sell A2 where I see my interest rate the NP is also important but here's one more thing I need to mention I want this to be broken down by month therefore I'm going to come in and add the ability to have it show

by a monthly amount so I'm going to go ahead and divide this by 12 because my payments will be made on a month monthly basis per year for the NP this is going to be the part of the argument that's required and it's the total number of payments but again I'm making monthly payments and right now the term is in years so I'm going to multiply it by 12 to represent the monthly payments the PMT is going to be the payment amount and again this number is going to be negative once I enter it into

the formula because it's an amount that I owe then you'll see that there's an F f v and also a type now FV stands for future balance this might be a cash balance you want to attain after the last payment is made and type is a logical value where the payment's made at the beginning or at the end so if it's made at the beginning you enter a one and if it's made at the end of the period you do a zero so it's omitted we're just going to assume that it's at the end so

we're going to leave it omitted which we could also have put a zero in so you can see here how I'm breaking it down by month and I'm going to click on okay and again based on this this this is showing as a negative value that's why it's in red because it's an amount that I will owe so again this is the amount that I will owe based on a 20-year loan period broken down by month and $800 payments now let's go ahead and try out something else future value future value is different the future

value function calculates the future value of an investment based on a constant interest rate so it's very similar arguments but it's kind of doing the opposite in this case you're investing you're not owing so right here you'll see that I have it set up I'm on the future value or FV sheet and in cell H2 we want to create a future value to see what this will amount to you're going to see I have a 12% interest rate that I'm earning at I'm making 12 payments my payments are negative because they're money that I'm putting

into something and also maybe there's a cash balance a present value and then the one represents the payments do it the beginning of the period all right let's try this out I've clicked in cell H2 again after going over all those different argument parts and we're going to the insert function box this time I'm going to type in FV for future value locate it and then again we see that very similar to the PV this has five arguments the first one is my interest rate so I'm going to go ahead and come in and I'm

going to type in what my interest rate is going to be so I'm going to click on sell A2 because that's where my interest rate is if I know that this is going to be broken down by months I'm going to divide it by 12 the next thing I need is the number of payments while I'm doing 12 payments so I'm going to go ahead and just click there because I'm not going to be doing this over 12 years it's actually 12 months I'm not going to multiply it by 12 then my payment amount you'll

see here is negative I'm going to click and sell C2 for that then I have a PB this would be if there's a lump amount of money already in already in the investment that could be my present value amount and there is so I'll put that in for the type because again I want to make sure that this is being made at the beginning of the period I'm going to go ahead and type in a one because that's the case then I'll click on okay so based on all this information the interest rate number of

payments also that the payments are going to be $1,000 and that there's a constant value already there or a cash value of a th000 and the payments being made at the beginning you can see what the future value of the investment will be pretty cool right so try it out welcome back everybody let's explore another one of Microsoft excel's Financial functions called npv or Net Present Value now this function will calculate the net present value of an investment by using a discount rate and a series of future payments negative values and income positive values in

the spreadsheet that I'm using and it's a practice file by the way called net presentes value dnpv feel free to open it up and follow along you're going to see that we have outlined the different data required we have the annual discount rate then we have the initial cost in year one and then the returns and notice they're negative if they're money that's been invested and then they're positive if they're again income I'm going to put my formula and cell D2 so I'm going to hit equal sign and then npv for Net Present Value and

double click now you're going to start by entering in the discount rate which is sell A2 then a comma the next thing I need are going to be the different future payments and also income over again a very steady period of time so we're doing yearly here so I'm going to click in sell A3 do a comma and then I'm going to click in sell A4 do a comma sell A5 comma and finally sell A6 then this is all included in parentheses but I don't need the closing parentheses I'll hit enter So based on again

the annual discount rate and then the different future payments and the different income this is the current present value of the investment super fun function try it out welcome back everybody let's look at another newer type of financial function called the xnpv function now this function is only available in the newest versions of Microsoft Excel so that will be Microsoft Excel 365 for PC and mac and then also Excel 2021 if you have an older version of excel this particular function will not be available to you now what exactly does the xnpv function do it

Returns the net present value for a schedule of cash flows that is not necessarily periodic remember with the npv function that we've also done in this course it uses a very periodic schedule of cash flows this is when your schedule of cash flows is not periodic you can use the xnpv function for that I have a practice file it's called xnpv feel free to open it up and use it to follow along I'm going to click down in cell B9 that's where I want to put my xnpv function I'm going to start with an equal

sign and type in the name of the function X and PV you can use the formula autocomplete to help you with it and also I'm going to use the insert function button so we see each of the three different arguments that are part of this function the first one is a rate now this is not actually a interest rate in this case it's a discount rate to apply to the cash flows I'm going to be using 7% here here so I'm going to put in 07 the second argument is going to be the values this

is the series of cash flows that correspond to the schedule of payments like you see here in the box and I'm going to go ahead and highlight cells A2 down to A6 now the third argument are the dates and this is the schedule of payment dates but again what makes it different is they are not periodic right they can have different amounts of time between them so now I will highlight my dates which are B2 through B6 then I'll hit enter and and right here you'll see my answer now if I don't like all the

decimal places please remember you can go up to the home ribbon tab click on the dollar sign and you can round that answer keeping in mind when you round decimal places your values can be rounded up or down but this is again a simple example of the X and PV function welcome back let's look at another Financial function called the irr function now what this function does is it Returns the internal rate of return for a series of cash flows represented by the numbers and values now these cash flows don't have to be even as

they would be for an annuity however they must occur at regular intervals so monthly or annually the internal rate of return is the interest rate received for an investment consisting of payments negative values and then income positive values that occur at regular values if you look at our data here it matches that so in cell A2 and by the way I'm using a practice file called irr function for this activity feel free to open it up and follow follow along you'll see that in column A we have again the setup cost for the business and

then the next 5 years of net income you can see that the first thing I want to do is figure out the investment return after four years and then after five years this is again a good use case for an irr function so I'm going to click in cell B8 and start by typing my equal sign and then the name of the function in this case is irr you can use the formula auto complete but I'm also going to open open up the insert function box just so we can see the description on these different

arguments it's fairly simple the first one we need are the values and notice it tells us this is an array or a reference to cells that contain numbers that you want to calculate the internal rate of return a couple things to know about these different values you have to have at least one positive and one negative value to calculate the internal rate of return also the irr uses the order of the values to interpret the order of cash flows so be sure you enter your payment and income values in the sequence that you want and

in this case we've done that by year so I'm going to go ahead and highlight the first four years worth of data in my case looking at the setup here that means that I want to do A2 down to A6 now the guess field is optional and what it allows you to do is basically have a guess that's close to your irr we're going to admit it for this activity then I'll click on okay and I'll see that I have my IR are right here the reason I'm getting the formula audit triangle of death is

because I didn't include cell A7 but that's what we're going to do down in cell B9 now what I want to do is calculate the internal rate of return after 5 years so same process equal sign i r and this time when I select my values I'll do A2 all the way down to A7 and hit enter and again we can see that internal rate of return percentage that's being calculated for us it's it's a fairly easy function to do but keep in mind you have to have the right setup so that it can work

hey everyone I want to show you how to start building your own loan schedule or what we call a loan amateurz schedule in Excel now there are lots of templates on the internet and in Excel that will do this for you but because we want you to be familiar with the functions you use we're going to do it on our own I have a practice file open called loan schedule with PMT function please use it to follow along now notice up in cells B3 through B6 I have information that I'll need to use in the

PMT function which stands for payment function by the way we're going to use it to calculate the total amount of periodic payments that stay constant through the entire duration of the loan basically my monthly payment so I want to click in cell B9 because that's where my PMT function will go and I'm going to start by typing my equal sign and PMT now this is another one that's good to use the FX button or insert functions so that you get an idea of what the different arguments are the first argument is the interest rate which

is in cell B3 now the tricky thing here is because I want it to be broken down by month I'm going to divide it by cell B5 which represents 12 payments per year for the second argument NP this is the term of the loan which is of course sell B4 but again it's a 2-year term but a payment every month so I'm going to multiply that by sell again B5 representing that monthly breakdown the third required argument is PV which is the amount I'm borrowing which will be in cell B6 the two additional arguments will

add more details but we just need the first three for this formula to work and I'll click on okay now you'll notice that when the formula result is in the cell it might be negative or red and that's because it's an amount that you'll be paying back to your borrower now is this a formula that you can drag unfortunately it isn't because it needs to have absolute references so to fix that what I'm going to do is come into the formula itself in the formula bar highlight all the cell references and then hit my F4

key up on my function keys this adds absolute references in front of all of the cells that are referenced in the function and then I'll hit enter now I'm going to go back to the original formula get the black Crosshair for the autofill and drag it down and now I'll see again the monthly payment for each month for this loan and this is again all being done through the PMT function try it out it's a fairly simple one hi everyone I want to introduce you to another Financial function in Excel that can be important when

you're building out a loan schedule this is called the ppmt function and it calculates the principal portion of a loan payment for a given period of time based on a constant interest rate and a payment schedule there are four required Arguments for this function but we're going to show you an example and try it out so first of all just notice that I have a new practice file open it's called ppmt function feel free to use it so that you can try this particular function out I'm going to click in cell C7 this is where

my ppmt function is going to go now the first thing I need to do is type an equal sign and then enter in ppmt make sure you get both the P's in there and then I'm going to do my opening parentheses now I'm also going to go up and use the insert function button to make it a little bit easier to see the different arguments of the function first thing is my interest rate and you can see up here in cell B1 I have my 8% interest rate but we want to slow down here because

this particular principal amount needs to be broken down by month because I'm making monthly payments therefore I need to come in and divide it by those 12 payments that I'll make in a given year which is sell B3 now the peer is a little bit different here the peer is the first payment for the period that I'm going to start paying and it happens to be in sell A7 so I'm going to enter that in then the NP is the total number of payments made during the time that I have the loan which is going

to be of course cell B2 but again it's 12 payments in that period of time so now I need to multiply this by cell B3 now I'm not done because I need to finish out by doing the fourth required argument which is PV this is how much I'm borrowing which is sell B four now that I've got all those portions filled out I'm going to click on okay now right now the function's working however there are a few parts of this function that need to be absolute or fixed references the B1 divided B3 I need

to go select that in the formula bar and F4 it to make it an absolute reference and then the B2 * B3 including the B4 at the end the only part of the function that will not be fixed is the A7 because I always want it to go back and re refer to that again breakdown then I'm going to hit enter now what I want to do is go and select my formula drag it down and what I want you to see is that this principal amount changes as I make more payments on the loan

because my loan amount is getting paid off further and further as I put more money towards the principal again a very important part of figuring out a loan schedule is separating the amount that you're paying between interest and principle hey everyone let's look at a function that focuses on the interest of a loan this is called the ipmt function and basically what it does is it Returns the interest payment for a given period of an investment or loan based on periodic constant payments and a constant interest rate so what we're going to do is open

up the practice file called ipmt and try it out you'll see here that there's some set data for us we have an interest rate the period of time for which we want to find the amount of Interest the loan's term and then also the value of the loan I want to start by figuring out what the first month will be for interest to do this I'm going to hit an equal sign and then start typing in my function ipmt and then double click on it to get again the Formula Auto Complete to kick in now

with this function if we go up and click on the insert function button you'll see that there are four required again arguments the first one is going to be of course my interest rate so I'm going to click and sell B2 in this case and again because it's a monthly payment I need to divide this by 12 the next thing I need is again the period for which I want to find the interest in this case it's the first period of the loan or the first month so I'll click and sell B3 then the next

thing I want to do or in this case I could just type in the amount but I want to click in the sell the next thing I need is the number of payments this is the total number of payments for the investment which is three years but again it's broken down by month so I need to multiply that by 12 fourth argument is the present value in this case it's again cell B5 so now that I have all these pieces in my function I'm going to go ahead and hit enter now what we'll see is

this is formatted as a negative value in red and that's because this is the interest so this is the amount of money that I owe now the second one we want to do is to figure out the interest due in the final year of the loan that has yearly payments in a constant term right so again I'm going to type an equal sign and my function ipmt and again if you want to use that Formula Auto Complete feel free the first thing I need is my payment or my interest rate so I'm going to come

in and select again cell B2 it's where my interest rate is then I need the number of again the period for which I want in this case it's going to be period three so I'm actually just going to type that in CU that means it's the third year then I'm going to come in and do my number this is the total number of payments in this case that's going to be sell B4 and finally the present value of the loan which is the 8,000 then I'm going to click on okay to finish out the function

and we'll see that in this case during the last final year of the loan the yearly payment on the interest will be $292 now this is again rounded so if I were to come up to my home ribbon Tab and I were to increase the decimals we'd see that it would add a few decimal places onto this formula so this one's all about the interest hello let's look at another Financial function that helps you to locate cumulative interest paid on a loan between a start period and an end period This is called the cui PMT

function or cumi payment like cumulative payment but really it's interest that we're focused on I have a practice file here for this it's called cui PMT the name of the function please use it to follow along with me now this function is a lot like the PMT functions that we've already used also be aware that this is a fairly new function it works in Excel for Microsoft 365 Excel Microsoft 365 for Mac Excel for the web and Microsoft Excel 2021 so how do you use it well in the practice file I'm going to click in

cell B5 and then hit an equal sign and start typing in the name of the function cumi PMT feel free to use the formula auto complete to help you type that in and then to see the different arguments we're going to go up and use our friend the insert function button because it helps to describe the different arguments right here you're going to see that the first one is rate which is the interest rate which is in cell B2 but because I'm breaking it down by month I'll need to divide it by 12 the number

of payments or term is going to be in cell B3 but again we're breaking it down by month so I'm going to multiply that by 12 now the next thing we have is what's being borrowed the present value that's in cell B4 this is where it gets a little bit different I have to tell it what period of time to calculate the interest for for me it's the second year so I'm going to do months 13 through 24 now there's one final part to this function that is not accounted for in the insert function box

so up in my formula bar I'm going to put a comma zero this is called type what happens here is if you're accounting for the the interest being all the way through the end of the payment period it's going to need to be a zero if it's going to be happening at the beginning you'd put a one and you have to add this because if you don't you can get a number sign error message in the formula then I'm going to click on okay again it's negative right because it's Interest being calculated on the loan

let's try one more this is going to be interest paid in the very first month of the loan but we're going to use the function to calculate it again I've clicked in cell B6 I'm hitting an equal sign we're going to type in the name of the function and then we're going to get the opening parentheses same thing let's go up to the insert function box and again very similar arguments interest rate divided by 12 right and then we need the term which is in cell B3 multiplied by 12 then we need the present value

being borrowed which is cell B4 this time though my start period will will be one and my end period will be one because I'm only accounting for the interest accumulated during the very first month in addition I only want it to account through the end period of the payment especially since this is the first month so what I'm going to do is rather than putting it here in the function arguments box I'll go up to the formula ribbon tab type a comma zero and then I'll click on okay and you can see here this is

just calculating the first month's worth of Interest very cool again keep in mind if you do this in Excel for the web and you want to get the proper format you'll need to select the cell where the formula is located go up to the home ribbon tab to the number group and then format it as a number and make it General but again the reason these values are negative is because interest is something that you will pay on the loan very cool function try it out hey everyone after spending so much time looking at the

different functions that are involved with a loan schedule it's important to note that there are actually several pre-built tools that come from Microsoft that will do a lot of the functions for you and give you a head start a lot of these are going to be contained in the Microsoft templates now keep in mind some organizations choose not to give employee access to these templates but if you do have the right licensing and they're available they might be another opportunity to save time when it comes to setting up a loan schedule to access these we're

going to go up to the file ribbon Tab and come down to new but rather than just opening up a new blank template I'm going to type in the word loan and then do a search and you'll see that there are several different types of templates built around loans inside of excel there's three that I want to show you the first one is the loan calculator now this one when you click on it it will tell you about it it's provided by Microsoft this is important to note because it means that it's been vetted by

the Microsoft team and you can also see a description of what it does I'm going to click on create now you'll see that there's already information fed in here but notice I can come in any of these cells it gives me a description of what the items are and then I can type my own information over the top so for example if I come in here I can actually change the values for the loan amount for the interest rate also for the term and even for example when the start date of the loan will be

and based on that that it will actually begin showing me information I can even adjust for example the monthly payment that I'm going to make and it will update the entire loan schedule Below based on that information this saves me having to make my own loan schedule now another one that's similar to this if you go back to file and new again and search again for templates based on the search topic of loan that I also like is the loan analysis spreadsheet or worksheet this one again is a simple idea built by Microsoft but it

lets you again put in your own interest rate loan term and amount and then it breaks down the monthly payments and it calculates interest so you can see here how I have the information provided at the top that I can also title and remember these are templates so the templates are like a stamp that you base your own files on so you can name it then you can come in and customize it and save it as a normal mic Microsoft Excel spreadsheet a final one to also look at that's built around the idea of building

a loan schedule that's again provided by the templates in Microsoft is going to be the one that's in this list called the loan amateurz schedule this one's really great to use when you're taking out a loan from a financial institution and you'll see here that at the top you can put in all the information around the loan even who the lender is and then down below it builds your schedule so it's the same idea that we've been putting together with all these different individual functions that are the financial functions but the great thing about these

spreadsheets is they are pre-built and by basing them on a template you're saving yourself having to go through and make the entire schedule on your own but by understanding some of the functions we've covered in this particular second part of this course it will help you understand why the loan schedule is being calculated the way it is so try some of these templates out if you have them available to you to you because they might save you a significant amount of time hey everyone thank you so much for joining us for this Excel for Finance

and Accounting course part two in this course we've explored a lot of functions that are part of Microsoft Excel that can help you again do Financial Accounting we've looked at the X lookup function we've also spent time in Microsoft Excel exploring being able to build our own loan schedule we've also spent time exploring some of the different Financial functions for example the ppmt function the PMT function and even the MPV function all of these functions are the beginning of being able to use Microsoft Excel to craft your own financial data please join us for the

next course the part three where we're going to spend time exploring this idea of financial accounting in more detail with things like being able to create specialty charts like combo charts pivot tables pivot charts waterfall charts lots of charts and also building out some of our own Finance case studies using Microsoft Excel hi everyone welcome to learnet Microsoft Excel for Finance and Accounting course part three my name is Elsa Smith and I am an IT facilitator with over 25 years of experience teaching people like you how to maximize their skills in platforms like Microsoft Excel

now in this part three course our Focus will be how to visually represent your data once you've actually created it and are using Microsoft Excel to keep track and analyze your data we're going to learn how to use some of the charts that come with the software like bar charts spark lines we're also going to explore pivot tables and pivot charts additionally we'll look at ways to fix formatting in Data before you analyze it we're also going to look at how you can visually find insights inside your data so please join us on this course

hi everybody I want to show you one of the simplest and easiest ways to represent your data in Microsoft Excel now there is a practice file for this it's called bar charts remember you can go into the description click on the link to find the practice files that go at this course how do you create a bar chart and what is it well first of all bar charts are also sometimes called column charts they're useful for showing data changes over a period of time or for illustrating comparisons among items in column charts categories are typically

organized along the horizontal axes and values are going to be along the vertical axes so how do I create one of these bar or column charts first of all you need data which we have then we have to decide what data we're charting it's important to remember with charts you generally want to select the simplest information this means titles like we see up in cell A1 which is a merg cell you don't want that because it's extra and then down in row eight I have totals and if I were to chart these with my data

it could skew my chart results so instead I'm going to go to the simplest pieces of data which will be cells A3 Down 2 to G7 and highlight them this tells Excel what I want charted then to select a bar chart I'm going to go to the insert ribbon Tab and come to the middle of the ribbon to the charts group here you'll see on the top row that there is a colum or bar charts button that you can click on and it will show you the different subtypes that you can select there are 2D

3D and even 3D bar charts the column charts will be at the top the bar charts will be along the side now to pick a specific chart you hover over it and it will live preview what your data will look like if it is applied to this particular sub chart type once you pick the type that you want you're going to click on it and you have a chart this type of chart where it resides on top of the cells is called an embedded chart now charts are like Graphics so this means that if you

come to the sides of them while you see their sizing Corners turned on you can actually drag them in and out to size them I recommend doing it by the corners additionally if you want to move your chart so it's not taking up too much space or over the top of cells you can come to any side of the chart you'll notice that your mouse pointer will look like an arrow pointing up down left and right you can just drag the chart to where you want it to go and that way it won't be over

the top of your data when you click outside the chart in a normal cell the chart will deselect and it will no longer be selected so to select it you just click on it and again now my chart is showing me the data that's currently in my table and remember the goal of charts is to quickly represent your data so that people don't need to actually see the spreadsheet they can look at your chart and easily understand it and then go back to the data when they need more detail so create a chart of your

own welcome back let's look at another type of chart called an area chart now area charts are similar to line charts but the difference is that the area below the line is filled in with color in an area chart and both area charts and line charts are great for showing Trends but if you're needing to print it's better usually to do a line chart now I have a practice file for this available feel free to use it it's called area charts first step is to select my data always remember you want to select again what

you're charting and in this case I don't need the title or row eight to be part of that so I'm going to highlight celles A3 through G7 then I'm going to go up to my insert ribbon Tab and come to the charts group now it's interesting about area charts is there really isn't a button representing them here so if you come to the bottom right hand corner of the charts group you're going to see this very small rectangle with an arrow pointing down and to the right it doesn't look like a button but it is

when you click on it it's going to take you into the insert chart box you want to go to the all charts button so that you can see again all the different types of charts you can create in Microsoft Excel the left hand side are the main categories I'm going to select area and along the top I'll see the different subcategories I can click on any of these subcategories to see my data represented in the box below and if I hover it'll zoom out on it or I should say zoom in and make it easier

for me to see once I find an area chart that I like I can click on it and select it and then click on okay at the bottom of the box and you'll see that the chart actually gets created for me again I can size it now briefly if you want to update this chart and change the way it looks remember that in your chart if you go to the top right or left Corners you'll see three buttons the green plus sign is called the chart elements button it allows you to quickly do things for

example like add data labels to your chart or even add a data table underneath of it the green paint brush that you see when you rightclick is going to be the chart Styles button it's all about updating either the style of the chart as you can see here or if you go to the second tab at the top you can update the color scheme of the chart just make sure that the formatting you pick for your chart does not again decrease its visibility you want to make sure that this chart is very easy for people

to see and understand same thing goes for colors now the third button that you'll see in the top right corner is the filter I love this one because it allows you to filter what's showing in your chart when you filter you're not deleting what you do is come in and select an item you want to turn off and uncheck it then come to the bottom right hand corner and click on apply let me show you one more time what I'm talking about here when you click on apply it just removes that particular element from the

chart to return it you're going to go back up and reclick and say apply again and it will come back but remember area charts are a great way to show Trends hello everyone let's talk pie and donut charts there is a practice file for this called pie and donut charts feel free to use it to follow along now pie charts are really important they are a great way to visually represent data as a fraction part of a hole hence a pie with pie slices they're also probably the simplest type of chart you can create in

Excel now in my practice file to get started I'm going to highlight what I want to chart it's going to be my headings so A2 down to D3 and my top customer rep so the cells I have selected are A2 to D3 then I'm going to go up to my insert ribbon Tab and come to the charts group I'll click on the charts button you'll see that there are 2D charts 3D charts and donut charts you can hover over any of them to see the chart get created once you've selected the type of chart you

want I'm doing a 3D pie chart you click and your charts created now one interesting thing about pie charts is because they're so simple you can actually adjust their range and adjust what information's being charted to do this I'm going to make sure that my chart is clicked on and I'm going to come into Excel and go to the right hand corner of one of the ranges of cells that's being charted and I'm going to left drag you'll see that my mouse pointer turns into a double headed Arrow when I do this it actually adjusts

the range of cells that are being charted and now for example I can chart Jose instead now for a donut chart I'm going to click in a normal cell outside again my chart I'm going to highlight all my information A2 down to D6 go back up to the insert ribbon tab come to the charts group and find the donut chart subtype click on it and now you'll see that I have a dut chart dnut charts do a little bit better job again charting multiple rows of data unlike a pie chart so they are able to

represent more complicated data if you ever want to take a pie chart or a doughnut chart and edit them remember you can use the buttons in the top rightand corner of your chart or on a PC you can go up to the contextual ribbons that come with charts called chart design and format these two ribbons have great tools like different chart Styles and also the ability to update the layouts of your charts using the different tools that you'll see up in the ribbon it's a great way to quickly again update the layout of the chart

without changing the chart type so try a pie chart and a doughnut chart they're really simple hello let's talk combo charts there is a practice file for this called combo chart so please use it to follow along now a combo chart is a combination of two charts so it could be a line graph and a column chart or an array chart and a line chart but you can make a combo chart with a single data set or two days sets that share a common string filled we're going to use this again Excel spreadsheet to compare

sales totals to commission fees the first step is the same as all the other charts we've made we need to highlight the appropriate cells that are being charted again we're not going to highlight titles or totals I'm going to come in and start by clicking in cell A3 and highlight A3 over to C8 then I'm going to go up to my insert ribbon tab come to the charts group and look for the combo button it has a column bar chart with again a line chart on top when I click you'll see that there are three

classic combo charts that you can pick from we're going to do the middle one which is going to be the clustered column line on a secondary axis I'll show you why when I click on it you'll see that again I have the column chart behind with the line chart on top I'm going to make it a little bit bigger now what can you do to edit this chart well if you rightclick and come into the rightclick menu you have a change chart type button I so don't so much want to change the chart type as

let you see the options that you have here when I click on it it's going to show me my current chart again you'll see down below that it's showing me what charts are being charted on which on so right now my sales total is on my clustered column and my commission fee is on the line if I'd like to switch that notice I can come in and I can change that myself by picking a different chart type and again selecting a line for the top and you'll see it switches the information also here I can

decide if I have a secondary Axis or if I want them both charted on the same axis now that I've made that change I can click on okay don't forget you can further update the way the chart looks by going up to the contextual ribbon called chart design come over to the quick layout button to try out different again quick layouts with your chart that may help you better represent the data that you're trying to chart once you've selected your chart type click away from it and you now have a combo chart welcome back we

want to explore another type of chart called a waterfall chart now these charts are really great at showing a running total as values are added or subtracted so they can be really helpful for understanding how an initial value like say net income is affected by a series of positive and negative values The Columns are colorcoded so you can tell positives from negatives very quickly in our spreadsheet called create waterfall charts which by the way is one of the practice files you can use to follow along and make a waterfall chart of your own we're going

to comparing income and expenses for the second quarter in an organization I'm going to start by highlighting my information it sells starting in A3 down to B13 then I'll go up to the insert ribbon tab come down to the charts group look for the waterfall subtype now it's actually combined with the funnel stock surface and radar charts so you might have to look for it a little bit but the waterfall chart will be the very top option when you click on the main button once you select it you'll see the beginnings of a waterfall chart

I like to make the these charts bigger because there's a lot going on inside of them so I'm going to make it bigger now you'll see that one set of bars will be blue that represents positive values then my negative values are a different color now to be more accurate I would like to have these actually be charted as negative because right now they're just compared to the values to do this I'm going to go to my first negative point in my chart and I'm going to click on it when I do this it selects

all the data points in that series but I'm going to click again to get that data point highlighted by itself you'll notice the other data points in the chart or the data fields don't highlight anymore I'm then going to rightclick in the rightclick menu second from the bottom you'll see set as total when I do this it gives a more accurate protrayal of where this negative value is in comparison to the positive values I'm also going to do the same thing with the additional negative value I'm going to again click on it so it's separately

selected right click again and say set as total and now I'm getting a more accurate portrayal of where the positives and negatives in my waterfall are happening and again once I deselect the chart I'll see the colors become highlighted again these are very powerful again for helping you see how an initial value compares to positives and negatives and they are used all over the place so try making one of your own hey everybody we're ready to create what's called a football field chart now this is not a chart for keeping track of football scores football

field bu charts are floating bar charts or stock charts from Excel that put several valuation analyses side by side to provide you with a full context of your company's value they use a variety of methodologies and assumptions so a typical football field Matrix will include a company's value based on for example DCF valuation or lbo analysis now we're going to use a stock chart for our example today and there is a practice file for this that I suggest you use to try this out it's called create football field charts so the first step is to

select our data and unlike the other charts we've created where we've selected column and row labels we'll do that after the fact so I'm going to come into my spreadsheet and highlight cells B3 over 2 E7 then I'll go up to my insert ribbon tab I'll come to the bottom right hand corner of the charts group to get into that insert chart box and go to the second tab at the top called all charts this is where I'll find the different categories of charts and of course we want a stock chart we're going to pick

the second one in that's called open high low close and then I'll click on okay now I'm going to size this chart out just a little bit because this is the beginning of our football field chart and when you see it sized it does start looking more like a football field hence the name now we want to continue to make some more changes we want to get the correct labels down here on the bottom of the chart so we're going to go ahead and just right click on any of the data bars this will include

the entire series because when you click on one you get all we'll come down to select data now from here I'm going to go to edit because what I want to do is get the correct category AES labels these are located in cells A3 through A7 and these are of course the different various valuation methods that we're going to use I'm going to click on okay now once I click on okay again we'll notice that they're added to the category axes I don't need this Legend anymore because it's not providing me with anything so I'll

click on it and hit delete and then the grid lines I don't need either you could keep them but again they're just kind of making a extra space that we don't need so I'll select them and hit delete as well so now our chart's getting really clean and ready for us to add more customizations to it all right the next thing I want to do is add data labels to my chart to help show those highs and lows that we're trying to keep track of so I'm going to click on any of the data bars

to select the data series and then if I go up to the top middle of the border of any of the data bars and click I'll see that I get a small Middle Point this is of course one of the data values that I can track in the chart or a data label now to format this specific data label I'm going to come over to the top right hand corner of my chart if it's too big this button will appear in the top left corner and I'm going to click and come down to data labels

now you'll see as I hover it puts them on the top right side and I want to again control and put them in the top middle so I'm going to click on the Arrow at the side and actually come in and say more op options my format data labels task pane opens up over on the right this will allow me to come down and place those data labels above the current data bars I also want to format these as a currency so I'm going to go to number and of course this will allow me to

come in and pick currency for their format with two decimal places so that they're formatted correctly to make it easier for people to understand what they are I want to do the same thing with the low point so I'm going to close that format data labels task Pane and come over and click on the bottom middle border of one of those again data bars and you get those same little markers representing the low point then if I come over to the green plus sign for the chart elements button again I can go down to data

labels and again you'll see that it's placing the data labels kind of off on the bottom right but I'm going to go ahead and come to the arrow on the side go to more options just like I did previously and this will give me the ability to open up the format data labels task pane again and I'm going to select under label position below then again I'm going to come under number and format them as a currency then I can close the format data labels box okay as we continue with our journey let's format the

data series I'm going to come into my chart and click on any of the data bars this will of course select them if I right click again I can go down to format data bars this will open up the format datab bars box let's add a fill color to the datab bars remember that's the inside a solid fill and a color that's easy on the eyes something that won't be too dark additionally I don't want to border around the data bars so I'm also going to come to border and say no line This Again simplifies

things I want to go to the Chart title and I'm going to go ahead and add a chart title evaluation summary now I'm going to type it in my formula bar and as soon as I hit enter it gets added to the Chart additionally I want to make sure that in my chart area there's no fill for the chart area and also want to make sure that if I do want to fill I could do a solid fill that is white right so that's another thing that I can do is come in and say just

a white fill additionally if I don't want a border around the chart I can come in go down to border and say no line when I deselect the chart we'll see how those two things have come into play as well okay next we want to format our axis so I'm going to come over to my vertical axes and click you'll see that after I click it will select the axes and then if I right click I can go down and say format axes and I'd like to set the minimum to 10 to just kind of

help spread things out you'll see that as soon as I do this it updates in my chart additionally if I go back and click on the axes again I'm going to add a border just to separate the axes from the actual valuation to do that I'm going to come in again click on my axes to make sure it's selected so that I have the format axes box selected then select the paint count at the top come down to line and say solid line to increase its width I'll come right here and make it maybe two

points wide and then also for the color I'm going to click on the color here and I'm going to pick a gray color just let it set apart when I click away from it we can see that line is now in the chart okay I want to do the same thing with my category or x axis as I did to my y AIS so I'm going to come in click on any of the items in my X or category axis selected if my format axes task pane doesn't open up I can right click in the

axes and select format axes then I'm going to come up and make sure I click on the paint can at the top this will allow me to go in and again I want to make sure that I have a line I'm going to pick the same color gray that I did previously make it two points in its width and then I'll go ahead and click away from it so that now I have again two borders that are similar it looks like one of them's a little bit different but you want to make them similar if

possible so they kind of look like a line that's just continuous as far as those two axes go okay we're just about done with our chart I want to add to the title up here Beyond valuation summary I'm going to click up in the title and also add to my title Equity value per share in dollars and I'll just add this to the end of the current existing title that's there now to help really represent what the target reason for this chart is for for example are you trying to represent a current share in this

particular football field chart or a Target price or a share price so we're going to create a line to show that so I'm going to come over to the side of my chart after I deselect it go up to my insert ribbon tab come into shapes and I'm going to pick a line I'm just going to draw this line out with my again mouse then what I'm going to do is come in and format this line I can use the format shapes task pane if it opens up if this task pane doesn't open open right

click on your line and go down to format shape I'd like to make this line dashed so coming into again the paint can side let's go ahead and change the color first we want a solid line I'm going to make it an orange color so it sticks out I'm also going to increase its width this will make it a little bit easier for people to see we'll make it two points wide and then of course to change the style you'll notice here that you can click on different ones I want to do a dash line

and to make sure it's straight we can just use the shift key and kind of pull it out then I'll bring it into my chart and put it where I want it to go right here at the 40 point mark or the $40 share price Mark and then I can drag it out across the chart and to make it straight what I suggest is to hold down your shift key as you again drag the line out if you ever want to change the way the line Looks you can come back to the format shaped menu

here's the thing to remember about football charts right their goal is to be a great way to summarize and visualize all of your valuation analyses that's what these charts are for and they do take a little bit of work but once you understand how they work and what they can represent they're very powerful welcome back I want to show you the smallest type of chart you can create in Microsoft Excel called a spark line these small charts fit directly inside cells in a sheet now because they're condensed size spark lines help you to see patterns

in large sets of data in a very concise and visual way they're great for showing Trends in a series of values like seasonal increases and decreases or economic cycles and they help to highlight maximum and minimum values a spark line has its greatest effect when it's positioned right near the data that it represents and they're very easy to make so I have a practice file for this called create spark lines feel free to open it up to use it and follow along I'm going to start by clicking in the cell where I want my spark

line to go cell F4 then we're going to go back up to the insert ribbon Tab and come to the middle right of the ribbon to the spark lines group now there are three types of spark Lines line column and win loss now let me clue you in with win loss it works best with highs and lows so positive and negative values we're going to start with a line type of spark line and select it now it's going to ask me what the range of cells is that has my data I'm now going to highlight

cells A4 over 2 E4 and then I'll click on okay and voila you have your spark line now when you click on the spark line you will see that that there is a contextual ribbon that you can use to edit your spark line the right hand side has spark line Styles these basically change the color but on the far left you can actually edit the range of data that you're editing or creating your spark line from and you can change your line spark line to a column spark line or a win loss spark line in

addition you can also highlight different points in your spark line using the show group you can edit to show a high point in a different color and for example a low point in a different color and the spark line editing contextual ribbon will allow you to do this let's also try out a High Low Spark line for this I'm going to go click and sell f8 this is where I want my spark line to go I'll return up to the insert ribbon tab again go over to the right hand side to the spark lines group

and select win loss remember this one is for highs and lows or positives and negatives I'm going to highlight my data A8 over to f8 and then click on okay and here you're seeing an example of a win loss again even with the win loss I can highlight specific points within the spark line so for example if I want to make one of the bars turn a different color I can do that by coming to the marker color and picking the specific point I want to edit now the only downside to spark lines is when

it comes to deleting them you cannot click on the cell where the spark line is located and hit delete it will not go away so to clear a spark line if you click on the spark line and go up to the spark sparkline contextual ribbon the very last button in the right hand corner of the sparkline ribbon is a clear button it gives you options to clear selected spark lines which is what I want to do and this is how if you click on a cell and you go in and select clear selected spark line

it will delete the spark line out of the cell these again are a very small simple way to reveal patterns in data and I love to try them on rows hi I want to talk about one of my favorite features in Microsoft Excel it's called a pivot table now the value of a pivot table is that you can use summary functions in value fields to combined values from underlying Source data and it allows you to take a large data set and analyze specific columns out of that data set without having to worry about having to

reformat hide columns and rows and messing up your Source data there is a practice file for this called create pivot tables please feel free to use it to follow along so here I have my spreadsheet called create pivot tables open how can I make sure that when I go to make my pivot table this will work first of all let's look at Row one super important I have column headers these are especially critical when you're creating a pivot table second of all I don't have any entire blank rows now blank cells are fine but entire

blank rows will stop your analyses at the point where you hit the blank row we're ready to create a pivot table now remember the goal of the pivot table is to allow me to analyze parts of my data not all of it so say for example your supervisor comes to you and says hey with this data I would like to know the total sales by commission for each destination well think if you had to do that on your own it would require tons of functions and also sorting and filtering so a pivot table can do

it for me in just a few clicks the first step is to click anywhere inside the source data and then go up to the insert ribon tab now if you've never made a pivot table before I highly recommend checking out recommended pivot table this box will actually take your data on the left hand side you'll see examples of pivot tables you could build from your data and in the middle you can actually preview them remember my idea was to see again the sum of tickets sold by destination you can see that right here I already

have that separated by commission and when I click on okay this recommended pivot table gets put on its own sheet right here my source data is back on my original sheet totally unimpacted by the pivot table I've just created this time let's make a pivot table on our own again click anywhere in the data we're going to go back up to the insert ribbon Tab and this time we're going to come to the far left button now this is one of those double buttons the top half is actually going to take you directly to the

insert pivot table box the bottom half lets you pick where your data is located we're going to go ahead and just click on the top portion because our data is right here in the spreadsheet it is not uncommon to have your pivot table data be in a different workbook file or even even a different file Source right here you're going to see that it's already assuming the range of data this is why those entire blank rows are a noo and you could reselect this but Excel knows exactly what my data is because it can find

it then the default is to have your new pivot table be placed in a new worksheet perfect I'm going to click on okay so now I'm in a new sheet and you'll see on the right that a pivot table field list has opened up ready for me to build my pivot table now very mistake that newbies make with pivot tables is to click in a Cell outside this pre-built pivot table area when I click here everything turns off just come back over and click where you want your pivot table to go now to create your

pivot table what you need to do is take fields from the top and drag them down to your pivot table area down below the top portion of the pivot table field list are those column headers that I mentioned at the beginning of our video so what I need to do is pick the columns that I want to analyze I'm going to start by taking my office field and dragging it down to the Rose area I'm just going to left hold down my mouse button and drag it down as I do this I'll see my pivot

table get built in the middle over on the left hand side I'm going to go ahead now and take another field and drag it to the columns area now in the middle I need to pick again some field that I can summarize in the middle so it works best to use value based Fields I have three columns that have value based information amount take tickets and total I'm going to go ahead and drag total down and you'll see that there are a lot of blank cells in my pivot table these blank cells are just as

important as the cells that actually have information because these tell me when something didn't happen so again I'm not analyzing all the data from my source data just three specific pieces now you'll notice that for the summary function it always defaults to do a sum let's say that I'd like it to do an average I can rightclick right here where it says sum of total or I can actually left click right here next to where it says sum of total and come in and pick value fi settings this box will allow me to pick from

a variety of Microsoft Excel functions like average and I can change that summary function and it updates let's say that I would like to format these as a currency because that's what they are I'm going to rightclick again come down to number format and I can pick a currency instead and again having the correct format helps people to understand that this is an amount of money that someone bought now how easy is it to manipulate pivot table data let's say that I don't want to see my destinations here anymore I'd like to see them on

the rows I can take destination off of The Columns area drag it under the rows area and now I'm seeing a pivot table where they're both stacked together but notice again everything's alphabetized and I'm even seeing subtotals and at the very very bottom I see a summary function the other value of pivot tables is how manipulable they are they're they're very easy to update and change and you can build as many of them as you want one final caution with pivot tables if you go back to your Source data nothing is updated but let's say

it does for example some of the number of tickets sold change and you'll see this will change the total if my summary data changes unfortunately my pivot table does not automatically update it's critical that if you do have updates in your Source data when you go back to your pivot tables you need to refresh them because they don't auto update to do this you're going to rightclick anywhere in your pivot table come down and select refresh this will ensure that the pivot table is updated to its source data pivot tables are very easy to make

and they're a very powerful way to quickly analyze big sets of data all right guys one of the reasons that pivot tables are so powerful is because they allow us to create pivot charts there's a practice file for this called called create pivot chart feel free to open it up to follow along now why would a pivot chart be so important when I have a large data set like this one if my boss comes to me and says hey can you chart this if I try to do that notice this is what I end up

with the problem we have in a data set like this is there are too many columns to chart Microsoft Excel does not know what to pick but if I can create a pivot chart just like a pivot table it allows me to select specific pieces of data out of this wide data set and just chart those pieces of data so how do we do that well first of all remember you need the same setup column headers no entire blank rows then we're going to click anywhere in our data and we're going to go up to

insert we're going to come over this time to the charts group and on the right hand side you'll see the pivot chart button again the bottom half of the button lets you decide if you're going to do a pivot chart the second half will ask for both a pivot chart and pivot table but they end up taking you pretty much the same place so we're going to just click on the top portion this will open up the create pivot chart dialogue box again the first step is to make sure you have the correct range selected

and then select where you want your new pivot chart to be built we're going to do ours in a new worksheet and then click on okay so a new sheet opens up and we'll see right here that the new thing that's been added is there's now a place for a pivot chart rather than a pivot table field list on the right I have a pivot chart field list and rather than columns and rows I have Legends and axes areas but the top is still going to be the different column headers for my data the steps

to build the pivot chart are very similar to the steps to build a pivot table I need to add the fields that I want in my chart I'm going to start by taking destination to the axes and then I'm going to take ticket sold and put it on the values area and notice I already have a chart now as I showed you previously I could not chart the data set in my promotion CES sheet it was too wide but but with the pivot chart the pivot table allows it to bridge the gap in the data

again I'm able to focus on the data I want and then chart it and this is super cool let's say that I want to add office to the legend and notice that as I do this my chart updates with this information there is a pivot table behind and if I come in and I update my Pivot chart watch what happens to my pivot table I'm going to take office and drag it down to the axes and take the destination off notice as I do this the chart updates the pivot table also updates now can you

edit a pivot chart not as much as a normal Excel chart but let's look at a few things you can do if you come to the top right hand corner of your pivot chart you'll notice that you have a chart elements button that'll allow you to do things like add data labels and even data tables if you want and also the paintbrush will give you different pivot chart Styles the great thing about these is we're not changing our chart type we're just changing our chart Style and don't forget the color tab that will allow you

to apply a different color palette to your chart the important thing to remember with pivot charts is that they always update to their data so if promotion sales updates I'll need to remember that I'll need to rightclick on either my Pivot chart or my pivot table and refresh so that my Pivot chart and my pivot table are always up to date with their data but these are very important and for many users the reason for their pivot tables is their ability now to chart data that previously they could not hey everyone super important topic sorting

and filtering your data in Excel we also want to talk about sorting and filtering data in a pivot table there's a practice file called sorting and filtering feel free to open it up to follow along now let's just start how would I sort and filter this particular spreadsheet a lot of people will highlight the cells they want to sort and filter by but again the problem with that is you separate your data right now each of these pieces of data is like a record per row I want to keep it together how do I do

that what I like to do is go up to the data ribbon Tab and come in and come to the sort and filter group on the middle right there's a large filter button it looks like a funnel when you click on this if your data has column headers like mine does it automatically adds the auto sorting and filtering arrows now these arrows allow you to both sort and filter let's start with sorting I can go to any column click on the arrow and at the top I have an ascending and a descending sort and notice

how quickly that allows me to keep each row together but sort it now to do a filter I can come in and I'm going to go to the commission Arrow this time click on it and notice down here at the bottom I can just uncheck the item I don't want check the one that I do and it filters just by that item notice the little funnel next to the column header to turn in any of these filters off I can either go to the particular Arrow where there's been filtering applied and say clear filter from

or up on the same data ribbon tab in the sort and filter group there's a clear button that I can click on to turn it off now let's talk for just minute about some of the custom filters you can do say for example on my amount column I only want to see the amounts over $500 I can click on the starting and filtering Arrow come down to number filters and can do what's called a custom auto filter I only want to see amounts that are equal to or above 500 I'm going to click on okay

and then if I want to I can come in and also do an ascending sort and I can see that I'm only seeing when the amount of tickets sold is over 500 to clear this filter I'll come in and click on the clear button again now in this same practice file there are pivot tables if you go down to the sheet called sum of amount by Dez hyphen off it'll open up a pivot table how do I customize the sorting and filtering in my pivot table well first of all in any pivot table if you

have columns and rows you'll see that those same Auto sorting and filtering arrows are available in both the column area and the row area and I can use these to change the sort from an ascending to a descending sort notice now that if I look at my columns they're sorted from again right to left in a descending order or Z to A and if I go to my row labels I can do the same thing again now I'm doing in a descending sort when it comes to filtering those same arrows if I come in I

could filter and say I only want to see anything that begins for example with a letter N notice that what it starts doing is looking for anything with an n in it if I add a wild card which is an asterisk afterwards it deselects everything except in this case again my row items that begin with the letter n and it filters everything in the pivot table by that criteria to turn off the filters in a pivot table I can come right here again to the row or column label where the filter's been applied come in

and say clear filter from and the filtering turns off a final thing that you can use in pivot tables for filtering is an overall filter in my pivot table field list you'll see there's a filters area we haven't yet addressed I can take any field from again my pivot table Fields drag it down to this pivot table filters area and above the top left corner of my pivot table I will see again an overall filter that I can run for my entire pivot table where it allows me to filter for one item inside my entire

pivot table if I'd like to use the filter and have multiple items available to me I can come right here and check off select multiple items I'll check off all the different items I'd like to filter by and then again my pivot table will filter for those specific items to clear again an overall pivot table filter you just go up again to the auto sarting and filtering area come in and just you want to recheck the select all button and you'll notice that then you're back to having the ability to do the filter but have

the filter turned off in your pivot table very powerful tools that can be applied inside a pivot table or as we showed you on the promotion sales sheet just to a normal set of data Final thing how do I turn these arrows off if I'm in a normal data set to turn the auto sorting and filtering arrows off you just need to go back up to the data ribbon tab to the sort and filter button click on the filter and they turn off so there's something that you can turn on when you need them and

turn off when you don't hey everybody let's talk regression analysis now regression analysis is a statistical method used to estimate again the relationship between a dependent variable and independent variables you're basically assessing the strength between the variables and it can be used to model future relationships between them to use this tool you need a data analysis tool pack installed in Excel there's also a practice file for this that you can use called regression analysis our first step is to make sure that we have the data analysis tool pack installed in Excel so for this I'm

going to come into desktop Microsoft Excel go to my file ribbon Tab and come all the way to the bottom left corner to look for the options box remember this is where your defaults for Excel are on the left hand side side of the Excel options box I'm going to come down to add-ins once I go to add-ins I'm going to look at the list of available addin parts of excel these are things that are not installed by default in the platform the top one is a tool called the analysis tool pack it's not the

analysis tool pack dvba just analysis tool pack at the bottom you'll see that you can click on the go button and this is going to take you and have you check off the analysis tool pack I've already installed it so that's why I'm seeing it already checked off in this list but I'm showing you this process so you can make sure that the data analysis option is available for you to use I'm going to click on okay now once I've installed the tool pack what I'm going to see is that when I go to my

data ribbon tab on the far right hand side of the ribbon I'm going to have a new group called analyze on the analyze ribbon there will now be a data analysis button and this is what we will do to run our regression analysis so when I click on this button there are several different statistics iCal analysis tools that come with this but I'm going to come down and select regression and then I'm going to click on okay now for just a moment let's talk about what regression means inside of excel basically what we're doing again

is we're estimating or we're going to see how close and how good the relationship is between two or more variables we'll notice in my spreadsheet that I have the number of flu cases and then the number of associated flu vaccines that were done during the same time period these are two again variables that are different but very closely related so my regression analysis again what it's going to try to do is help me to predict again the relationship between these two variables in the regression analysis box the first thing I need to do is input

my y range that's going to be the number of flu vaccines so I'm going to come in delete what's here and actually insert the correct cells which is C3 through C15 notice the absolute again references it's doing on the cells my X input is going to be the flu cases now the other two boxes I'm going to check off is that I do have labels and then I need to have an output range this is where it will actually list the analysis that it's going to do for me so I can actually click right here

on this scrollup button and click in the cell it's going to be for me cell a18 that's where the analytics report will go after I'm done then I'm going to go ahead and expand the box back out making sure that everything else is set up the way I want it to and I'll click on okay and this is when the magic happens all right now that we can see the output we're going to take a minute and briefly go through so you can understand again there's a lot of Statistics here and if you're not a

statistician this can be a little bit difficult to understand I want to start here in cell a18 with my summary output and if you need to widen some of these columns sometimes when the regression analysis comes in everything's kind of scrunched together so the first thing you need to know is that the multiple R correlation coefficient is going to be something that measures the strength of a linear relationship between two variables the larger the absolute value the stronger the relationship so what we're seeing here is anything that is closer to a positive one is going

to be a more positive relationship if it's a negative one or closer to a negative one it means it's a negative strength of relationship zero means there's no correlation or relationship at all and when you come down you're going to see the R square this signifies a coefficient of determination which shows the goodness of the fit and notice that ours is about 96 which is an excellent fit this means in other words that 96% of our dependent variables our y values are explained by the independent values are X values so this means that really with

the adjusted r squared that we have a very good correlation and relationship between the information that we're again doing statistical analyses on now going down a little bit further in the report you're going to see that you have an A NOA section down in cell a27 depending on where you've put your again output a NOA stands for analysis of variance and it gives you information about the levels of variability in your regression model so you're going to see here that there are different pieces of information that are shared DF is the number of degrees of

freedom associated with your sources of variance SS is going to be the sum of the squares Ms is going to be the mean square and F is going to be the F static or F test for full null hypotheses then you're going to see here that this significance F and let me spread this out a little bit so it's easier to see is going to be the P value of the F again a lot of Statistics involved here now you can also graph this information and there are some scatter plot charts inside of excel that

can help you to chart this information so back up in my data I'm going to highlight cells A3 all the way down to C15 I'm going to go up to my insert ribbon tab come to my charts group now right here there is a scatter chart or bubble chart option that I can click on you'll see that there are different options I'm going to hover over a few of these so you can kind of see which ones might be better for your data that you have and notice that as I go through these you'll see

that a lot of them are going to allow you to see the information plotted together I'm going to pick this very first one which is the scatter chart right and I can see that it's going to again show me the correlation between these two pieces of information in my chart keep in mind that you can change the chart type at any time for this I'm going to go up to the Chart design ribbon come to change chart type and say for example I only want to see maybe the flu vaccine side of my information I

can do that and even more than that if I pick a point on the chart I can right click on a given point of the chart and I can add a trend line and that will also help me to see what that trend for the flu cases is in my chart so again regression analysis is very complicated it requires a lot of statistical knowledge but in its basic core we're trying to see if two again variables have some sort of relatability to each other and the strength of that relatability and with the regression analysis we

can do that so please try it out hi everyone what if you just have a spreadsheet and you want to analyze it you're not sure where to start there is another tool that you can use called the analyze data button it's available in Microsoft 365 and Excel desktop it's a fair new tool but the idea is it actually will help suggest questions to help you analyze your data in different ways so you'll see there's a practice file for this called analyze table feel free to use it I'm going to zoom in just a little bit

to make it easier to see my data then what we're going to do is we're going to come in and highlight the data which is all the cells A1 down to h19 on my home ribbon tab I'm going to come to the far right hand side to the analysis group and click on the the analyze data button a new window opens up called analyze data it does all kinds of things it suggests pivot tables I could insert it suggests charts that I might want and again this is just Based on data that it's finding inside

my table that I have you'll see at the bottom once it runs out of room it has 22 additional options that it could create for me now at the top it even suggests possible questions I could ask and it will create pivot tables for me based on that information I can come in and also type in a question of my own like I could say average of income hit enter and it will actually show that for me or to me and if I click right here on insert pivot table it inserts a new sheet for

me and puts that information on the sheet if there are other suggestions in the analyze table box that you'd like to use you can clear a previous search find one of their suggestions for example right here I have an income chart I can come down and say insert chart it inserts it into my data and allows me to use it so the idea is that I can take these analyze data suggestions and actually use them to help me again understand my data and it's built right into the platform and all I have to do is

click on one of the options and it will put it again into my spreadsheet for me so I don't have to come up with these ideas they're created for me just by again coming into this tab now sometimes they're useful sometimes they're not but the more you play with this the more options it gets because it actually watches the types of questions that you ask it and it actually learns so the more you use it the better it gets sometimes the information isn't so good at the beginning but it will get better with time notice

up here at the top there's a little arrow that will allow you to move it you can actually undock it and bring it into your spreadsheet if it's easier to work with it in place and when you're done with it click on the X and it will close hey everybody let's say you want to create a stock chart or a stock graph these of course are used to display the trend of a Stock's price over time some of the values that can be used in these charts include opening price closing price high low volume they're

very beneficial for visualizing stock price trends and volatility over time and they're really pretty easy to make there's a practice file for this called called stock chart feel free to use it the first step is going to go ahead and select my data sells A3 down to D21 then I'm going to go up to my insert ribbon Tab and come into the charts group you'll see kind of on the top right or on the right hand side that you have again the waterfall funnel stock service and radar chart options I'm going to click on this

there is actually a again a group of charts called stock charts inside and you can hover over these to kind of get a feel for for what they'll look like with your data with the data that I have the first one which is high low close is the one that I'm going to use because those of course are columns b c and d in my data I'm going to select them and then again I suggest coming to the edges of the chart and dragging them out a little bit to make them bigger now with this

chart there's some additional things you can also add to help format it and I also suggest playing with some of the different elements that you can add for example I would like to add a trend line to the Chart so I'm going to click on the chart so it's selected and come to the top right or left button and look for the green plus sign this will take you into the chart elements box and one of the things you can do is add a trend line notice it will let you pick what series high low

or close to do it for I'm going to do it for the high now I can also actually format this trend line by clicking on it and if I come up to the format ribbon tab I can do things for example I change the color of the trend line I can also come in and make it wider by again updating the style of the trend line there are also going to be different chart Styles so if you click on the chart and come into chart design you'll see that there are different chart Styles and some

of these are going to represent the chart data in a little bit better way easier to see and might make again your stock chart look a little bit more visually appealing and then don't forget that you can also come in and update the color scheme of the chart if you want to do that it can also help make it a little bit easier to see some of the data points on your chart but creating a stock chart is really fairly easy to do so give it a try just ensure that you have the right kind

of information hi everyone let's talk about purchase price variance now this is important because it leads us into building our own pricing variance tables and ppv or purchase price variance is one of the beginning topics when it comes to this it's also involved with ppv financing this is the difference between the purchase price and the actual cost for a good or service and it's called purchase price variance analysis it's important because it measures how much a company spends on goods and services and again this really affects the bottom line and profitability of an organization it's

also critical to inventory management because you want to purchase items at a good amount and not have too much or too little in your stock so how do you calculate this well we have a practice file and there's an example of the form formula in the practice file the practice file is called creating a pricing variance table and you'll see that in a black box kind of by cell A9 you'll see how to do ppv you're going to take your standard purchase price for an item subtract it from your actual purchase price and then divide

it by quantity so let's try it out I'm going to click and sell E3 and I'm going to hit an equal sign now I want the first part of this formula to happen first so I need to put it in parentheses I'm going to take my again standard purchase price subtract it from my actual purchase price all in parentheses then this will be divided by the quantity and I'll hit enter now if it's not formatted as a percentage I should say come up and make sure you click on the percentage button this is a Formula

that you can drag down and again you can see that where you have a positive price variance it's a positive value where you have a negative price variance it's a negative price variance and you want it to be positive because this shows how the company is saving money there are a lot more facets to this topic and other parts of pricing variance that you can calculate this is just a beginning example for you to try out but again a very important topic to help you understand the bottom line and profitability of your organization hi everyone

thanks again for joining us for this part three of our Microsoft Excel for Finance and Accounting in this course we've explored lots of different ways to create charts like bar charts combo charts we even looked at how to create a football field chart and a stock chart in addition to that we've explored creating pivot tables pivot charts how to create regression analysis in a spreadsheet how to use find insights create a trading chart and the beginnings of a pricing variance table now please join us for our Microsoft Excel for Finance and Accounting part four course

where we're going to go in and look at how to create create your own income statements set up balance sheets and even do income statement forecasts welcome everyone to learn it's Microsoft Excel for Finance and Accounting part four my name is Elsa Smith and I will be your instructor for this course I come to you with over 25 years of experience teaching people like you how to maximize their skills and platforms like Microsoft Excel now in this part four of the course we're going to take many of the things we've learned in the first three

portions of the the course and build our own financial and accounting statements with those skills we're going to start by setting up income statements and balance sheets we're also going to look at how to create income statement forecasts and even create statement cash flow statements and debt schedules so join me let's get started 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 ow 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 Investments 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 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 going to come up to the search bar and actually look for either the stock initials or again the name of a company we're going to

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 the top and look for historical data this will take us to again more data for a specific period of time right here you can see that mine's doing one year I'd like to change this to two years

and I can do that just by clicking right here coming over and selecting again an additional period of time that will increase it to two years worth of data I'll update that and then when I'm ready I'm going to come in and download notice one important thing to remember when you're calculating HPR is you have to select a period of time this will allow you to do daily weekly or monthly on this site 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 number one 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 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 Clos 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 that in parenthesis and then I'm going to use the B slash 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 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 five 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 five this will take them and turn them into again a percentage rather than

a decimal and it makes 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 hpr4 very simple function use an 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 three I'm going to come in and click in cell G2 and start my average function I just need

to type in an a pool 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 control shift down arrow to include the entire range of cells that I'll be be averaging I don't need the closing parentheses 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 increased 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 in 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 rightclick 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 Rouse 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 geomine 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 and the first thing I 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 goo you'll see again Microsoft excel's Formula Auto complete lists come up go mean 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 geom mean 4 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 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 EX exer 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 sheetss and cash flow 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 p&l 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 statements that we'll 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 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 noncash 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- 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 major categories ra 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 hardcoded 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 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 statement 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 oldfashioned Microsoft Excel basic formulas for gross price we need or 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 I'm going to hit my equal sign and go down and take my first new customers which is sell in this case B6 we're going to use the

asterisk 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 growth Revenue multiplied by our refunds down in cell a18 which is 5% but a couple 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 and sell 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 cellb 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 asteris and come in and this is going to be down in again my refunds amount which is

the 5% we can see that cell B8 very simple formula I'm going to hit enter it calculates notice if I don't want the decim deal 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'm 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 into cell B9 do an equ 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 hyphen part 4 and again I've 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 sell 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 down below under my assumptions for my product percentage this is again the percentage of my materials cost then I'm going to hit enter and once I get this calculated I can copy this formula and the great thing about that 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 spread sheet 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 account so we're going to come in and do a good old sum function again I'm going to click ins B15 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 account 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 so we're just going to come in to sell a815 and say this is our total cogs and again I'm going to 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 cost of goods sold so I'm going to go up and click in sell B9 and subtract that using the hyphen from my total C or 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 and sell B8 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 we 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 and sell B21 for this now what we're going to do is take net revenue and multiply it by our assumption for the percent of cost for our 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 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 asteris 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 asteris to get the Assumption amount I'm going to come down to my operating expenses and click on the assumed percentage for our HR cost 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 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 Auto Complete 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 and 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 the appreciation once we get our balance sheet and capex schedule 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 opics so I'm going to do an equal sign go up and click and sell B17 again do a hyphen and subtract that from my total Opex which to start with will be cell B25 then I'm going to go ahead and again drag

this over to the rate 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 parenthesis 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 7 for this activity if you're following along with me in the practice files I'm going to click in sell B 31 and exactly why is net interest before taxes important

well you 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 sell b31 I'm going to hit my equals 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 all right now we're ready to actually calculate our again taxes remembering that because some of 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 I actually want

to put in a few additional blank rows here so feel free to insert a few blank rows but under my ni I'm going to go down one blank row and then come in and do taxes again it will be in bold then I'm going to come in and remember that your taxes are just going to be your net interest before taxes times your assumed tax rate which is down under the assumption section so here I'm going to do an equal sign go up and click on my ni and then come down and multiply that by

my assumed tax rate of 22% hit enter and then I'm going to go ahead and drag that over to copy that formula to complete the calculation for our assumed taxes all right we're almost ready to wrap up most of our income statement we need to add a few more things come into the practice file called three statement model hyphen part 8 if you want to follow along with exactly what I'm doing I'm coming down to the middle and clicking in sell b35 I want to calculate my net income now net income is just going to

be net income before taxes minus taxes very simple subtraction problem so I'm clicking in cell b35 I'm going to do an equal sign and take my ni or my net interest before taxes and subtract that by my tax amount now to get my net income percent I need to take my net income and divide that by my net r Revenue so again I'm going to click in cellb 36 do an equal sign I'm going to come up and click in my net income and then I'm going to divide that by going all the way up

to my net revenue so again we're looking at net income divided by net revenue and net revenue is up in sell B9 and then I'm going to go ahead and hit enter now again it will come out as a percentage so if you don't already have the sell formatted as a percent go up and click on your percent symbol so that you see that percentage this again can be drug over on each row so that that's calculated for you the final thing we're going to look at is our eitaa now what is ebit du ebit

du is a critical private Equity metric and it involves looking at your operating income with your Plus or depreciation now we're ready to calculate the ebit DU this is an important cash flow proxy so what we're going to do is click in cell b38 do an equal sign this is simp simply taking operating income and adding it to your depreciation now our depreciation isn't filled in yet but again we're going to get it ready so that once it is calculated we'll be able to complete our income statement so I'm going to go up and click

in cell B27 where I see my operating income this will be using the plus sign I will add this to my depreciation which is in cell B24 and again I can drag this over now don't forget that you can finish off by adding more formatting to your income statement like putting my net income in bold it's also sometimes not a bad idea to put some of those percents like your ni percent and italics and don't forget that you've got awesome keyboard shortcuts that you can use for that like your control I your control B those

keyboard shortcuts will save a tremendous amount of time if you start using Excel a lot in a career next we're going to start building some of the other Finance statements that are part of the three statement model hi everyone we're now going to set up a depreciation schedule for this activity you need to have the practice file called capex and depreciation schedule hyphen part one open feel free to use it to follow along now if you go to the capex and depreciation schedule sheet you'll see that we've set it up with some information that I

want to briefly describe in column A you're going to see that we will show the different assets that we will be tracking depreciation for these include for example inell A4 laptops a custom software application in A5 and also some new ovens now in column B we have useful life this is the number of years that we think these items will be used in the organization and we have four different years here that we're going to be using for our depreciation now let's review quickly what depreciation is it's the cost of an asset over its useful

lifetime it includes wear and tear you'll also notice that down in cell a13 that our total depreciation includes a term called amortization and amortization is basically where you're tracking the depreciation of intellectual assets not a physical asset so it is part of depreciation so let's go ahead and get started I'm going to start by clicking in cell C10 where I want to start by tracking my laptops depreciation to do this we're going to do an equal sign and a simple division problem we're going to take C4 which we're going to use F4 to make it

into an absolute reference or locked cell and divide that by the number of years that we think it will be in use which is cell B4 so five years and we're going to hit enter now for the four years that we're tracking depreciation this is the same depreciation we'll take each year so we're just going to left drag that over to the right and we'll see our depreciation next thing we want to do is come in and track our depreciation for our custom software application this is a little bit different because notice that we have

different amounts for three of the years that we're tracking depreciation so so let's start by going to cell C11 clicking there and doing our first Year's depreciation again very similar to what we did with the laptops and equal sign we're going to click and sell C5 again we'll use F4 we'll divide that again by the three years that we're hoping to keep that particular asset now we're going to come over and I can copy this formula but notice it includes an additional amount in year two so for this particular one we need to add on

that additional amount so what we're going to do is open up the formula we're going to add a plus sign and then we're going to again account for that additional amount so I'm going to do D5 again making it into an absolute reference dividing that by the three years again another absolute reference so that when we copy the formula over it accounts for that adjustment now we come to the third year we now have a third Year's worth that we have to account for so we're going to also include it in the formula so I'm

again going to do the same process adding on a plus sign at the end of the formula coming in and this time I'm going to go up to sell E10 or I should say E5 apologies making it an absolute reference dividing It Again by the three years which will also be an absolute reference now when we get to the fourth year what's happened is the first year all of it is already accounted for so what I'm going to do here in this fourth year is I'm going to go ahead and just get rid of the

first Year's depreciation and just include year 2 and three in the depreciation and so you'll see here that when I come into the formula I just want to make sure that the first Year's worth of depreciation is gone and I'm just going to delete it one more time so you can see how it's gone out of the formula bar and now it will update that depreciation next we need to depreciate the new ovens so I'm going to click in sell E12 for this hit my equals sign and again it's the same process I'm going to

go up and I'm going to take cell E6 making it an absolute reference and dividing that by the five years that we are going to be having the ovens and again this is a Formula that I can drag over to the right then I need to go through and in row 13 I'm going to again just sum my total DNA or my depreciation and amortization so I can either use Auto sum for this or I can use any of the other keyboard shortcuts that we've uh gotten so used to using but you can see here

how it's just moving each of those formulas over for me now the next thing we're going to do is tie our now totaled DNA back to our balance sheet so now that our depreciation is totaled we're going to take it over to the income statement to do this I'm going to click on the income statement sheet I'm going to go to cell B24 where I have my first Year's worth of depreciation and I have that over on my capex and depreciation schedule so if I go ahead and hit an equal sign I can jump back

over to the capex and depreciation schedule and create a linked cell to cell C13 which is my year run depreciation and because this is a Formula I can just drag it over and then of course we want to change the formatting so that it matches the other cells that are above this and then also I'm going to turn off the crazy background color that was just a reminder so we knew we need to come back and fix it now that we have our depreciation you'll notice that our operating income is calculating correctly and also down

in row 38 are e bit du but we still are going to need to go to the balance sheet to continue the process so that's what we'll be doing next welcome back we are ready to forecast a balance sheet and before we do that I want to look at a basic balance sheet and see the different pieces that go into it for this activity you need to be in the practice file called balance sheet hyphen part one remember there's a link to all the exercise files down in the description now also I'm down on the

actual sheet called balance sheet for this activity and we have preset it up to help save you sometime so let's take a minute and actually explore what we're seeing here first of all at the top notice that this is for the first year that we've been in business and you'll see that we have cash and accounts receivable cash is again just the money that's coming into the business accounts receivable is basically going to be money that people owe you for example invoices that are still incoming this is total for your current Assets Now we just

got done doing a lesson on calculating depreciation so here you can see this is going to be physical or assets that the company owns along with cell B10 which is our accumulated depreciation notice those two are being again calculated for a total net fixed asset and then we have all our assets totaled in cell B13 Coming Down Under that you're going to have your liabilities this is the money that the company owes to someone else so first of all we have our accounts payable this is going to include money that we owe that can be

paid within less than a year also deferred revenue is a little bit different sell B18 this is going to be where you've sold a service but haven't done it yet so it could be like a down payment but it can't be on the income statement because it needs to be dated when it actually happens so we're including it in deferred revenue then we come down and have long-term debt this could be loans all those different things that we owe then we have our total liabilities in cell b23 coming down we have the equity section this

is a little bit different if you haven't owned a company but if people have invested in your company you'll often have common stock really it doesn't provide a very useful number it's usually based on the original share price that people pay when they come into the company but it is something that's included now retained earnings are important this is your net income from your balance sheet remember in this example we're using some of the numbers are skewed but that's usually what your retained earnings will be then you'll see that this is going to be total

to give you your total shareholders Equity now down in cell b31 we have what is called our liability and shareholders Equity it's really really important to understand that what you're trying to do here is figure out your total assets total assets are going to be your liabilities plus your Equity that should equal your total assets so notice that cell b113 and cell b31 need to equal each other if they do not it means you have a mistake and it's super important that you fix it down in cell b33 it's important to always include a balance

check and your balance check what it does is it takes your total assets and subtracts it from your total liabilities and shareholders Equity to ensure that it's zero because they need to be the same so the balance check is just to make sure that your assets are correct all right now we're ready to start forecasting on our balance sheet so go to the next lesson hi everyone we're ready to do a balance sheet forecast now for this particular lesson there is a practice file called balance sheet hyphen forecast please feel free to open it up

and follow along now we've done some basic setup for you we're on the balance sheet of the workbook and if you go down to row 35 you'll see that there are some assumptions that we're going to use in our forecast the first one is in cell a37 our net revenue then we also have accounts receivable percent of Revenue accounts payable percent of Revenue and then deferred revenue generally speaking an organization is always going to owe or someone will owe you so these are fairly easy calculations to create let's also notice that up in columns CDE

E and F I've set up that we're going to be doing some four-year forecasts let's go ahead and start with our first one for net revenue for this I'm going to click down in row 37 in sell c37 and I need to get this off of my income statement sheet I'm actually going to be creating a linking formula or a linking cell to do that we're going to do an equal sign jump over to the income statement sheet and go down to cell B9 this is our first year net revenue and it's all we need

we're going to hit enter and it will bring it over now notice I've already formatted it as a currency with no decimal places and then I'm going to drag this over to the other three cells and it will bring over the net revenue for each cell you can see it up and our formula bar and we're ready to head to our next step now we're ready to calculate our accounts receivable to do this we need to take our net revenue and multiply it by our accounts receivable percent of Revenue this is going to go up

in this up in the spreadsheet so let's start where the formula begins we're going to go up and actually kick click next to our accounts receivable in cell C6 we're going to do an equal sign to begin the formula and then go down to our assumptions we'll take our net revenue for year 1 and multiply that by of course our accounts receivable percent and hit enter and it will calculate up above we can see it here then we can go ahead and just copy this formula over to the other three years you'll see it calculate

and then please remember that in cell B7 that's a sum function we do want this to come down to the other three years so we'll copy the formula over let's go ahead and fix the formatting just a little bit we're going to remove the dollar signs so you can just take off the dollar signs by selecting the no dollar signs and then also remove the decimals if you want so that we don't have all the extra decimals on everything the other thing that I want to do as well is I would also like to make

sure that the values are the same size so I'm going to re-update that and then also let's make sure we still have the decimals in the formatting that way then the other thing we're going to do here is we're going to come in and we're going to put a border between the accounts receivable and the total current assets and to do that I can just highlight the values up above and click on the bottom border button just to get my formatting the way I want it to be next we need to do our accounts payable

and the process is very similar we're going to go up click in the cell that we want to start the formula in it happens to be cell c17 do an equal sign and then go down get our net revenue again this time it will be multiplied by our accounts payable and then we're going to go ahead and hit enter and finish that formula off we'll just double check it to make sure that it's correct again you need your equal sign then we're going to go ahead and select our net revenue and multiply that by our

accounts payable percentage now we're going to copy that formula over like we've done previously we'll do the same thing with deferred revenue and sell C8 equals sign go down and get net revenue get that first Year's assumption for our deferred revenue hit enter and again we're going to copy that over and then we also want to not have the dollar sign so I'm actually just going to go up and copy the format from my accounts receivable it'll save me having to do the formatting and then I might have an extra border here that I don't

need if I need to get rid of that I can I'll just go up and select the cells remember you can go to your borders button and just say no border it'll turn that off and then I'm also going to drag over my formula again just to make sure that it's totaling that formula for me and if I don't need this in bold I can come in and just turn the Bold off and now we have started getting the basic forecast for our balance sheet there's still more to come so go ahead and get ready

for the next lesson hi everyone we're ready to calculate our fixed assets and accumulated depreciation in our balance sheet forecast for this there is a practice file called balance sheet hyphen forecast part two that you're more than welcome to use as you follow along I'm going to go up and click in sell C9 this is where I will be calculating my fixed Assets in my balance sheet forecast for this what I basically need to do is take my assets from last year and add them to my total capex this will be found on the capex

and depreciation schedule so we're going to start with an equal sign and then of course I'm going to take my fixed assets and I'm going to add those by going down to the capex and depreciation schedule sheet and clicking in my total capex for year 1 then of course I can take this form and drag it over and then don't be afraid to copy the format of the values up above that you've already calculated just to get rid of the dollar signs now for accumulated depreciation we need to remember that it's negative because it shows

something dropping in value and for this what I need to do is take my current accumulated depreciation that's in cell B10 and I'm going to subtract that from my total DNA on the capex and depreciation schedule so again we're going to do an equal sign start with the amount that's in B10 we're going to do a subtraction and go back to the capex and depreciation schedule sheet and select our total DNA which is our depreciation and amortization that's in cell C13 and hit enter now of course it's in parentheses because it's negative I'm going to

copy this over and then of course if I want to make sure it has a similar format to what's above I can go up and copy that format down then the other thing I want to do is now take I can calculate my net Assets Now and that of course that formula is in cell B1 so we'll also drag it over and notice how this amount staying pretty steady or even in some cases flat and that's just again because things are losing value over time let's finish calculating total assets all I need to do is

click in the cell I am going to update the format just a little bit we actually do not need that bright format on there so I'm just going to come in and turn the fill color off and then of course I can drag this over to the right and it will finish calculating now the other thing I'm going to do is take off some of the dollar signs that I'm seeing on some of these fields because we actually don't need them because these of course are all assumptions so I'm just going to go ahead and

take off that formatting so that it's not showing for something that is not a real value yet it's an assumption then the other thing to note right now is that cash cannot actually be calculated yet up in row five because we have not yet calculated our cash flow statement all these statements need to be done together so I'm just going to format that so we don't forget about it as we're moving forward in doing this balance sheet forecast hi everyone we're ready to work with debt inside our balance sheet forecast for this I have the

practice file balance sheet hyphen forecast part three open feel free to use it to follow along now if you notice up in our spreadsheet in cell B21 we have an assumption of our total debt but of course we also would like to have a forecast so down below we've set up a few additional things that I want you to see in the balance sheet of the workbook when you go down you'll see that we have net borrowing this would be new debt that we're taking on we also have debt payments interest rate and interest payments

now we have put in two assumptions here notice that in cell c42 we don't have any new debt in year one but we do have $500,000 in debt payments and then in cell d42 we have a million dollar in year two of new debt that we're taking on now to help us calculate for year 1 we're going to go up to cell c21 and we're going to hit an equal sign we're going to start by taking our longterm debt and we're going to go ahead and add this to new debt well for year one that's

zero but we're still going to include it in the formula this will all be subtracted from our debt payments that are located in celc 43 then we're going to hit enter and you'll see here that we have paid down our debt by about $500,000 which is exactly what we want to do now the next thing we need to do is realize that it's very common for us to have more debt as time goes by so down here we're going to click and sell d43 and our debt is going to increase to 700,000 and I'm going

to go ahead and just drag that over so that it's increased for each of those years then what I want to do is go back up to cell c21 where we put in our first Year's debt we're going to drag it over you can see how that is decreasing over time and maybe going up a little bit in year two just because we've taken on more debt now this will allow me to go down to sell b23 now that I've calculated my long-term debt I can go ahead and take my total liabilities drag that over

and it will now be calculated I'm also going to take the dollar signs off of that because of course we don't need it there and I can use the formatting for something up above to remove that but I also want to make sure that I maintain the border on the top and bottom of that so I'll fix that in just a minute just remember the formatting that you use means different things so it's important that you maintain the formatting that you've seen us use throughout the different lessons the next thing we're going to look at

is our common stock okay let's look at our common stock we're not planning on making any changes to this or any changes to our organizational ownership so we're just going to take cell if you notice cell B27 we're just going to reference it over with a formula and then we can just drag that over now retained earnings this is very very important in fact it's so important we're going to highlight it just a little bit so it doesn't get forgotten what we need to do here is take last year's retains earnings and add that to

net income from our balance sheet if you don't do this your balance sheet doesn't work so we're going to go ahead and click and cell c28 do an equal sign then we're going to take cell b28 we're going to to do a plus sign and we're going to jump over to our income statement and go down to our net income it's about sell b35 as I recall and you'll see that it's here inside the spreadsheet and hit enter again this is really critical I'm going to change the format here in just a minute because we

definitely don't want it to have that yellow background on it but something very critical that you must remember to do correctly so now we need to figure out some of the debt for this we're going to go down to our interest rate which is currently not filled in but you know reasonable interest rate down in row 45 we'll say is 7% just make sure that you get it formatted as an interest rate we can copy it over and again I'm having trouble with mine so I'll fix the format then to calculate the interest payments we

just need to take our interest rate and multiply it by our first year of debt so I'm going to do an equal sign get my interest rate which is in cell c44 multiply that by my first year debt you can see it's up in cell c21 and then of course I can go ahead and drag that over to the right to finish calculating what that interest payment debt looks like so let's go ahead and get this interest payment tied into the income statement I do want to come in and just update the format on the

interest payment because it's not an assumption there it's been calculated then I'm going to go to my income statement sheet and I'm going to click in sell b29 you'll notice it has the Highlight so we can't forget forget about it do an equal sign and then link that back to the interest payment for year one that we just calculated then I can go ahead and copy that format over or I should say copy the cell over so that we link it then let's get rid of the yellow format there just so that it's turned off

great news though we now have our interest payment it's totally done on the income statement okay so to complete we need to go back to the balance sheet and finish calc calculating our returned earnings so for this I just need to come in and do the sum function for the cells above and I can copy this over or I can just go up and auto Summit either one and then drag it over and just remember that this retained earnings is basically going to be your earnings over time right so we can see here how it

helps to calculate it out and get it equal and then of course don't forget your formatting right we always want to have that similar formatting going on now the other thing to remember is that we haven't yet calculated our cash up in row five until we do our cash flow statement this balance sheet will not equal itself so you need to remember that it's still not going to calculate your balance check will not be there yet that's what we're going to do in the next lesson hi everyone we're ready to set up our cash flow

statement I'm in a new practice file called cash flows hyphen part one please feel free to use it and follow along there's a new sheet in the workbook called cash flow statement we're going to use it to set up our cash flow statement there are a few things that have already been prepared for you in the spreadsheet and I want to actually describe for you what they are you'll see at the top we have our four different years and then on the left in column A we have some different categories net income has been linked

in from the income statement then you have operating activities investment activities and financing activities we're going to start up in row six under depreciation now our depreciation is actually over on the capex and depreciation schedule we just need to link it in so I'm going to do an equal sign in cell B6 click on the capex and depreciation schedule sheet and locate the total DNA or depreciation and amortization I'm going to click in year one hit enter and it will bring that amount over then I'm going to go ahead and just drag it over to

years 2 three and four the next thing is going to be our change in accounts receivable now for this we need to go to the balance sheet we're basically going to take what was last year's accounts receivable and subtract it from this year's so again we're going to click and sell B7 this time do an equal sign go to our balance sheet and find our accounts receivable it's up towards the top and again what we want to do here is take last year's and subtract it from this year's so I'm going to click in cell

B6 and subtract that from my year one which is this current year which is C6 then I'll hit enter then I'm going to copy this amount over for years 2 three and four now it's very common for this amount to be negative depending on the amount and the reason that is is that people are going to owe us more money as the years's commence now let's go down and look at our change in AP or accounts receivable now for our change in accounts receivable or I should say accounts payable we're going to go down to

sell B8 now this is the opposite of what we just did with our accounts receivable when we look for accounts payable we're going to take this year and subtract it from the previous year so I'm going to do an equal sign and head back to the balance sheet again and this time we look for our AP so again I want to take this year and subtract it from the previous amount so in that case it's going to be c17 minus B17 I also want you to take note while I'm still on this sheet prior to

finishing the formula that if we look at Row 18 it's deferred revenue and that's actually the next thing we need in our cash flow sheet but before we do that let's copy this formula over and you'll notice that as opposed to the change in AR it's more common for change in AP to be positive rather than negative now for our change in the next area down what we want to do is actually just copy the formula because as I previously showed you on the balance sheet if you look at Row 18 deferred revenue it's already

in the balance sheet right below the AP formula we just did so we can actually just copy the formula down and it works great just remember that you don't want to lose your border below row 9 so we'll go ahead and add that border back on okay to set up our operating cash flow we need to add all these things together so we're going to start by clicking in cell B10 of the cash flow statement sheet do an equal sign we need to start with our net income and then we're going to add that to

the total of all of our operating activities now to make this formula a little bit easier I'm going to use a sum function for that portion of the formula and again we just need to get the opening parentheses drag it down hit enter it will remind me that I need a closing parentheses which is great and remember that when we look at our operating cache this is basically how much the company is making we can drag this formula over and now we've completed the first step of setting up our cash flows we're going to continue

figuring out our cash flow statement for this in a new practice file called cash flows hyphen part two we're going to be down under cell a12 we're going to be bringing in our capex information now just remember this is basically investments into fixed assets and it's going to be available to us on our capex and depreciation schedule which is on that sheet so all I need to do is hit an equal sign in cell B13 and then I'm going to click on my capex and depreciation schedule go to cell C7 which is actually where that

amount is is for year one and Link it in then of course I can make sure that I format it the way I want it to be formatted so just to make sure that you can fix the format on the first one and then when you drag that over it's going to be fixed for the subsequent three years now you may have noticed that I don't have anything in cell e in this case 13 and that's okay because maybe we didn't have any investing activities for capex in that year now we're going to go down

after you've calculated capex you're actually going to be able to calculate a category called free cash flow and free cash flow is basically going to be cash from operations minus any assets so let's get this category typed in and then to calculate this I'm going to go over to cell B15 do an equal sign I need to take my first year operating cash flow and I'm going to subtract that from my capex and that'll give me a total and again this is something that can be positive or negative depending on you know what's been going

on with the business but then we'll copy this over and then notice that I'm also going to make sure it's formatted as a currency in this case which it is let's talk about the finishing activities inside our cash flow statement I have a new practice file called cash flows hyphen part 3 open feel free to use it to follow along now you'll notice that down in cell a17 under the financing activities we've set up three new categories the first one is called debt repayment this is of course cash that goes away followed by net borrowings

this is an increase in our cash flow and then you have ncf which is our net cash flow from financing now let's start with our debt repayment we're going to go ahead and click here we need this to be a negative value and it is going to come off the balance sheet we'll link to it so after we click we'll hit the equal sign go to our balance sheet come down towards the bottom under our assumption section we're going to click in sell c43 our first year of debt payments but also up in the formula

bar I'm going to go ahead and put a negative in front of that to make it negative when it comes over to my cash flow statement then of course I'll drag that over for years one through for I'll get filled in now for net borrowing we're going to get that again off of our balance sheet it's already there so all I need to do is click in sell in this case b19 do an equal sign jump over to my balance sheet you'll see that we actually don't have net borrowing for all the years but we

do have it for at least one we're going to hit that enter key and then drag that formula over so that it gets brought over to the other cells and then again make sure that you have a similar format on all those cells so that you can see what it represents and just remember that net borrowing represents any new cash that came into the balance sheet our next C category is to calculate the net cash flow from financing for this we just need to Total the two amounts above so I am going to come in

and put in a border so that we can see that this is a total and then we're just going to go ahead and sum the two amounts in the CES above for debt repayment and also net borrowings once I have that total then I can go ahead and just drag it over to the other cells and we can see that it's all ready to go the next thing we want to calculate is a category called net cash flow so I'm going to come down and add this to the spreadsheet it's not actually there right now

so just type it into about cell a22 and you can format it after it's typed if it doesn't automatically update now basically what this is is you're looking at the total cash that's come into your business or that's produced in that year so what I'm going to do is do a sum function where I add up my free cash flow with in this case my ncf from financing now this can be a negative amount because it's our first year in business but then it can turn positive as more time goes by it's time for Our

Moment of Truth we need to now tie our cash flow statement to the balance sheet for this what we need to do is take our basic first year cash flow and we're going to tie it to the balance sheet so to do that what I'd like you to do is go ahead and go to your balance sheet and again we're still trying to calculate our cash that's actually up in row five we're going to do an equal sign and get the previous year's again cach and then we're going to add that go back to our

cash flow statement and get our net cash flow that will give us again our cash flow for the balance sheet this we then going to copy over now the really important thing at this point is you want to make sure that your balance sheet uh again balances this is where we're going to come down to that balance check in row 33 and this is what gives everybody a attack if it doesn't work to make sure that the balance check is zero and this is going to mean that our um again assets equal liabilities plus uh

equity in every year and this is the really important thing to check for now at this point you can go through and clear out any of this formatting that doesn't look right inside your balance sheet and your cash flow statement because you're ready to go hi everyone I am in the practice file called three statement model hyphen fin check off now as we get ready to wrap up the making of a three statement model there are a few things that I want to remind you about notice that I've gone through the different sheets in our

income statement and made sure that in this statement model I've turned off the extra highlighting just remember that highlighting is a way to remind yourself that as you're tying these different statements together that you don't forget anything so I've gone through the balance sheet and the income statement and the capex and make sure those are turned turned off now the sheet that I'm going to focus on while we go through this is the final check this is normally not going to be in your three statement model but these are some things to think about as

you're getting ready to wrap up the big question always comes in why doesn't my balance sheet balance and if you're remember in the previous lesson if you go back to the balance sheet and look at row 33 we can see that our balance check is balancing out this is the big thing that you want to have happen now back on the final check sheet you're going to notice some common questions that can make it so the balance sheet doesn't work out the first one is is your net income your pnl linked to your retained earnings

also is your cash flow linked to your cash these are common areas that can cause the balance sheet to not balance this third one is probably the biggest one have you included all your balance sheet accounts in the cash flow statement if you've forgotten an account like your AP for example in your cash flows your balance sheet is not going to balance and keep in mind different kinds of organizations have very different balance sheets if you have a longer balance sheet you're going to have a longer cash flow statement finally are your historic Financial accounts

balancing they also play into your balance sheet balancing all these things come together and again we can see how amazing Excel is to allow us to have this capability of building such a customized through statement model hi everyone thank you so much for joining us for this Microsoft Excel for finance an accounting course part four in this course we've explored what a three statement financial model is we've also looked at how to set up the different parts of that model including an income statement a capex and depreciation schedule a balance sheet with balance sheet forcast

and don't forget the cash flow statement thank you so much for joining us for this learn it course thanks for watching to earn certificates and watch our courses without ads check out learn it anytime.com [Music]

Excel for Finance and Accounting Full Course Tutorial (3 Hours)