 Income tax 2022-2023. Create a tax formula worksheet using Excel part number two. Let's do some wealth preservation with some tax preparation. Here we are in our example form 1040. We've started to populate it using Lucert tax. Tax basis, tax base. Software, you don't need tax software to follow along, but if you have access to tax software, it's a great tool to run different scenarios with. You can also get access to the form 1040 related forms and schedules. IRS website, website renewal, dependents.com, irs.gov, irs.gov. So we have here our outline of just a single filer, no dependence, and we have our basic information down below and have been using that to construct an income tax formula in our Excel worksheet as a way to kind of double check the data input as we input the information into the tax software. Support accounting instruction by clicking the link below giving you a free month membership to all of the content on our website, broken out by category, further broken out by course. Each course then organized in a logical, reasonable fashion, making it much more easy to find what you need than can be done on a YouTube page. We also include added resources such as Excel practice problems, PDF files, and more like QuickBooks backup files when applicable. So once again, click the link below for a free month membership to our website and all the content on it. Software, as a way for us to basically visualize the tax formula in a more simplified way. So last time, we basically did the outline of the income tax formula. Note that when using tax software, oftentimes they'll have a similar outline down here. This is where it's located in LaSert where they're just basically getting it down to the bare bones, basically an income tax formula. We are reconstructing it in Excel because that allows you to kind of see all the stuff that's feeding into the tax formula, allows you to kind of construct your own kind of tax calculation in a way similar to how the income tax forms are actually calculating it, allowing you to get a better understanding of what is going on, what is included in each of these line items of the income tax formula, and it gives you that double check when doing returns in practice to basically redo the data input in an Excel format so you could double check if you had any kind of data input errors or any errors related to a tax calculation that the tax software did something funny because of the way you put the data input in it so that you can kind of double check that as we go. So last time, we got to basically the outline here which is going to be income and then we've got the adjustment. So if I mirror that to what's on the tax return, you've got the income, all of this stuff up top, I'm going to put on a different form or a different schedule on our worksheet. Therefore, we get down to, in essence, just the taxable income down below and then we have the adjustments to income. We don't have any here. We'll put some more adjustments later in it so we can have another schedule for this line item instead of just putting a zero there. We got the adjustments to income which ties out to the adjusted gross income here. Then you've got the itemized or standard deductions which we're pulling from our table which is outlined on the left or the itemized deductions would be schedule A which we'll take a look at later. We're going to pull the larger of these two items and then let's put an underline here. I'm going to go to the home tab and let's put a little underline there. That'll give us to our, well actually no, let's undo that. That'll give us to our qualified business income. This is that one line item that they kind of threw in there as kind of a plug when they adjusted the tax returns a little while ago, a few years ago, I mean. And that's going to have to do with schedule C income oftentimes or self-employment income. We'll talk about that later. Let's put an underline under this one. Home tab font group underline that gets us to our taxable income which is going to be our adjusted gross income minus these two items. We have a formula to calculate that. We're not going to calculate the actual rate because it's using tables. So if we reconstruct it in our Excel worksheet, we can then use the software to calculate the actual tax which comes out to 14774 in this example. Then we can use back into the rate which will give us the average rate. I'm going to put an underline here home tab font group underline and then we've got other credits. So the credits down below other taxes and that will then give us to our total tax. Collecting taxes. And then we've got the payments and refundable credits. Let's put an underline here and that'll give us to the amount due or refund. So let's go ahead and just what I'm going to do now is try to format this so we can make it look a little bit nicer, clean it up a bit. So up top, I'm going to put a header on it. So I want to put something up above line number one. So I need to add a row. So to do that, I can select the entire row one by putting my cursor on the number one, selecting it, right-clicking the selected area and insert it will always insert an entire row if you are inserting an entire row above. So there we have it. I'm just going to call this the tax formula for 2022. So I'm going to call it that and then I want to center it across the formula. Now I could do that by going to the home tab, alignment and merge. That's what probably comes to most people's minds, but I don't really like that because then you've got this big merged cell which kind of messes stuff up. So I'm going to undo that and I'm going to do it this way. I'm going to right-click and I'm going to go to the format, formatting of the cells and then I will go to the alignment and then in the horizontal, I want to make it center across the selection. So there we have it and I'll say save. So now even though it's in cell A1, I still have these other cells that haven't been merged. So it's a little confusing to see where that cell is coming from, but it's not like grouping all the cells together. Then I'll make it look like a header. I usually do that by selecting the entire area of the header, going up top, home tab. I'm going to go to the fonts. I'm going to make the color black and then white. That's what I typically do to the headers to make it stand out a bit. So then I'm going to make this whole thing brackets. Let's put some borders around it to make it stand out a bit. So I'm going to put my cursor on A2 and select on down here to 16 and I'm going to go to the home tab, font group, put brackets around the whole thing. So that should make it stand out a bit more. Maybe column C, I can make that a little thinner. So I could put my cursor between C and D and make it a little thinner because that 12 is possibly like the largest number that's going to happen there. And so that's that should work. And then so I'm going to say, okay, this one, column A, the signs, I might want to center those. So I'm going to go to the home tab, alignment, center those. And now I centered this one again. Let's undo that. Undo that. Okay, hold on. Now I messed up. I messed up this whole thing. This thing right here, I don't want to center it. I want to left align it. And then I'm going to reformat it, format, right click, format, alignment, center across, boom. Okay. So then, so there we have that. Then this one, maybe I can make this a little smaller just to fit the signs. All I need is for it to be as big enough to fit those signs. So that looks like it's big enough. All right. So then I might color code this thing, which might make it a little bit easier to look at. So the basic color coding I'm going to use is I'm going to enter something as blue if it's going to be pulled from some, if I'm going to do the data input into the system here. So for example, the income line, I'm not going to put anything directly into the cell. It's going to be pulled from this second tab, the income tab. And that's where we had this blue area. I'm going to delete these two rows up above, putting my cursor on row one and two, right click and delete. So it starts at, you know, a one. I'm going to select the whole thing, also make it bold. So we'll make it a little bold. All right. So if I, if I continue down the adjustments to income later on, this is going to come from another place as well, another tab. And then this is a formula. So I'm not going to do any data input into this cell. This down here, itemized deductions is going to come from another tab. So I'm not going to make it blue. Standard deductions is going to come from this table down below. So that's kind of something that I might consider a data input tab. I'm not going to actually manually, I'm going to say it's equaling the table below, but I'm going to make that blue. Let's go up top. I'm going to make it this color blue to say that I have to do something to it or at least look at it. If you don't have that, it's in the more colors standard color. It's right there. That's the one that the Excel is fun guy used. So I started using that. It's a good color. I like it. So we'll use that one. And then this one is going to be the greater of these two. So we'll get back to that in a second. Other qualified deductions. This one I'm going to, I'm going to make blue as well. Because if there's a schedule C, I might put that directly in here or I might make another worksheet for it. We'll talk about that basically later. And that gets a taxable income. This is a formula. So I'm not going to do any actual data input here. This is actually a formula. So I'm not going to do any data input here. This cell, however, is one where I'm going to do the data input because I'm going to pull this cell in letting the software do the calculation. So I'm going to make that blue to indicate that I'm going to actually do the data input there. This one is actually going to come from another tab. So it's not yet, but I will do that later. This one's going to come from another tab. So we'll do that later. And then the total tax is a formula. So I'm not going to do any data input here. And then the payments and other credits are also going to come from other tabs. So I'm not going to make that. I'm not going to do any data input there. And the total is a formula. So I'm not going to do any data input there. So you can see most of this whole formula is going to be drawn from data input that are on other sheets that we'll be constructing in future presentations. Now let's just try to make the format of the cells look a little different. I'll make like the income, let's make it green. So the income is green versus the deductions I'll make red. So I'm going to go to the home tab and I'm going to go to the font group and let's make this green. I use, let's use this darker green. And let's do that. Now notice that the first half of this formula is an income statement. When we think about an income statement, usually income is good, deductions are bad. It's kind of the reverse with taxes. I don't want, I'd like to have income that I don't have to include as taxable income. That would be better. But I'm still going to kind of reflect it in the formula as income is good, the positive numbers and the deductions are going to be the red, the negative numbers. So I'm going to say, which are basically expenses, right? So I'm going to say these, these are basically expenses. I'll make them red. So I'll go to the tab up top and I'll make them red. And that's going to be equal. Usually this will still be a positive number. So I'm going to make that green. And then that's a subtotal of our income on the way down. And then all of this stuff, the greater of the itemized or standard deductions are deductions. So I'll make those red. Those are going to be the decreases as we get down to the taxable income. This is another deduction. So I'm going to make that red a decrease as we get down to the taxable income, which is like net income. And if our income was greater than the deductions, this line will typically still be positive. I'm going to make it green to indicate positiveness here. So then down below, we've got the tax, the tax rate, which is kind of, it is what it is. It's going to be the rate. So I won't make that any other color. Now the bottom of the formula then is a little bit funny because now we've got the actual tax that is being calculated. So then the question, the tax is actually kind of bad and the credits would be kind of good down here now that we're at the bottom after we've gotten to the taxable income. So I'm going to try to reflect it that way, right? So I'm going to say, okay, this is the tax. So the actual tax is bad. That's how much we would owe if we owed money. So I'm going to make that red. I'm going to say, okay, that's red. And then the other credits, credits are good on the bottom half. So I'm going to make that green because if I got a credit that would lower the amount of taxes. And so notice I'm using the terms good and bad down here, whereas up top, good and bad is kind of reversed. Up top, I used income minus expenses. In essence, down here, I'm using taxes, bad, you know, credits, good kind of thing. Okay, so then here I'm going to say other taxes, more taxes are bad. That would increase how much we're going to owe. So I'll make that red. And that's going to give us our total tax. So if we still owe tax, that's still bad, right? So I'm going to say that's red. And then we're going to compare that to the payments that we made and other credits, refundable credits. Those are both good. Not that we had to make the payments, but the fact that we already made them means it's going to lower how much we owe at this point in time. So I'm going to make that green, and then that will get us down to the tax due or refund amount down below. Now, the tax due or refund will be good or bad, depending, you know, if it's the amount that's due, if it's a tax due, that amount is going to be bad, right? And if it's a refund, it's going to be good, right? That would be a good thing. So and if it's tax due, it's actually going to be a positive number. The refund is going to be a negative number. So I'll actually represent that with brackets. So let's represent that with brackets. And then I could do a formula. Let's make this bracket green. And then I could do a formula down here so that it will show conditional formatting so that if it's a positive number, it's actually going to represent as red, which means it's going to be amount that you owe, right? And if it's a negative number, it's actually going to be green. That's going to be the amount that's going to be refunded to you. So let's do a conditional formatting, kind of a neat tool in Excel. You could do that by going to the hometown, so on the cell, hometown conditional formatting, hitting the drop down. I'm going to say if this is greater than, if it's greater than a zero, if it's greater than zero, then we want it to be, if it's greater than zero, we want it at that red. So that's the default red. So that's what it is there. So that looks good. So I'm going to say, okay, so I'm just going to change this for now just to show you what it is. This is that formula. If I made this like negative two, then it's not red. If I make it five, it's red. Let's change it so that let's do it. So now if it's negative, negative two, I want it to be green. So I'm going to go, okay, let's go to the home tab, conditional formatting, and say if this is less than- We order less than a thousand. Now, zero, if it's less than zero, I want it to be green, green with dark green. So it looks like that. So I'm going to say, okay. So now if it was positive six, red. If it's negative one, green. If it's zero, which is unlikely, then no conditional formatting is being applied. So if I say this is equal to this minus this, there it is. It's red. It's a positive number in this case, because the tax calculation was positive, right? So the positive number is kind of bad, right? Because we would be comparing the taxes to how much we paid. If it flips to negative, that would mean we'd get a refund and that would be good. So that's the general outline there. Let's also do another kind of formula on this one. So you can, you could say this equals, I think we could just use a simple max formula, max of these two. I want to take the higher of these two. In other words, max of those two. So if this happened to be, if the itemized deductions were 13,000, it would take 13,000. You can also use an if formula, a logic formula, but that max formula obviously is the most easy one to use. So let's stick with that one. So that's what, that looks pretty good for now. So in a future presentation, maybe we'll go into a little bit more detail and break out at least the bare bones of these other items that we'll be possibly making more tabs for. And we'll keep building out these other tabs, like this other tab only has W2 income in it right now. But obviously everything that's on this page one could be part of that other tab. All this stuff, all these other kinds of income over here could be part of that other tab. And so we'll add to it as we start to do our taxes going forward. But we'll construct and build our Excel worksheet as we're adding detail as you might do in practice. If you were using kind of this technique to double check your numbers, you could just, you can add as necessary to these subordinate tabs to add more subordinate calculations and whatnot that will feed into the formula. And we'll see how that goes as we enter data.