I appreciate the informative video. Currently, I'm experiencing problems with the index formula as it doesn't capture the total amount for a specific asset class, eg for furniture and fittings. The index formulae seems to be missing some asset values when calculating the gross black for the same asset asset class on different rows
I am very sorry for late response. I may need to look the spreadsheet with you using not sensitive data. otogca@gmail.com. However, the index function returns a value and not the sum of values. The sumifs function should do the job if I understood what you have stated. Thank you for the compliments
Just ensure that full date is entered for the date column and the model will respond accordingly. Please, if still not clear, kindly restate your question. Thank you for watching
Thank you for this. Which formula will be used to add up multiple items in the same class in the summary table? and how would you treat Gains/losses on Disposal? For Example, if I have 3 Motor Vehicles in the Fixed Asset Register and want to show their summary as a total value, total Acc.depn and total net book value since they are in the same class.
Thanks for watching. Let me get you clear. If I buy the assets at the Middle of the year, do you want it to assume a full year depreciation at the end of the purchase year?
Its good, simple and logical. Only focuses on straight line depreciation and also left out the formula to remove the disposed asset from the "gross block". As it is it will still be overstating that total.
Thank you sir for your kind views. I think a sumifs function can be used to get the gross block. Also, depending on the depreciation method, the model can be customized. Trying to model all methods in one model will make for complex formulas which is possible but Wii be too convoluted to manage. Thanks. I appreciate your view and look forward to making a model with other methods.
Thanks Charles this is brilliant. Please help should I want my accumulated depreciation on 01 Jun 20 to include the accumulated depreciation of the asset disposed on 31 Mar 20, how do I go about it.
Happy to know you like it. If I get your question clearly, you just need to enter 31-Mach-2020 as the disposal date in the row of that asset and your report date will be entered as 01-Jun-2020. The formulas should take care of the rest.
Is a good practical PPE register. I tried it but released that the depreciation per day is not zero when the asset reached its useful life. Therefore push the lapse days more than the uesful life days abd the net effect is in negative
Check your cell that have the start and end date. Make sure the date reads month number/day number/year number. Or dd-mmm-yyyy format. Hopefully, this will help
The model is built based on useful life span with straight line depreciation. An asset could have a 10 year useful life. This will mean it depreciates by 10% annually. At 20% it will mean a 5 year useful life.
Check the condition in your sumif formula. Make sure it matches. Also make sure that the sum and criteria range is completely referenced. If you are using multiple conditions, use sumifs function.
Just like in monthly reports, the report date is the last day of a report month. However, the report date can be any date from which the report need to be evaluated
Great tutorial!
Speaking about the disposal, what about if I want to dispose a part of an asset amount not an entire asset?
Thank you so much Charles. This is quite in details covering all possible scenarios and easy to understand/work with.
Happy to know it helps. Thanks
Thank you for this very insightful tutorial. Please can you help on how to build the 'List sources' on the second tap?
I appreciate the informative video. Currently, I'm experiencing problems with the index formula as it doesn't capture the total amount for a specific asset class, eg for furniture and fittings. The index formulae seems to be missing some asset values when calculating the gross black for the same asset asset class on different rows
I am very sorry for late response. I may need to look the spreadsheet with you using not sensitive data. otogca@gmail.com.
However, the index function returns a value and not the sum of values. The sumifs function should do the job if I understood what you have stated. Thank you for the compliments
@@high5tutorials135
Thank you for reaching out. It has been sorted already
Thank you for this tutorial, I hope if make some development wit use SLN Deprecation method
VERY HELPFUL AND easy to apply
Thank you Sir
Thank you for this. How do you treat additions made within the year and disposal
Just ensure that full date is entered for the date column and the model will respond accordingly. Please, if still not clear, kindly restate your question. Thank you for watching
Thank you for this. Which formula will be used to add up multiple items in the same class in the summary table? and how would you treat Gains/losses on Disposal?
For Example, if I have 3 Motor Vehicles in the Fixed Asset Register and want to show their summary as a total value, total Acc.depn and total net book value since they are in the same class.
Thanks for watching. You will need a column for Assets Class. E.g, "Vehicle". Then, use the SUMIFS function to sum if it is vehicle.
Thanks for the insightful tutorial, Sir. Can the formula be adjusted to depreciate for a full year irrespective of the purchase date of the asset?
Thanks for watching. Let me get you clear. If I buy the assets at the Middle of the year, do you want it to assume a full year depreciation at the end of the purchase year?
Its good, simple and logical. Only focuses on straight line depreciation and also left out the formula to remove the disposed asset from the "gross block". As it is it will still be overstating that total.
Thank you sir for your kind views. I think a sumifs function can be used to get the gross block. Also, depending on the depreciation method, the model can be customized. Trying to model all methods in one model will make for complex formulas which is possible but Wii be too convoluted to manage. Thanks. I appreciate your view and look forward to making a model with other methods.
This is really helping, Thanks really for this.
Pls can you forward me the link to get this document??
Hi - Trying to do the Gross Block - the formula only calculates the first row of figures and not the 10 ( I have 11 rows of assets) any ideas ??
Use Sumifs to calculate the gross block
Golden, thanks!
What is the Report Date and how do I know about the Report Date
This indeed is very helpful
Happy to know. Thank you
Thanks Charles this is brilliant. Please help should I want my accumulated depreciation on 01 Jun 20 to include the accumulated depreciation of the asset disposed on 31 Mar 20, how do I go about it.
Happy to know you like it. If I get your question clearly, you just need to enter 31-Mach-2020 as the disposal date in the row of that asset and your report date will be entered as 01-Jun-2020. The formulas should take care of the rest.
this is very helpful
Thank you
Is a good practical PPE register. I tried it but released that the depreciation per day is not zero when the asset reached its useful life. Therefore push the lapse days more than the uesful life days abd the net effect is in negative
Could it be because of the salvage value?
To get the end date I can't get the answer,is showing #Value
Check your cell that have the start and end date. Make sure the date reads month number/day number/year number. Or dd-mmm-yyyy format. Hopefully, this will help
WHAT IS THE DEPRECIATION PERCENTAGEES FOR EACH CATEGORIES PLEASE
The model is built based on useful life span with straight line depreciation. An asset could have a 10 year useful life. This will mean it depreciates by 10% annually. At 20% it will mean a 5 year useful life.
Merci beaucoup pour la vidéo. Please can you send me the file?
please text sumif formula apply in gross block
Check the condition within your sumif function. If you have multiple conditions, use sumifs.
Check the condition in your sumif formula. Make sure it matches. Also make sure that the sum and criteria range is completely referenced.
If you are using multiple conditions, use sumifs function.
What is the Report Date and how do I know about the Report Date
Just like in monthly reports, the report date is the last day of a report month. However, the report date can be any date from which the report need to be evaluated