 Income tax 2023-2024, create a tax formula worksheet using Excel part number two. Get ready and some coffee so you can recognize the quacks when doing income tax preparation 2023-2024. First, a word from our sponsor. Yeah, actually, we're sponsoring ourselves on this one because apparently the merchandisers, they don't want to be seen with us. But that's okay whatever because our merchandise is better than their stupid stuff anyways. Like our CPA six pack shirts, a must have for any pool or beach time, mixing money with muscle, always sure to attract attention. Yeah, even if you're not a CPA, you need this shirt so you can like pull in that iconic CPA six pack stomach muscle vibe, man. You know that CPA six pack everyone envisions in their mind when they think CPA. Yeah, as a CPA, I actually and unusually don't have tremendous abs. However, I was blessed with a whole lot of belly hair. Yeah, allowing me to sculpt the hair into a nice CPA six pack like shape, which is highly attractive. Yeah, maybe the shirt will help you generate some belly hair too. And if it does make sure to let me know, maybe I'll try wearing it on my head. And yes, I know six pack isn't spelled right, but three letters is more efficient than four. So I trimmed it down a bit, okay? It's an improvement. If you would like a commercial free experience, consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com. Here we are in Excel. If you don't have access to this workbook, that's okay because we basically built this from a blank worksheet but started in a prior presentation. So if you want to build this from a blank worksheet, you may want to begin back there. If you do have access to this workbook though, there's a bunch of tabs down below the green tabs representing in essence the answer key. The blue tabs showing the information we have done up to this point in the practice problem where we will continue at this point in time. This practice problem having been created from scratch in a blank worksheet in a prior presentation. What have we done thus far? We've been constructing the income tax formula in a formula type setting or basis so that we can do the data input understanding and visualizing how the formula works. And also so that we can create a secondary data input as an internal control as we enter the information into say software or populated in our tax forms. So here's our example tax form that we've basically been mirroring over here in our worksheet starting with a very basic problem so we can give an outline of our formula. As we continue on more complex problems, we will do comparisons and expand our worksheet. For now, let's just make it look a little bit fancier. What we have is we've got the income line item, we've got the adjustments to income, the above the line deductions in other words, giving us the subtotal of adjusted gross income AGI. Then we have the greater of the itemized deductions or standard deductions and that's going to give us the greater of those two in this case being the standard deduction. That is what we can use to calculate the taxable income which is often done. That's the bottom line of our income statement and then we can calculate the tax often done by the software backing into the average tax rate with a formula. And then we'll have other credits, other taxes that will build on in more complex problems giving us the total tax which we have to then compare to the payments and any refundable credits which we'll talk about in more complex problems. We'll then give us the total tax that is due or refund. Let's put an underline here and so now what I'd like to do, let's put some brackets around it to start off. Let's put some brackets around this thing. I'm going to go home tab, font group, put some brackets around it. I'd like to center this top header. Note that many people will probably say okay what I would do is do this home tab alignment and merge but I don't like that because then it kind of messes up my ability to see different columns because I have this one large cell. So I'm going to undo that or unmerge it and I prefer to select this area right click and go to the format cells and then within the alignment and the horizontal alignment. I like going to the center across selection and then I can do that and so now it's still centers and it's a little confusing here to see where that text is because I could see it's in that first cell as you can see in the formula bar. But it's a lot nicer that I haven't kind of made one large cell. Okay so then I'm going to try to color code this whole thing to try to indicate the good numbers and the bad numbers which is a little bit tricky because on the top half of the formula down to here you have an income statement income good or incomes is usually good on an income statement. But when you're thinking about it from a tax perspective the income is is actually bad but I'm still going to color the income as green and the deductions which are kind of similar to expenses as red because that's probably what we normally visualize in an income statement. And then it gets even a little bit trickier down here because down here we're talking about basically taxes and and credits which is a little wonky. So let's try to come up with a color scheme because color schemes actually do help. So in an income statement typically income let's make it green. So I'm going to go to the home tab I'm going to select the A and I'm just going to make that our dark green so hopefully we can see it in our practice problem. And then the deductions which are like expenses I'm going to make them red so I'm going to select this whole area here home tab font group and let's make that red. Hopefully that's dark enough that we can see in the practice problem and that's going to give us our adjusted gross income. So it's usually still going to be green because it's still positive because the deductions usually haven't taken it down to zero. Then we're going to take the greater of the itemized or standard deductions. These are decreases kind of like expenses on an income statement would be typically red. So I'm going to or you know a decrease. So I'm going to say that's going to be red. And then you could make like the standard deduction like a negative number here. But usually I would make this positive that'll make it easier to create our worksheets on the right and then just do a subtraction problem here. So let's put an underlined under this under this red one home tab font group underline and that gets to our taxable income which again would typically be green. So we're going to go home tab font group and green because it's usually still going to be positive in which case we're going to be owing taxes. And then we've got the tax calculation. Now notice I'm backing into that number. I'll talk about that in a second. But I'm going to keep it black at this point because now we're multiplying that times a rate which is kind of neutral I guess. And then on the bottom half now we've got the tax taxes are kind of like bad. So I'm going to try to visualize the taxes as red now. So now I'm going to say on the bottom half the taxes are red and then the credits are good. So I'm going to make that green. So I'm going to say all right the credits are green and then other taxes are bad. That's going to increase the taxes are going to make that red. And that will give us our total tax taxes bad. So I'm going to make that red. And then the payments and refundable credits the payments that we have made are good because we already made them although we had to make them which is not nice. But and the credits are good. So I'll make those green. And then the tax due or refund in this case the tax due is going to be red and that it would be a positive number. And then and then the refund is going to be green. And so that would be now we can actually show that over here with a with a conditional formatting I can try to say hey look if this is a positive number. I want to make it actually red because that means there's tax that is still owed at this point in time. And if it's negative I want to make it green because that means that there's an actual refund. So notice it gets a little wonky again on the second half of the formula why because the taxes are bad. That's what we owe right. So it's red. And then if there were credits that would reduce the amount of taxes that we owe. And then if there's other taxes that would increase the taxes we owe. Therefore this minus this plus this would give us the taxes. The payments that we already made have been made. And therefore if I subtract these two out if I get a positive number that means we still have tax that is due bad right. If it's negative right. So you could try to flip all the signs here but so that you end up with a with a positive number that would be a refund. But I think this is actually the best way. I think this works well. We can indicate that this is not a refund by using our color scheme so we can easily see it right. So we can do our conditional formatting. This is in the home tab styles conditional formatting. So you could do something like this and say hey look if this is greater than if it's greater than zero. Then we want it to be we want it to be negative or red. So there it is. It's red. Perfect. And then if it's and then I'll do another one if I say if it's less than zero then I'm going to hit the drop down. I want you to make it green. So let's test that out. Let's make this like negative two turns it to green. So now we've got that pretty pretty fancy conditional formatting that will kind of give us an indication if it's good or bad refund or the amount that is due. Now the next thing I like to do is basically indicate in my formula which of these formulae are coming from another worksheet and which are going to be data input. So in other words this line item is coming from another worksheet. And so I'm not going to hard code or type in a hundred thousand here because it's coming from this worksheet at the least will add more worksheets later. So that therefore I'm going to leave that is the adjustments to income will be the same. I haven't got a worksheet for it yet. So it's hard coded as a zero. But eventually I'm going to create another worksheet once we do some adjustments to income. And therefore I'm not going to just type in like $50 here right because there's going to be a bunch of different categories for the adjustments as you can see in the tax software. Where we can see the adjustments are going to be here's the income here's the adjustments for the schedule one part number two. So all of this part number two I can make a whole another worksheet based on this but I'm going to construct it piece by piece as we see those in our practice problem. So then we have our this one also a formula. It's not pulling from another worksheet but it's just a formula so I'm not going to hard code anything in it. So I'm just going to leave it as is itemized deductions also are going to pull from another worksheet. That's going to be pulling from the schedule a I'm not going to create a schedule a yet. We'll build the schedule a as we talk about each of these items within the schedule a so it's hard coded now but it won't be once we go later. This one is something that I'm actually I want to pull from the table down here. So this is something that has to be changed based on whether or not or what the marital status is filing status. So I'm going to make this one blue. So I'm going to look we can do that by going to the home tab font group and make it blue. If you don't have that blue it's in the more colors standard. There's the blue. So that means that this one is going to is going to have we're going to have to do data input with it. I'm not going to type it in which is and that's what I'm trying to say with the asterisk here. So that's why I tried to put the asterisk to show that these two are tied together. But I'm going to pull this from I have to do something to it. So usually the blue means hard coding. I'm going to type it in there. I'm not going to type it in there but I have to manually do something to this cell. That's why I make it blue. This cell I don't have to do anything manually to this one because it's going to take the greater of these two. In other words, I can test that if these itemized deductions came out to 15,000 it would take 15,000. So I'm not going to hard code the itemized deductions. Those are going to pull in eventually from another worksheet. But you can see how this max thing works. So that means I'm not going to do anything to this cell because it's just going to automatically populate. And then the taxable income again is a formula. So I'm not doing anything to this cell. I'm not going to manually input anything into this cell. The same with this cell because I don't actually type in the tax rate. What I'm going to do is go to the software and say, okay, let the software calculate the tax as we did on page number two. Boom, 14266. That means I'm actually going to physically input this into the cell. So I'm going to go home to Alphonic Group. This one is a hard coded number. Why? Because I'm not going to try to simulate the table. You could try to say I want to double check the tax calculation applying out the progressive tax tables like this. But that's a pretty complex Excel worksheet. I mean, you could do it. It's not that bad. But that's probably we're going to rely and say, okay, that looks pretty good on the software, possibly to do that instead of trying to recalculate it and then simply hard code it. So I'm double checking this number in my data input by having other worksheets that flow into this income statement giving me the detail. And then I let the software calculate this number, which I hard code into the system. That's why it's blue. And then the average tax will automatically populate. That's why it's not blue because I'm not going to do anything to that cell because that will populate with and you can see in the tax summary, that it's properly populating here 16.6 the effective tax rate or average tax rate. So that's the idea with that one. All right. So then so then we have the other other credits. So these are going to be other credits that could come into play child tax credit and so on and so forth that but that's going to come in from another worksheet again. However, I'm going to create those worksheets basically as we go. And then we have the other taxes like self-employment tax, for example, which again will pull into another worksheet as we go. Therefore, I'm not going to make it blue because I'm not going to hard code the numbers into here but rather create the worksheet when we get to that part of the practice problem. That gets us to the total tax, which is a formula. Here's what we had before and then we're going to subtract out the credits and add the more taxes. And that's going to be a formula. Therefore, it's not blue. I'm not going to hard code that number. I'm not going to type it in there going to leave the formula alone. And then the payments also something that I'm not typing into here, but rather we created another worksheet over here, which is going to pull into that line item. We will expand on this worksheet later. So it'll be more complex adding more types of payments such as we currently have W2. We can add estimated payments and credits, refundable credits, for example, earned income tax credit and part of the child tax credit. And then we have the tax due or refund calculating down below, which I didn't actually, we're not going to hard code it. We just use conditional formatting. So it shows it as red or green. So that's going to be our basic starting point. Now, like I say, when we start to do more complex, complex tax returns, we will add to our worksheet. So at the end of this whole cycle, we will end up with a pretty expansive worksheet that's going to have a whole bunch of tabs to the right, which will allow us to do data input, which will feed into this first tab. And we'll see how we can construct the worksheet as we go. Now, if you want to just start with that end worksheet, which is the worksheet that I would basically use as my kind of starting point to double check my tax data input, then hopefully we'll give you a copy of that. So you can just use that worksheet if you just want to do the data input and you don't want to practice the formatting as we go. But I want to just show you how you can build the worksheet in Excel as we go because that'll give you a better idea of the formula. We can practice Excel with it and then we can see how to basically we can make this external worksheet if you want to put it together. So that is that. Like I said, it's a work in progress and we'll continue building on it as we go double checking it for errors because we have now an internal control, meaning with accounting, we have a double entry accounting system for the balance sheet being in balance, for example. Do not have that with the tax return. So if we can double check what has happened two times for our data input in the tax return as well as in with our Excel worksheet and do some of the calculations again in Excel, although we have to find a nice balance between not redoing everything, not being so detailed it's going to take too much time, versus doing enough data input so that we have the internal control, the double check and can basically understand what's going on, then that's going to give us another level of assurance and be something that you might want to do as a general practice if you're doing data input into tax software.