Dominate Excel With These 10 Accounting Formulas! + FREE BONUS

for many years accounting and Excel go hand in hand what makes up the most of these applications that is the accounting formulas hi this is Randy with Excel for Freelancers and today I'm going to go over the 10 best accounting formulas in Excel and we're going to see how those formulas work in an actual application I cannot wait so let's get started all right thank you so much for joining me I've got a really fantastic training based on demand your questions comments so thank you for them today is the top 10 accounting formulas that everybody needs to know and I'm going to be sharing those with you not only am I going to be sharing those with you I'm going to be sharing those with you inside a complete accounting application so you see how they actually work inside the real life and I've got a special bonus for you so make sure you stick around till the end we're going to get started right away on this all right what I do want to share with you of course is that these trainings are every single week I bring these to you every single Tuesday for these comprehensive application development and Saturday is for VBA beginners make sure you do get subscribed don't forget to click the like button comment below I respond to each and every comment every single week I also do a updated training on patreon along with free download so make sure you get signed up on our patreon platform there's a lot of benefits and a lot of people there learning even even more than on the channel here and it's a great way to also help and share out this Channel all right so let's get started we're going to go over this list of 10 incredible formulas that are used specifically on accounting and we're going to show how they work now together I've got this incredible accounting application if this does look familiar to some of you it was part of an Erp software however it has been updated I've added multiple features to it for this training so it is very different we are not going to be going over this entire application the brunt of that is on the ultimate Erp application we're going to focus on how these special formulas are incorporated into applications when you do learn these formulas you're going to understand exactly how most of parts of the accounting software work and how you can Implement them for your own use so we're going to get started this application just a brief overview it contains a toolbar up here and let's go ahead and drop that down we'll be using it and also we've got users we've got invoices purchase orders work orders employees payroll customers vendors products and transactions we're going to focus a lot on the products list here and we're going to focus a little bit on the transactions here now each one of those these are lists so we can see a list this isn't the original data here on products or the transactions we're going to focus a lot on the original data so if we look at the products here we do have original product list now this is the original data we're going to be f focusing a little bit on that let's bring it over here all the way on the left I'm just going to move that sheet up so we can focus cuz there's a lot of sheets in this incredible workbook so we've got transaction database this is also the original data so if we're going to be working with this original data in some formulas it's a good idea to learn just about the tables that I have created for that now I don't often always create tables but in this one it was really necessary because we're working a lot with the data so this is a transaction datab base if we were going to edit the transactions we would just go in here and then we could edit an existing transaction or we can also add a new transaction so that's how it affects the database inside that database we have as I mentioned tables now I've created these tables before and if we look at the table design the most important thing is the name is called transactions and of course for the products I've got another one called Product so we're going to be using these inside the formulas so let's go start with the first one the number one here is the inventory on hand now this is an incredible very very important formula in fact one of the most requested most popular videos that I've done involve some sort of inventory whether it's purchasing or selling or calculating the inventory on hand the inventory on hand essentially means how many do you currently hold now often the formula is the quantity on hand it's the total number of quantity purchased minus the total number of sold now we have also included in the list is not only those sold if there was others that were lost or broken cannot be sold you may want to deduct that as well so essentially the formula is the quantity on hand equals the total quantity purchased minus the total quantity sold now if we see inside our order we have purchase orders and we have invoices so if an invoice here this is simply an invoice these are when we sell it so this is an invoice if I've got a purchase order this is a purchase order let's goad and pull up there we go we got a purchase order here so these are purchase orders when we purchase something so it's a total purchase minus the total sold all of that data is located inside a database called order items I've got an order list that lists the orders and I've got the order items here so here's where we really want to focus on the order items we've got purchase orders in here we've got invoices inside the same table if we look in this table design these are called order items so once again basically the idea is I want to total up all of the items that were purchased and I want to subtract all of the items that were sold so that's essentially it and where would that formula be that would be somewhere inside the product database so for every product for this product I want to determine how many of those were purchased minus how many were sold and we would put that as items in stock or quantity on hand or items in stock so let's take a look at this formula inside so remember here again just to review it's the total quantity per purchase minus a total quantity sold lost or broken so understanding that when we go back inside the product database and we drill down into this formula here we're going to see just that we're going to use the sum ifs formula and we're basing it on the order items quantity that is our sum range and our criteria range is based on the unique product idea I want to look inside that order items and I want to look for a very specific product ID now that product ID is located right here if we move all the way to the left we're going to focus on this product ID product ID one so I want to specifically call out only this product and I want to know the total numbers that were purchased so the order type is purchased so that means all of those purchased items for that specific product minus all of them that were sold now this is almost the same formula however the order type is invoice and this is why organizing your data is very very important because we have both of our purchases and are invoice items inside a single list they're both in the order items list take a quick another look at that they're both in this list they're separated buy the order type purchase or order type invoice so we're simply looking for the product ID number one we're determining how many we purchased we're going to sum them all up and then what we're going to do is we're going to deduct any that might have been sold for example this one is product ID number one it has been sold we're going to deduct those five were sold on this invoice and that is how we handle the total total quantity on hand inside our product database so it happens right here that's relatively it once again the total number of purchase minus the total number of sold now in more complex accounting applications you would have a possible separate list or you could put it on the item list you would create another one if something gets lost or stolen or it's broken or it's unable to be used you could Mark that down so let's say we could also subtract another order type maybe we'll do something like minus any that were lost broken or discontinued so we could subtract those out too and this I wanted to keep it simple but it would just be a simple one more additional sum if subtraction on those that were lost or stole so that would simply be it and it's relatively easy so that is how we calculate the inst stock otherwise known as quantity on hand or Q is often referred to so that is our first one called inventory or quantity on hand there's a few different ways to do that I'll probably change this to quantity on hand it's a little bit more common but it is the same exact thing there are several ways to refer to it so quantity on hand or inventory on hand all right great so I also have a video on that and I'll make sure to include this template you can select on that to see a more drill down video on that the next one is net income and profit right this is very very important for the company Health it lets us know right away whether the company is making money or not and that determines our net profit now the idea is it is our total income minus the cost of good sold minus other expenses now total income can be any type of income in this type of application we have many types of income so when we create orders that's one source of income and then when we also do transactions this particular application we can create transactions in this one so let's take a look at some of the transactions here so if we earn a salary that's another type of income here so we can have different types of income so what we want to do is we want to sum up all the different types of income and then what we want to do is we want to subtract out any types of cost of goods sold so for example we might have a cost of goods sold and that is a type of expense that is directly related to the work that was put in so it could be labor or materials into what was sold so I think I do have some cost of goods sold here so if we take a look at this let's scroll down here I believe I do have a few here but it's in here for sure here we go in this column so for example labor paid if you paid for labor for a specific work it would be cost of goods sold so that's the first thing all of our cost of goods sold would want be expensed out then we would get our gross profit so our gross profit is simply our total income Minus cost of goods sold and that is our gross profit next up we would subtract any other expenses so any other expenses that would also come into play to get to our net profit also very important to understand the differences so again net profits equal total income Minus cost of good sold minus other expenses and I've got a dedicated video on that right here as well all right so let's take a look inside there if we take a look inside here we have a 2024 profit summary so here's a nice little summary of where we get to our net profit formula so the first thing what we want to do is we want all of our invoice sales remember all of our invoice sales is part of our income so to do that we would use a su if and again once again I want to know the order totals now this is also based on only 2024 so we want to make sure to use criteria that we only want orders based within the current year however it's going to be all of them we're summing up all of the orders and the order total just a little brief we're looking in the orders and we're looking in now let's go to the database here Order list we're looking at the order totals and I only want those for invoice so that's what we're starting with inside that so once we have that I also want to know based on only those specific date so the orders and the order date must be greater than or equal January of the current year and it also must be less than or equal to the date of the current year which is the year of today December 31st so those are the criteria there's three criteria in here we need to make sure that it's an invoice type we need to make sure that the order is greater than or equal the first day of the year and we also need to make sure that the order is less than or equal the last day of the year and that's going to get us our total orders also we have other income other income would be focused on transactions that's going to come from our transaction database I am looking for something like this from account income if we earn a salary it's coming from salary and it's going into our checking account so I want to look inside our transaction database and I want to look from account type and I want to see those that are only associated with income and then what I want to do is I want to Total the amount so let's take a quick look back into our dashboard we see we have transactions and we're going to use the sum if amount so the transaction is going to be that amount we're also looking for the from account as mentioned where it's income and once again we also need to use criteria because we're only looking for income for the given date so the transaction date must be greater than equal the first of the year and less than or equal the last day of the year great so that's basically all our other income within our transaction we're going to combine those two simply summing them up and that's going to get us our gross income in this application we also have payroll which is an incredible payroll and we want to make sure that our payroll is part of cost of goods sold so we want to make sure that our payroll is also included I've got a pay R database on here it's going to track all of our payroll it's a massive application one of the best applications and so we've got a full payroll screen that we're doing here and a payroll database inside the payroll database we've got a pay date we've got a gross amount and we've got a net amount so we're going to keep that in mind when we understand that back inside the dashboard we go to to go drill down inside this payroll we're looking at a gross pay amount and based on a pay date that's greater than or equal the first of the year and a pay date that's less than equal we want to make sure that we're calculating payroll we also want to know the total purchases how many purchases were in that so we need to understand to make sure that the purchases are also included so not only focused on income but our purchase orders must also be inside here so our purchase orders are going to be the total number of purchase orders here's our total and remember the order type must be purchased also the date must be within the current year so it's almost the same formula and that's going to calculate all of our purchases and once again I also mentioned we want to focus on the cost of good sold the cost of goods sold is very important we're going to sum the transaction database so here we've updated the transaction let me go ahead and update that I want to make sure that it's updated so we're going to sum what we're summing if the transactions here I want to just update it and then we're going to subtraction that amount right so that's what we're totaling is the amount and next up what we're going to do is I want to know the cost of good sold that's very very important it's going to focus on G but column G is also located as the two accounts so transactions it's going to be our two two account type actually let's focus on that it's a two account type must be cost of goods sold so we want to understand those specific expenses that are focused on the goods that we sell also what we want to do is we want to make sure that the transaction date here is also greater than or equal so transactions and then we're going to focus on the transaction date and we need to make sure that of course it's less so the transaction date let's put in the bracket there once the bracket's going to recognize that date and likewise we we want to make sure that we also do that it's less than or equal the last day of the year so we can get rid of that here and we want to make sure it just shortens it up it's almost the same but it shortens it up and it also makes it clear and easier to read which I really like great so we see how we've got that and that's going to add it up total so it's going to give us an updated number there and then we have our gross profit now our gross profit is simply our gross income minus our payroll minus purchases and minus goods sold now you could consider payroll and purchases cost of goods sold as well those are included however I wanted to separate them out here so it's clear so that's going to get us our gross profit next up we have other expenses and other expenses are here again we can shorten this transaction up a little bit using our name branges which is kind of nice again transactions we're going to add that in I want to make sure that we're going to sum our amounts so we can see how it's written in real time and so we're going to add in those amounts we're going to use the sum ifs and again we want to know what other expenses are located so the other expenses here let's get rid of this here it's going to be based on any type of EX we've already covered cost of goods sold I also want to know again the transaction date very important just like we have this is always going to be active for the current year so transactions and then the date here just like we did before put in the brackets and we have to make sure so our profit here is always going to be accurate regardless of it and then the last one we'll put in the last date that's going to shorten up our formula a little bit and make it a little more clear let Ed in that that two type two we need to make sure that's to the account type we want to make sure those are expenses so that's all we need to do so in summary we take our invoice sales and other income and that's going to give us our gross income we then deduct our payroll purchases and other cost of goods sold and that's going to get us our gross profit we then subtract other expenses to get us our net profit and that's a very important profit summary formula next up inside our formula is the balance sheet formula very very important it lets us know the health of the company and that's simply our assets is equal to our liabilities plus shareholders Equity so let's take a look at that and inside our dashboard what we have here is a balance sheet summary now we can have other account types and then there are asset accounts so let's take a look at what types might be assets what type might be liabilities and what might be in equity inside our admin we were able to set up multiple different accounts so here we have expense accounts we have cost go sold income and so on and so forth but we also have asset accounts asset accounts are things that you have such as a check-in account such as a cash account such as maybe let's say a savings account we also have liabilities liabilities are things that you owe such as a mortgage or car loan or a credit card so those are liabilities things that you owe to other people or other companies then we have Equity accounts Equity accounts are owner's equity so if an owner puts in a certain amount of money is an equity account that belongs to the owner and it's owner's equity so it's very important that it always balance out so let's take a look at that formula inside our dashboard the total assets must always equal the liabilities plus the equity that's a law of average is the assets equal the liabilities plus the equity so here we see we have our asset accounts our liability accounts and we have our Equity accounts so how do we manage that basically I don't have any formulas in this screen itself I just put them inside here in the chart data here so it's the same exact thing I just linked it so once again let's take a look at our asset accounts how do we total them well the formula is almost the same for each one of them except we're focus on the individual account so here's what we want to do we want to use the sum if once again we want to know based on the transactions to accounts so what is our transactions to account so anything coming into the checkin account very important I want to subtract out anything going out so everything that comes into the account we're going to total and we're going to subtract out everything that goes out of the account so some if to account this account b23 which is our check-in account and I want a total amount so it's simple sum if formula and I want to subtract out anything taken so if I'm taking something from the account and I'm paying something I need to subtract that out so that's going to be from account is going to be equal to b23 and we're going to take the amount so this is going to Simply sum everything that was taken out of the account and this is going to sum everything that was put in the account and that's going to get us our current account balance oops it referred to a date we don't need that let's focus focus on the currency kind of annoying so we've got 12,856 70 so we've done exactly the same for the cash and the savings account and that's going to get us our total assets once we have our total assets I want to know the same I don't have anything on the personal loan and I want to know the MX card let me copy this here and I'll paste it up here this formula needs to be updated and the reason we want to update it is we want to make sure that we understand okay let's take a quick look inside the transaction database to see how something like that might work so if we take a look inside let's say the Visa we used our Visa card it's a liability to pay for an auto expense for $145 so in this case our from account here is our Visa card so we're adding it up when we make a payment it's going to be opposite so I want to take a look inside our chart data and here we're going to take all of the from accounts and we're going to subtract any payments which would be the two accounts so if we make a payment to an account it's going to be a deduction if we charge from the account it's going to be an addition so we're taking all the froms and we're subtracting all the twos so that's going to get us the balance on those credit card accounts and so we want to make sure that we have the totals and we have the total liabilities which is everything that we have here now the owner's equity if we have specific transactions inside the owners the owner puts in money inside the account that is going to be the total money that's our owner's equity we also make sure that the total liabilities plus the equity must always equal the total assets so we're simply creating that balance sheet summary and then I'm bringing the data over into the dashboard here and we've simply Linked In the data each individual is linked to the Chart data so that's automatically linked to whatever is located inside the chart data so that is our balance sheet summary another very very important formula net worth also very important I want to know what the worth of personal or whether it's a company we need to understand the net worth and the net worth is very simple formula simply the total assets minus the total liabilities to get our net worth so if we look back inside our data we also have information here and let's take a look I got it somewhere around here here it is right here our net worth right here let's take a look at our assets our liabilities and our net worth right here inside p37 if we look in our dashboard all the way up I've got a net worth here this particular number is linked to p37 so that's where our formula is going to originate and that's where we're going to go to take a look at it so let's look inside the chart data and to get our total assets what I'm going to do is I'm going to sum all of the two account types that are assets and I want to know the amount so we're simply summing anything that comes into our asset accounts and I want to subtract anything that got taken away from our asset accounts so we're subtracting all that out just like we did with checking and that's going to get us our total assets likewise the liabilities here again I want to know everything from the liability account and I'm going to subtract out anything that got a paid to so if something was paid into an account I want to subtract that charged from the account paid to the account subtracting those out to get our total liabilities so now that we understand our total liabilities we're simply going to get the net worth which is of course the assets minus the liabilities which is 20540 so very very simple but very important simply our total assets minus our total liabilities to get us that net worth and that is presented right here inside our dashboard very good net worth a very important formula I also want to know the employee net pay if you have payroll we want to understand what's the correct amount to pay an employee let's take a quick look inside our payroll up here cuz I got so many screens we can pull it up from here and so I've got a full payroll that's going to let us know what our net pay is and that's going to be down here so that's where we're going to originate it from and of course let's take a quick look back inside here just so we can go over that the total net pay is equal to the total period salary or hourly so how much are they earning on a per period basis whether they're salary or hourly I want to subtract any employee paid taxes now this is not employer right if the company is paying taxes for the employee that's not included I want to figure out what is their net pay so we must understand what deductions must come on their paycheck that they must personally pay we also want to subtract any other deductions that we're taking away from their paycheck if they have other deductions and we also want to add in any other additions so we have all that inside our payroll so let's take a quick look at that here's the payroll R and so here's our summary if I select on a salary employee it's going to be a little bit different here we have a regular pay and we have an hourly employee so either one of them both have gross pay so if we take a look inside here that is our gross pay right up in here in Z4 whether there's salary here or hourly here we still got a gross pay we want to take in the total deductions so those total deductions we've got all of our tax deductions located right here so we need to Total all those up those are the employee paid taxes so we're going to put those here so those total deductions must come here and that's going to be here inside the amount so we've got state tax here all that information here we need to understand suing out whether Social Security Medicare or any other taxes must first be deducted although it seems quite High there then if there's any other deductions here's where our other payroll deductions there's none but if I were to put in something like $15 it would show up here as an other deductions and also if there's other payroll additions a fuel reimbursement or something that would go here and we must add that in and that's going to get us our total net pay so once again it is our gross pay how much we're paying for that and our gross pay is calculated based on regular over time a little bit more complex but we do get that gross pay amount we subtract out all of our tax deductions we subtract out any other deductions we add in any additions and that's going to get us our total employee net pay so also very very important formula for that so that's the employee net pay employee actual hourly cost now this is extremely important that I learned a lesson the harder way when I ran my company and it's also one of those formulas that's almost never talked about yet in fact I've got an entire application called the labor burden calculator that I've been selling for the last 10 years that does a great job with that and that looks a little bit something like this it's a really big software that's really amazing it calculates the actual labor burden for all of your employees but what I'm going to do is I'm going to give away the formula right now so you don't necessarily need to buy this unless you want to track all your employees I'll include the link down below so what we're going to do is we need keep this open the employee actual hour cost and the idea is this is when you have a company when you have employees you know you pay them a certain amount let's say we're paying them $20 an hour but that is not our actual cost of the employee when we figure in our insurance costs or workers compensation costs or benefits or paid time off or taxes or anything else employee cost our actual employee hourly cost for that specific employee is a lot higher and if we don't understand that rate if we don't know what that actual hourly cost is it could be detrimental for our company because we're also often billing on an hourly basis so it's very important that we understand what the actual cost of that employee is and I've got that here inside this application of course but essentially it is this it is the total annual pay whether it's hourly or salary plus all the employer now this is employer pay taxes very important this was Employee here this is employer meaning the taxes that the company must pay often the the company will also pay taxes for an employee so we must figure in those costs as well plus any employer paid benefits on an annual basis plus any employer paid insurance so we need to add up all this on an annual basis how much what is the total actual cost of that employee now what we want to do is we want to determine the total hours that we're actually paying them let's say we are paying them 40 hours a week and 52 weeks well that's 280 hours but maybe we're also paying for their vacation time off but paying them 40 hours a week so we must actually deduct those 40 hours from the hours paid why is that because they're paid but they're not working so these I want to figure out what their actual work hours are so if we know they're paid 2080 but they're getting 40 hours of pay time off their actual working hours are 2040 so that's important so we would then divide it by 2040 let's see how this formula actually works in real life and I've put that inside our payroll here so if we take a look inside our payroll I've added in our actual employee hourly cost so what I want to do is I want to add the pay frequency so there's something called a frequency divisor here if we look inside the admin screen here and I've got something uh payroll frequency is monthly so if it's monthly that divisor is going to be 12 if I change it to let's say biweekly and we go back to the payroll let's just take a look just so I want to show you this that's going to change to 26 meaning the number of payrolls on a perear basis so this number is very important so let's go back and change it back to 12 for Simplicity but we want to understand that that number is based on the number of payrolls in a year so if our paying monthly then we have 12 payrolls in a year so let's go back into the payroll now now that I've changed it so our pay frequency pa yfr e q is 12 in this case so we need to multiply that so we're going to assume that it's the same so for example if our gross pay is 9,000 remember we want to annualize all the expenses so for this pay period if it's 9,000 we need to multiply it times 12 so that's very very important in this particular application we really focused on what the employee was paying but I want to know what the employer what the company actually pays for their employees so that's also an important cost so before we get into the formula let's take a one more look at something that I did add it so the employer paid let's say you want to add up all the taxes maybe there's federal andate taxes that the employer the company must pay so that's a certain percentage so I've put that in the admin screen as well and I'm calling that let's select on that cell here calling that the employer paid tax percentage now you can put whatever you want but I wanted to get as accurate as possible so let's say it's 133% that the employeer is actually paying I want to add that cost in too so when we go back inside the payroll and we take a look at our hourly cost the first thing what we want to do is I want to understand our gross pay which is located in Z4 for that specific pay period And I want to multiply times all the pay period because remember we want to annualize all of our costs so this is going to annualize the cost I then want to determine what is the total number of tax that the employer would owe so I'm going to take that same number that same payroll cost Z4 and I'm going to multiply it times the employer pay taxes so this section right here is what the company will have to pay as far as taxes are concerned for an employee for an entire year so we're adding this to the actual amount of pay this is the total pay plus I want to add in z7 minus Z6 what is z7 z7 - 6 times a frequency is our total additions minus our total deductions so we're going to assume that we're making the same additions and deductions every month although it could change but we're simply going to say let's say our additions are 100 and our deductions are 15 so that means we have an $85 deduction here so I want to make sure that we have that and then I'm going to multiply that times the pay frequency so that I understand exactly what those costs are so this is our combined deductions and additions and multiplying it times the frequency so this right here is our total annual cost there's also insurance if we want to add insurance but we can add those inside as well those usually in the additions and deductions as well so then we understand now what I want to do is I want to determine what the pay frequency is so now I want to know their total hours here what we're going to do is we've got our total number of hours here times the pay frequency this is going to determine the total number of hours and then we're going to sum whatever is in u26 through u36 now what is that u26 through u36 is of course all the PID time off so if there's any paay time off we want to know that inside a year those are days that we're actually paying so we're subtracting the total hours as you probably add something inside this this is good if it's an hourly employeed because they have the hours but if it's in Sal employee we should probably assume that they're working maybe full-time so I'll make some provision in this formula if it's a salary employee but basically the idea is to determine the total number of hours that they're working we're taking the regular hours and their overtime hours and we're multiplying that times the frequency meaning an entire year we're subtracting out the total company paid time off that's going to get us our total actual hours work again we're taking our total expenses dividing it by the total hours work and that's going to get us our actual hourly so if we're paying this employee $35 an hour our actual hourly cost is a little bit over $43 so it's a very important formula for a company to understand what their actual costs are for an employee on a per hour basis so again a very very important formula all right very good so that is the employee actual hourly cost also we want to understand our average inventory item cost if we're buying inventory items and buying them at a different prices it's very very important to understand what is the average cost of a specific item because when we have to cost it out we need to understand what the average cost is so the average cost is simply the total purchase amount what is the total amount spent total dollar amount or whatever currency you're under divided by the total number of units purchased so it's relatively simple let's go ahead and look inside our product database and here we have a formula called average cost and here's where we're going to determine exactly that average cost and so inside this formula I simply want to sum up the total amount purchased for this specific item we're going to wrap that on if air in case there were no purchases or in case there's a zero so we're going to use the sum once again again we're summing the total of the order items but only on those items that are specifically those purchased items that's all I want to know and we're simply going to sum it up for a very specific product ID and that product ID is located here in A1 so we're going to take that total amount and we're going to divide it by the quantity of of those purchased this time we're using the sum quantity but everything else is the same the order type must be purchased the order item must be that and so what we're going to do is we're going to take that product ID and I want to specifically focus on this product only and also what I want to do if for some reason there is an error I will simply want to defer to the purchase price so deferred to the purchase price if there's an error that's why there's a comma here so this is part of the if error and so otherwise here is the total amount this avoids any errors so we're simply summing the total quantity purchased here and then we have the total amounts purchase and that's going to get us our average purchase price so here we can see that our purchase price is $185 and then our average cost is 172 because there are times when we may have purchased it for less than 185 so we have that average cost that average cost is very important when we're trying to figure out the value on hand because the value on hand is is going to be the quantity in stock which we went over the quantity on hand times the average cost and that is going to determine that actual value on hand very important when we have to use inventory as an asset we need to know what that total asset amount so that average cost is very important because we must multiply that by the total number in stock to get that actual value on hand great so that's very important the average inventory item cost extremely important when determining the amount of inventory especially when you have ventory is an asset next up is the estimated loan payments now this is really important anytime we're paying credit cards or we have some kind of liability or we have a home mortgage or if you're a business you have maybe some business type of loan we need to understand what those payments are going to be based on that amount that was taken out that loan amount so we can use the payment function to help us with that and simply me the payment function is the monthly interest rate whatever the interest is that we've agreed to pay the total number of payments now this could fluctuate maybe you want to pay that debt off in 12 months or maybe if it's a home loan it's going to be you know a 30 years so it could be different and then what we want to do is the negative and the principal amount so if the principal amount is 100,000 it's going to be negative 100,000 so how would we apply this if we have debt if we have liabilities and we want to make those payments how do we determine the payment amount that we should be making on a regular basis to pay that off and we've got that inside our chart data as we remember here inside our chart data we have a list of liabilities now let's say we want to set up a monthly payments and I left this blank so that we can make this Dynamic maybe we have Dynamic interest rates so let's say we want to make 12 payments and we're paying it off at 8% interest rate so 8 now there's no amount on here but let's say we have this one is$ 1,290 we want to make that payment so let's go ahead and change this we have the payment amount now it's basically the rate and so what we want to do is we want to understand the rate divided by the number of payments so here we have the rate divided by and then we'll make this dynamic in case we want to change it here 12 so that's going to get us our rate so what we want is a per monthly rate so we're taking the annual interest rate we're dividing it by 12 or the number of payments we can keep that at 12 actually because there's always going to be 12 payments so we want to get our monthly interest rate not necessarily the annual the monthly so if our annual interest rate is 8% we need to determine the monthly interest rate then what we want to do is we want to make number of payments so at this point we can make make this dynamic in case we want to change it and then we want to understand what is the amount that we're paying now that amount is in C30 and we're going to make it negative and so what that's going to do is going to tell us that we have $112 payments on the monthly for 30 months and then if we want this formula to apply to the other ones all we need to use is to make those absolute so we can use F4 here and then F4 here to make them Absolute we can then copy and paste this formula for our other liabilities here so we'll just copy and paste it right into there now what we want to do is if we decide we want to pay our liabilities off and less maybe we want to do a six months we can do that and we can see how that affects our balance so we know that we need to make a certain amount of payments so the payment is a really really great function and formula that allows us to understand our liabilities and how quickly to pay them off or of course if you're earning interest or something that's going to also help you too but payment is a great great formula and one of the most important for understanding what the payments is and the debt that you take on and how quickly and how much cost it will take you to pay those off all right another really great measurement of health of any company is the debt to equity ratio now essentially the debt to equity ratio is your total liabilities divided by your total equity and what we want to do is we want to have as low ratio as possible and that means the lowest number of liabilities with the highest greatest Equity so if you have $100 in liabilities and you have $1,000 in equity you're going to have a 10% or10 debt to equity ratio and if we take a look in our dashboard here we do have a debt to equity ratio which is our debt of 1580 and our total Equity of 2,548 which is a08 debt to equity ratio if we take a look it comes from our chart data and if we take a look inside here we see that we have our total Equity of 20548 we have our total debt which is our liabilities of 1580 and that's going to provide us with our formula then all we need to do is determine what that debt to equity ratio is in this case it is 0.8 and up here I've summed it up here so we can see our debt we can see our equity and we simply need to use division e22 divided e23 which is our total debt divided by our total Equity to get that ratio and that's one of our simplest yet most important it's a quick way to get a very fast look at the health of any company and so if you're running a company you can keep an eye on this and you want that rate to go down as low as possible and it's a good measurement of any company very good last up on our list of the top 10 accounting formulas is the current cash flow one of the most important things is cash flow and cash flow runs out you're in big trouble if you don't have to borrow money or generate cash cash is a very critical component of running any company and therefore the formula current cash flow which is equal to the total cash inflows minus the total cash outflow so it's also one of the most important yet simplest to understand and so for our purposes in this application total cash inflows means all of the invoices that were paid along with all the other income that we collected while the cash outflows means all the purchases that we personally have paid for plus all the expenses that we have also paid so let's take a look inside our dashboard and this is where we're going to have it and this is our current cash flow So currently it is 26579 and this comes directly from our chart data in h24 so we're going to look inside h24 and we see that we have a current cash flow summary so again once again let's take a quick look inside our order list so we can see we're looking at this payment I want to know what was actually paid whether it is an invoice or a purchase I want to know either way right so if it's an invoice we know we have been paid if it's a purchase we know we have personally paid for the item and likewise we also want to add in our transactions database so we want to understand any other income that we may have had and also any other expenses or cost of goods sold that we may have had so let's take a look inside that formula so we're going to take a look inside our total income now our total income is once again summing if we're basing on the orders type I only want to know those types that are invoice and only going to sum those that are payments so I want to know all of the invoices that have been paid I also want to add in any additional items based on our transactions that are labeled as income and that's going to be from account so from account being income and that's our transactions amount that we're actually toing so that's going to constitute all of our cash inflows now what about all of our cash outflows once again we want to focus on our orders those that we've purchased and those that we've actually paid so we want to make sure that we're looking for the order types that's purchase and we're summing just the payment column we're going to add in both expenses and in cost of goods sold to those so again we're going to look in the transactions at expenses and the amount and we're going to also add in those that are cost of goods sold so both cost of goods sold and expenses are considered expenses and cash outflows so we want to make sure to take those into consideration along with the purchase orders so combined it's a total of 39,000 then our cash flow is simply our total income minus our total expenses to get that 22838 and then it's not linked to our cash flow right here so we have everything there just correctly all right very very cool here is your special bonus this incredible accounting Erp is absolutely yours for free all you need to do is look for the link down in the description and I do appreciate your continued support this has been a great training we covered top 10 counting formulas that you can use today and of course you've got this incredible Erp software complete with admin users invoicing purchase orders work orders employees payroll customers vendors products transactions and a complete dashboard I do appreciate your continued support go ahead and give me a like if you appreciated this video and you want to learn more let me know with the comments down below and also don't forget to subscribe and click the notification icon Bell I create these trainings twice a week the complex ones are on Tuesday while the simple VBA Basics are on the weekends thanks again I do appreciate it and we'll see you next week [Music]

Channel: Excel For Freelancers

Views: 4,814

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, 10 accounting formulas, accounting, accounting basics, accounting formulas, excel formulas, excel accounting formulas, excel functions, erp, erp system, erp software, accounting in excel, accounting in excel for small business, excel accounting, excel accounting tutorial

Id: Ga-cytqK0E4

Length: 44min 0sec (2640 seconds)

Published: Tue Jun 11 2024