 In this presentation, we will break out and allocate a lump sum purchase to its components including land, improvements, and building. Situation here being that we have a purchase of land, building, and improvements for $110,000. We don't have an allocation for the $110,000 that is exact, meaning when we purchase the land, building, and improvements, we had a lump payment of $110,000. Often times when we do that, we don't negotiate what the components are of this $110,000. In other words, how much of the $110,000 is allocated to land, how much to the improvements, and how much to building. So we need some other way to do that. We may have a property tax statement which is typically based on some type of appraisal based on often a breakout between the land and the building. We also could have an appraisal. Now note that these appraisals, whatever the appraisal may be or where the appraisal may come from, will not often, usually will not equal the actual sales price of the building. The appraisal may have happened before or after the building and it is just an appraisal, just an estimate. But so it's all we have, however, is this appraisal. So if we had an appraisal on the building and the land and the improvement, what we purchased as one lump together for $110,000 and the appraisal said that the land was $35,000, the improvements $15,000 and the building $90,000, then that adds up to $140,000. That's going to be our appraised value. So remember the cost was $110,000. We broke it out between the appraised value $35,000 and $19,000 for the $140,000. Now what we need to do is try to use this appraisal to break out the $110,000. How could we do that? Well, we're going to do some type of ratio analysis. We're going to take the percentage or ratio and to do that, we're just going to compare everything to the total. So $35,000, for example, divided by the total of $140,000 gives us 25%, 0.25 or 25%. The $15,000 divided by the $140,000 gives us 10%, 10.71%. And then the $90,000 divided by the $140,000 gives us 62.29%. So we're going to do that same thing here with Excel. We'll use our formulas to do that. We're in cell C8. We're going to say equals and point to that $35,000 in B8 and then divide by point to the $140,000 in B11. And that'll give us our 25%. That one comes out exact, which is nice. We're going to do the same thing for C9. Note if you wanted to copy this down, by the way, it's possible to do that, but you would need to not change this cell. But this top cell, you do want this to change because we want to move down to the 15. But this bottom cell, we don't want to change. So we could use absolute references. One is by using F4. You can just say F4 puts a dollar sign before the B, dollar sign before the L, and enter. So then if I copied this down and double-check it, did it do what I wanted to do, 15 divided by 140. So that's one way you can use the absolute references. For example, for Excel, I'm going to delete it and do it this way just so we can practice the calculation. In C9, this equals the 15,000 in B9 divided by the 140,000 in B11. And enter. Now, Excel is rounding that, that's 10.71 about. If we go to the Home tab, Numbers and Increase, it's actually a little bit more than that. So notice that Excel will take the actual number here, the ratio, when we use that in calculation, not the 10.71, but the actual real number in that cell. We're going to do this once again in cell C10 by saying equals, pointing to the 90,000 divided by 140,000, giving us the 6429. Again, that really, if we go to the Home tab, Numbers, Increase, the decimal is a little bit different than that. So it's actually 64.28 and more change. OK, so then we're going to sum this up. If we add this up, it should add up to 100%. If we did that in a calculator, of course, it would be 25 plus 10.71 plus 64.29 100%. If we do that here, we're just going to use the sum function by saying equals SUM, double click that sum function, highlight the 25 to the 64 and enter. There's our 100%. Now we're going to take each of these columns and we're going to multiply it times the actual amount, 110. So we have this nice ratio now it adds up to 100, and we're just going to multiply it times the amount to find each category, land, land improvements and building. So I'm going to put 110 into each area. I'm going to do this with a formula up here. I'm going to say equals, the one above it. We could just type it in there, but I use formulas as much as possible. Equals the one above it. We're not going to sum this up because we're going to this is the total. It wouldn't make sense to sum it up. We're just using it to multiply across. So then when we multiply across, we're going to say that 110 total cost times the 25%. So 25% of the 110. So of course, that would be 110 times 0.25. That gives us our 27,500. We'll do the same thing here in E9. This equals the 10.71% times the 110 and enter. We'll do the same for E10. This equals the 64.29 times the 110 and enter. Now, if we add these up, it should add up to our 110. Once again, so we've got the 27.5 plus the 11.786 plus the 707.14, 110. We'll do that with our sum function. So within E11, we will say equals SUM double click that sum function. Highlight the 27,500 down to the 70,714. That will give us our 110. So what we've done is we've allocated this 110. And we've done so using the appraisal, although the appraisal did not add up to 110, by taking what the appraisal did have, breaking it down to ratios, percentages, and then multiplying them times 110. As long as we have something that adds up to 100%, we can then use those categories to break out the whatever number we have, the 110 in this case, using or by an accordance with that same ratio.