Welcome back. In this section I'll go through into more detail of this model I'll show you how to add rows into the block delete rows if you want to delete it and also add next year sheet, I have already created other blocks of the assets as you can see here. In the previous example, I had walk you through plant and machinery section where we have calculated appreciation in the various scenarios and also calculate profit or loss in case of sale takes place of the asset. In practical scenario, you will have more blocks of asset not only plant and machinery, so I have a leasehold improvement as a new block equipments as a new block furniture and fixtures as a new block and computer peripherals so you can add as many as blocks you want in this model. I already populated some dummy data's for asset purchase date and other details as well.

I also have shown asset sale to check the accuracy of model. Now let me show you how to add more rows in particular block. For example in plant and machinery block after sale number seven. I want To add few additional rules to do that, you have to go to the last non blank cell in that particular model. So, for us it is nothing but b 17. As there is a data validation tape is there where you have to be in the last non blank row.

So, to do that we have to select this particular row, go to this button called add new row and it will ask you how many rows you would like to add in this block of asset table. So, suppose you want to add five rows and click OK. So, as you can see total number of rows are total 12 in this block automatically, the formulas will be copied, you can start inputting new assets, for example, I'll be inputting number eight with five years of useful life we have bought that song for July For $50,000 and you can see the depreciation is calculated from July suppose you are mistakenly added more rows now you want to delete the row or you want to delete existing records you can do that as well. To do that you have to select the rows you want to delete. So like this will be selecting the rows here to click this button called delete rows.

It will give you a warning message like this are you sure want to delete the selected rows once related you will not be able to undo this task. If you're familiar with VBA and macros in Excel, any task or action done by a macro cannot be undone. So be really careful what you do with macros, especially here once you delete the rows you cannot get it back. So this is the warning it shows up and you have to select yes or no if you select No Nothing will happen. It will just pop a simple message and all the selected rows were deleted. You can close this dialog box now but we Want to delete it so let's do it again.

Select the cells for which you want to delete entire row, click on Delete row button and click Yes. And now the rows are deleted. Now to create more blocks of assets just like this, we need to unlock this model for that I'll be providing you unlock password as well. So let's suppose I want to create one more block. And to do that I just have to select any block. For example I'm selecting plant and machinery select an entire row like this copy go down to the location for example you're going to the down and paste now you're to remove the details which already exist in this particular block of asset for example.

Also, you need to copy this Yellow cell to the other cells as well to remove the override off sale of asset which we did in the plant and machinery block. So by default it will be selecting and and if there is any sale of asset you can always override it with a manual entry. Now this new block of asset is ready for your use. And similarly you can add as many as blocks you want now let's try to add next year sheet. Now suppose this is the first time you have used this model for year 2016 your input all your records here. Now as an accountant you know you need to carry forward your balances next year and you want to carry forward your all this workings and depreciation calculation next year.

But you don't want to carry forward the sale values. So this model is smartly designed. I'll show you what exactly will happen to the sale values and the sale of assets and how the balance will behave. So to add a new tab or next year sheet, it will click this button called Add New Year sheet. It will ask to add a new tab name or sheet name. This can be anything you want.

But I would prefer to keep a year's name which would be 2017 here, click OK. So, now, this is most important step here. In next year sheet, you want this cell v3 in next year sheet to be a your format like 2016 1718 you cannot put anything else if you do that this model will not work. So be careful. You can give any name you want for the sheet, but keep this particular input as a year number. So we have to put 2017 again as my year and name of the sheet is same.

But as I told you before, you can keep the sheet name anything you want. Click OK. And new your sheet is added. As you can see when you add a new sheet, the sale value which were here for all the blocks has been deleted completely. The same time for the assets which were sold in previous year are highlighted in yellow with the same dates. So you can keep the track of asset which were sold in previous years as well. But as I told you before, in order for this model to work accurately, you need to remove this sale values if there is no sale has taken place, but since the sale has taken place in periods what not this year, the dates would be here, but the values will be gone.

Also the values in cross block is completely gone, as well as accumulate depreciation and add book value. But if you want to refer what actually happened, you must go to that previous year like this. Since this is the current year's data, we want to keep the profit or loss on sale of assets for the current year. That's the reason I'm deleting the sale values for the previous year. I've also tried to input some data validation checks. For example, you cannot keep same sheet name.

Like if I want to add another 2017 here by clicking this add next year sheet Suppose I put 2017 again or any sheet name which already exists, it will not allow as is already accessed here. One of the most important thing when you carry forward your thesis or data to next year is to bring closing balance of previous years to opening balance. So, for example, closing balance of PNM one here in row 11 is 50 to 145 it should be opening balance, but the balances are same. So, it won't matter but for example, this particular asset PNM two which was sold in the year, so, their opening balance should be nil. Similarly, if there is a sale again opening balance should be needed in the block of accumulated depreciation scenario is same. So, this particular balance column should be carried forward as opening balance.

So, this model takes care of that as well by using a UDF user defined function, which is here you can see previous your sheet the quarter feature will be available in your VBA module for this particular sheet. Now, I will show you this particular feature Line action and how these balances are carried forward in next year by adding a new year sheet. As you can see, in your sheet, all the balances are carried forward it for the asset returns sold, there is no carry for balance. For example, PNM 264212, which had a closing balance of zero since it was sold, there is no opening balance in 2017. Let's check accumulated depreciation which is most important. So, in 2016, we had a closing balance of 42 to six zero for PNM, one in row 11 which is the opening balance for asset which were sold it should have an opening balance of zero.

So PNM two was sold. So accumulate balance as on December 2016, or year in 2016, was zero should be zero as you can see here in row 12 Same way, in gross cost of asset all the balances are carried forward correctly you can verify it. You can also quickly verify by taking the closing balance of totals like 160708 for plant and machinery should be opening balance 160708 You can also put a small validation here when making the other cell. So, for second block the closing balance of accumulate depreciation was 39 885 an opening balance is 3985. So, this is a fantastic model to calculate your depreciation and work out profit or loss on sale of assets also it calculates in month accurate depreciation. For example, if you're buying assets in May of the month You're selling the asset in middle of the month, it takes care of depreciation accordingly in the month block as you can see here.

As I mentioned before, you will get the free copy of this template with the unlock password. Also, I'll be providing you unlock password for the VBA module as well. But I highly suggest if you're giving this model to your other users, or your colleagues or your staff make sure you lock this template with the proper instruction. Thank you very much guys for watching. If you have any question, please feel free to post them in the question and answer section. I'll be happy to reply that