 Here we are in Excel. If you don't have access to this workbook, that's okay because we've 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 haven't 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 populate it 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. So 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 be 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 will 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 uh 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 on 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 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 which 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 underline under this under this red one home tab font group underline and that gets to our taxable income which again would typically be green right 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 I'm going to make that red and that'll give us our 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 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 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 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 we'll 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